【正文】 INDEX_RS_ASC(@SEL$1 T2@SEL$1 (T2.STATUS))39。, 39。END_OUTLINE_DATA39。)。 (cl_sql_text, v_hhint, 39。SQLPROFILE_1304vvhkfctzq39。, force_match = true, replace = true)。end。/獲取PFOFILE的名字SELECTname,created,category,sql_Textfromdba_sql_profilesORDERBYcreatedDESC。刪除PFOFILEBEGIN (name=39。PROFILE_NAME39。)。END。/ 獲取OUTLINE HINT的信息SELECT outline_hints FROM dba_sql_profiles sql_profiles, $ATTR sql_attr WHERE = AND = 39。SQLPROFILE_1304vvhkfctzq39。 ORDER BY ASC。STAIn order to execute Sql Tuning Adviser API, one must be granted with “ADVISER” role.grant adviser to user。– for a specific statement from AWRSET SERVEROUTPUT ON Tuning task created for specific a statement from the AWR.DECLAREl_sql_tune_task_id VARCHAR2(100)。BEGINl_sql_tune_task_id := (begin_snap = 764,end_snap = 938,sql_id = 39。19v5guvsgcd1v39。,scope = ,time_limit = 60,task_name = 39。19v5guvsgcd1v_AWR_tuning_task39。,description = 39。Tuning task for statement 19v5guvsgcd1v in AWR.39。)。(39。l_sql_tune_task_id: 39。 || l_sql_tune_task_id)。END。– or for a specific statement from Shared Library CacheDECLAREl_sql_tune_task_id VARCHAR2(100)。BEGINl_sql_tune_task_id := (sql_id = 39。fhahkc71k304u39。,scope = ,time_limit = 60,task_name = 39。fhahkc71k304u_tuning_task39。,description = 39。Tuning task for statement fhahkc71k304u.39。)。(39。l_sql_tune_task_id: 39。 || l_sql_tune_task_id)。END。/ Interrupt and resume a tuning task.EXEC (task_name = 39。emp_dept_tuning_task39。)。EXEC (task_name = 39。emp_dept_tuning_task39。)。 Cancel a tuning task.EXEC (task_name = 39。emp_dept_tuning_task39。)。 Reset a tuning task allowing it to be reexecuted.EXEC (task_name = 39。emp_dept_tuning_task39。)。execute the tuning taskEXEC (task_name = 39。fhahkc71k304u_AWR_tuning_task39。)。report tuning task findingsSET LONG 100000。SET PAGESIZE 1000SET LINESIZE 300SELECT (39。fhahkc71k304u_AWR_tuning_task39。) AS remendations FROM dual。SET PAGESIZE 24accept remendationsexecute (task_name =39。fhahkc71k304u_AWR_tuning_task39。, replace = TRUE)。useful viewsDBA_ADVISOR_TASKSDBA_ADVISOR_FINDINGSDBA_ADVISOR_RECOMMENDATIONSDBA_ADVISOR_RATIONALEDBA_SQLTUNE_STATISTICSDBA_SQLTUNE_BINDSDBA_SQLTUNE_PLANSDBA_SQLSETDBA_SQLSET_BINDSDBA_SQLSET_STATEMENTSDBA_SQLSET_REFERENCESDBA_SQL_PROFILESV$SQLV$SQLAREAV$ACTIVE_SESSION_HISTORY收集統(tǒng)計信息exec (ownname = 39。SYS39。, tabname = 39。T239。, estimate_percent = 100, cascade = true, method_opt = 39。for all columns size 139。 )。顯示執(zhí)行計劃select * from table()。select * from table((null,null,39。ALLSTATS LAST39。))。select * from table((39。a7tgurqg403wp39。, null, null, 39。ALL39。))。顯示執(zhí)行計劃的outlineselect * from table((39。sql_id39。, null, 39。outline39。))。顯示query block(qb_name)select*fromtable((null,null,39。typical alias rows bytes cost39。))。柱狀圖刪除柱狀圖declare srec 。 m_distt number。 m_density number。 m_nullt number。 m_avgclen number。 n_array 。 begin ( ownname = user, tabname = 39。t139。, colname = 39。n139。, distt = m_distt, density = m_density, nullt = m_nullt, srec = srec, avgclen = m_avgclen )。 := null。 := ( (), () )。 := 2。 (srec, )。 m_density := 1/m_distt。 ( ownname = user, tabname = 39。t139。, colname = 39。n139。, distt = m_distt, density = m_density, nullt = m_nullt, srec = srec, avgclen = m_avgclen )。exception when others then raise。 should handle div/0end。/表碎片整理Oracle 10g之前一般使用alter table table_name move tablespace，然后rebuild索引，因?yàn)閙ove會導(dǎo)致rowid改變，從而原來的索引失效。查看哪些表在空間上存在問題，可以查看dba_tables視圖的avg_space列，這列顯示的是數(shù)據(jù)庫塊的平均空閑空間大小，我們在建表的時候可以通過制定PCTFREE參數(shù)來設(shè)置每個數(shù)據(jù)塊中保留空間的百分比，這部分空間用于因更新塊內(nèi)的行而導(dǎo)致的增長，默認(rèn)值是10%，也就是819 bytes左右（塊大小為8KB）。如果avg_space大于2KB，可能重整空間會有意義，這個值太大說明空間利用率較低，塊空閑較多。我們調(diào)整空間的主要目的是降低高水位線（HIGH WATER MARK），使掃描的表塊數(shù)變小，從而提高效率。Oracle 10g之后alter table table_name enable row movement。alter table table_name shrink spac