【筆記】使用 plan_table

使用plan_table
@?/rdbms/admin/utlxplan.sql
create public synonym plan_table for plan_table;
grant all on plan_table to public ;
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$session to plustrace;
grant plustrace to dba with admin option;
grant plustrace to public ;

關於Autotrace幾個常用選項的說明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 報告，這是預設模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只顯示優化器執行路徑報告
SET AUTOTRACE ON STATISTICS -- 只顯示執行統計資訊
SET AUTOTRACE ON ----------------- 包含執行計劃和統計資訊
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不顯示查詢輸出

使用analyze獲得統計資料

SQL> analyze table tab1 compute statistics;
 
Table analyzed
 

檢視統計資訊

QL> select a.owner,a.table_name,a.ini_trans,a.max_trans,a.initial_extent,a.logging,a.blocks,a.buffer_pool,a.num_rows,a.avg_row_len,a.avg_space from dba_tables a where a.table_name = 'TAB1';
 
OWNER                          TABLE_NAME                      INI_TRANS  MAX_TRANS INITIAL_EXTENT LOGGING     BLOCKS BUFFER_POOL   NUM_ROWS AVG_ROW_LEN  AVG_SPACE
------------------------------ ------------------------------ ---------- ---------- -------------- ------- ---------- ----------- ---------- ----------- ----------
TEST                           TAB1                                    1        255          65536 YES              5 DEFAULT              0           0       8072
 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-588787/，如需轉載，請註明出處，否則將追究法律責任。