| Explain Plan (merged 2) [message #422178] |
Mon, 14 September 2009 23:53  |
raywong9i
Messages: 40
Registered: April 2008
|
Member |
|
|
Hi all,
I have a stored procedure which run only 3 minutes in our development database.
However, it takes around 2.5 hours to run in the production database.
The production server is considered to be more powerful, and both DB have the same volumne of data.
I am new to SQL tuning and DBA jobs. Can someone help me....?
The attached is the explain plan, blue colour is Dev. DB.
|
|
|
|
|
|
|
|
| Re: Explain Plan (merged 2) [message #422308 is a reply to message #422178] |
Tue, 15 September 2009 07:58  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
|
Why do people continue to post attachments when you can just post it so everyone can see? I will not open attachments, so my advice may already be known. Have you gathered current statistics on the more "powerful" database?
|
|
|
|
| Re: Explain Plan (merged 2) [message #422372 is a reply to message #422178] |
Tue, 15 September 2009 22:58 |
raywong9i
Messages: 40
Registered: April 2008
|
Member |
|
|
Sorry, I forgot I saved as CSV, so no color in the file.
Blackswan, I attach the csv file because when I paste the explain plan here, the format is so messy.
Both DB already executed the DBMS_STATS.GATHER_TABLE_STATS command.
From the explain plan the Dev DB is using HASH JOIN, and it scan the TPLAN table first which have much fewer records in it.
But in Prod DB, it is NESTED LOOP , and it scan the TPOLICYS table which have more than 1.5 million records in it.
The following is the explain plan from the csv file :
development server:
Object Name Cost CPU Cost IO Cost Cardinality Bytes Optimizer
SELECT STATEMENT,
GOAL = ALL_ROWS 19289 1301561083 19196 11697 9299115 ALL_ROWS
HASH JOIN 19289 1301561083 19196 11697 9299115
TABLE ACCESS FULL TPLANS 29 8465220 28 1911 749112 ANALYZED
TABLE ACCESS FULL TPOLICYS 19259 1284608925 19168 11697 4713891 ANALYZED
production server
Object Name Cost CPU Cost IO Cost Cardinality Bytes Optimizer
SELECT STATEMENT, 20001 1769637613 17290 3586 2883144 ALL_ROWS
GOAL = ALL_ROWS
FILTER
NESTED LOOPS 20001 1769637613 17290 3586 2883144
PARTITION HASH ALL 19989 1762814373 17289 3586 1452330
TABLE ACCESS FULL TPOLICYS 19989 1762814373 17289 3586 1452330 ANALYZED
TABLE ACCESS BY INDEX ROWID TPLANS 1 11741 1 1 399 ANALYZED
INDEX UNIQUE SCAN PLA_PK 0 1900 0 1 ANALYZED
Thanks!!!
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
