--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Daniel Roy" <danielroy10junk_at_hotmail.com> wrote in message
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:
>
At least this part of the sql statement looks, um interesting, to say the
least
select
<somestuff>
FROM
SIEBEL.S_CONTACT T7,
SIEBEL.S_CONTACT T13,
SIEBEL.S_CONTACT T14,
SIEBEL.S_CONTACT T19,
SIEBEL.S_CONTACT T20,
SIEBEL.S_CONTACT T24,
<some other tables snipped>
WHERE
<some stuff including repeating conditions all outer joins>
I can't believe anyone wrote this stuff, it must be from an application
generator or end-user ad-hoc query? Either way i'd be looking for ways to
change the sql statement, this probably means working in new and interesting
ways with the application. I don't know enough about siebel, but would hope
that the datamodel or query tool is amenable to defining queries rather
better.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Received on Sat Nov 08 2003 - 08:57:54 CST
