oracle資料庫：耗cpu sql語句優化

教一個朋友學習oracle執行計劃如何選擇執行路徑

用如下語句查詢耗CPU最多的10條語句
 
select * from
   (select
    s.SQL_ID,
    sum(s.CPU_TIME_DELTA),
    sum(s.DISK_READS_DELTA),
    count(*)
    from DBA_HIST_SQLSTAT s
   group by s.SQL_ID
   order by sum(s.CPU_TIME_DELTA) desc
   ) 
where rownum < 11

SQL_ID        SUM(S.CPU_TIME_DELTA) SUM(S.DISK_READS_DELTA)   COUNT(*)
------------- --------------------- ----------------------- ----------
fb9s6t3xm9tvr            2759607595                       2        182
85j0bu1w40bck             585125959                  125790        197
bdtayq9gqwgcd             512783029                    2760        259
5jqycjn6350yf             469531232                  222602         15
b6usrg82hwsa3             441894821                 3900754         11
ab1a9mnnx09tz             357513338                 5335765         16
ct6ghwmxspjr6             323975234                 4460761          8
bwgq25s4b8fhn             293460407                 8266772         13
cdhvvs3scp62k             268952962                    9675        275
4aq1jam9gr5t0             268676518                      20         13

10 rows selected

已sql-id='fb9s6t3xm9tvr '查詢sql文字：

SQL> select sql_text from DBA_HIST_SQLTEXT where sql_id='fb9s6t3xm9tvr';

SQL_TEXT
--------------------------------------------------------------------------------
Select Command_SEQ,Command_Type,Parm_Info,App_Info From T_Java2C_Command Where S
tatus = '0'

檢視sql語句的執行計劃：

SQL> explain plan for Select Command_SEQ,Command_Type,Parm_Info,App_Info From T_Java2C_Command Where status=’0‘;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4203371258

--------------------------------------------------------------------------------
------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24957 | 584K| 140 (2)| 00:0
0:02 |

|* 1 | TABLE ACCESS FULL| T_JAVA2C_COMMAND | 24957 | 584K| 140 (2)| 00:0
0:02 |

--------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

1 - filter(("STATUS")=0)

Note
-----
- 'PLAN_TABLE' is old version

17 rows selected.

我們看到oracle走了全表掃描。接下來看看錶'T_JAVA2C_COMMAND'是否建立了索引；

SQL> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from dba_indexes where TABLE_NAME='T_JAVA2C_COMMAND';

看到表沒有建立索引。我們給表建立索引。

SQL> create index ind_T_Java2C_Com on T_Java2C_Command(status) nologging;

Index created.

現在查詢索引情況：

SQL> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from dba_indexes where TABLE_NAME='T_JAVA2C_COMMAND';

OWNER INDEX_NAME
------------------------------ ------------------------------
INDEX_TYPE TABLE_OWNER
--------------------------- ------------------------------
TABLE_NAME
------------------------------
BJHQMSS IND_T_JAVA2C_COM
NORMAL BJHQMSS
T_JAVA2C_COMMAND

在查詢oracle執行計劃情況。
SQL>  explain plan for Select Command_SEQ,Command_Type,Parm_Info,App_Info From T_Java2C_Command Where status='0';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1521489106

--------------------------------------------------------------------------------
----------------

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%
CPU)| Time     |

--------------------------------------------------------------------------------
----------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     4 |    96 |     2
 (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_JAVA2C_COMMAND |     4 |    96 |     2
 (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T_JAVA2C_COM |     4 |       |     1
 (0)| 00:00:01 |

--------------------------------------------------------------------------------
----------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"='0')

Note
-----
   - 'PLAN_TABLE' is old version

18 rows selected.

我們看到cpu成本從140降低2.oracle好智慧啊。

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