10G explain for 的强化
SQL> explain plan for select * from dual;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
已选择8行。
执行后 调用这个包来 查看执行计划
SQL> desc dbms_xplan;
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQLSET_NAME VARCHAR2 IN
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
PLAN_CUR REF CURSOR IN
I_FORMAT_FLAGS BINARY_INTEGER IN
FUNCTION VALIDATE_FORMAT RETURNS BOOLEAN
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HASPLANSTATS BOOLEAN IN
FORMAT VARCHAR2 IN
FORMAT_FLAGS BINARY_INTEGER OUT
10G 得到了强化
dbms_xplan.display_cursor 通过还保存在 shared pool中的 游标获得 执行计划
SQL> conn xh/a831115
已连接。
SQL> select distinct sid from v$mystat;
SID
----------
144
SQL> select count(*) from t1;
COUNT(*)
----------
10000
SQL>
SQL> select sql_hash_value,PREV_HASH_VALUE from v$session where sid=144;
SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
0 4235652837
SQL> select sql_id,child_number from v$sql where hash_value=4235652837;
SQL_ID CHILD_NUMBER
------------- ------------
5bc0v4my7dvr5 0
SQL> select plan_table_output from table(DBMS_XPLAN.DISPLAY_CURSOR('5bc0v4my7dvr
5',0,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 6 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| T1 | 10000 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
已选择25行。
SQL>
SQL> alter system flush shared_pool; 刷新shared pool让 cursor老旧
系统已更改。
SQL> select plan_table_output from table(DBMS_XPLAN.DISPLAY_CURSOR('5bc0v4my7dvr
5',0,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: 5bc0v4my7dvr5, child number: 0 cannot be found
通过awr报告来获取
比如awr中 这句
SQL> @ F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql生成awr报告
6aq34nj2zb2n7 select col#, grantee#, privilege#, max(mod(nvl(option$, 0), 2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#
用 SQL_ID 6aq34nj2zb2n7
SQL> select * from table(dbms_xplan.display_awr('6aq34nj2zb2n7'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 6aq34nj2zb2n7
--------------------
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
Plan hash value: 2874733959
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | TABLE ACCESS BY INDEX ROWID| OBJAUTH$ |
| 3 | INDEX RANGE SCAN | I_OBJAUTH1 |
---------------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
已选择21行。
SQL> alter system flush shared_pool;～～～刷新shared pool也可以
系统已更改。
SQL> select * from table(dbms_xplan.display_awr('6aq34nj2zb2n7'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 6aq34nj2zb2n7
--------------------
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
Plan hash value: 2874733959
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | TABLE ACCESS BY INDEX ROWID| OBJAUTH$ |
| 3 | INDEX RANGE SCAN | I_OBJAUTH1 |
---------------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
已选择21行。
SQL>
关于session 与process
通常情况下是一个 session对应一个 server processs，但SErVER PORCESSS 可以对应多个session
SQL> conn xh/a831115
已连接。
SQL> select distinct sid from v$mystat;
SID
----------
144
SQL> select username, sid, serial#, server, paddr, status from v$session where s
id=144;
USERNAME SID SERIAL# SERVER PADDR STATUS
------------------------------ ---------- ---------- --------- -------- --------
XH 144 27 DEDICATED 20E4CC3C INACTIVE
SQL> select program ,addr from v$process where addr=(select paddr from v$session
where sid=144);
PROGRAM ADDR
---------------------------------------------------------------- --------
ORACLE.EXE (SHAD) 20E4CC3C
SQL> select sid from v$session where paddr='20E4CC3C';
SID
----------
144
SQL> set autotrace on
SQL> select sid from v$session where paddr='20E4CC3C';
SID
----------
144
154
可以看 到 开启autotrace时候 一个process对应 2个 session