oracle實驗記錄 (oracle 10G dbms_xplan的強化)

fufuh2o發表於2009-10-26
Oracle


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

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

相關文章