【AUTOTRACE】SQL優化的重要工具--AUTOTRACE
提到SQL優化,不能不提AUTOTRACE的強大功能。使用起來非常便捷,不過在是使用之前,需要做一些配置的工作。簡要的描述一下這個過程,供沒有使用過的朋友參考。
1.使用sys使用者執行plustrce指令碼
sys@ora10g> @?/sqlplus/admin/plustrce
sys@ora10g> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
sys@ora10g> create role plustrace;
Role created.
sys@ora10g>
sys@ora10g> grant select on v_$sesstat to plustrace;
Grant succeeded.
sys@ora10g> grant select on v_$statname to plustrace;
Grant succeeded.
sys@ora10g> grant select on v_$mystat to plustrace;
Grant succeeded.
sys@ora10g> grant plustrace to dba with admin option;
Grant succeeded.
sys@ora10g>
sys@ora10g> set echo off
2.將plustrace授權給public使用者,以便保證所有使用者都有權使用autotrace功能
sys@ora10g> grant plustrace to public;
Grant succeeded.
3.連線到具體的使用者中,這裡是sec使用者
sys@ora10g> conn sec/sec
Connected.
4.使用“set autotrace”檢視autotrace的命令提示
sec@ora10g> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
5.這裡演示一種設定方法
sec@ora10g> set autotrace traceonly
sec@ora10g> select * from cat;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3442307057
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 646 | 47804 | 33 (4)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | OBJ$ | 1324 | 97976 | 33 (4)| 00:00:01 |
|* 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 12 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=6 OR
"O"."TYPE#"=2 AND NOT EXISTS (SELECT /*+ */ 0 FROM "SYS"."TAB$" "T"
WHERE "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)))
2 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."LINKNAME" IS
NULL)
3 - filter(BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)
4 - access("T"."OBJ#"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
207 consistent gets
0 physical reads
0 redo size
876 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
更多的有趣內容,請您慢慢挖掘。
-- The End --
1.使用sys使用者執行plustrce指令碼
sys@ora10g> @?/sqlplus/admin/plustrce
sys@ora10g> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
sys@ora10g> create role plustrace;
Role created.
sys@ora10g>
sys@ora10g> grant select on v_$sesstat to plustrace;
Grant succeeded.
sys@ora10g> grant select on v_$statname to plustrace;
Grant succeeded.
sys@ora10g> grant select on v_$mystat to plustrace;
Grant succeeded.
sys@ora10g> grant plustrace to dba with admin option;
Grant succeeded.
sys@ora10g>
sys@ora10g> set echo off
2.將plustrace授權給public使用者,以便保證所有使用者都有權使用autotrace功能
sys@ora10g> grant plustrace to public;
Grant succeeded.
3.連線到具體的使用者中,這裡是sec使用者
sys@ora10g> conn sec/sec
Connected.
4.使用“set autotrace”檢視autotrace的命令提示
sec@ora10g> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
5.這裡演示一種設定方法
sec@ora10g> set autotrace traceonly
sec@ora10g> select * from cat;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3442307057
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 646 | 47804 | 33 (4)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | OBJ$ | 1324 | 97976 | 33 (4)| 00:00:01 |
|* 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 12 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=6 OR
"O"."TYPE#"=2 AND NOT EXISTS (SELECT /*+ */ 0 FROM "SYS"."TAB$" "T"
WHERE "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)))
2 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."LINKNAME" IS
NULL)
3 - filter(BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)
4 - access("T"."OBJ#"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
207 consistent gets
0 physical reads
0 redo size
876 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
更多的有趣內容,請您慢慢挖掘。
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-616048/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【優化】ORACLE set autotrace in SQL*Plus優化OracleSQL
- Sql最佳化(十八) 調優工具(1)set autotrace和excute plan tableSQL
- set autotrace in SQL*PlusSQL
- 設定sql plus 的autotraceSQL
- Oracle最佳化工具——AutoTraceOracle
- Oracle AutotraceOracle
- Autotrace工具使用——小工具,大用場
- Autotrace的配置與分析
- SQL效能的度量 - 語句級別的SQL跟蹤autotraceSQL
- EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plansAISQL
- 檢視sql執行計劃--set autotraceSQL
- 關於ORACLE AUTOTRACEOracle
- ORACLE 使用AUTOTRACE功能Oracle
- Autotrace 用法總結
- Autotrace的設定與使用
- (轉)oracle效能工具包Explain plan、Autotrace、TkprofOracleAI
- set autotrace on [configure]
- ORACLE 10G AUTOTRACEOracle 10g
- 檢視sql執行計劃--set autotrace [final]SQL
- Oracle 9I 下的AutoTraceOracle
- 設定autotrace全過程
- 安裝並設定autotrace
- Set autotrace命令及解釋
- AutoTRACE是分析SQL的執行計劃,執行效率的一個非常簡單方便的工具SQL
- oracle10g_10.2.0.1_sql tuning_set autotrace配置OracleSQL
- 自動設定autotrace環境
- oracle之autotrace統計資訊分析Oracle
- oracle 9i 開啟autotrace onOracle
- 核心表AUTOTRACE結果出錯
- 10g 中使用toad的sql編輯的autotrace的問題?SQL
- set autotrace的用法和含意及區別
- 如何啟用sqlplus的AutoTrace功能SQL
- 【SQL優化】SQL優化工具SQL優化
- Oracle 開啟SQL跟蹤執行SET AUTOTRACE ON命令時出錯OracleSQL
- 獲取執行計劃之Autotrace
- 開啟執行計劃set autotrace on
- EXPLAIN PLAN FOR 和 SET AUTOTRACE之間的差別AI
- 設定autotrace的報錯問題解決