sql profile

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4      这两天一直在观察东莞数据库，因为一条 sql 语句占用大量的 cpu ，查看该语句的执行计划走的是全表扫描，该表有 60 多万行，不重复记录有 40 多万行，该条语句每次查询得到一条记录。通过检查发现该列上并没建立索引，建立索引再次查看执行计划，还是走的全表扫描。

SQL> select subContentId, title, setNO, attrName, subContentType, fileSize, playUrl, fileFormat, fileExtension, dlFlag, drmType, language, published_At, publishArea, publishCompany, updated_At, created_At from cms_subcontent where srcFlag=3 and status=6 and RIGINALID=21190001100010908042220020270829 order by SETNO;

Execution Plan

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

Plan hash value: 4274525815

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

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

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

|   0 | SELECT STATEMENT   |                |     1 |   231 |  7990   (2)| 00:01:36 |

|   1 |  SORT ORDER BY     |                |     1 |   231 |  7990   (2)| 00:01:36 |

|*  2 |   TABLE ACCESS FULL| CMS_SUBCONTENT |     1 |   231 |  7989   (2)| 00:01:36 |

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

Predicate Information (identified by operation id):

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

   2 - filter("STATUS"=6 AND "SRCFLAG"=3 AND

              TO_NUMBER("ORIGINALID")=21190001100010908042220020270829)

至此没想到其他好的方法了。借助sql profile来看看（记录下大概的过程，没保存完整的操作过程）

创建任务：

declare

  my_task_name varchar2(30);

  my_sqltext clob;

begin

  my_sqltext :='select subContentId, title, setNO, attrName, subContentType,

  fileSize, playUrl, fileFormat, fileExtension, dlFlag, drmType, language,

  published_At, publishArea, publishCompany, updated_At,

  created_At from cms_subcontent

  where srcFlag=3 and status=6 and RIGINALID=21190001100010908042220020270829 order by SETNO';

  my_task_name := dbms_sqltune.create_tuning_task(

                                     sql_text => my_sqltext,

                                     user_name=> user,

                                     scope => 'COMPREHENSIVE',

                                     time_limit =>3600,

                                     task_name =>'sql_tuning_test',

                                     description => 'Tuning Task');

end;

/

2.执行任务

exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

3.查看任务状态

select * from user_advisor_tasks where task_name='sql_tuning_test';

4.得到执行任务结果

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;

。。。。。。。。。。。。。。。。。。。。。。。。

- Index Finding (see explain plans section below)

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

  The execution plan of this statement can be improved by creating one or more

  indices.

 

  Recommendation (estimated benefit: 100%)

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

  - Consider running the Access Advisor to improve the physical schema design

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')

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

    or creating the recommended index.

    create index GDDEMOCMS.IDX$$_35F20001 on

    GDDEMOCMS.CMS_SUBCONTENT(TO_NUMBER('ORIGINALID'),'STATUS','SRCFLAG');

 

  Rationale

  ---------

    Creating the recommended indices significantly improves the execution plan

    of this statement. However, it might be preferable to run "Access Advisor"

    using a representative SQL workload as opposed to a single statement. This

    will allow to get comprehensive index recommendations which takes into

    account index maintenance overhead and additional space consumption.

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')

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

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

 

在ORIGINALID列中存在一隐式转换导致索引不能使用，在表中ORIGINALID为varcha2类型，查询语句中直接为数字。

5.删除任务

exec dbms_sqltune.drop_tuning_task('sql_tuning_test');

 

create index ORIGINALID_NUMBER on cms_subcontent(to_number(originalid));

 

SQL> select subContentId, title, setNO, attrName, subContentType, fileSize, playUrl, fileFormat, fileExtension, dlFlag, drmType, language, published_At, publishArea, publishCompany, updated_At, created_At from cms_subcontent where srcFlag=3 and status=6 and RIGINALID=21190001100010908042220020270829 order by SETNO;

 

Execution Plan

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

Plan hash value: 1514254587

 

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

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

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

|   0 | SELECT STATEMENT             |                   |     1 |   231 |     5  (20)| 00:00:01 |

|   1 |  SORT ORDER BY               |                   |     1 |   231 |     5  (20)| 00:00:01 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| CMS_SUBCONTENT    |     1 |   231 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | ORIGINALID_NUMBER |     2 |       |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("STATUS"=6 AND "SRCFLAG"=3)

   3 - access(TO_NUMBER("ORIGINALID")=21190001100010908042220020270829)

改变很大哦

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22779291/viewspace-692822/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22779291/viewspace-692822/