| SQL Execution Plan Export/Import within 10.2.0.4 version databases [message #590290] |
Tue, 16 July 2013 13:57  |
TRaj
Messages: 82
Registered: September 2006
|
Member |
|
|
Hi,
An SQL query is taking a lot of time than usual and not completing even left after hours! The query joins a table with a quite complex view.
The same query in a test database completes in less than 2 mins.
I would like to export the sql plan from test database to prod database.
Pls help how to export/import in 10.2.0.4 version for a particular sql statement's execution plan.
Thanks.
|
|
|
|
|
|
| Re: SQL Execution Plan Export/Import within 10.2.0.4 version databases [message #590667 is a reply to message #590290] |
Sun, 21 July 2013 14:27  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
thiagesh wrote on Wed, 17 July 2013 00:27The same query in a test database completes in less than 2 mins. I guess your next question would be that even after exporting the execution plan, the query takes more than 2 minutes, so how to tune it?
Now, since you are comparing the performance in two different environments, there are a lot of factors which decide the efficiency of a query to retrieve the requested rows.
Before you take a step ahead of handling the CBO and forcing it to do things that you wish, focus on why the performance has degraded in other environment. Exporting the execution plan and pinning them should be the last option when EVERYTHING ELSE FAILS.
BlackSwan wrote on Wed, 17 July 2013 00:40
Read The Fine Manual
http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#CHDGDICI
I am not sure if that would work in 10g.
Having said that, all these are same:-
Stored Outline in 9i OR SQL Profile in 10g OR SQL Plan Baseline in 11g.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
