Performance Issue

AIX 5.1
Oracle 9.2.0.5 RAC configuration
EMC SAN Ocationally there are performance issue with the db. Some jobs become very slow. The job are data loading jobs. I recevied a call from an developer the other day saying a job was running very slowly. I figured out which node and the session of the connection running the job. I then set an event 10046 level 12. As it turned out the vast majority of the time ~ 86% was spent like this:

 WAIT #10: nam='db file sequential read' ela= 365 p1=103 p2=12648 p3=1

WAIT #10: nam='db file sequential read' ela= 414 p1=103 p2=12649 p3=1
WAIT #10: nam='db file sequential read' ela= 364 p1=103 p2=12650 p3=1
WAIT #10: nam='db file sequential read' ela= 419 p1=103 p2=12651 p3=1
WAIT #10: nam='db file sequential read' ela= 364 p1=103 p2=12652 p3=1
WAIT #10: nam='db file sequential read' ela= 423 p1=103 p2=12653 p3=1
WAIT #10: nam='db file sequential read' ela= 372 p1=103 p2=12654 p3=1
WAIT #10: nam='db file sequential read' ela= 431 p1=103 p2=12655 p3=1
WAIT #10: nam='db file sequential read' ela= 369 p1=103 p2=12656 p3=1
WAIT #10: nam='db file sequential read' ela= 428 p1=103 p2=12657 p3=1
WAIT #10: nam='db file sequential read' ela= 357 p1=103 p2=12658 p3=1
WAIT #10: nam='db file sequential read' ela= 422 p1=103 p2=12659 p3=1
WAIT #10: nam='db file sequential read' ela= 365 p1=103 p2=12660 p3=1
WAIT #10: nam='db file sequential read' ela= 427 p1=103 p2=12661 p3=1
WAIT #10: nam='db file sequential read' ela= 364 p1=103 p2=12662 p3=1
WAIT #10: nam='db file sequential read' ela= 428 p1=103 p2=12663 p3=1
WAIT #10: nam='db file sequential read' ela= 369 p1=103 p2=12664 p3=1
WAIT #10: nam='db file sequential read' ela= 423 p1=103 p2=12665 p3=1
WAIT #10: nam='db file sequential read' ela= 370 p1=103 p2=12666 p3=1
WAIT #10: nam='db file sequential read' ela= 420 p1=103 p2=12667 p3=1

using P1 I found the tablespace using P2 I got the table. I donot have a tool to look through all 100M + of the trace file to say the SQL was accessing more than one table, but it sure looked that Oracle was looking at each and every block of the table. I did not see any locks

I do not know the SQL which caused Oracle to want to visit each block as the trace was started after the SQL. I assume if and update was done to one + colmun for all rows one may expect to see something like this. But I have been assured that was not the case, and of course, it has worked fine before and since.

So I will assume the person is not BS'ing me which leads me to my question:

How can find out what other SQL's/Procedures ... which are accessing the table? We do use Cognos as a reporting tool. So it is only selects which are occuring. For all I know there were many reports being run against this table thus taking up much of the time.

Next, because this is AIX and RAC we are using Raw Devices. The Raw Devices are logical volumes, therefore I really do not know how to tell if other(s) were accessing other tables in the same tablespace thus slowing down this job. How can I quickly tell if other sessions are wanting to get at other tables in the same tablespace?

Next, because a logical volume is made up of at least part of one or more disks. It seems like Oracle 'looses vision' on what is really happening on the disk level. How can I figure this out quickly in order to say something like, There are five other reporting jobs accessing the same disk your data loading job is accessing that is why your job is slow. To fix we will need to move the table to a different tablespace?

Thanks   Received on Sun Jun 19 2005 - 12:20:57 CDT