Re: Performance Issue

"Oxnard" <shankeypNO_SPAM_at_comcast.net> wrote:
> 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

Was it all WAITs on #10, are were these interspersed with FETCHs and EXECs an #10?

What is a typical ela for sequential reads when when the system is not being slow?

>
> 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,

It should be pretty simple to automate what you did in the first place, using Perl.

> 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.

It is likely that this SQL will stick out like a sore thumb in the disk_reads column from v$sqlarea (or maybe disk_reads/executions will stick out)

> 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?

Why do you care? It doesn't seem like there was any evidence of table-specific contention going on.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB