与SQL Tuning Set相关的操作
1. 从cursor cache中收集tuning set, 持续12分钟，间隔5秒钟：begin
dbms_sqltune.create_sqlset(sqlset_name => 'SQLTUNINGSET1');
dbms_sqltune.capture_cursor_cache_sqlset(
sqlset_name => 'SQLTUNINGSET1' ,
time_limit => 12*60,
repeat_interval => 5,
basic_filter=> q'# ((module like 'DWH_TEST%' and sql_text not like '%applicat%') or
sql_text LIKE '%fact_pd_out_itm_293%')
and parsing_schema_name in ('APPS')
#');
end;
/
2. 查看SQLTUNING SET的方法：
selectowner,namefrom 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
selectsql_id , sql_textfrom dba_sqlset_statements
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的中间表：
SQL> conn / as sysdba
Connected.
SQL> drop table pack_sqlset purge;
drop table pack_sqlset purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.03
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('PACK_SQLSET','APPS');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.29
SQL> desc apps.pack_sqlset
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(128)
OWNER VARCHAR2(128)
DESCRIPTION VARCHAR2(256)
SQL_ID VARCHAR2(13)
SQL_SEQ NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
CON_DBID NUMBER
SQL_TEXT CLOB
PARSING_SCHEMA_NAME VARCHAR2(128)
BIND_DATA RAW(2000)
BINDS_CAPTURED CHAR(1)
BIND_POSITION NUMBER
BIND_VALUE ANYDATA
MODULE VARCHAR2(64)
ACTION VARCHAR2(64)
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(2000)
PRIORITY NUMBER
COMMAND_TYPE NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
STAT_PERIOD NUMBER
ACTIVE_STAT_PERIOD NUMBER
OTHER CLOB
LAST_EXEC_START_TIME VARCHAR2(19)
PLAN_HASH_VALUE NUMBER
PLAN_STATEMENT_ID VARCHAR2(128)
PLAN_PLAN_ID NUMBER
PLAN_TIMESTAMP DATE
PLAN_REMARKS VARCHAR2(4000)
PLAN_OPERATION VARCHAR2(128)
PLAN_OPTIONS VARCHAR2(255)
PLAN_OBJECT_NODE VARCHAR2(128)
PLAN_OBJECT_OWNER VARCHAR2(128)
PLAN_OBJECT_NAME VARCHAR2(128)
PLAN_OBJECT_ALIAS VARCHAR2(261)
PLAN_OBJECT_INSTANCE NUMBER
PLAN_OBJECT_TYPE VARCHAR2(128)
PLAN_OPTIMIZER VARCHAR2(255)
PLAN_SEARCH_COLUMNS NUMBER
PLAN_ID NUMBER
PLAN_PARENT_ID NUMBER
PLAN_DEPTH NUMBER
PLAN_POSITION NUMBER
PLAN_COST NUMBER
PLAN_CARDINALITY NUMBER
PLAN_BYTES NUMBER
PLAN_OTHER_TAG VARCHAR2(255)
PLAN_PARTITION_START VARCHAR2(255)
PLAN_PARTITION_STOP VARCHAR2(255)
PLAN_PARTITION_ID NUMBER
PLAN_DISTRIBUTION VARCHAR2(128)
PLAN_CPU_COST NUMBER
PLAN_IO_COST NUMBER
PLAN_TEMP_SPACE NUMBER
PLAN_ACCESS_PREDICATES VARCHAR2(4000)
PLAN_FILTER_PREDICATES VARCHAR2(4000)
PLAN_PROJECTION VARCHAR2(4000)
PLAN_TIME NUMBER
PLAN_QBLOCK_NAME VARCHAR2(128)
PLAN_OTHER_XML CLOB
PLAN_EXECUTIONS NUMBER
PLAN_STARTS NUMBER
PLAN_OUTPUT_ROWS NUMBER
PLAN_CR_BUFFER_GETS NUMBER
PLAN_CU_BUFFER_GETS NUMBER
PLAN_DISK_READS NUMBER
PLAN_DISK_WRITES NUMBER
PLAN_ELAPSED_TIME NUMBER
PLAN_LAST_STARTS NUMBER
PLAN_LAST_OUTPUT_ROWS NUMBER
PLAN_LAST_CR_BUFFER_GETS NUMBER
PLAN_LAST_CU_BUFFER_GETS NUMBER
PLAN_LAST_DISK_READS NUMBER
PLAN_LAST_DISK_WRITES NUMBER
PLAN_LAST_ELAPSED_TIME NUMBER
PLAN_POLICY VARCHAR2(10)
PLAN_ESTIMATED_OPTIMAL_SIZE NUMBER
PLAN_ESTIMATED_ONEPASS_SIZE NUMBER
PLAN_LAST_MEMORY_USED NUMBER
PLAN_LAST_EXECUTION VARCHAR2(10)
PLAN_LAST_DEGREE NUMBER
PLAN_TOTAL_EXECUTIONS NUMBER
PLAN_OPTIMAL_EXECUTIONS NUMBER
PLAN_ONEPASS_EXECUTIONS NUMBER
PLAN_MULTIPASSES_EXECUTIONS NUMBER
PLAN_ACTIVE_TIME NUMBER
PLAN_MAX_TEMPSEG_SIZE NUMBER
PLAN_LAST_TEMPSEG_SIZE NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 BLOB
SPARE4 CLOB
SPARE5 NUMBER
SPARE6 NUMBER
SPARE7 CLOB
SPARE8 CLOB
SQL> select sql_id from apps.PACK_SQLSET;
no rows selected
Elapsed: 00:00:00.00
3.2 转移SQLSET到sqlset的中间表：
SQL>exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET('SQLTUNINGSET1','SYS','PACK_SQLSET','APPS');
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.48
查看里面的数据：
SQL>select sql_id from apps.PACK_SQLSET;
SQL_ID
-------------
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
SQL_ID
-------------
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
SQL_ID
-------------
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
0mcv88ft7fwdj
28 rows selected.
Elapsed: 00:00:00.00
SQL>
为了转移SQLTUNING SET（当然还包括整个方案设计），为了防止版本冲突，建议先删除apps用户方案的plan_table后导出该用户方案（expdp数据泵操作略）。然后将apps用户方案倒入另外一个数据库（impdp数据泵操作略）。此处模拟在原库删除SQLTUNINGSET1，再在原库从apps.pack_sqlset导出SQLTUNINGSET1：
SQL> begin
dbms_sqltune.drop_sqlset(sqlset_name => 'SQLTUNINGSET1');
end;
/
SQL>exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('SQLTUNINGSET1','SYS',true,'PACK_SQLSET','APPS');
4. 各种查找顶级SQL的方法：
SQL> desc v$sql
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(76)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
DIRECT_READS NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(2000)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(128)
KEPT_VERSIONS NUMBER
ADDRESS RAW(8)
TYPE_CHK_HEAP RAW(8)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
FULL_PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
SERVICE VARCHAR2(64)
SERVICE_HASH NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER
CHILD_ADDRESS RAW(8)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(76)
IS_OBSOLETE VARCHAR2(1)
IS_BIND_SENSITIVE VARCHAR2(1)
IS_BIND_AWARE VARCHAR2(1)
IS_SHAREABLE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
SQL_PATCH VARCHAR2(128)
SQL_PLAN_BASELINE VARCHAR2(128)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)
TYPECHECK_MEM NUMBER
IO_CELL_OFFLOAD_ELIGIBLE_BYTES NUMBER
IO_INTERCONNECT_BYTES NUMBER
PHYSICAL_READ_REQUESTS NUMBER
PHYSICAL_READ_BYTES NUMBER
PHYSICAL_WRITE_REQUESTS NUMBER
PHYSICAL_WRITE_BYTES NUMBER
OPTIMIZED_PHY_READ_REQUESTS NUMBER
LOCKED_TOTAL NUMBER
PINNED_TOTAL NUMBER
IO_CELL_UNCOMPRESSED_BYTES NUMBER
IO_CELL_OFFLOAD_RETURNED_BYTES NUMBER
CON_ID NUMBER
IS_REOPTIMIZABLE VARCHAR2(1)
IS_RESOLVED_ADAPTIVE_PLAN VARCHAR2(1)
IM_SCANS NUMBER
IM_SCAN_BYTES_UNCOMPRESSED NUMBER
IM_SCAN_BYTES_INMEMORY NUMBER
DDL_NO_INVALIDATE VARCHAR2(1)
IS_ROLLING_INVALID VARCHAR2(1)
IS_ROLLING_REFRESH_INVALID VARCHAR2(1)
RESULT_CACHE VARCHAR2(1)
SQL_QUARANTINE VARCHAR2(128)
AVOIDED_EXECUTIONS NUMBER
SQL>
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_text like ''update%'''))
ORDER BY sql_id;
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'));
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('CPU_TIME>29000000'));--单位为10的-6次方秒
页:
[1]