Oracle application performance problem

[alberthsu_1]

We are running a process accessing Oracle 7.3.4. We found the process takes a lot of time to complete the task. During the process running, we use GlancePlus to monitor the process. The following are the results of the whole system:

CPU Utilization: about 50%
DISK Utilization: about 50%
Mem Utilization: about 35%
Swap Utilization: about 11%

Our process consumes about only 10% of CPU and always blocks on "SOCKT". The oracle process often blocks on "SEM" and "PRI".

What would be the possible bottleneck for running the process? Any suggestion to make the process run faster?

[Steve Slade]

What does the process do? Does it only access Oracle or does it do something with the result? If it accesses Oracle is it accessing a table(s) - is it/are they a large table? We need more information to work out what is actually going on.

[alberthsu_1]

Since I cannot reach the original writer of the program, so I can only "guess" what the process do.

The program will check records in table A which contains about 32000 records. This will result about 4000 records from table A. For each result from table A, it will check records in table B, which has about 700,000 records, for records related to the record from table A. Then the records in both A and B will be exported to a text file. In our case, we will get 160000 records from table B to text file.

[Steve Slade]

Sounds like the best thing you can do is run an sql-trace and/or some stats whilst the program is running. Depending on how it takes to run.

To run the sql trace, you will need to find the session_id and sid of the program. To do this you need to logon to the databsae with access to the v$views - system/sys unless specifically setup.
1. Query the view v$session and obtain the sid and serial# for the program - could use username field.

2. Issue the command below to turn on timed_statistics

alter database set timed_statistics=true;

3. Issue the command below to turn on tracing for program (using values from 1.):

execute sys.dbms_system.set_sql_trace_in_session(session_id, serial#, TRUE);

4. Allow process to run a while. Then turn off tracing and timed_statistics:

execute sys.dbms_system.set_sql_trace_in_session(session_id, serial#, FALSE);

Alter database set timed_statistics=FALSE;

5. Step 4 will create a trace file in the location defined as the user dump directory. Use the utility tkprof to then analyse the trace file to find out what sql statements are taking the time.

If you are using utlbstat/estat - this will check overall database performance. As DBA

1. Turn on timed stats as above.
2. Run script ?/rdbms/admin/utlbstat
3. Wait for a while.
4. Run script ?/rdbms/admin/utlestat. This will produce a file called report.txt
5. Turn off timed stats as above.

The report will highlight contentions, etc in the database.

Good Luck!!
(I have probably forgotten some steps so I appologise now)

[Dragan Krnic]

Slow hardware probably but there is a chance that your application unnecessarily accesses oracle on the same host through TNS instead of via IPC. I found out that for some reason TNS on the same host is 2-3 times slower than TNS from a remote host and about 6-7 times slower than using just the plain ORACLE_SID instead of TWO_TASK/TNS_ADMIN.

That would explain massive waits on sockets.