"Oxnard" <shankeypNO_SPAM_at_comcast.net> wrote in
news:KtGdnT-vysOBICjfRVn-vQ_at_comcast.com:
>
> "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.
>
>
Then, keep hoping & I wish you success.
Received on Sun Jun 19 2005 - 15:08:10 CDT