API tools faq
paste
Advertisement
ExaGridDba

copy good plan to another sql

ExaGridDba
May 11th, 2015
412
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 12.72 KB | None | 0 0
raw download clone embed print report
  1. Download AND unpack sqltxplain FROM Oracle Customer support.
  2. Running coe_load_sql_profile.SQL does NOT require that sqltxplain be installed IN the DATABASE.
  3. Go TO utl DIRECTORY.
  4.  
  5. @ coe_load_sql_profile.SQL 83rd64s4a149m f3pga3w75j6rw 3930194399
  6. 83rd64s4a149m IS the sqlid WITH the bad PLAN
  7. f3pga3w75j6rw IS the sqlid WITH good PLAN 3930194399
  8. Copy PLAN 3930194399 TO 83rd64s4a149m
  9.  
  10. oracle@stormking cdb12102 coedemo]$ sqlplus /nolog @ ins.SQL
  11.  
  12. SQL*Plus: RELEASE 12.1.0.2.0 Production ON Tue May 12 00:03:23 2015
  13.  
  14. Copyright (c) 1982, 2014, Oracle.  ALL rights reserved.
  15.  
  16. Connected.
  17. SQL> SET linesize 200
  18. SQL> SET pagesize 100
  19. SQL> SET trimspool ON
  20. SQL> WHENEVER oserror EXIT 1
  21. SQL>
  22. SQL> DROP TABLE p;
  23.  
  24. TABLE dropped.
  25.  
  26. SQL> CREATE TABLE p (
  27.   2  m NUMBER,
  28.   3  n NUMBER,
  29.   4  a NUMBER
  30.   5  );
  31.  
  32. TABLE created.
  33.  
  34. SQL> CREATE INDEX i ON p ( m );
  35.  
  36. INDEX created.
  37.  
  38. SQL> INSERT INTO p ( m, n, a )
  39.   2  SELECT
  40.   3  CASE MOD ( LEVEL, 499979 ) WHEN 0 THEN 0 ELSE 1 END,
  41.   4  MOD ( LEVEL, 17 ),
  42.   5  MOD ( LEVEL, 137 )
  43.   6  FROM dual
  44.   7  CONNECT BY LEVEL <=1000000;
  45.  
  46. 1000000 ROWS created.
  47.  
  48. SQL> COMMIT;
  49.  
  50. COMMIT complete.
  51.  
  52. SQL>
  53. SQL> EXEC dbms_stats.gather_table_stats(NULL,'p' );
  54.  
  55. PL/SQL PROCEDURE successfully completed.
  56.  
  57.  
  58.  
  59. SQL> @ unwantedplan.SQL
  60. SQL> @ conn
  61. SQL> conn u1/u1@//stormking:1521/pdbm
  62. Connected.
  63. SQL> SET echo ON
  64. SQL> SET linesize 200
  65. SQL> SET pagesize 100
  66. SQL> SET trimspool ON
  67. SQL> WHENEVER oserror EXIT 1
  68. SQL> @ xvar
  69. SQL> variable sqlid VARCHAR2(13)
  70. SQL> variable cld NUMBER
  71. SQL> variable phv NUMBER
  72. SQL> variable xplanopts VARCHAR2(40)
  73. SQL> EXEC :xplanopts := NULL
  74.  
  75. PL/SQL PROCEDURE successfully completed.
  76.  
  77. SQL> SELECT COUNT(a) FROM p WHERE m = 0;
  78.  
  79.   COUNT(A)
  80. ----------
  81.          2
  82.  
  83. SQL> @ phv
  84. SQL> BEGIN
  85.   2  SELECT sql_id, child_number, plan_hash_value INTO :sqlid, :cld, :phv
  86.   3  FROM v$sql
  87.   4  WHERE ( sql_id, child_number ) IN (
  88.   5          SELECT prev_sql_id, prev_child_number
  89.   6          FROM v$session WHERE sid = SYS_CONTEXT( 'userenv','sid' )
  90.   7  );
  91.   8  END;
  92.   9  /
  93.  
  94. PL/SQL PROCEDURE successfully completed.
  95.  
  96. SQL> @ xpl
  97. SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor( :sqlid, :cld, :xplanopts ));
  98.  
  99. PLAN_TABLE_OUTPUT
  100. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  101. SQL_ID  83rd64s4a149m, child NUMBER 0
  102. -------------------------------------
  103. SELECT COUNT(a) FROM p WHERE m = 0
  104.  
  105. PLAN hash VALUE: 179592301
  106.  
  107. ---------------------------------------------------------------------------
  108. | Id  | Operation          | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  109. ---------------------------------------------------------------------------
  110. |   0 | SELECT STATEMENT   |      |       |       |   538 (100)|          |
  111. |   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
  112. |*  2 |   TABLE ACCESS FULL| P    |   500K|  3417K|   538   (2)| 00:00:01 |
  113. ---------------------------------------------------------------------------
  114.  
  115. Predicate Information (IDENTIFIED BY operation id):
  116. ---------------------------------------------------
  117.  
  118.    2 - filter("M"=0)
  119.  
  120.  
  121. 19 ROWS selected.
  122.  
  123.  
  124.  
  125. SQL> @ wantedplan.SQL
  126. SQL> @ conn
  127. SQL> conn u1/u1@//stormking:1521/pdbm
  128. Connected.
  129. SQL> SET echo ON
  130. SQL> SET linesize 200
  131. SQL> SET pagesize 100
  132. SQL> SET trimspool ON
  133. SQL> WHENEVER oserror EXIT 1
  134. SQL> @ xvar
  135. SQL> variable sqlid VARCHAR2(13)
  136. SQL> variable cld NUMBER
  137. SQL> variable phv NUMBER
  138. SQL> variable xplanopts VARCHAR2(40)
  139. SQL> EXEC :xplanopts := NULL
  140.  
  141. PL/SQL PROCEDURE successfully completed.
  142.  
  143. SQL> SELECT /*+ index( p ) */ COUNT(a) FROM p WHERE m = 0;
  144.  
  145.   COUNT(A)
  146. ----------
  147.          2
  148.  
  149. SQL> @ phv
  150. SQL> BEGIN
  151.   2  SELECT sql_id, child_number, plan_hash_value INTO :sqlid, :cld, :phv
  152.   3  FROM v$sql
  153.   4  WHERE ( sql_id, child_number ) IN (
  154.   5          SELECT prev_sql_id, prev_child_number
  155.   6          FROM v$session WHERE sid = SYS_CONTEXT( 'userenv','sid' )
  156.   7  );
  157.   8  END;
  158.   9  /
  159.  
  160. PL/SQL PROCEDURE successfully completed.
  161.  
  162. SQL> @ xpl
  163. SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor( :sqlid, :cld, :xplanopts ));
  164.  
  165. PLAN_TABLE_OUTPUT
  166. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  167. SQL_ID  f3pga3w75j6rw, child NUMBER 0
  168. -------------------------------------
  169. SELECT /*+ index( p ) */ COUNT(a) FROM p WHERE m = 0
  170.  
  171. PLAN hash VALUE: 3930194399
  172.  
  173. ---------------------------------------------------------------------------------------------
  174. | Id  | Operation                            | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  175. ---------------------------------------------------------------------------------------------
  176. |   0 | SELECT STATEMENT                     |      |       |       |  1863 (100)|          |
  177. |   1 |  SORT AGGREGATE                      |      |     1 |     7 |            |          |
  178. |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| P    |   500K|  3417K|  1863   (1)| 00:00:01 |
  179. |*  3 |    INDEX RANGE SCAN                  | I    |   500K|       |   882   (1)| 00:00:01 |
  180. ---------------------------------------------------------------------------------------------
  181.  
  182. Predicate Information (IDENTIFIED BY operation id):
  183. ---------------------------------------------------
  184.  
  185.    3 - ACCESS("M"=0)
  186.  
  187.  
  188. 20 ROWS selected.
  189.  
  190. SQL>@ coe_load_sql_profile.SQL 83rd64s4a149m f3pga3w75j6rw 3930194399
  191.  
  192. Parameter 1:
  193. ORIGINAL_SQL_ID (required)
  194.  
  195.  
  196. Parameter 2:
  197. MODIFIED_SQL_ID (required)
  198.  
  199.  
  200.  
  201.      PLAN_HASH_VALUE          AVG_ET_SECS
  202. -------------------- --------------------
  203.           3930194399                 .003
  204.  
  205. Parameter 3:
  206. PLAN_HASH_VALUE (required)
  207.  
  208.  
  209. VALUES passed TO coe_load_sql_profile:
  210. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  211. ORIGINAL_SQL_ID: "83rd64s4a149m"
  212. MODIFIED_SQL_ID: "f3pga3w75j6rw"
  213. PLAN_HASH_VALUE: "3930194399"
  214.  
  215. SQL>BEGIN
  216.   2    IF :sql_text IS NULL THEN
  217.   3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  218.   4    END IF;
  219.   5  END;
  220.   6  /
  221. SQL>SET TERM OFF;
  222. SQL>BEGIN
  223.   2    IF :other_xml IS NULL THEN
  224.   3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for modified SQL_ID &&modified_sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  225.   4    END IF;
  226.   5  END;
  227.   6  /
  228. SQL>
  229. SQL>SET ECHO OFF;
  230. 0001 BEGIN_OUTLINE_DATA
  231. 0002 IGNORE_OPTIM_EMBEDDED_HINTS
  232. 0003 OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
  233. 0004 DB_VERSION('12.1.0.2')
  234. 0005 ALL_ROWS
  235. 0006 OUTLINE_LEAF(@"SEL$1")
  236. 0007 INDEX_RS_ASC(@"SEL$1" "P"@"SEL$1" ("P"."M"))
  237. 0008 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "P"@"SEL$1")
  238. 0009 END_OUTLINE_DATA
  239. dropping staging TABLE "STGTAB_SQLPROF_83RD64S4A149M"
  240. creating staging TABLE "STGTAB_SQLPROF_83RD64S4A149M"
  241. packaging NEW SQL PROFILE INTO staging TABLE "STGTAB_SQLPROF_83RD64S4A149M"
  242.  
  243. PROFILE_NAME
  244. ------------------------------
  245. 83RD64S4A149M_3930194399
  246. SQL>REM
  247. SQL>REM SQL PROFILE
  248. SQL>REM ~~~~~~~~~~~
  249. SQL>REM
  250. SQL>SELECT signature, name, category, TYPE, status
  251.   2    FROM dba_sql_profiles WHERE name = :name;
  252.  
  253.            SIGNATURE NAME                                                                                                                             CATEGORY                     TYPE    STATUS
  254. -------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------- --------
  255. 17202475392156976596 83RD64S4A149M_3930194399                                                                                                         DEFAULT                      MANUAL  ENABLED
  256. SQL>SELECT description
  257.   2    FROM dba_sql_profiles WHERE name = :name;
  258.  
  259. DESCRIPTION
  260. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  261. ORIGINAL:83RD64S4A149M MODIFIED:F3PGA3W75J6RW PHV:3930194399 SIGNATURE:17202475392156976596 CREATED BY COE_LOAD_SQL_PROFILE.SQL
  262. SQL>SET ECHO OFF;
  263.  
  264. ****************************************************************************
  265. * Enter U1 password TO export staging TABLE STGTAB_SQLPROF_83rd64s4a149m
  266. ****************************************************************************
  267.  
  268. Export: RELEASE 12.1.0.2.0 - Production ON Tue May 12 00:04:34 2015
  269.  
  270. Copyright (c) 1982, 2014, Oracle AND/OR its affiliates.  ALL rights reserved.
  271.  
  272. Password:
  273.  
  274. EXP-00004: invalid username OR password
  275. Username: u1@//stormking:1521/pdbm
  276. Password:
  277.  
  278. Connected TO: Oracle DATABASE 12c Enterprise Edition RELEASE 12.1.0.2.0 - 64bit Production
  279. WITH the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  280. AND REAL Application Testing options
  281. Export done IN AL32UTF8 character SET AND AL16UTF16 NCHAR character SET
  282. Note: grants ON tables/views/sequences/roles will NOT be exported
  283. Note: indexes ON tables will NOT be exported
  284. Note: CONSTRAINTS ON tables will NOT be exported
  285.  
  286. About TO export specified tables via Conventional PATH ...
  287. . . exporting TABLE   STGTAB_SQLPROF_83RD64S4A149M          1 ROWS exported
  288. Export terminated successfully without warnings.
  289.  
  290.  
  291. IF you need TO implement this Custom SQL PROFILE ON a similar SYSTEM,
  292. import AND unpack USING these commands:
  293.  
  294. imp U1 FILE=STGTAB_SQLPROF_83rd64s4a149m.dmp tables=STGTAB_SQLPROF_83rd64s4a149m ignore=Y
  295.  
  296. BEGIN
  297. DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
  298. profile_name => '83RD64S4A149M_3930194399',
  299. REPLACE => TRUE,
  300. staging_table_name => 'STGTAB_SQLPROF_83rd64s4a149m',
  301. staging_schema_owner => 'U1' );
  302. END;
  303. /
  304.  
  305. updating: coe_load_sql_profile_83rd64s4a149m.LOG (deflated 77%)
  306. updating: STGTAB_SQLPROF_83rd64s4a149m.dmp (deflated 86%)
  307.   adding: coe_load_sql_profile.LOG (deflated 62%)
  308.  
  309. deleting: coe_load_sql_profile.LOG
  310.  
  311.  
  312. coe_load_sql_profile completed.
  313. SQL>
  314.  
  315.  
  316.  
  317. SQL> @ unwantedplan.SQL
  318. SQL> @ conn
  319. SQL> conn u1/u1@//stormking:1521/pdbm
  320.  
  321. Connected.
  322. SQL> SET echo ON
  323. SQL> SET linesize 200
  324. SQL> SET pagesize 100
  325. SQL> SET trimspool ON
  326. SQL> WHENEVER oserror EXIT 1
  327. SQL> @ xvar
  328. SQL> variable sqlid VARCHAR2(13)
  329. SQL> variable cld NUMBER
  330. SQL> variable phv NUMBER
  331. SQL> variable xplanopts VARCHAR2(40)
  332. SQL> EXEC :xplanopts := NULL
  333.  
  334. PL/SQL PROCEDURE successfully completed.
  335.  
  336. SQL> SELECT COUNT(a) FROM p WHERE m = 0;
  337.  
  338.   COUNT(A)
  339. ----------
  340.          2
  341.  
  342. SQL> @ phv
  343. SQL> BEGIN
  344.   2  SELECT sql_id, child_number, plan_hash_value INTO :sqlid, :cld, :phv
  345.   3  FROM v$sql
  346.   4  WHERE ( sql_id, child_number ) IN (
  347.   5          SELECT prev_sql_id, prev_child_number
  348.   6          FROM v$session WHERE sid = SYS_CONTEXT( 'userenv','sid' )
  349.   7  );
  350.   8  END;
  351.   9  /
  352.  
  353. PL/SQL PROCEDURE successfully completed.
  354.  
  355. SQL> @ xpl
  356. SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor( :sqlid, :cld, :xplanopts ));
  357.  
  358. PLAN_TABLE_OUTPUT
  359. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  360. SQL_ID  83rd64s4a149m, child NUMBER 0
  361. -------------------------------------
  362. SELECT COUNT(a) FROM p WHERE m = 0
  363.  
  364. PLAN hash VALUE: 3930194399
  365.  
  366. ---------------------------------------------------------------------------------------------
  367. | Id  | Operation                            | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
  368. ---------------------------------------------------------------------------------------------
  369. |   0 | SELECT STATEMENT                     |      |       |       |  1863 (100)|          |
  370. |   1 |  SORT AGGREGATE                      |      |     1 |     7 |            |          |
  371. |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| P    |   500K|  3417K|  1863   (1)| 00:00:01 |
  372. |*  3 |    INDEX RANGE SCAN                  | I    |   500K|       |   882   (1)| 00:00:01 |
  373. ---------------------------------------------------------------------------------------------
  374.  
  375. Predicate Information (IDENTIFIED BY operation id):
  376. ---------------------------------------------------
  377.  
  378.    3 - ACCESS("M"=0)
  379.  
  380. Note
  381. -----
  382.    - SQL PROFILE 83RD64S4A149M_3930194399 used FOR this statement
  383.  
  384.  
  385. 24 ROWS selected.
  386.  
  387. Done.
  388. PROFILE 83RD64S4A149M_3930194399 created.
  389. PLAN 3930194399 IS copied TO TO 83rd64s4a149m.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!