Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance Issue

Re: Performance Issue

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Sun, 19 Jun 2005 17:36:15 GMT
Message-ID: <Xns967A6BDF66647SunnySD@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. Received on Sun Jun 19 2005 - 12:36:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US