- 论坛徽章:
- 0
|
oracle显示所查询的解释计划和所用资源分析工具.
配置autotarce:
1. 登陆oracle
-bash-3.00$ sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Sun Mar 9 15:45:14 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
2.执行utlxplan和plustrce两个sqlp脚本
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
Role dropped.
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$session to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL> CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
create public synonym plan_table for plan_table
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop public synonym plan_table;
Synonym dropped.
SQL> create public synonym plan_table for plan_table;
Synonym created.
SQL> grant all on plan_table to public;
Grant succeeded.
SQL> grant plustrace to public;
Grant succeeded.
3.连接测试
SQL> conn sys/hsmail as sysdba
Connected.
SQL> set autotrace on
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL> select username from dba_users;
USERNAME
------------------------------------------------------------
CITY
HSMAIL
MDDATA
DIP
SCOTT
TSMSYS
DBSNMP
SYSMAN
MDSYS
ORDSYS
CTXSYS
USERNAME
------------------------------------------------------------
ANONYMOUS
EXFSYS
DMSYS
WMSYS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
MGMT_VIEW
SYS
SYSTEM
USERNAME
------------------------------------------------------------
OUTLN
23 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 96174755
--------------------------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 8 | 632 |
27 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 8 | 632 |
27 (12)| 00:00:01 |
|* 2 | HASH JOIN | | 8 | 608 |
21 (15)| 00:00:01 |
|* 3 | HASH JOIN | | 8 | 584 |
14 (15)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 8 | 560 |
12 (17)| 00:00:01 |
|* 5 | HASH JOIN | | 8 | 360 |
9 (12)| 00:00:01 |
|* 6 | HASH JOIN | | 8 | 344 |
7 (15)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN| | 1 | 16 |
4 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | PROFILE$ | 1 | 8 |
2 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 1 | 8 |
2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | PROFILE$ | 1 | 8 |
2 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | USER$ | 23 | 621 |
2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | PROFNAME$ | 1 | 2 |
2 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | RESOURCE_GROUP_MAPPING$ | 2 | 50 |
2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | USER_ASTATUS_MAP | 9 | 27 |
2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | TS$ | 16 | 48 |
6 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | TS$ | 16 | 48 |
6 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("U"."TEMPTS#"="TTS"."TS#")
2 - access("U"."DATATS#"="DTS"."TS#")
3 - access("U"."ASTATUS"="M"."STATUS#")
4 - access("CGM"."VALUE"(+)="U"."NAME")
5 - access("U"."RESOURCE$"="P"."PROFILE#")
6 - access("U"."RESOURCE$"="PR"."PROFILE#")
8 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
10 - filter("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
11 - filter("U"."TYPE#"=1)
13 - filter("CGM"."STATUS"(+)='ACTIVE' AND "CGM"."ATTRIBUTE"(+)='ORACLE_USER')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
66 consistent gets
0 physical reads
0 redo size
819 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
23 rows processed
SQL>
SQL> !uname -a
SunOS ORACLE 5.10 Generic_118855-33 i86pc i386 i86pc
SQL> select * from v$version where rownum<3;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
Execution Plan
----------------------------------------------------------
Plan hash value: 1517457201
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | FIXED TABLE FULL| X$VERSION | 1 | 47 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<3)
2 - filter("INST_ID"=USERENV('INSTANCE'))
Statistics
----------------------------------------------------------
60 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> |
|
免费注册
发表于 2008-03-09 15:54