Home » RDBMS Server » Performance Tuning » Shows difference in EXPLAIN PLAN result for same query.
Shows difference in EXPLAIN PLAN result for same query. [message #284886] Sun, 02 December 2007 09:51 Go to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Hi
I have two different schemas with same table structure, constraints, indexes and data.
when i run the same query in two different schema it
Shows different EXPALAIN PLAN result on different schema.
Query is :
SELECT 
  D.DEAL_NAME,
  B.BORROWER_FULL_NAME,
  T.STATUS_ID
FROM 
  TASK T,
  SLT_DEAL D,
  SLT_BORROWER B
WHERE 
  T.TASK_ID = D.TASK_ID     AND
  D.BORROWER_ID = B.BORROWER_ID

Explain plan on schmea A:
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3947720190

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  2685 |   697K|    70   (3)| 00:00:01 |
|*  1 |  HASH JOIN          |              |  2685 |   697K|    70   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | TASK         |  4791 |   121K|    42   (3)| 00:00:01 |
|*  3 |   HASH JOIN         |              |  2685 |   629K|    28   (4)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SLT_BORROWER |  2653 |   220K|    13   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| SLT_DEAL     |  2685 |   406K|    14   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."TASK_ID"="D"."TASK_ID")
   3 - access("D"."BORROWER_ID"="B"."BORROWER_ID")

Note
-----
   - dynamic sampling used for this statement

22 rows selected.

SQL> 


Explain plan on schmea B:
SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 3577686412

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |     1 |   266 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                 |     1 |   266 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                 |     1 |   181 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | TASK            |     1 |    26 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| SLT_DEAL        |     1 |   155 |     0   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | SLT_DEAL_AK2    |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | SLT_BORROWER    |     1 |    85 |     0   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN          | SLT_BORROWER_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T"."TASK_ID"="D"."TASK_ID")
   7 - access("D"."BORROWER_ID"="B"."BORROWER_ID")

20 rows selected.

SQL> 

My problem is why in shchema A, query is not using indexes and
it showing different explain plan result compare to schema B.
I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0.
Table data :
Task =5364 rows
slt_deal=2685 rows
slt_borrower=2653 rows

Your help will be appreciated. If you required additional information to give a solution please ask me.

Thanks
Pravin
Re: Shows difference in EXPLAIN PLAN result for same query. [message #284887 is a reply to message #284886] Sun, 02 December 2007 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Decent post as a starting point
Quote:


Table data :
Task =5364 rows
slt_deal=2685 rows
slt_borrower=2653 rows


I wonder if the above is really true for both schemas.
Are statistics current for all tables & indexes for BOTH schemas?
Something must be different between the 2 schemas otherwise Oracle have & use the same plan for both queries.
It depends upon how much time & effort you are willing to expend to obtain a definitive answer to your question.
Re: Shows difference in EXPLAIN PLAN result for same query. [message #284893 is a reply to message #284886] Sun, 02 December 2007 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not same statistics
Not same optimizer goal
Not same workarea policy
Not same number of rows (even if you say the opposite, see rows in plan)
Not same workload
...

Many and many reasons.

Regards
Michel
Re: Shows difference in EXPLAIN PLAN result for same query. [message #284913 is a reply to message #284893] Sun, 02 December 2007 19:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Analyse your tables in Schema B with DBMS_STATS.GATHER_TABLE_STATS().

Ross Leishman
Re: Shows difference in EXPLAIN PLAN result for same query. [message #285020 is a reply to message #284913] Mon, 03 December 2007 02:27 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Thanks to all for your decent reply
As suggested by ross i run
DBMS_STATS.GATHER_TABLE_STATS() for each table on schema B.
And now it showing the same explain plan result as of schema A.
SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 3929511242

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  2685 |   138K|    70   (3)| 00:00:01 |
|*  1 |  HASH JOIN          |              |  2685 |   138K|    70   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | SLT_BORROWER |  2653 | 53060 |    13   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |              |  2685 | 88605 |    56   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SLT_DEAL     |  2685 | 67125 |    14   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| TASK         |  5364 | 42912 |    42   (3)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."BORROWER_ID"="B"."BORROWER_ID")
   3 - access("T"."TASK_ID"="D"."TASK_ID")

18 rows selected.

SQL> 

Now i am clear with
Quote:

Something must be different between the 2 schemas otherwise Oracle have & use the same plan for both queries.

Please advise me, How i can improve CPU cost for this query.
and why indexes are not used when i execute the query.

Thanks
Pravin.
Re: Shows difference in EXPLAIN PLAN result for same query. [message #285023 is a reply to message #285020] Mon, 03 December 2007 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: Shows difference in EXPLAIN PLAN result for same query. [message #285190 is a reply to message #285023] Mon, 03 December 2007 20:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Indexes are not used because there is nothing much to index. You are performing joins, but you are not filtering the results. This means that the SQL will return EVERY SINGLE ROW.

If you are going to return every single row, its faster to do so with FULL scans and HASH joins, not indexed nested loops.

Ross Leishman
Re: Shows difference in EXPLAIN PLAN result for same query. [message #285297 is a reply to message #284886] Tue, 04 December 2007 03:49 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi friend rleishman,

what about you? how many years you have experience in PL/SQL.
Can you give your mail id?

Thanks

Mano

Re: Shows difference in EXPLAIN PLAN result for same query. [message #285301 is a reply to message #285297] Tue, 04 December 2007 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use PM button (or at least Community Hangout forum) for this.
Do not hijack topic for your private communication.

This post and the previous one will be removed in a short while.

Regards
Michel
Re: Shows difference in EXPLAIN PLAN result for same query. [message #285305 is a reply to message #284886] Tue, 04 December 2007 03:56 Go to previous message
spmano1983
Messages: 269
Registered: September 2007
Senior Member

Sorry Michel

I will send mail to him.
Previous Topic: Performance Degrads
Next Topic: DBMS_STATS Problem
Goto Forum:
  


Current Time: Sun Dec 04 20:54:27 CST 2016

Total time taken to generate the page: 0.09395 seconds