第二季度自主sql优化学习笔记

合集下载

相关主题

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

湖南电信sql语句优化学习笔记

一、首先通过高耗sql语句查询出所需要优化的sql：

--Elapsed_Time语句

select *

from (selectnvl((sqt.elap / 1000000), to_number(null)),

nvl((sqt.cput / 1000000), to_number(null)),

sqt.exec,

decode(sqt.exec,

0,

to_number(null),

(sqt.elap / sqt.exec / 1000000)),

(10000 *2*

(sqt.elap/1000000 / (SELECTsum(e.VALUE) - sum(b.value) FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e

WHERE B.SNAP_ID = &beg_snap_id

AND E.SNAP_ID = &end_snap_id

AND B.DBID = &dbid

AND E.DBID = &dbid

AND B.INSTANCE_NUMBER = &instance_number

AND E.INSTANCE_NUMBER = &instance_number

and e.STAT_NAME = 'DB time'

and b.stat_name = 'DB time'))) norm_val,

sqt.sql_id,

to_clob(decode(sqt.module,

null,

'Module: ' || sqt.module)),

nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** '))

from (select sql_id,

max(module) module,

sum(elapsed_time_delta) elap,

sum(cpu_time_delta) cput,

sum(executions_delta) exec

from dba_hist_sqlstat

where dbid = &dbid

and instance_number = &instance_number

and&beg_snap_id

and snap_id<= &end_snap_id

groupby sql_id) sqt,

dba_hist_sqltextst

where st.sql_id(+) = sqt.sql_id

and st.dbid(+) = &dbid

orderbynvl(sqt.elap, -1) desc, sqt.sql_id)

whererownum<65

and (rownum<= 10or norm_val>1);

--提出cpu比较高的语句