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

转载于:http://blog.itpub.net/28985005/viewspace-764378/