Home » SQL & PL/SQL » SQL & PL/SQL » Explain Plan (merged 2)
Explain Plan (merged 2) [message #422178] Mon, 14 September 2009 23:53 Go to next message
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 [message #422182 is a reply to message #422178] Mon, 14 September 2009 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>The attached is the explain plan, blue colour is Dev. DB.
I'm color blind & do not see anything in blue.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Explain Plan [message #422184 is a reply to message #422178] Tue, 15 September 2009 00:00 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/t/150055/136107/

Do not cross/multi-post

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Explain Plan (merged 2) [message #422308 is a reply to message #422178] Tue, 15 September 2009 07:58 Go to previous messageGo to next message
joy_division
Messages: 4617
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 Go to previous messageGo to next message
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!!!
Re: Explain Plan (merged 2) [message #422374 is a reply to message #422178] Tue, 15 September 2009 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
Posting Guidelines provides instruction how to have EXPLAIN PLAN displayed in readable format by using <code tags>.

while using sqlplus do the following on both servers.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SQL> -- invoke problem SQL

CUT whole session & PASTE results back here using <code tags>
Re: Explain Plan (merged 2) [message #422887 is a reply to message #422178] Mon, 21 September 2009 01:46 Go to previous message
raywong9i
Messages: 40
Registered: April 2008
Member
The problem seems ok after I add the /*+ USE_HASH (a b) */
to the select statement.

Thanks!
Previous Topic: numbering partition numbers
Next Topic: Replicate Rows
Goto Forum:
  


Current Time: Wed Sep 28 10:56:03 CDT 2016

Total time taken to generate the page: 0.52098 seconds