sql tuning set
sql tuning set是效能調整中重要的一個環節。以下我們先看該功能的使用,再來說明其用途。
(1)建立:
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 22 21:48:06 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn scott/scott
Connected.
SQL> begin
2 dbms_sqltune.create_sqlset(sqlset_name=>'my_sql_tuningset',
3 description=>'i/o intensive workload');
4 end;
5 /
PL/SQL procedure successfully completed.
(2)顯示:
SQL>select * from table(dbms_sqltune.select_sqlset('my_sql_tuningset','(disk_reads/buffer_gets)>=0.75'));
no rows selected
SQL> select * from dba_sqlset;
ID NAME OWNER DESCRIPTI CREATED LAST_MODI STATEMENT_COUNT
---------- ---------------------- -------------- --------------------------- ---------------- --------------------- ---------------------------
1 my_seq_tuning_set SCOTT I/O intensive workload 22-DEC-12 22-DEC-12 0
2 my_sql_tuningset SCOTT i/o intensive workload 22-DEC-12 22-DEC-12 0
注:ID為1的記錄是以前建立的。
這時,dba_sqlset_statements和dba_sqlset_binds表中並沒有相關記錄
(3)刪除:
SQL> begin
2 dbms_sqltune.drop_sqlset(sqlset_name=>'my_seq_tuning_set');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from dba_sqlset;
--再次查詢
ID NAME OWNER DESCRIPTI CREATED LAST_MODI STATEMENT_COUNT
---------- ---------------------- -------------- --------------------------- ---------------- --------------------- ---------------------------
2 my_sql_tuningset SCOTT i/o intensive workload 22-DEC-12 22-DEC-12 0
(4)載入:
load_sqlset過程將sql語句放入sql tuning set(簡稱STS)中。
可以放入STS的資源包括負載報告(workload repository)、其他STS或者共享sql區(shared SQL area)。
對於負載報告和STS,預定義的表函式可以從資源表中選擇列並放入新的STS中。
下面這個例子中,呼叫名字為peak baseline的AWR基線載入到my_sql_tuning_set中。
資料按照花費時間排序,只選擇前三十個sql語句。
未操作:
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;--宣告遊標型別
BEGIN
OPEN baseline_cursor FOR--將負載報告內容放入該遊標中
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
'peak baseline',NULL, NULL,'elapsed_time',NULL, NULL, NULL,30)) p;--
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_sql_tuningset',
populate_cursor => baseline_cursor);--將負載報告的內容放入已經定義的STS中
END;
/
修改:
BEGIN
DBMS_SQLTUNE.DELETE_SQLSET(
sqlset_name => 'my_sql_tuning_set',
basic_filter => 'executions < 50');
END;
/
轉換:
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(
table_name => 'my_10g_staging_table',
schema_name => 'dba1',
db_version => DBMS_SQLTUNE.STS_STGTAB_10_2_VERSION );
END;
/
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(
sqlset_name => 'my_sts',
sqlset_owner => 'hr',
staging_table_name => 'my_10g_staging_table',
staging_schema_owner => 'dba1',
db_version => DBMS_SQLTUNE.STS_STGTAB_10_2_VERSION );
END;
/
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%',
replace => TRUE,
staging_table_name => 'my_10g_staging_table');
END;
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26451536/viewspace-753080/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sql tuning set/sql tuning advisor(待完善)SQL
- SPA_SQL Performance Analyzer_SQL Tuning SetSQLORM
- oracle10g_10.2.0.1_sql tuning_set autotrace配置OracleSQL
- sql tuningSQL
- STS(SQL Tuning Set)匯入匯出過程及錯誤處理SQL
- 熟悉SQL tuningSQL
- sql tuning task和sql profileSQL
- Automatic SQL Tuning and SQL ProfilesSQL
- Oracle OCP 1Z0-053 Q207(SQL Tuning Set&Analyzer)OracleSQL
- 【筆記】SQL tuning筆記SQL
- Oracle SQL Perfomance TuningOracleSQL
- SQL TUNING ADVISORSQL
- Automatic SQL Tuning 原理SQL
- sql tuning之變通SQL
- set autotrace in SQL*PlusSQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- 快速SQL TUNING——1分鐘搞定超慢SQLSQL
- oracle實用sql(3)--sql tuning advisorOracleSQL
- SQL Tuning Advisor簡介SQL
- sql tuning 第一章SQL
- 轉貼_push_subq_sql tuningSQL
- oracle sql tuning 14 --10046OracleSQL
- 二個SQL tuning例子(使用case)SQL
- 【筆記】sql tuning advidor筆記SQL
- ORACLE SQL Tuning Health-Check(SQLHC)OracleSQL
- sql tuning advisor(STA) 建議 建立sql profileSQL
- Oracle SQL優化之sql tuning advisor(STA)OracleSQL優化
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- Sql Tuning Advisor 使用方法SQL
- 深入瞭解SQL Tuning AdvisorSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- SQL Tuning Advisor使用例項SQL
- oracle sql tuning 2--調優工具OracleSQL
- SQL*PLUS命令, set命令大全SQL
- Oracle set unused的用法.sqlOracleSQL
- oracle SQL最佳化器SQL Tuning Advisor (STA)OracleSQL
- SQL Access Advisor、SQL Tuning Advisor 測試SQL