与SQL Tuning Set相关的操作

admin

1. 从cursor cache中收集tuning set, 持续12分钟，间隔5秒钟：

1. begin
   
2. dbms_sqltune.create_sqlset(sqlset_name => 'SQLTUNINGSET1');
   
3. dbms_sqltune.capture_cursor_cache_sqlset(
   
4.                                               sqlset_name => 'SQLTUNINGSET1' ,
   
5.                                               time_limit => 12*60,
   
6.                                               repeat_interval => 5,
   
7. basic_filter=> q'# ((module like 'DWH_TEST%' and sql_text not like '%applicat%') or
   
8.                                sql_text LIKE '%fact_pd_out_itm_293%')
   
9.                                and parsing_schema_name in ('APPS')
   
10.                          #');
    
11. end;
    
12. /
    

复制代码

2. 查看SQLTUNING SET的方法：

1. select  owner,name  from dba_sqlset;
   

复制代码

SYS    $$SQLA$$_1
SYS    $$SQLA$$_2
SYS    $$SQLA$$_3
SYS    $$SQLA$$_4
SYS    $$SQLA$$_5
SYS    $$SQLA$$_6
SYS    SQLTUNINGSET1
SYS    SYS_AUTO_STS

1. select  sql_id , sql_text  from dba_sqlset_statements
   
2. where sqlset_name='SQLTUNINGSET1';

复制代码

0mcv88ft7fwdj    "SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */
    'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
    'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
    'r' || t4.elementrange_id pg_featurevalue_15_id,
    'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
    'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
    'r' || t5.elementrange_id price_eur_id,
    'B' || t2.productgroup_id productgroup_id,
    'G' || t6.elementgroup_id period_id,
    SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur
FROM
    lu_item_293 t2,
    lu_pg_featurevalue_15 t3,
    lu_elementrange_rel t4,
    fact_pd_out_itm_293 t1,
    lu_elementgroup_rel t6,
    lu_elementrange_rel t5
WHERE
/* Attribute Joins */
      ((t1.item_id = t2.item_id
/* Customizing Begin */
  AND   t1.productgroup_id = t2.productgroup_id)
/* Customizing End */
  AND  (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
  AND  (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
  AND  (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
  AND  (t1.period_id = t6.value_id)
      )
/* Attribute Filters */
  AND ((t2.productgroup_id = 15520)
  AND  (t1.productgroup_id = 15520) /* Push Down Filters */
  AND  (t2.pg_featurevalue_01_id IN (103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,2477,24958,27445,297,3891,71,76,89,92,95))
  AND  (t2.pg_featurevalue_08_id IN (716,717))
  AND  (t2.pg_featurevalue_02_id IN (4165,4166))
  AND  (t2.pg_featurevalue_13_id = 5424)
  AND  (t4.elementrange_id IN (3091,3092))
  AND  (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699))
  AND  (t6.elementgroup_id = 14659)
  AND  (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */
      )
/* Fact Filters */
  AND (t1.project_type_id = '1'
      )
GROUP BY
    t2.pg_featurevalue_13_id,
    t2.pg_featurevalue_02_id,
    t4.elementrange_id,
    t2.pg_featurevalue_08_id,
    t2.pg_featurevalue_01_id,
    t5.elementrange_id,
    t2.productgroup_id,
    t6.elementgroup_id"

3. 转移SQLTUNIUNG SET的方法：

3.1 创建存放sqlset的中间表：

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> drop table pack_sqlset purge;
   
4. drop table pack_sqlset purge
   
5.            *
   
6. ERROR at line 1:
   
7. ORA-00942: table or view does not exist
   
8. Elapsed: 00:00:00.03
   
9. SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('PACK_SQLSET','APPS');
   
10. PL/SQL procedure successfully completed.
    
11. Elapsed: 00:00:00.29

复制代码

1. SQL> desc apps.pack_sqlset
   
2. Name                                           Null?    Type
   
3. ----------------------------------------- -------- ----------------------------
   
4. ID                                                    NUMBER
   
5. NAME                                                    VARCHAR2(128)
   
6. OWNER                                                    VARCHAR2(128)
   
7. DESCRIPTION                                            VARCHAR2(256)
   
8. SQL_ID                                             VARCHAR2(13)
   
9. SQL_SEQ                                            NUMBER
   
10. FORCE_MATCHING_SIGNATURE                            NUMBER
    
11. CON_DBID                                            NUMBER
    
12. SQL_TEXT                                            CLOB
    
13. PARSING_SCHEMA_NAME                                    VARCHAR2(128)
    
14. BIND_DATA                                            RAW(2000)
    
15. BINDS_CAPTURED                                     CHAR(1)
    
16. BIND_POSITION                                            NUMBER
    
17. BIND_VALUE                                            ANYDATA
    
18. MODULE                                             VARCHAR2(64)
    
19. ACTION                                             VARCHAR2(64)
    
20. ELAPSED_TIME                                            NUMBER
    
21. CPU_TIME                                            NUMBER
    
22. BUFFER_GETS                                            NUMBER
    
23. DISK_READS                                            NUMBER
    
24. DIRECT_WRITES                                            NUMBER
    
25. ROWS_PROCESSED                                     NUMBER
    
26. FETCHES                                            NUMBER
    
27. EXECUTIONS                                            NUMBER
    
28. END_OF_FETCH_COUNT                                    NUMBER
    
29. OPTIMIZER_COST                                     NUMBER
    
30. OPTIMIZER_ENV                                            RAW(2000)
    
31. PRIORITY                                            NUMBER
    
32. COMMAND_TYPE                                            NUMBER
    
33. FIRST_LOAD_TIME                                    VARCHAR2(19)
    
34. STAT_PERIOD                                            NUMBER
    
35. ACTIVE_STAT_PERIOD                                    NUMBER
    
36. OTHER                                                    CLOB
    
37. LAST_EXEC_START_TIME                                    VARCHAR2(19)
    
38. PLAN_HASH_VALUE                                    NUMBER
    
39. PLAN_STATEMENT_ID                                    VARCHAR2(128)
    
40. PLAN_PLAN_ID                                            NUMBER
    
41. PLAN_TIMESTAMP                                     DATE
    
42. PLAN_REMARKS                                            VARCHAR2(4000)
    
43. PLAN_OPERATION                                     VARCHAR2(128)
    
44. PLAN_OPTIONS                                            VARCHAR2(255)
    
45. PLAN_OBJECT_NODE                                    VARCHAR2(128)
    
46. PLAN_OBJECT_OWNER                                    VARCHAR2(128)
    
47. PLAN_OBJECT_NAME                                    VARCHAR2(128)
    
48. PLAN_OBJECT_ALIAS                                    VARCHAR2(261)
    
49. PLAN_OBJECT_INSTANCE                                    NUMBER
    
50. PLAN_OBJECT_TYPE                                    VARCHAR2(128)
    
51. PLAN_OPTIMIZER                                     VARCHAR2(255)
    
52. PLAN_SEARCH_COLUMNS                                    NUMBER
    
53. PLAN_ID                                            NUMBER
    
54. PLAN_PARENT_ID                                     NUMBER
    
55. PLAN_DEPTH                                            NUMBER
    
56. PLAN_POSITION                                            NUMBER
    
57. PLAN_COST                                            NUMBER
    
58. PLAN_CARDINALITY                                    NUMBER
    
59. PLAN_BYTES                                            NUMBER
    
60. PLAN_OTHER_TAG                                     VARCHAR2(255)
    
61. PLAN_PARTITION_START                                    VARCHAR2(255)
    
62. PLAN_PARTITION_STOP                                    VARCHAR2(255)
    
63. PLAN_PARTITION_ID                                    NUMBER
    
64. PLAN_DISTRIBUTION                                    VARCHAR2(128)
    
65. PLAN_CPU_COST                                            NUMBER
    
66. PLAN_IO_COST                                            NUMBER
    
67. PLAN_TEMP_SPACE                                    NUMBER
    
68. PLAN_ACCESS_PREDICATES                             VARCHAR2(4000)
    
69. PLAN_FILTER_PREDICATES                             VARCHAR2(4000)
    
70. PLAN_PROJECTION                                    VARCHAR2(4000)
    
71. PLAN_TIME                                            NUMBER
    
72. PLAN_QBLOCK_NAME                                    VARCHAR2(128)
    
73. PLAN_OTHER_XML                                     CLOB
    
74. PLAN_EXECUTIONS                                    NUMBER
    
75. PLAN_STARTS                                            NUMBER
    
76. PLAN_OUTPUT_ROWS                                    NUMBER
    
77. PLAN_CR_BUFFER_GETS                                    NUMBER
    
78. PLAN_CU_BUFFER_GETS                                    NUMBER
    
79. PLAN_DISK_READS                                    NUMBER
    
80. PLAN_DISK_WRITES                                    NUMBER
    
81. PLAN_ELAPSED_TIME                                    NUMBER
    
82. PLAN_LAST_STARTS                                    NUMBER
    
83. PLAN_LAST_OUTPUT_ROWS                                    NUMBER
    
84. PLAN_LAST_CR_BUFFER_GETS                            NUMBER
    
85. PLAN_LAST_CU_BUFFER_GETS                            NUMBER
    
86. PLAN_LAST_DISK_READS                                    NUMBER
    
87. PLAN_LAST_DISK_WRITES                                    NUMBER
    
88. PLAN_LAST_ELAPSED_TIME                             NUMBER
    
89. PLAN_POLICY                                            VARCHAR2(10)
    
90. PLAN_ESTIMATED_OPTIMAL_SIZE                            NUMBER
    
91. PLAN_ESTIMATED_ONEPASS_SIZE                            NUMBER
    
92. PLAN_LAST_MEMORY_USED                                    NUMBER
    
93. PLAN_LAST_EXECUTION                                    VARCHAR2(10)
    
94. PLAN_LAST_DEGREE                                    NUMBER
    
95. PLAN_TOTAL_EXECUTIONS                                    NUMBER
    
96. PLAN_OPTIMAL_EXECUTIONS                            NUMBER
    
97. PLAN_ONEPASS_EXECUTIONS                            NUMBER
    
98. PLAN_MULTIPASSES_EXECUTIONS                            NUMBER
    
99. PLAN_ACTIVE_TIME                                    NUMBER
    
100. PLAN_MAX_TEMPSEG_SIZE                                    NUMBER
     
101. PLAN_LAST_TEMPSEG_SIZE                             NUMBER
     
102. SPARE1                                             NUMBER
     
103. SPARE2                                             NUMBER
     
104. SPARE3                                             BLOB
     
105. SPARE4                                             CLOB
     
106. SPARE5                                             NUMBER
     
107. SPARE6                                             NUMBER
     
108. SPARE7                                             CLOB
     
109. SPARE8                                             CLOB
     

复制代码

1. SQL> select sql_id from apps.PACK_SQLSET;
   
2. no rows selected
   
3. Elapsed: 00:00:00.00
   

复制代码

3.2 转移SQLSET到sqlset的中间表：

1. SQL>  exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET('SQLTUNINGSET1','SYS','PACK_SQLSET','APPS');
   
2. PL/SQL procedure successfully completed.
   
3. Elapsed: 00:00:03.48
   

复制代码

查看里面的数据：

1. SQL>  select sql_id from apps.PACK_SQLSET;
   
2. 
   
3. SQL_ID
   
4. -------------
   
5. 0mcv88ft7fwdj
   
6. 0mcv88ft7fwdj
   
7. 0mcv88ft7fwdj
   
8. 0mcv88ft7fwdj
   
9. 0mcv88ft7fwdj
   
10. 0mcv88ft7fwdj
    
11. 0mcv88ft7fwdj
    
12. 0mcv88ft7fwdj
    
13. 0mcv88ft7fwdj
    
14. 0mcv88ft7fwdj
    
15. 0mcv88ft7fwdj
    
16. 
    
17. SQL_ID
    
18. -------------
    
19. 0mcv88ft7fwdj
    
20. 0mcv88ft7fwdj
    
21. 0mcv88ft7fwdj
    
22. 0mcv88ft7fwdj
    
23. 0mcv88ft7fwdj
    
24. 0mcv88ft7fwdj
    
25. 0mcv88ft7fwdj
    
26. 0mcv88ft7fwdj
    
27. 0mcv88ft7fwdj
    
28. 0mcv88ft7fwdj
    
29. 0mcv88ft7fwdj
    
30. 
    
31. SQL_ID
    
32. -------------
    
33. 0mcv88ft7fwdj
    
34. 0mcv88ft7fwdj
    
35. 0mcv88ft7fwdj
    
36. 0mcv88ft7fwdj
    
37. 0mcv88ft7fwdj
    
38. 0mcv88ft7fwdj
    
39. 
    
40. 28 rows selected.
    
41. 
    
42. Elapsed: 00:00:00.00
    
43. SQL>
    

复制代码

为了转移SQLTUNING SET（当然还包括整个方案设计），为了防止版本冲突，建议先删除apps用户方案的plan_table后导出该用户方案（expdp数据泵操作略）。然后将apps用户方案倒入另外一个数据库（impdp数据泵操作略）。此处模拟在原库删除SQLTUNINGSET1，再在原库从apps.pack_sqlset导出SQLTUNINGSET1：

1. SQL> begin
   
2. dbms_sqltune.drop_sqlset(sqlset_name => 'SQLTUNINGSET1');
   
3. end;
   
4. /

复制代码

1. SQL>  exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('SQLTUNINGSET1','SYS',true,'PACK_SQLSET','APPS');

复制代码

4. 各种查找顶级SQL的方法：

1. SQL> desc v$sql
   
2. Name                                           Null?    Type
   
3. ----------------------------------------- -------- ----------------------------
   
4. SQL_TEXT                                            VARCHAR2(1000)
   
5. SQL_FULLTEXT                                            CLOB
   
6. SQL_ID                                             VARCHAR2(13)
   
7. SHARABLE_MEM                                            NUMBER
   
8. PERSISTENT_MEM                                     NUMBER
   
9. RUNTIME_MEM                                            NUMBER
   
10. SORTS                                                    NUMBER
    
11. LOADED_VERSIONS                                    NUMBER
    
12. OPEN_VERSIONS                                            NUMBER
    
13. USERS_OPENING                                            NUMBER
    
14. FETCHES                                            NUMBER
    
15. EXECUTIONS                                            NUMBER
    
16. PX_SERVERS_EXECUTIONS                                    NUMBER
    
17. END_OF_FETCH_COUNT                                    NUMBER
    
18. USERS_EXECUTING                                    NUMBER
    
19. LOADS                                                    NUMBER
    
20. FIRST_LOAD_TIME                                    VARCHAR2(76)
    
21. INVALIDATIONS                                            NUMBER
    
22. PARSE_CALLS                                            NUMBER
    
23. DISK_READS                                            NUMBER
    
24. DIRECT_WRITES                                            NUMBER
    
25. DIRECT_READS                                            NUMBER
    
26. BUFFER_GETS                                            NUMBER
    
27. APPLICATION_WAIT_TIME                                    NUMBER
    
28. CONCURRENCY_WAIT_TIME                                    NUMBER
    
29. CLUSTER_WAIT_TIME                                    NUMBER
    
30. USER_IO_WAIT_TIME                                    NUMBER
    
31. PLSQL_EXEC_TIME                                    NUMBER
    
32. JAVA_EXEC_TIME                                     NUMBER
    
33. ROWS_PROCESSED                                     NUMBER
    
34. COMMAND_TYPE                                            NUMBER
    
35. OPTIMIZER_MODE                                     VARCHAR2(10)
    
36. OPTIMIZER_COST                                     NUMBER
    
37. OPTIMIZER_ENV                                            RAW(2000)
    
38. OPTIMIZER_ENV_HASH_VALUE                            NUMBER
    
39. PARSING_USER_ID                                    NUMBER
    
40. PARSING_SCHEMA_ID                                    NUMBER
    
41. PARSING_SCHEMA_NAME                                    VARCHAR2(128)
    
42. KEPT_VERSIONS                                            NUMBER
    
43. ADDRESS                                            RAW(8)
    
44. TYPE_CHK_HEAP                                            RAW(8)
    
45. HASH_VALUE                                            NUMBER
    
46. OLD_HASH_VALUE                                     NUMBER
    
47. PLAN_HASH_VALUE                                    NUMBER
    
48. FULL_PLAN_HASH_VALUE                                    NUMBER
    
49. CHILD_NUMBER                                            NUMBER
    
50. SERVICE                                            VARCHAR2(64)
    
51. SERVICE_HASH                                            NUMBER
    
52. MODULE                                             VARCHAR2(64)
    
53. MODULE_HASH                                            NUMBER
    
54. ACTION                                             VARCHAR2(64)
    
55. ACTION_HASH                                            NUMBER
    
56. SERIALIZABLE_ABORTS                                    NUMBER
    
57. OUTLINE_CATEGORY                                    VARCHAR2(64)
    
58. CPU_TIME                                            NUMBER
    
59. ELAPSED_TIME                                            NUMBER
    
60. OUTLINE_SID                                            NUMBER
    
61. CHILD_ADDRESS                                            RAW(8)
    
62. SQLTYPE                                            NUMBER
    
63. REMOTE                                             VARCHAR2(1)
    
64. OBJECT_STATUS                                            VARCHAR2(19)
    
65. LITERAL_HASH_VALUE                                    NUMBER
    
66. LAST_LOAD_TIME                                     VARCHAR2(76)
    
67. IS_OBSOLETE                                            VARCHAR2(1)
    
68. IS_BIND_SENSITIVE                                    VARCHAR2(1)
    
69. IS_BIND_AWARE                                            VARCHAR2(1)
    
70. IS_SHAREABLE                                            VARCHAR2(1)
    
71. CHILD_LATCH                                            NUMBER
    
72. SQL_PROFILE                                            VARCHAR2(64)
    
73. SQL_PATCH                                            VARCHAR2(128)
    
74. SQL_PLAN_BASELINE                                    VARCHAR2(128)
    
75. PROGRAM_ID                                            NUMBER
    
76. PROGRAM_LINE#                                            NUMBER
    
77. EXACT_MATCHING_SIGNATURE                            NUMBER
    
78. FORCE_MATCHING_SIGNATURE                            NUMBER
    
79. LAST_ACTIVE_TIME                                    DATE
    
80. BIND_DATA                                            RAW(2000)
    
81. TYPECHECK_MEM                                            NUMBER
    
82. IO_CELL_OFFLOAD_ELIGIBLE_BYTES                     NUMBER
    
83. IO_INTERCONNECT_BYTES                                    NUMBER
    
84. PHYSICAL_READ_REQUESTS                             NUMBER
    
85. PHYSICAL_READ_BYTES                                    NUMBER
    
86. PHYSICAL_WRITE_REQUESTS                            NUMBER
    
87. PHYSICAL_WRITE_BYTES                                    NUMBER
    
88. OPTIMIZED_PHY_READ_REQUESTS                            NUMBER
    
89. LOCKED_TOTAL                                            NUMBER
    
90. PINNED_TOTAL                                            NUMBER
    
91. IO_CELL_UNCOMPRESSED_BYTES                            NUMBER
    
92. IO_CELL_OFFLOAD_RETURNED_BYTES                     NUMBER
    
93. CON_ID                                             NUMBER
    
94. IS_REOPTIMIZABLE                                    VARCHAR2(1)
    
95. IS_RESOLVED_ADAPTIVE_PLAN                            VARCHAR2(1)
    
96. IM_SCANS                                            NUMBER
    
97. IM_SCAN_BYTES_UNCOMPRESSED                            NUMBER
    
98. IM_SCAN_BYTES_INMEMORY                             NUMBER
    
99. DDL_NO_INVALIDATE                                    VARCHAR2(1)
    
100. IS_ROLLING_INVALID                                    VARCHAR2(1)
     
101. IS_ROLLING_REFRESH_INVALID                            VARCHAR2(1)
     
102. RESULT_CACHE                                            VARCHAR2(1)
     
103. SQL_QUARANTINE                                     VARCHAR2(128)
     
104. AVOIDED_EXECUTIONS                                    NUMBER
     
105. 
     
106. SQL>
     

复制代码

1. SELECT *
   
2. FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_text like ''update%'''))
   
3. ORDER BY sql_id;

复制代码

1. SELECT *
   
2. FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'));

复制代码

1. SELECT *
   
2. FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('CPU_TIME>29000000'));  --单位为10的-6次方秒
   

复制代码