Advertisement
ExaGridDba

Different Pstart/Pstop same plan_hash_value

Apr 26th, 2015
304
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. [oracle@stormking cdb12102 sqlplan]$ sqlplus /nolog @ addpartition.SQL
  2.  
  3. SQL*Plus: RELEASE 12.1.0.2.0 Production ON Sun Apr 26 13:53:06 2015
  4.  
  5. Copyright (c) 1982, 2014, Oracle.  ALL rights reserved.
  6.  
  7. Connected.
  8. SQL> SET linesize 200
  9. SQL> SET pagesize 0
  10. SQL> SET trimspool ON
  11. SQL> WHENEVER oserror EXIT 1
  12. SQL> variable sqlid1 VARCHAR2(13)
  13. SQL> variable cld1 NUMBER
  14. SQL> variable phv1 NUMBER
  15. SQL> variable sqlid2 VARCHAR2(13)
  16. SQL> variable cld2 NUMBER
  17. SQL> variable phv2 NUMBER
  18. SQL> variable xplanopts VARCHAR2(40)
  19. SQL> EXEC :xplanopts := 'basic'
  20.  
  21. PL/SQL PROCEDURE successfully completed.
  22.  
  23. SQL> EXEC :xplanopts := NULL
  24.  
  25. PL/SQL PROCEDURE successfully completed.
  26.  
  27. SQL>
  28. SQL> DROP TABLE p;
  29. DROP TABLE p
  30.            *
  31. ERROR AT line 1:
  32. ORA-00942: TABLE OR VIEW does NOT exist
  33.  
  34.  
  35. SQL>
  36. SQL> CREATE TABLE p
  37.   2  (
  38.   3  pdt        DATE,
  39.   4  a       NUMBER
  40.   5  )
  41.   6  PARTITION BY RANGE ( pdt )
  42.   7  (
  43.   8  PARTITION p20150425 VALUES less than ( TO_DATE ( '20150426', 'yyyymmdd' ) )
  44.   9  )
  45.  10  ;
  46.  
  47. TABLE created.
  48.  
  49. SQL>
  50. SQL> INSERT INTO p ( pdt, a ) VALUES ( TO_DATE ( '20150425', 'yyyymmdd' ), 20150425 );
  51.  
  52. 1 ROW created.
  53.  
  54. SQL>
  55. SQL> COMMIT;
  56.  
  57. COMMIT complete.
  58.  
  59. SQL> EXEC dbms_stats.gather_table_stats(NULL,'p' );
  60.  
  61. PL/SQL PROCEDURE successfully completed.
  62.  
  63. SQL>
  64. SQL> SELECT pdt, a FROM p WHERE pdt = TO_DATE ( '20150425', 'yyyymmdd' );
  65. 20150425 00:00   20150425
  66.  
  67. SQL> @ phv1
  68. SQL> BEGIN
  69.   2  SELECT sql_id, child_number, plan_hash_value INTO :sqlid1, :cld1, :phv1
  70.   3  FROM v$sql
  71.   4  WHERE ( sql_id, child_number ) IN (
  72.   5          SELECT prev_sql_id, prev_child_number
  73.   6          FROM v$session WHERE sid = SYS_CONTEXT( 'userenv','sid' )
  74.   7  );
  75.   8  END;
  76.   9  /
  77.  
  78. PL/SQL PROCEDURE successfully completed.
  79.  
  80. SQL> @ xpl1
  81. SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor( :sqlid1, :cld1, :xplanopts ));
  82. SQL_ID  d4b6hf9535fdf, child NUMBER 0
  83. -------------------------------------
  84. SELECT pdt, a FROM p WHERE pdt = TO_DATE ( '20150425', 'yyyymmdd' )
  85.  
  86. PLAN hash VALUE: 102990640
  87.  
  88. -----------------------------------------------------------------------------------------------
  89. | Id  | Operation              | Name | ROWS  | Bytes | COST (%CPU)| TIME     | Pstart| Pstop |
  90. -----------------------------------------------------------------------------------------------
  91. |   0 | SELECT STATEMENT       |      |       |       |     2 (100)|          |       |       |
  92. |   1 |  PARTITION RANGE SINGLE|      |     1 |    14 |     2   (0)| 00:00:01 |     1 |     1 |
  93. |*  2 |   TABLE ACCESS FULL    | P    |     1 |    14 |     2   (0)| 00:00:01 |     1 |     1 |
  94. -----------------------------------------------------------------------------------------------
  95.  
  96. Predicate Information (IDENTIFIED BY operation id):
  97. ---------------------------------------------------
  98.  
  99.    2 - filter("PDT"=TO_DATE(' 2015-04-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  100.  
  101.  
  102. 19 ROWS selected.
  103.  
  104. SQL>
  105. SQL> ALTER TABLE p
  106.   2  ADD PARTITION p20150426 VALUES less than ( TO_DATE ( '20150427', 'yyyymmdd' ) );
  107.  
  108. TABLE altered.
  109.  
  110. SQL> INSERT INTO p ( pdt, a ) VALUES ( TO_DATE ( '20150426', 'yyyymmdd' ), 20150426 );
  111.  
  112. 1 ROW created.
  113.  
  114. SQL>
  115. SQL> COMMIT;
  116.  
  117. COMMIT complete.
  118.  
  119. SQL> EXEC dbms_stats.gather_table_stats(NULL,'p' );
  120.  
  121. PL/SQL PROCEDURE successfully completed.
  122.  
  123. SQL> SELECT pdt, a FROM p WHERE pdt = TO_DATE ( '20150426', 'yyyymmdd' );
  124. 20150426 00:00   20150426
  125.  
  126. SQL> @ phv2
  127. SQL> BEGIN
  128.   2  SELECT sql_id, child_number, plan_hash_value INTO :sqlid2, :cld2, :phv2
  129.   3  FROM v$sql
  130.   4  WHERE ( sql_id, child_number ) IN (
  131.   5          SELECT prev_sql_id, prev_child_number
  132.   6          FROM v$session WHERE sid = SYS_CONTEXT( 'userenv','sid' )
  133.   7  );
  134.   8  END;
  135.   9  /
  136.  
  137. PL/SQL PROCEDURE successfully completed.
  138.  
  139. SQL> @ xpl2
  140. SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor( :sqlid2, :cld2, :xplanopts ));
  141. SQL_ID  fq3ywc03zjnsv, child NUMBER 0
  142. -------------------------------------
  143. SELECT pdt, a FROM p WHERE pdt = TO_DATE ( '20150426', 'yyyymmdd' )
  144.  
  145. PLAN hash VALUE: 102990640
  146.  
  147. -----------------------------------------------------------------------------------------------
  148. | Id  | Operation              | Name | ROWS  | Bytes | COST (%CPU)| TIME     | Pstart| Pstop |
  149. -----------------------------------------------------------------------------------------------
  150. |   0 | SELECT STATEMENT       |      |       |       |     2 (100)|          |       |       |
  151. |   1 |  PARTITION RANGE SINGLE|      |     1 |    14 |     2   (0)| 00:00:01 |     2 |     2 |
  152. |*  2 |   TABLE ACCESS FULL    | P    |     1 |    14 |     2   (0)| 00:00:01 |     2 |     2 |
  153. -----------------------------------------------------------------------------------------------
  154.  
  155. Predicate Information (IDENTIFIED BY operation id):
  156. ---------------------------------------------------
  157.  
  158.    2 - filter("PDT"=TO_DATE(' 2015-04-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  159.  
  160.  
  161. 19 ROWS selected.
  162.  
  163. SQL> @ cmp
  164. SQL> SELECT :sqlid1, :cld1, :phv1, :sqlid2, :cld2, :phv2, CASE WHEN :phv1 = :phv2 THEN 'SAME' ELSE 'DIFFERENT' END cmp FROM dual;
  165. d4b6hf9535fdf                             0  102990640 fq3ywc03zjnsv                             0  102990640 SAME
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement