Skip to main content

oracle import sql profile

 

How to create SQL profile

Pickup the best hash from the history

SYS@TST3 $
@awr_plan_change_v2 0a99buvm9gszh

   SNAP_ID   NODE BEGIN_INTERVAL_TIME               SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO
---------- ------ --------------------------------- ------------- --------------- ------------ ------------ --------------
         1      1 16-NOV-24 11.43.11.000 AM         0a99buvm9gszh      4020639609            4   11,964.283  137,065,366.8
         1      2 16-NOV-24 11.44.41.000 AM         0a99buvm9gszh                            2   11,985.675  145,016,795.0
     60593      1 28-FEB-25 11.00.29.449 AM         0a99buvm9gszh       389675671            2       38.798   18,428,828.0
     60593      2 28-FEB-25 11.00.29.454 AM         0a99buvm9gszh                            2       34.657   18,428,869.5
     60594      2 28-FEB-25 12.00.32.413 PM         0a99buvm9gszh                            2       33.034   18,426,252.0
     60698      1 04-MAR-25 08.00.06.974 PM         0a99buvm9gszh      4020639609            4      974.607   11,864,236.3
     60698      2 04-MAR-25 08.00.06.980 PM         0a99buvm9gszh                            2      998.371   12,389,792.0

Note: In this example 389675671 is the best plan.            

Generate execution plan for the best hash.

select * from table(DBMS_XPLAN.DISPLAY_AWR(  '0a99buvm9gszh',389675671,null,'ADVANCED'))

It includes the Outline Data section

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$9" "P"@"SEL$9")
      USE_HASH(@"SEL$9" "P"@"SEL$9")
      INDEX_FFS(@"SEL$E41E6FF9" "P"@"SEL$10" ("PERSON"."PRSN_RK"))
      ...
      NLJ_BATCHING(@"SEL$25" "PE"@"SEL$25")
      INDEX_RS_ASC(@"SEL$24" "PEBE"@"SEL$24" ("PENSION_BENEFIT"."PLNIV_RK"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$24" "PEBE"@"SEL$24")
      END_OUTLINE_DATA
  */

Create a profile script based on on the "Outline Data" information.


DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '0a99buvm9gszh';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  SQL_TEXT => SQL_FTEXT,
  profile => sqlprof_attr(
      q'^FULL(@"SEL$9" "P"@"SEL$9")^',
      q'^USE_HASH(@"SEL$9" "P"@"SEL$9")^',
      q'^INDEX_FFS(@"SEL$E41E6FF9" "P"@"SEL$10" ("PERSON"."PRSN_RK"))^',
      q'^USE_HASH(@"SEL$E41E6FF9" "P"@"SEL$10")^',
      q'^FULL(@"SEL$39A1367D" "PI"@"SEL$13")^',
...
      q'^USE_NL(@"SEL$25" "PC"@"SEL$25")^',
      q'^USE_NL(@"SEL$25" "PE"@"SEL$25")^',
      q'^NLJ_BATCHING(@"SEL$25" "PE"@"SEL$25")^',
      q'^INDEX_RS_ASC(@"SEL$24" "PEBE"@"SEL$24" ("PENSION_BENEFIT"."PLNIV_RK"))^',
      q'^BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$24" "PEBE"@"SEL$24")^'
  ),
  NAME => 'Q_0pmcwss3rmj2y_389675671',
  REPLACE => TRUE,
  FORCE_MATCH => TRUE
);
END;
/

Comments

Popular posts from this blog

rman delete backup without mounting db

######################################################## Description : BIDTST database is accidently dropped before it's RMAN database backups on tapes are removed. so we need to remove the backups without having BIDTST db mounted. The doc retains the error messages which help troubleshooting... Date : June 1, 2011 DBA : Jiulu Sun & Kevin Ma ######################################################## $ rman catalog rman/password@catalog target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 2 15:26:52 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: DUMMY (not mounted) connected to recovery catalog database RMAN> @generic_config.rman RMAN> configure channel device type sbt parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_rman_agcoux043.opt)'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMA...

database not registered to scan_listener

created a new database COGSTD, but it's not registered on scan_listener, when connect, the error is: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor In the alert log, when the database started, I usually see the remote_listener and local_listener are set dynamically before database is mount, but on this problematic database, I only see local_listener are set, but not remote_listener ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx0)(PORT=1521))' SCOPE=MEMORY SID='COGSTD3'; Troubleshooting: 1. The service is registered with local listener: node1>lsnrctl status listener|grep COGSTD Service "COGSTD" has 1 instance(s).   Instance "COGSTD1", status READY, has 1 handler(s) for this service... Similar output on node2 and node 3 with the local instnance name 2. The service is not registered with scan_listener: -bash-4.1>srvctl status scan_listener ...

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory ...