Re: Performance Issue

"IANAL_VISTA" <IANAL_Vista_at_hotmail.com> wrote in message news:Xns967A6BDF66647SunnySD_at_68.6.19.6...
> "Oxnard" <shankeypNO_SPAM_at_comcast.net> wrote in
> news:zvSdnb55fOvhNCjfRVn-tw_at_comcast.com:
>
>> 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.
>
> enable EVENT 10046 on them, too
>
>>
>> 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?
>
> enable EVENT 10046 on them, too
>
>
>>
>> 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
>>
>
> process the trace file with TKPROF explain=user/password to see the SQL
> which produced the trace file & its EXPLAIN PLAN.

I do agree with you that this would work, however I am hoping to get something a bit more real time. Received on Sun Jun 19 2005 - 13:44:41 CDT