| Shows difference in EXPLAIN PLAN result for same query. [message #284886] |
Sun, 02 December 2007 09:51  |
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 #285020 is a reply to message #284913] |
Mon, 03 December 2007 02:27  |
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 #285190 is a reply to message #285023] |
Mon, 03 December 2007 20:42 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
