Re: Performance Issue

"IANAL_VISTA" <IANAL_Vista_at_hotmail.com> wrote in message news:Xns967A85A0EA572SunnySD_at_68.6.19.6...
> "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.
>

Very cold dude. Received on Mon Jun 20 2005 - 08:21:30 CDT