dbms_spm.migrate_stored_outline

All posts relating to Oracle database administration.

Moderator: Tim...

dbms_spm.migrate_stored_outline

Postby nelsonjcelisc » Mon May 06, 2013 8:10 pm

Hi Tim,

we had a problem with one query and I want to know if I can transport the sql plan of the original database to a new database installation.

regards,

Nelson.
nelsonjcelisc
Member
 
Posts: 2
Joined: Mon May 06, 2013 8:08 pm

Re: dbms_spm.migrate_stored_outline

Postby Tim... » Tue May 07, 2013 7:59 am

Hi.

The dbms_spm.migrate_stored_outline is used to convert a stored outline into a SQL Profile, so it is not really appropriate for moving execution plans between servers.

Typically, this sort of thing would be done by capturing the plan on the first server, then moving it to the second. I speak about this here:

http://www.oracle-base.com/articles/11g ... _baselines

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: dbms_spm.migrate_stored_outline

Postby nelsonjcelisc » Tue May 07, 2013 2:42 pm

Hi Tim,

thanks for your reply.

when we migrate the database see an improvement on all the other querys, but one query that took 5 seconds start to long 50 minutes. If I migrate the baseline can I had a regression on the improvement on the other querys?

regards.

Nelson.
nelsonjcelisc
Member
 
Posts: 2
Joined: Mon May 06, 2013 8:08 pm

Re: dbms_spm.migrate_stored_outline

Postby Tim... » Tue May 07, 2013 4:11 pm

Hi.

I wasn't really expecting you to migrate the whole lot. I was thinking you could pack the single statement, then transfer it. The PACK_STGTAB_BASELINE procedure allows you to decide what you want to pack.

http://docs.oracle.com/cd/B28359_01/app ... m#CACEGFAI

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 6 guests