Home » SQL & PL/SQL » SQL & PL/SQL » joining 3 tables - how Oracle decides about execution plan (oracle 10)
joining 3 tables - how Oracle decides about execution plan [message #438369] Fri, 08 January 2010 07:11 Go to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Sorry if you find my question too "unclear" to be answered but I will try to be as specific as possible.

I have a normal SQL query, targeting 3 tables.
One of the tables is a table used for storing some logging information and we have one such table for every month.

I.e.:
table_x
table_y
table_01_2009

I needed to run my query for every month of the year, i.e. something like:
/.../ select...
       from table_x
           ,table_y
           ,table_01_2009
      where table_x.external_id = table_y.external_id
        and table_x.network = table_01_2009.network
        and table_y.country = table_01_2009.state;


...and then 11 same queries, only replacing "table_01_2009" for "table_02_2009", "table_03_2009" etc.

All "table_01_2009"-"table_12_2009" tables are basically the same: similar number of rows, identical structure, identical indexes, same "last analysed" date and so on. Even explain plans of all 12 queries are more or less the same (cost, bytes, CPU cost).


And now my problem: some of these 12 queries ran quickly (< 1 min), some of them were disasted (> 3 hours).

In explain plan I found out, that Oracle sometimes makes joining of my 3 tables this way:
1. joining table_x and table_01_2009
2. result of this join is joined with table_y

..and sometimes this way
1. joining table_x and table_y
2. result of this join is joined with table_01_2009


It's absolutely correct that second alternative of joining is disasterous. Because of my data distribution and absence of indexes on key columns it is correct that this query is unusable. (That's OK, I could easily fix it with "LEADING" hint.)

But why Oracle sometimes chooses one way of joining and sometimes the other?
I actually ran first query with January, it ran OK, and when I saw all other queries have basically the same explain plans (cost, bytes, CPU cost), I launched also the other ones without worries - but! - half of them never finished...

And my questions are:
1. Could I have predicted such behaviour? Is it normal that cost, bytes, CPU cost in explain plan are same, but performance is fatally different?
2. Is there any data dictionary table where I could see, what could be Oracle's reasons for choosing first way of joining or the second? Is there any way understand closer how Oracle decides when constructing this "joining sequence" in general?

Thanks for any answers,
jan
Re: joining 3 tables - how Oracle decides about execution plan [message #438372 is a reply to message #438369] Fri, 08 January 2010 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your statistics up to date and then Oracle will most likely find the best plan.
Make it calculate histograms on the join conditions.

Regards
Michel
Re: joining 3 tables - how Oracle decides about execution plan [message #438418 is a reply to message #438372] Fri, 08 January 2010 16:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you provided filtering predicates, Oracle would try scanning the table whose filter predicates would filter the most rows from the result set.

In the absence of filter predicates (you have only join predicates), it doesn't really matter. With very large tables, Oracle will consider the usage of temporary space and memory.

If you have joins with non-matching rows, you can get funny results. <What follows is my opinion - not proven fact...> The CBO assumes that every row is "joinable". If that is not the case, it can be more efficient to join a particular pair of tables early because you get the benefit of filtering. The CBO does not anticipate this, and will not consider it in the plan.

Ross Leishman
Re: joining 3 tables - how Oracle decides about execution plan [message #438565 is a reply to message #438369] Sun, 10 January 2010 23:35 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
And my questions are:
1. Could I have predicted such behaviour? Is it normal that cost, bytes, CPU cost in explain plan are same, but performance is fatally different?
2. Is there any data dictionary table where I could see, what could be Oracle's reasons for choosing first way of joining or the second? Is there any way understand closer how Oracle decides when constructing this "joining sequence" in general?



Hmm..The point is from where you are watching the execution plan..is it
1>set autotrace
2>EXPLAIN PLAN
3>Any Oracle tool
4>in TKPROF

In SQL*Plus, you can automatically obtain the execution plan and some additional statistics about the running of a SQL command by using the AUTOTRACE setting. Unlike the EXPLAIN PLAN command, the statement is actually run. However, you can choose to suppress statement execution by specifying AUTOTRACE TRACEONLY EXPLAIN.AUTOTRACE is an excellent diagnostic tool for SQL statement tuning. Because it is purely declarative, it is easier to use than EXPLAIN PLAN.
Don't Trust the explain plans generated by Oracle tools for sure.you can google why??
TKPROF plans are the most reliable plans and it actually tells what path has been followed.But please don't analyze the tables between the time of taking trace and using TKPROF to explain trace.

Regards
Ayush
Re: joining 3 tables - how Oracle decides about execution plan [message #438693 is a reply to message #438565] Mon, 11 January 2010 20:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
ayush_anand wrote on Mon, 11 January 2010 16:35
But please don't analyze the tables between the time of taking trace and using TKPROF to explain trace.


Not really a problem since (I think) v9.1, as the steps of the plan are now stored in the trace. The EXPLAIN= option to TKProf now just confuses the issue, because it causes the output to show both the actual plan used and the plan as it would be if run now.

But yes, I agree that TKProf is the most reliable. Equally reliable is dbms_xplan.display_cursor(), which will display the actual plan used by previously parsed statement, provided it is still cached.

Ross Leishman
Re: joining 3 tables - how Oracle decides about execution plan [message #438717 is a reply to message #438693] Mon, 11 January 2010 23:35 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Excerpt from Expert one on one

Note I prefer to never use the EXPLAIN= however and would recommend the same for you.
The reason is that the explain plan query may differ radically from the actual query used at run‐time. The only plan that can be trusted is the plan saved in the trace file itself.

Unless you knew that I analyzed the table after you executed your query, but before you ran TKPROF, you would be unable to explain this disparity. After I analyzed the table, the default plan for that query changed dramatically.
TKPROF just uses the explain plan facility in Oracle. This will return the query plan that would be used at this point in time; not the plan that was actually used. Many features can impact the plan visible in the trace file versus the plan returned by explain plan. For example, the application could have been used stored query outlines The query plan would have been based on a stored outline at run‐time whereas the query plan returned by explain plan would be some other plan. In general, if you do use the EXPLAIN= parameter to TKPROF, you must verify that the two plans agree with each other step by step.
And I dont think it has changed for 10g documentation

http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#PFGRF010

Execution Plan in TKPROF
If you specify the EXPLAIN parameter on the TKPROF statement line, then TKPROF uses the EXPLAIN PLAN statement to generate the execution plan of each SQL statement traced. TKPROF also displays the number of rows processed by each step of the execution plan.


Regards
Ayush

[Updated on: Mon, 11 January 2010 23:38]

Report message to a moderator

Previous Topic: create trigger problem
Next Topic: Error
Goto Forum:
  


Current Time: Tue Sep 27 12:48:10 CDT 2016

Total time taken to generate the page: 0.09008 seconds