Home » RDBMS Server » Performance Tuning » SQL Execution Plan Export/Import within 10.2.0.4 version databases (10.2.0.4 in AIX)
SQL Execution Plan Export/Import within 10.2.0.4 version databases [message #590290] Tue, 16 July 2013 13:57 Go to next message
TRaj
Messages: 80
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 #590291 is a reply to message #590290] Tue, 16 July 2013 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
when all else fails, Read The Fine Manual

http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#CHDGDICI
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 Go to previous message
Lalit Kumar B
Messages: 2348
Registered: May 2013
Location: World Wide on the Web
Senior Member
thiagesh wrote on Wed, 17 July 2013 00:27
The 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.
Previous Topic: Exec plan explanation
Next Topic: TDE Related Question
Goto Forum:
  


Current Time: Mon Sep 22 01:32:24 CDT 2014

Total time taken to generate the page: 0.08340 seconds