Re: Help interpreting TKPROF output

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in news:3722db.0311071254.74f91dfe_at_posting.google.com:

> Hi everyone,
> I'm trying to figure out what might have slowed down considerably
> a specific query these past days. I asked for a TKPROF output for only
> this statement, and the relevant sections of it are displayed at the
> bottom of this message. First some background: They use Oracle 8.1.7,
> and RBO (this is enforced by the vendor, Siebel, who digests "new"
> database features like cost-based optimizing VERY slowly). I'm told
> that this query used to run in about 20 seconds, and it takes now 11
> minutes!! I asked if anything to the SQL generated by Siebel got
> modified, and I was told no. (for you Sybrand:)I went through the
> Performance Tuning manual, in order to get some tips on how to
> interpret TKPROF output, but I'm afraid that's not enough. I don't see
> anything wrong with the execution plan. Here is the TKPROF output:
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.34 0.53 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 366.73 659.36 140803 28921647 15
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 367.07 659.89 140803 28921647 15
> 0
>

Wow, almost 30 million logical reads to retrieve nothing. On the other hand, look at your BCHR: 99.5% Most people would give their firstborn for such an efficient buffer cache. What makes you think there is a problem?

Seriously, what changed? Something must have changed to bring about such a drastic drop in performance. What was the execution plan when it ran in 20 seconds?

I'm not saying it has anything to do with the problem, but why is the outer join S_EVT_ACT.TARGET_PER_ID = S_CONTACT.PAR_ROW_ID (+) repeated 3 times? Twice using alias T20 for S_CONTACT and once using alias T14.

-- 
What lies behind us and what lies before us are small matters when 
compared to what lies within us.

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP