Performance problem in disk IO

[Sajith V Mannadiar]

Hello there,

We have an Oracle query that runs very slow between 02:30 and 04:30AM

Glance shows a very high, almost 98%, PROC_OTHER_IO_WAIT_PCT for the corresponding oracle process. Database is on raw disks.

The PROC_IPC_SUBSYSTEM_WAIT_PCT for this process drops to 0 during this period.

The oracle process is in this state for about 5 minutes and we could see the SQL query just freezes at this time.

We have requested our DBAs to help. In the mean while, it would help if you could share your thoughts on this.

Could this indicate an IO bottle neck or some application issue where the table is locked or something like that?

Thanks,
Sajith

[Steven E. Protter]

Shalom Sajith,

These problems are almost all caused and solved by the dba sql code review process. The dba's must be required to review the code and make it more efficient.

You can provide evidence of the issue to the dba's with the following sar scrips, which collect performance data in the background.
http://www.hpux.ws/system.perf.sh

They will show a serious i/o wait problem on the logical volumes that contain the database tables involved in the query.

SEP

[B. Hulst]

Hi,

Usually a case of a database resource locked by another process doing an SQL statement.

Check to see what other process is talking to the database at that same time...

Regards,
Bob

[Sajith V Mannadiar]

SEP and Bob,

Thanks for your inputs.

The strange thing is the SQL query seems to freeze only at specific times (between 02:30 and 04:30 hrs) and today for some reason it happened at 06:40

Measureware stats indicate that the corresponding oracle process was waiting on OTHER_IO (98%) but there is no significant queue on disks (it is around 0.2)

Could you tell me, if there is an issue with the SQL query or if the table is locked due to another process, would measureware stats show as waiting for OTHER IO?

Please help

Thanks,
Sajith

[Julio Yamawaki]

Hi,

You probably have more than one heavy query in this time.
Find this query by searching v$sqlarea, for queries with more than, say, 100000 disk reads.
After this, put this queries on a query analyzer (can be sqlplus) and find what is going on (full table scan, index missing, etc.).

Regards,

[Sajith V Mannadiar]

Hello there,

Could someone tell me if there is any relation between oracle processes (oracle
We use KEON (BOKS) for additional security on unix authentication. At present, we have intermittent issues with KEON. It seems that the oracle performance issue and the KEON issue always coincide, though it doesn't make any sense to me.

Please advise..

Thanks,
Sajith

[Steven E. Protter]

Sounds like you have done some trend analysis yourself. If the problems coincide, it may not be bad sql code. I'd still institute code review on SQL code. It's the only way to keep killer queries from bringing production to its knees.

I've never heard of your authentication environment. The OS works with a number of good authentication protocols for security.

Trusted System: No problems with this an oracle
Kerberos: No problems with oracle this and oracle.

SEP

[Sajith V Mannadiar]

SEP,

Thanks for the note.

My question is, if there is a delay in OS authentication would it impact the performance of Oracle processes. I am referring to the oracle processes that is named as oracle

We are facing issues in our unix environment where there is a significant delay, ocassionally, in authenticating any user.

Is there any link at all between these two? Oracle processes and delay in unix authentication..

Thanks
Sajith

[Alzhy]

Probable you are doing Oracle backups at those times. Whichmeans the database may probably be in "HotBackup" mode or in a suspended state.

Otherwise.. use Glance.. look at what lvols/vols/filesystems are busy and correlate with possibilities -- one of which is mentioned above.

You're a SysAdmin right? If I were you.. better start learnng the basics of your Database payload.. at least know how to check what it is doing..

[TwoProc]

Since many other things have been discussed, I'll throw in one more to consider that I've seen at times cause what you're seeing.

Statistics in two cases: case one: a gather stats is being run during your batch, and case two: a gather stats needs to be run.

Are you perhaps running a "gather stats" during those times along with the query?
Keep in mind that gathering stats on a table while it is in a state of heavy update usually causes massive slowdowns, AND not very good statistics when the processes are over.

Does/Are the table(s) involved experience a high amount of change like a full replacement of all rows during a nightly batch, or possibly a high rate of change on important indexed keys?

If so, it could be that your batches need to include an "estimate statistics" command at the point at which the data in a highly turbulent table is highly stable, and before being used to join to other regularly stable tables. It could also be that histograms need to be refreshed while statistics are being gathered on columns.