PLSQL_统计信息系列10_统计信息过旧导致程序出现性能问题

简介: 2014-11-15 Created By BaoXinjian 一、摘要 在性能的稳定数据库中,所有的job都需要在一定时间内完成 以前在一个银行系统中,突然某一个job原本在30minutes内完成的,但是在3hours之后,还在运行 排除了数据量大小变化的影响,需要查看该程序的解析计...

2014-11-15 Created By BaoXinjian

一、摘要


在性能的稳定数据库中,所有的job都需要在一定时间内完成

以前在一个银行系统中,突然某一个job原本在30minutes内完成的,但是在3hours之后,还在运行

排除了数据量大小变化的影响,需要查看该程序的解析计划是否变更,在稳定的系统中,job对应的explain plan一般通过sqlplan management固定,很少变化

所以需要查看,是否job对应的sqlplan explain是否变化

一般来说,解析计划变更也会导致此类问题

 

二、解决方式


Step1. 查看程序对应的session

select sid, sql_id from v$session where sid = 1001

 

Step2. 查看该长时间运行sql对应的解析计划是否在这段时间有过变更

select b.begin_interval_time, 
a.disk_reads_total,
a.buffer_gets_total,
a.buffer_gets_delta,
a.rows_processed_total,
a.plan_hash_value from dba_hist_sqlstat a, dba_hist_snapshot b where a.snap_id = b.snap_id
and sql_id = '466bpq7055f4c'
order by 1 desc;

 

Step3. 查看对应session目前在运行的sql text

select* from v$sql_plan

 

Step4. 查看表的统计时间

select name, last_analized from dba_tables

 

Step5. 对表进行产生统计信息

exec dbms_stats.gather_table_stats(
ownname=>'SCOT',
tabname=>'GAVIN',
estimate_percent=>3,
degree=>32,
granularity=>'GLOBAL',
cascade=>FALSE,
no_invalidate=>FALSE);

 

Step6. 该sql_id所对应的执行时间和次数

  SELECT DBMS_LOB.SUBSTR (sql_text, 100, 1) SQL_SHORT,
         tab1.sql_id,
         DBMS_LOB.getlength (sql_text) SQL_Len,
         ROUND (TOTAL_WAIT / 1000000, 2) TOTAL_WAIT_SECS, ROUND (ELAPSED_TIME_DELTA / 1000000, 2) TOTAL_TIME_SECS, TO_CHAR (BEGIN_INTERVAL_TIME, 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN') Week_Day, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, EXEC_COUNT, AVG_CPU_TIME_SECS, AVG_ELAPSED_SECS, ROUND (ELAPSED_TIME_DELTA / 1000000, 0) ELAPSED_TIME_DELTA, ROUND (AVG_ROWS_PROCESSED, 1) AVG_ROWS_PROCESSED, PLAN_HASH_VALUE, MODULE, ACTION, PARSING_SCHEMA_NAME, (SELECT username FROM dba_users WHERE user_id = PARSING_USER_ID) PARSING_USER, AVG_BUFFER_GETS, AVG_DISK_READS, AVG_IOWAIT AVG_iowai_secs, AVG_CCWAIT AVG_ccwait_secs, AVG_CLWAIT AVG_clwait_secs, AVG_APWAIT AVG_apwait_secs, AVG_PX_SERVERS, AVG_PARSE_CALLS, tab1.SNAP_ID, tab1.INSTANCE_NUMBER, AVG_CELL_UNCOMPRESSED_BYTES, AVG_DIRECT_WRITES, AVG_IO_INTERCONNECT_BYTES, AVG_IO_OFFLOAD_ELIG_BYTES, AVG_IO_OFFLOAD_RETURN_BYTES, AVG_JAVEXEC_TIME, AVG_OPTIMIZED_PHYSICAL_READS, AVG_PLSEXEC_TIME_DELTA, AVG_SORTS_DELTA, -- BIND_DATA, -- -- (Falta arreglar) listagg( (select * from table(dbms_sqltune.extract_binds(bind_data)), '#') WITHIN GROUP (ORDER BY 1)) Binds , DBMS_LOB.SUBSTR (sql_text, 2000, 1) SQL_TEXT1, DBMS_LOB.SUBSTR (sql_text, 2000, 2001) SQL_TEXT2, DBMS_LOB.SUBSTR (sql_text, 2000, 4001) SQL_TEXT3, DBMS_LOB.SUBSTR (sql_text, 2000, 6001) SQL_TEXT4, DBMS_LOB.SUBSTR (sql_text, 2000, 8001) SQL_TEXT5, DBMS_LOB.SUBSTR (sql_text, 2000, 10001) SQL_TEXT6, DBMS_LOB.SUBSTR (sql_text, 2000, 12001) SQL_TEXT7, DBMS_LOB.SUBSTR (sql_text, 2000, 14001) SQL_TEXT8, DBMS_LOB.SUBSTR (sql_text, 2000, 16001) SQL_TEXT9, DBMS_LOB.SUBSTR (sql_text, 2000, 18001) SQL_TEXT10 FROM DBA_HIST_SQLTEXT D, DBA_HIST_SNAPSHOT N, ( SELECT S.SQL_ID SQL_ID, instance_number, SUM (EXECUTIONS) EXEC_COUNT, ROUND ( (SUM (CPU_TIME_DELTA) / SUM (EXECUTIONS)) / 1000000, 4) AVG_CPU_TIME_SECS, ROUND ( (SUM (ELAPSED_TIME_DELTA) / SUM (EXECUTIONS)) / 1000000, 4) AVG_ELAPSED_SECS, SUM (ELAPSED_TIME_DELTA) ELAPSED_TIME_DELTA, ROUND ( (SUM (ROWS_PROCESSED_DELTA) / SUM (EXECUTIONS)), 4) AVG_ROWS_PROCESSED, PLAN_HASH_VALUE, MODULE, ACTION, PARSING_SCHEMA_NAME, PARSING_USER_ID, ROUND (SUM (BUFFER_GETS_DELTA) / SUM (EXECUTIONS), 4) AVG_BUFFER_GETS, ROUND (SUM (DISK_READS_DELTA) / SUM (EXECUTIONS), 0) AVG_DISK_READS, SNAP_ID, ROUND ( (SUM (IOWAIT_DELTA) / SUM (EXECUTIONS)) / 1000000, 4) AVG_IOWAIT, ROUND ( (SUM (CCWAIT_DELTA) / SUM (EXECUTIONS)) / 1000000, 4) AVG_CCWAIT, ROUND ( (SUM (CLWAIT_DELTA) / SUM (EXECUTIONS)) / 1000000, 4) AVG_CLWAIT, ROUND ( (SUM (APWAIT_DELTA) / SUM (EXECUTIONS)) / 1000000, 4) AVG_APWAIT, ROUND (SUM (PX_SERVERS_EXECS_DELTA) / SUM (EXECUTIONS), 4) AVG_PX_SERVERS, ROUND (SUM (PARSE_CALLS_DELTA) / SUM (EXECUTIONS), 4) AVG_PARSE_CALLS, ROUND (SUM (CELL_UNCOMPRESSED_BYTES_DELTA) / SUM (EXECUTIONS), 4) AVG_CELL_UNCOMPRESSED_BYTES, ROUND (SUM (DIRECT_WRITES_DELTA) / SUM (EXECUTIONS), 4) AVG_DIRECT_WRITES, ROUND (SUM (IO_INTERCONNECT_BYTES_DELTA) / SUM (EXECUTIONS), 4) AVG_IO_INTERCONNECT_BYTES, ROUND (SUM (IO_OFFLOAD_ELIG_BYTES_DELTA) / SUM (EXECUTIONS), 4) AVG_IO_OFFLOAD_ELIG_BYTES, ROUND (SUM (IO_OFFLOAD_RETURN_BYTES_DELTA) / SUM (EXECUTIONS), 4) AVG_IO_OFFLOAD_RETURN_BYTES, ROUND (SUM (JAVEXEC_TIME_DELTA) / SUM (EXECUTIONS), 4) AVG_JAVEXEC_TIME, ROUND ( SUM (OPTIMIZED_PHYSICAL_READS_DELTA) / SUM (EXECUTIONS), 4) AVG_OPTIMIZED_PHYSICAL_READS, ROUND (SUM (PLSEXEC_TIME_DELTA) / SUM (EXECUTIONS), 4) AVG_PLSEXEC_TIME_DELTA, ROUND (SUM (SORTS_DELTA) / SUM (EXECUTIONS), 4) AVG_SORTS_DELTA, SUM ( IOWAIT_DELTA + CCWAIT_DELTA + CLWAIT_DELTA + APWAIT_DELTA) TOTAL_WAIT, MAX (BIND_DATA) BIND_DATA FROM (SELECT S.*, DECODE (EXECUTIONs_DELTA, 0, 1, EXECUTIONS_DELTA) EXECUTIONS FROM DBA_HIST_SQLSTAT S) S WHERE 1 = 1 -- and ( PARSING_SCHEMA_NAME like 'EUS%' ) -- Filter by schema name GROUP BY S.SQL_ID, instance_number, S.SNAP_ID, PLAN_HASH_VALUE, MODULE, ACTION, PARSING_SCHEMA_NAME, S.INSTANCE_NUMBER, PARSING_USER_ID HAVING ROUND ( (SUM (ELAPSED_TIME_DELTA) / SUM (EXECUTIONS)) / 1000000, 4) >= 10 -- Filter >= n secs ORDER BY 5 DESC) TAB1 WHERE D.SQL_ID = TAB1.SQL_ID AND TAB1.snap_id = N.snap_id -- and upper(tab1.module) like '%PKG_BATCH_PROCESSES_SDM%' AND d.sql_id IN ('6tmcqrydfgdtc') -- AND D.SQL_ID in ( select distinct sql_id from DBA_HIST_ACTIVE_SESS_HISTORY where top_level_sql_id = '85xkhugz5kt8h' ) -- AND upper(DBMS_LOB.SUBSTR (sql_text, 100, 1)) LIKE 'INSERT%' -- AND upper(DBMS_LOB.SUBSTR (sql_text, 1000, 1)) LIKE 'SELECT%' -- AND upper(DBMS_LOB.SUBSTR (sql_text, 3000, 1)) LIKE '%INACTIVE%' AND BEGIN_INTERVAL_TIME > SYSDATE - 60 -- and BEGIN_INTERVAL_TIME between to_date('05/09/2013 07:50:00', 'DD/MM/YYYY hh24:mi:ss') AND to_date('05/09/2013 16:10:00', 'DD/MM/YYYY hh24:mi:ss') -- and BEGIN_INTERVAL_TIME >= (SELECT job_start_datetime - (2/24) FROM job_status WHERE job_name = 'RAVLDBSK' AND cob_date = (SELECT current_cob_date FROM eus_cob)) -- and BEGIN_INTERVAL_TIME <= (SELECT job_end_datetime + (2/24) FROM job_status WHERE job_name = 'RAVLDBSK' AND cob_date = (SELECT current_cob_date FROM eus_cob)) -- order by AVG_ELAPSED_SECS DESC -- order by ELAPSED_TIME_DELTA DESC ORDER BY BEGIN_INTERVAL_TIME DESC -- order by TOTAL_WAIT_SECS desc;

 

 Thanks and Regards

 

ERP技术讨论群: 288307890
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
相关文章
|
Oracle 关系型数据库
Oracle并发(CONCURREMT)收集统计信息 (文档 ID 1555451.1)
 Oracle并发(CONCURREMT)收集统计信息 (文档 ID 1555451.1)   >                    >    >  >      >  > ...
793 0
|
SQL Oracle 关系型数据库
Oracle 判断 并 手动收集 统计信息 脚本
CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS AUTHID CURRENT_USER IS BEGIN SYS.DBMS_STATS.
1106 0
|
Go 数据库 索引
第十二章——SQLServer统计信息(2)——非索引键上统计信息的影响
原文: 第十二章——SQLServer统计信息(2)——非索引键上统计信息的影响 前言:         索引对性能方面总是扮演着一个重要的角色,实际上,查询优化器首先检查谓词上的统计信息,然后才决定用什么索引。
987 0
|
Go 数据库 索引
第十二章——SQLServer统计信息(1)——创建和更新统计信息
原文: 第十二章——SQLServer统计信息(1)——创建和更新统计信息 简介: 查询的统计信息: 目前为止,已经介绍了选择索引、维护索引。
920 0
|
SQL 数据库
第十二章——SQLServer统计信息(3)——发现过期统计信息并处理
原文: 第十二章——SQLServer统计信息(3)——发现过期统计信息并处理 前言:         统计信息是关于谓词中的数据分布的主要信息源,如果不知道具体的数据分布,优化器不能获得预估的数据集,从而不能统计需要返回的数据。
889 0
|
Go 索引
第十二章——SQLServer统计信息(4)——在过滤索引上的统计信息
原文: 第十二章——SQLServer统计信息(4)——在过滤索引上的统计信息 前言:         从2008开始,引入了一个增强非聚集索引的新功能——过滤索引(filter index),可以使用带有where条件的语句来创建非聚集索引,过滤掉不需要的数据,降低索引的维护开销和存储空间,提高查询性能。
1069 0
|
关系型数据库
PLSQL_统计信息系列08_统计信息生成和还原
2015-02-01 Created By BaoXinjian 一、摘要 统计信息在重新生成后,发现并不能改善程序的性能,甚至更差的时候 Oracle提供了dbms_stat包,对统计信息进行还原 1.
746 0