Configuring Shared Server

We have recently configured shared server in some of our databases. Steps to follow to enable shared server are below.

The following configuration is on Enterprise Edition 9.2.0.5. The numbers used in the settings shown are only examples. We are running an OLTP system with about 2000 concurrent users with these parameters without any problem.

The parameters you need to set for the shared server and their default values are:

NAME                           VALUE
------------------------------ ----------
circuits                       0
dispatchers
max_dispatchers                5
max_shared_servers             20
mts_circuits                   0
mts_dispatchers
mts_max_dispatchers            5
shared_server_sessions         0
shared_servers                 0

The ones starting with mts are for backward compatibility, setting circuits, dispatchers and max_dispatchers or mts_circuits, mts_dispatchers and mts_max_dispatchers is the same and is up to you.

If we go over these parameters one by one:

Circuits: This is a static parameter and it defaults to the sessions parameter when the shared server is used. You can leave it to the defalt value if your users are not using database links extensively. Because every session connected through the shared server adds to the circuits count for each distinct database link it opens. In our case we had sessions=3500 and circuits=3500 derived from that sessions parameter. But each user queried from two or three database links, so when the session count was about 1500 we exhausted circuits and began to get “error 18 creating virtual circuit” errors. So, you may need to set this parameter explicitly to a higher value if you are using database links.

Dispatchers: This parameter is said to be dynamic in Database Reference but if you have it as null in your database like the above settings you can not set it dynamically.

SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)';
alter system set dispatchers='(protocol=tcp)(dispatchers=2)'
*
ERROR at line 1:
ORA-00105: dispatching mechanism not configured for network protocol
(ADDRESS=(PARTIAL=YES)(PROTOCOL=tcp))

You can not even set it with scope=spfile.

SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile;
alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile
                                                             *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

If you have it null on your system, the only way to set this is to create a pfile from the spfile and put it in there and then shutdown the database, create a new spfile and startup.

Shared_servers: This is a dynamic parameter and defaults to 1 when shared server is used.

Max_shared_servers: This is a static parameter and defaults to 20 or 2*shared_servers.

The steps we followed were:

SQL> alter system set circuits = 10000 scope=spfile;

System altered.

SQL> alter system set max_shared_servers=200 scope=spfile;

System altered.

SQL> alter system set shared_servers=50 scope=spfile;

System altered.

SQL> create pfile from spfile;                                  

File created.

We have inserted the following line to the pfile created.

dispatchers='(protocol=tcp)(dispatchers=5)'

Then shutdown, create spfile from pfile and startup the instance.

TARs

I wonder if Oracle is aware of the users’ feelings about the quality of support for TARs. I am doing everything to save myself from opening a TAR and dealing with it. I open a TAR, Support requests some logs and traces, i supply them. 2 weeks pass, i update the TAR asking for a response, they ask for more traces. 2 more weeks pass, again i ask for a response. Then look at what Support says:
“We have noticed that the logs you sent are missing some lines, can you upload all the logs available?”
This is where words end. Every TAR becomes a new challange to deal with. I do not know what Oracle is thinking about the quality of support.

Magic Exceptions Into

One of our developers sent me a procedure code that removes duplicate records from a table. The code was something like this:

Cursor c1 is select * from tab1;
Open c1;
Loop
Fetch c1 into r1;
delete from tab1
where col1=r1.col1
and rownum=1;
commit;
end loop;

This was supposed to delete about 600M rows. Surely this is not the way to go for removing duplicate records from a table.

There are more than one way to remove duplicate records. Here is a simplified test case.

Create table t_dups as select * from all_objects;
insert into t_dups as select * from t_dups;

DELETE FROM t_dups a
WHERE ROWID<>(SELECT MAX(ROWID)
FROM t_dups b
WHERE a.object_id=b.object_id);

This does the work in a reasonable time if we have an index on object_id. But in this case we did not have an index on the related column. Without an index on object_id, here are the stats:

DELETE FROM t_dups a
WHERE ROWID<>(SELECT MAX(ROWID) FROM t_dups b WHERE a.object_id=b.object_id)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.06          0          0          0           0
Execute      1    342.16     335.17          0    2306810       7113        6396
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    342.17     335.23          0    2306810       7113        6396

Instead of creating an index on a big table just for the purpose of removing duplicates, we can use the exceptions into clause. The EXCEPTIONS table can be created using $ORACLE_HOME/rdbms/admin/utlexcpt.sql.

alter table t_dups add constraint cons01 unique(object_id) exceptions into exceptions;
*
ERROR at line 1:
ORA-02299: cannot validate (CONS01) - duplicate keys found

This puts the duplicate rows into the EXCEPTIONS table. The thing to be careful about is that it puts rowids of all the duplicates including the rows we want to keep. So, if you have two object_id’s that are the same, both are in EXCEPTIONS not just one of them.

As we have the rowids of the duplicate rows, we can remove one of each duplicate by using the following sql:

delete from t_dups
where rowid in (select minrid from (SELECT object_id,MIN(e.row_id) minrid
FROM EXCEPTIONS e,t_dups t
WHERE t.ROWID=e.row_id
GROUP BY object_id)
)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.10          0          1          0           0
Execute      1      2.27       2.27          0      19429       7247        6396
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.30       2.38          0      19430       7247        6396

2.38 seconds compared to 335.23 seconds! So, using the exceptions into clause for removing duplicates is a good alternative when we do not have an index on the related columns.

First Post and Blogger Word Add-in

I have been thinking about starting a blog about my day-to-day Oracle work. I have been trying to make my mind clear about what i would be posting. Hard thing is to begin, continuing posting will be easy i hope.

I will try to publish anything i find interesting mostly related to the Oracle database and rarely non Oracle related stuff. Any corrections to my English are welcome. So, here is one more blog added to the Oracle blogosphere.

Last week Google has provided a free Word add-in to publish blog posts. This post is written using that.