ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (oracle 10G dbms_xplan的强化)

oracle实验记录 (oracle 10G dbms_xplan的强化)

原创 Linux操作系统 作者:fufuh2o 时间:2009-10-26 12:57:00 0 删除 编辑


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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426818