提到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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-616048/