How to Create and Transfer Profile from One Database to Another

 The following example illustrates the process of moving a SQL Profile from 10.2 onwards.


1. Create SQL Profile in SCOTT schema

The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:


DECLARE 

my_task_name VARCHAR2(30);

my_sqltext CLOB; 

my_sqlprofile_name VARCHAR2(30); 


BEGIN 

  my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=7839'; 

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, 

        user_name => 'SCOTT', 

        scope => 'COMPREHENSIVE', 

        time_limit => 60, 

        task_name => 'my_sql_tuning_task', 

        description => 'Demo Task to tune a query'); 

  

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task'); 


my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task', 

        name => 'my_sql_profile'); 

END; 

/


PL/SQL procedure successfully completed. 

 


set lines 130

set autotrace on


select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7839 KING       PRESIDENT            17-NOV-81       5000                    10



Execution Plan

----------------------------------------------------------

Plan hash value: 4066871323


--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - access("EMPNO"=7839)


Note

-----

   - SQL profile "my_sql_profile" used for this statement

 


NOTE: Even though no_index hint included, the plan uses an index as determined by the SQL profile. The Note section provides plan information  that indicates that  "my_sql_profile" is used.

2. Creating a staging table to store the SQL Profiles

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');


PL/SQL procedure successfully completed.

table_name => name of the table to store the SQL Profiles.

schema_name => name of the schema where the table is to be created.

3. Pack the SQL Profiles into the Staging Table

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');

PL/SQL procedure successfully completed.

staging_table_name => name of the table to store the SQL Profiles.

profile_name => name of the SQL Profile to be packed.

Note: The table_name and schema_name are case-sensitive.


SQL> desc STAGE

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PROFILE_NAME                                       VARCHAR2(30)

 CATEGORY                                           VARCHAR2(30)

 SIGNATURE                                          NUMBER

 SQL_TEXT                                           CLOB

 DESCRIPTION                                        VARCHAR2(500)

 TYPE                                               VARCHAR2(9)

 STATUS                                             VARCHAR2(8)

 BOOLEAN_FLAGS                                      NUMBER

 ATTRIBUTES                                         SQLPROF_ATTR

 VERSION                                            NUMBER

 SPARE1                                             CLOB

 SPARE2                                             BLOB

4. Export the Staging Table to the Target Database

Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.


4a. Export from Source Database

my_linux_1:~> exp scott/<PASSWORD> tables=STAGE


Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)


About to export specified tables via Conventional Path ...

. . exporting table                          STAGE          1 rows exported

Export terminated successfully without warnings.

4b. Import into Target Database

my_linux_1:~> imp scott/<PASSWORD> tables=STAGE


Import: Release 11.2.0.3.0 - Production on Mon Feb 13 14:49:12 2012


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.



Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning and Real Application Testing options


Export file created by EXPORT:V10.02.01 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8MSWIN1252 character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. importing SCOTT's objects into SCOTT

. . importing table                        "STAGE"          1 rows imported

Import terminated successfully with warnings.

5. Unpack the SQL Profiles

5a. Test before unpacking

SQL> set lines 130

SQL> set autotrace on

SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7839 KING       PRESIDENT            17-NOV-81       5000                    10



Execution Plan

----------------------------------------------------------

Plan hash value: 2872589290


--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   1 - filter("EMPNO"=7839)

 


Note: The NO_INDEX hint has been honoured and a FULL table scan has been done on EMP

5b. Unpack Staging Table

If importing to the same schema, schema owner does not need to be specified:

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');


However, if importing to different schema, the staging schema owner needs to be changed:|

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE',staging_schema_owner => 'SQLTXPLAIN');


PL/SQL procedure successfully completed.

NOTE: SQLTXPLAIN referred in staging_schema_owner is just an example. No need to install SQLT for using the step. You can input any schema name to import the staging table.

 


6. Check the SQL Profile is enabled in Target Database

set lines 130

set autotrace on


select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7839 KING       PRESIDENT            17-NOV-81       5000                    10



Execution Plan

----------------------------------------------------------

Plan hash value: 4066871323


--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - access("EMPNO"=7839)


Note

-----

   - SQL profile "my_sql_profile" used for this statement


For Reference Oracle Metalink Id: 457531.1

Comments

  1. SHREYAJune 24, 2022 at 9:41 PM

    A) CREATE STAGING TABLE

    First Create the staging table which will hold sql profiles to be moved over.

    SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>’SQL_STG_TAB’,schema_name=>’SYS’);
    (This will create a table SQL_STG_TAB, which will be able to hold the required sql profiles, the structure of this table is exactly same as that of DBA_SQL_PROFILES table)


    B) FIND PROFILE NAME THAT YOU WANT TO MOVE

    If you want to pack a particular SQL profiles and you don’t know the names of profiles, then you can query dba_sql_profiles table.

    SQL> select name from DBA_SQL_PROFILES where SQL_TEXT like ‘%SELECT%EMPLOYEE%NAME%’;
    NAME
    ——————————
    SYS_SQLPROF_014b


    C) PACK CUSTOM PROFILE TO STAGING TABLE

    To pack a custom profile to stage table,

    SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => ‘SQL_STG_TAB’, profile_name=>'SYS_SQLPROF_014b');
    If you want to Pack all profiles under DEFAULT category to stage table,

    SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => ‘SQL_STG_TAB’);


    D) EXPORT STAGING TABLE

    Export stage tab using exp.

    $ exp “‘/ as sysdba'” tables=SQL_STG_TAB file=SQL_STG_TAB.dmp


    E) IMPORT STAGING TABLE

    Copy the dmp file to the target database instance

    Import the stage tab on the target database using imp



    $ imp “‘/ as sysdba'” file=SQL_STG_TAB.dmp full=y


    F) UNPACK THE PROFILE

    Unpack the profiles at the target database,

    SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => ‘SQL_STG_TAB’);

    ReplyDelete

Post a Comment

Popular posts from this blog

How to drop index and before dropping it how to get the DDL.

  Oracle DROP INDEX statement DROP INDEX [schema_name.]index_name; DECLARE index_count INTEGER; BEGIN SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'index_name' ; IF index_count > 0 THEN EXECUTE IMMEDIATE 'DROP INDEX index_name' ; END IF; END; / In case want to check whether index exists and then delete it. If try to drop a non-existing, you will get the following error: SQL Error : ORA -01418 : specified index does not exist In SQLplus, set these before running the commands set long 100000 set longchunksize 100000 select DBMS_METADATA.GET_DDL( 'INDEX' , 'index_name') from all_indexes where owner in ( USER , 'USER_OTHER_THAN_LOGGED_IN_USER' ); Use below script to fetch metadata other information. SELECT DBMS_METADATA,GET_GRANTED_DDL('SYSTEM_GRANT','SSO******') from dual;
Read more

PRVG-11250 : The check "RPM Package Manager database" was not performed because

Following message reported from cluvfy while performing pre-requisite for Clusterware Installation:  Verifying RPM Package Manager database ...INFORMATION PRVG-11250 : The check "RPM Package Manager database" was not performed because it needs 'root' user privileges. SOLUTION Execute runcluvfy manually with "-method root" option and enter root password when prompted : Example: $/runcluvfy.sh stage -pre crsinst -n <HOSTNAME1>,<HOSTNAME2> -method root Enter "ROOT" password: For reference metalink doc id :  2548970.1
Read more

ORA-00257:archiver error, connect internal only until freed

  Issue: Application log shows below error and users are unable to connect to the database. 0RA-00257:archiver error, connect internal only until freed (OR) 0RA-00257:archiver error, connect internal only until freed ORA-16020: less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST Fix: 1) Check if there are any errors for the archive destination(s). Make sure that the number of VALID archive destinations is greater than or equal to the value specified by LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter. set pages 1000 col dest_name for a30 col destination for a60 SELECT dest_id, dest_name, binding, status, destination, error FROM v$archive_dest; SHOW PARAMETER log_archive_min_succeed_dest 2) If space is full in one or more of the archive destinations (or if destination is not available), take any of the following steps: 2.a) Manually move the archives to another location and delete them from archive destination.   (OR) 2.b) Change the archive des...
Read more

Verifying Daemon “Avahi-Daemon” Not Configured And Running …FAILED (PRVG-1360)

  Execute "/tmp/CVU_19.0.0.0.0_grid/runfixup.sh" as root user on nodes "shsebs7idb1" to perform the fix up operations manually Press ENTER key to continue after execution of "/tmp/CVU_19.0.0.0.0_grid/runfixup.sh" has completed on nodes "shsebs7idb1" Fix: Daemon "avahi-daemon" not configured and running   Node Name                             Status   ------------------------------------  ------------------------   shsebs7idb1                           failed ERROR: shsebs7idb1: PRVG-9023 : Manual fix up command "/tmp/CVU_19.0.0.0.0_grid/runfixup.sh" was not issued by root user on node "shsebs7idb1" Result: "Daemon "avahi-daemon" not configured and running" could not be fixed on nodes "shsebs7idb1" SOLUTION: To fix this error, stop and disable avahi-daemon service. 1. Check avahi-daemon status: [root@shsebs7idb1 ~]# ls -...
Read more

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

  This is due to below bugs: OL7 -  Unpublished Bug 21441387- 12.2/MAIN/OL7.1: CVU REPORTS /DEV/SHM NOT MOUNTED WHEN IT IS MOUNTED  21441387.8 RHEL 7 - Unpublished Bug 25907259 : RHEL7.2: CVU REPORTS /DEV/SHM NOT MOUNTED WHEN IT IS MOUNTED  25907259.8 As a work-around in OL7: In OL7, the /dev/shm is not mounted in /etc/fstab on a fresh system, it is done in: [root@testc140 u02]# ll /usr/lib/dracut/modules.d/99base/init.sh -rwxr-xr-x. 1 root root 11379 Mar 26 10:14 vi /usr/lib/dracut/modules.d/99base/init.sh and comment the below lines 62 if ! ismounted /dev/shm; then 63 mkdir -m 0755 /dev/shm 64 mount -t tmpfs -o mode=1777,nosuid,nodev,strictatime tmpfs /dev/shm >/dev/null 65 fi so, this validation is not really valid for OL7.    We can also safely ignore this message. For reference metalink id: -  Doc ID 2065603.1
Read more

SKIP DNS RESLOV.CONF CHECK DURING RAC CONFIGURATION

 shsebs7idb1: PRVG-2064 : There are no configured name servers in the file              '/etc/resolv.conf' on the nodes "shsebs7idb1" Solution: [root@shsebs7idb2 ~]# ls -ltr /etc/resolv.conf -rw-r--r--. 1 root root 78 Jul  3 11:02 /etc/resolv.conf [root@shsebs7idb2 ~]# mv /etc/resolv.conf /etc/resolv.conf.sid Rename resolv.conf to resolv.conf.sid for example and re run the installation again . It  worked for me
Read more

CPU Patch Analysis

 Oracle CPU patch analysis Identifying the Latest Critical Patch Update for Oracle E-Business Suite Release 12.2 (Doc ID 2484000.1) Database Quick-Link If you only care about the database patches, this MOS note steps you through really easily. Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patch sets and Base Releases (Doc ID 2118136.2) https://www.oracle.com/security-alerts/ Critical Patch Updates Critical Patch Updates provide security patches for supported Oracle on-premises products. They are available to customers with valid support contracts. Starting in April 2022, Critical Patch Updates are released on the third Tuesday of January, April, July, and October (They were previously published on the Tuesday closest to the 17th day of January, April, July, and October). The next four dates are: 16 April 2024 16 July 2024 15 October 2024 21 January 2025 A pre-release announcement will be published on the Thursday preceding eac...
Read more

How to write to a CSV file using Oracle SQL*Plus

  SPOOL command  The  SPOOL  command is  unavailable  in the browser-based SQL*Plus version,  iSQL*Plus . To generate files while using iSQL*Plus, change the necessary preference settings to directly output to a file. This is accomplished using the  SPOOL  statement. While  SPOOL  is  active , SQL*PLus will store the output of any query to the specified file. Therefore, the next command to enter is  spool : spool filepath Skipping ahead slightly,  after  your query is inserted, you also need to halt  spool  so the file output is closed by using the  spool off  command: spool off Insert the query  The last step after the settings are modified and  spool  is running is to insert your query. For our simple example, we’re outputting all books from our  books  table. SELECT   title,   author FROM   authors; Don’t forget the semi...
Read more

How to troubleshoot Long Running Concurrent Request in EBS 12.2

 The following points need to answer before jumping into troubleshooting: Are there any recent code changes done in the concurrent program?           Check long_running_sid and verify the recent code change of last_ddl_timestamp after a             discussion with the developer. Was this running long in the last few run as well, or was this time only?            ==> Check the program history. How much time does it take to complete?           Once again program history can give us some idea. Are these jobs fetching higher data compared to the last run           ===> Confirm on data change . Query Plan change will indicate this issue. Does this job runs at any specific time or it can be run anytime           ===> The nature of the job can help to understand the job's nature. Does this job fetch data using DB Link...
Read more

How To Manage Space of The FRA in the Oracle DB

  Delete Old files connect with the target database using RMAN RMAN target / Delete the old files RMAN> delete obsolete; Remove Restore Points SQL> SELECT name, storage_size FROM gv$restore_point; Drop the restore point SQL> DROP restore point RP; Delete the archive logs RMAN> DELETE archivelog ALL Increase the Space SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 20G scope=both; Disable the Flashback ALTER DATABASE flashback off;
Read more