通过ADDM报告完成性能优化实例

最新推荐文章于 2021-04-10 10:31:29 发布
最新推荐文章于 2021-04-10 10:31:29 发布
阅读量93 收藏
点赞数

今天用ADDM生成的报告发现有几个表存在较严重的segment fragment，检查发现是统计信息(statistics collection)太旧.进一步检查发现是对象收集统计信息被锁,无法收集统计信息.优化过程如下：

SQL> SELECT a.num_rows,a.avg_row_len FROM dba_tables a WHERE a.table_name='ZDRYJCXX';

NUM_ROWS AVG_ROW_LEN

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

3015344 396 --统计信息行数

SQL> select count(*) num_rows from zdry.zdryjcxx;

NUM_ROWS

----------

909548 --实际行数

SQL> exec dbms_stats.gather_table_stats(ZDRY,'ZDRYJCXX');

BEGIN dbms_stats.gather_table_stats(ZDRY,'ZDRYJCXX'); END;

*

第 1 行出现错误:

ORA-06550: 第 1 行, 第 37 列:

PLS-00201: 必须声明标识符 'ZDRY'

ORA-06550: 第 1 行, 第 7 列:

PL/SQL: Statement ignored

SQL> exec dbms_stats.gather_table_stats('ZDRY','ZDRYJCXX');

BEGIN dbms_stats.gather_table_stats('ZDRY','ZDRYJCXX'); END;

*

第 1 行出现错误:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: 在 "SYS.DBMS_STATS", line 13056

ORA-06512: 在 "SYS.DBMS_STATS", line 13076

ORA-06512: 在 line 1

SQL> show parameter statistics

NAME TYPE VALUE

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

statistics_level string TYPICAL

timed_os_statistics integer 0

timed_statistics boolean TRUE

SQL> exec dbms_stats.gather_table_stats('ZDRY','ZDRYJCXX',cascade=>true)

BEGIN dbms_stats.gather_table_stats('ZDRY','ZDRYJCXX',cascade=>true); END;

*

第 1 行出现错误:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: 在 "SYS.DBMS_STATS", line 13056

ORA-06512: 在 "SYS.DBMS_STATS", line 13076

ORA-06512: 在 line 1

SQL> exec dbms_stats.gather_table_stats('ZDRY','ZDRYJCXX')

BEGIN dbms_stats.gather_table_stats('ZDRY','ZDRYJCXX'); END;

*

第 1 行出现错误:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: 在 "SYS.DBMS_STATS", line 13056

ORA-06512: 在 "SYS.DBMS_STATS", line 13076

ORA-06512: 在 line 1

SQL> exec dbms_stats.unlock_table_stats('ZDRY','ZDRYJCXX');

--解锁统计信息

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats('ZDRY','ZDRYJCXX')

PL/SQL 过程已成功完成。

SQL> SELECT a.num_rows,a.avg_row_len FROM dba_tables a WHERE a.table_name='ZDRYJCXX';

NUM_ROWS AVG_ROW_LEN

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

909548 397

SQL> select count(*) num_rows from zdry.zdryjcxx;

NUM_ROWS

----------

909548 --收集statistics后行数一样

SQL> select /*+ rule */ a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) "MB",

2 round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED from dba_segments a, dba_tables b

3 where a.owner=b.owner and a.owner not like 'SYS%' and a.segment_name = b.table_name

4 and a.segment_type='TABLE' group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)

5 having round(bytes/1024/1024,0) >100

6 order by round(bytes/1024/1024,0) desc;

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE MB WASTED

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

ZDRY

ZDRYJCXX

TABLE 408 64

ZDRY

ZDRYCKYJCZFK

TABLE 144 -613

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE MB WASTED

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

SQL> alter table zdryjcxx shrink space;

alter table zdryjcxx shrink space

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> alter table zdry.zdryjcxx shrink space;

alter table zdry.zdryjcxx shrink space

*

第 1 行出现错误:

ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table zdry.zdryjcxx enable row movement;

表已更改。

SQL> alter table zdry.zdryjcxx shrink space;

表已更改。

SQL> select /*+ rule */ a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) "MB",

2 round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED from dba_segments a, dba_tables b

3 where a.owner=b.owner and a.owner not like 'SYS%' and a.segment_name = b.table_name

4 and a.segment_type='TABLE' group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)

5 having round(bytes/1024/1024,0) >100

6 order by round(bytes/1024/1024,0) desc;

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE MB WASTED

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

ZDRY

ZDRYJCXX

TABLE 408 64

ZDRY

ZDRYCKYJCZFK

TABLE 144 -613

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE MB WASTED

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

SQL> alter table zdry.zdryjcxx shrink space;

表已更改。

SQL> alter table zdry.zdryjcxx shrink space;

表已更改。

SQL> select /*+ rule */ a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) "MB",

2 round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED from dba_segments a, dba_tables b

3 where a.owner=b.owner and a.owner not like 'SYS%' and a.segment_name = b.table_name

4 and a.segment_type='TABLE' group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)

5 having round(bytes/1024/1024,0) >100

6 order by round(bytes/1024/1024,0) desc;

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE MB WASTED

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

ZDRY

ZDRYJCXX

TABLE 408 64

--试了几次shrink space优化后大小都没有变化 。说明其实不存在segment fragment, 是由于统计信息陈旧导致optimizer误判。

ZDRY

ZDRYCKYJCZFK

TABLE 144 -613

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE MB WASTED

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

SQL> alter table zdry.zdryjcxx disable row movement;

表已更改。

SQL> select /*+ rule */ a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) "MB",

2 round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED from dba_segments a, dba_tables b

3 where a.owner=b.owner and a.owner not like 'SYS%' and a.segment_name = b.table_name

4 and a.segment_type='TABLE' group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)

5 having round(bytes/1024/1024,0) >100

6 order by round(bytes/1024/1024,0) desc;

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE MB WASTED

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

ZDRY

ZDRYJCXX

TABLE 408 64

ZDRY

ZDRYCKYJCZFK

TABLE 144 -613

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE MB WASTED

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

SQL> SELECT a.num_rows,a.avg_row_len FROM dba_tables a WHERE a.table_name='ZDRYCKYJCZFK';

NUM_ROWS AVG_ROW_LEN

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

1262022 629 --统计信息行数

SQL> SELECT COUNT(*) FROM ZDRYCKYJCZFK;

SELECT COUNT(*) FROM ZDRYCKYJCZFK

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> SELECT COUNT(*) FROM zdry.ZDRYCKYJCZFK;

COUNT(*)

----------

60 --实际行数

SQL> exec dbms_stats.gather_table_stats('ZDRY','ZDRYCKYJCZFK');

BEGIN dbms_stats.gather_table_stats('ZDRY','ZDRYCKYJCZFK'); END;

*

第 1 行出现错误:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: 在 "SYS.DBMS_STATS", line 13056

ORA-06512: 在 "SYS.DBMS_STATS", line 13076

ORA-06512: 在 line 1

SQL> analyze table zdry.ZDRYCKYJCZFK compute statistics;

analyze table zdry.ZDRYCKYJCZFK compute statistics

*

第 1 行出现错误:

ORA-38029: 对象统计信息已锁定

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

OWNER TABLE_NAME STATT

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

ZDRY ZDRYLKYJZLXX ALL

ZDRY ZDRYLKBKZXZT ALL

ZDRY ZDRYLKBKZLFBSPXX ALL

ZDRY ZDRYLKBKZLFBZXZT ALL

ZDRY ZDRYLKBKZLFB ALL

ZDRY ZDRYLKBKYWB ALL

ZDRY ZDRYLKBKQSXX ALL

ZDRY ZDRYLKBKCZFK ALL

ZDRY ZDRYLKBKZLXX ALL

ZDRY ZDRYLBBJ ALL

ZDRY ZDRYCKYJZXZT ALL

OWNER TABLE_NAME STATT

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

ZDRY ZDRYCKYJQBQS ALL

ZDRY ZDRYCKYJCZSZ ALL

ZDRY ZDRYCKYJCZFKSPXX ALL

ZDRY ZDRYCKYJCZFK ALL

ZDRY XXBKZXJGTSRZ ALL

ZDRY XXBKZLTJRZ ALL

ZDRY XXBKZLJSFWRZ ALL

ZDRY XXBKJGCXQQRZ ALL

ZDRY XXBKJGCXFWRZ ALL

ZDRY TRANSFER_OBJECT_LOG ALL

ZDRY TRANSFER_OBJECT_HISTORY ALL

……

已选择1191行。1191个对象统计信息被锁，由于应用程序在使用造成的.

SQL>

SQL> l

1* select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null

SQL> exec dbms_stats.unlock_table_stats('ZDRY','ZDRYCKYJCZFK');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats('ZDRY','ZDRYCKYJCZFK');

PL/SQL 过程已成功完成。

SQL> SELECT a.num_rows,a.avg_row_len FROM dba_tables a WHERE a.table_name='ZDRYCKYJCZFK';

NUM_ROWS AVG_ROW_LEN

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

60 633

SQL> SELECT COUNT(*) FROM ZDRYCKYJCZFK;

SELECT COUNT(*) FROM ZDRYCKYJCZFK

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> SELECT COUNT(*) FROM zdry.ZDRYCKYJCZFK;

COUNT(*)

----------

60

SQL> SELECT owner,segment_name,BYTEs/1024/1024 "MB" FROM dba_segments t WHERE t.segment_name='ZDRYCKYJCZFK' AND t.owner='ZDRY';

OWNER

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

SEGMENT_NAME

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

MB

----------

ZDRY

ZDRYCKYJCZFK

144 --shrink space优化前144MB

SQL> alter table zdry.zdryckyjczfk enable row movement;

表已更改。

SQL> alter table zdry.zdryckyjczfk shrink space;

表已更改。

SQL> set timing on;

SQL> SELECT owner,segment_name,BYTEs/1024/1024 "MB" FROM dba_segments t WHERE t.segment_name='ZDRYCKYJCZFK' AND t.owner='ZDRY';

OWNER

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

SEGMENT_NAME

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

MB

----------

ZDRY

ZDRYCKYJCZFK

.0625 -- shrink space优化后仅有0.0625MB，说明确实存在较严重的segment fragment.

已用时间: 00: 00: 00.01

SQL> alter table zdry.zdryckyjczfk disable row movement;

表已更改。

已用时间: 00: 00: 00.03

SQL> spool off;

结论：

有时ADDM报告的内容不一定是实事，只是表面现象.不过我们可以通过表面现象找出真正的性能问题.

[@more@]

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

转载于:http://blog.itpub.net/16298743/viewspace-1048052/

cksddfeinwqlse5952
关注
ORACLE 11g自带DBMS函数包
HXC_HUANG的博客
06-03 2105
select object_name,status,object_type from dba_objects where object_name like ‘%DBMS%’ and object_type=’PACKAGE’;1 DBMS_STANDARD VALID PACKAGE 2 DBMS_REGISTRY VALID PACKAGE 3 DBMS_REGIS
通过案例学调优之--Oracle ADDM
weixin_33892359的博客
08-15 308
通过案例学调优之--Oracle ADDM应用环境：操作系统： RedHat EL55Oracle： Oracle 10gR2一、ADDM简介 在Oracle9i及之前，DBA们已经拥有了很多很好用的性能分析工具，比如，tkprof、sql_trace、statspack、set event 10046&10053等等。这些工具能够帮助DBA很快...
oracle查看awr、addm报告
BASEDEDATO的博客
08-10 2523
--获取 dbid select dbid from v$database; --获取 实例号 select instance_number from v$instance; --查看dba_advisor_log视图获取历史快照信息 (注:在快照里就可以找到对应的dbid和instance_num) select * from dba_advisor_log order by task
oracle addm报告生成,生成ADDM报告
weixin_39711959的博客
04-08 800
Normal07.8 磅02falsefalsefalseMicrosoftInternetExplorer4table.MsoNormalTable{mso-style-name:普通表格;mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-parent:"";mso-padding...
Oracle ADDM性能诊断利器及报告解读
热门推荐
乐沙弥的世界
04-27 1万+
性能优化是一个永恒的话题，性能优化也是最具有价值，最值得花费精力深入研究的一个课题，因为资源是有限的，时间是有限的。在Oracle数据库中，随着Oracle功能的不断强大和完善，Oralce数据库在性能方面实现自我诊断及优化的功能也越来智能化，这大大的简花了人工优化的脑力和体力的开销，尤其是借助ADDM自动诊断并给出调整建议。
oracle addm报告分析,Oracle 11g：ADDM报告解析
weixin_39916479的博客
04-10 660
ADDM Report for Task 'TASK_49546'---------------------------------Analysis Period =》该部分指明了分析的时间范围。由于AWR是给定时间里的平均值，因此生成ADDM报告时也要选取业务比较忙的阶段，更有助于诊断特定故障。---------------AWR snapshot range from 16023 to 160...
使用AWR与ADDM指导数据库性能优化
## 1.1 数据库性能优化的重要性 数据库性能优化是保障数据库系统高效、稳定运行的关键步骤。随着业务需求的不断增长和数据量的增加，数据库性能可能会逐渐下降，导致应用程序响应变慢、系统资源过度消耗甚至系统...
Oracle Database 10g: 性能优化 第1卷 学习指南
04-07
1. 传统性能优化方法：这种方法通过调整数据库参数、优化 SQL 语句、调整存储结构等技术来提高数据库的性能。 2. 使用 ADDM 优化会话：ADDM（Automatic Database Diagnostics Monitor）是 Oracle 提供的一个自动...
ORACLE性能优化工具整理
11-16
性能优化不仅需要通过合理设计数据库模式、编写高效SQL语句等手段，而且还需要利用专门的性能优化工具来辅助识别和解决问题。本文主要介绍Oracle中的AWR、ASH和ADDM等工具，并通过详细介绍它们的原理和实践使用方法...
ORACLE DATABASE 11G性能优化攻略_高清_美 Sam R Alapati.pdf
11-10
根据提供的文件信息，“ORACLE DATABASE 11G性能优化攻略_高清_美 Sam R Alapati.pdf”，我们可以推断出这份文档主要讲述了Oracle Database 11g版本中的性能优化策略和技术。下面将从多个角度来深入探讨Oracle ...
ORACLE 数据库AWR和ADDM报告收集与SQL调优
Mr.Yu的专栏
09-20 892
ORACLE 数据库AWR和ADDM报告收集与SQL调优写在前面ORACLE启动配置文件说明PFileSPFile系统配置修改参数设置执行过程修改参数后启动失败解决方案AWR和ADDM报告收集命令下收集报告日志内容截取自动调优脚本 写在前面 好久不见，许久没有更新了，账号密码也是刚找回来，手机都换了，感觉物是人非。我依然是当年的ERP小开发一枚，不过时间是我不仅仅局限于java及报表中的设计开发，个人的兴趣爱好也越来越偏向于数据库，所以这次打算给大家分享一下在项目中遇到过的ORACLE数据库报告收集及调优的
ADDM报告分析
cri3224的博客
12-24 507
以下为一份addm报告，查看该报告，可以得到addm给我们的建议，addm和awr一样，每份addm报告的格式基本一样，只是内容不同而已。 $ cat addmrpt_1_20314_20317.txt 第一部分是指...
注册会计师会计第十五章 债务重组.doc
09-16
注册会计师会计第十五章 债务重组.doc
第0章 前言.doc
09-16
第0章 前言.doc
python链表实战制作学生管理系统（附带pyqt5的界面优化）
最新发布
09-16
python链表实战制作学生管理系统（附带pyqt5的界面优化）
首都体育学院在河北2021-2024各专业最低录取分数及位次表.pdf
09-16
全国各大学在河北2021-2024年各专业最低录取分数及录取位次数据，高考志愿必备参考数据
西南石油大学在河南2021-2024各专业最低录取分数及位次表.pdf
09-16
全国各大学在河北2021-2024年各专业最低录取分数及录取位次数据，高考志愿必备参考数据
Oracle SQL性能优化深度解析
"ORACLE-SQL性能优化课程涵盖了SQL语句执行的过程、ORACLE优化器、执行计划分析以及多种优化方法，旨在帮助用户理解和掌握SQL优化技术，提升系统性能。" 在ORACLE数据库环境中，SQL性能优化是提高系统效率的关键...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值