How do you explain Explain Plan. [message #370824] |
Fri, 11 February 2000 12:00 |
kat
Messages: 15 Registered: February 2000
|
Junior Member |
|
|
Can someone tell me how to read the result in the Plan_table after doing an Explain plan? I have a serious perf problem and dont know what's wrong with my query. I have run the Explain plan but have no idea what to do with the result. If there is a site I can go or if someone can tell me what each column/value in the column means, please advice!! Thanks in advance.
|
|
|
|
|
Re: How do you explain Explain Plan. [message #370828 is a reply to message #370824] |
Fri, 11 February 2000 15:10 |
Jeff Thomas
Messages: 3 Registered: February 2000
|
Junior Member |
|
|
Received a message from orafaq, to reply here.
The v7 item is A25421.pdf, which should be on your distribution media. You can probably get it from meta-link if the cd's walked. For explain plan, there isn't a lot of difference between v7 and v8. A (perhaps better) option is aftermarket documentation; I recommend to you "Oracle SQL*Plus, The Definitive Guide", by J Gennick, from O'Reilly publishing. You'll find it at bookpool.com for less.
How badly is this query doing??
Jeff
|
|
|
|
Re: How do you explain Explain Plan. [message #370832 is a reply to message #370824] |
Mon, 14 February 2000 10:28 |
Jeff Thomas
Messages: 3 Registered: February 2000
|
Junior Member |
|
|
Not sure now who I should reply to, maybe you should send your email address to correspond directly.
So you have a remote link with joins? These can be slow, and are a tuning problem - performance is gonna stink.
Get explain plan working. In using the plan, make sure that you hit the remote table LAST. Avoid having nested loops involving the remote object. Make sure that your query on the remote object uses an efficient index.
Two coding strategies: If you can, in your code put your qualifying records from the local tables into a table or plsql table, then go against the remote table. Or, if you need only a few rows from the remote table, query and insert them into a local "temp" table and use this in your joins instead.
Let me know how it goes,
Jeff
|
|
|
Re: How do you explain Explain Plan. [message #372388 is a reply to message #370824] |
Wed, 07 February 2001 16:31 |
Andrew
Messages: 144 Registered: March 1999
|
Senior Member |
|
|
Ditto. I just ran into this with a project I am working on -- performance is extremely poor. Even using HINTS doesn't affect performance well enough to show a distinquishable difference. Jeff, I like your two coding strategies, thanks.
Andrew
|
|
|