iowait 过高，怀疑大量row migration导致碎片过多，请教～～

quckros

原帖由 sundog315 于 2010-10-9 13:14 发表
可以考虑在线重定义

感谢sundog315提供宝贵意见。

Yong Huang

A few days ago I emailed one of my developers. I said performance gain goes up in this order, difference between every two is in the order of 10 to 10000: instance or database tuning < SQL tuning < business logic tuning. For a DBA, practically speaking, business logic is not something we can control. So we need to focus on SQLs before making a big effort on anything else.

quckros, did you check to see if there's any SQL having too many buffer gets?

Also, as cc59 said, "sysstat只是一个total的值而已". Does "table fetch continued row" change a lot within some period of time?

Yong Huang

quckros

原帖由 Yong Huang 于 2010-10-10 23:27 发表
----A few days ago I emailed one of my developers. I said performance gain goes up in this order, difference between every two is in the order of 10 to 10000: instance or database tuning < SQL tuning < business logic tuning. For a DBA, practically speaking, business logic is not something we can control. So we need to focus on SQLs before making a big effort on anything else.

谢谢你的建议。

----quckros, did you check to see if there's any SQL having too many buffer gets?

有看到Top 10 SQLs by buffer gets ，貌似都是oracle自己产生的，能解释下吗
语句一，在Top 10中出现了5次，buffer gets都在621973255左右
SELECT   procedure_catalog, procedure_schema, procedure_name, parameter_name,
         ordinal_position, parameter_type, parameter_hasdefault,
         parameter_default, is_nullable, data_type, character_maximum_length,
         character_maximum_length character_octet_length, numeric_precision,
         numeric_scale, description, type_name, type_name, overload
    FROM (SELECT NULL procedure_catalog, owner procedure_schema,
                 DECODE (package_name,
                         NULL, object_name,
                         package_name || '.' || object_name
                        ) procedure_name,
                 DECODE (POSITION,
                         0, 'RETURN_VALUE',
                         NVL (argument_name, CHR (0))
                        ) parameter_name,
                 POSITION ordinal_position,
                 DECODE (in_out,
                         'IN', 1,
                         'IN/OUT', 2,
                         'OUT', DECODE (argument_name, NULL, 4, 3),
                         NULL
                        ) parameter_type,
                 NULL parameter_hasdefault, NULL parameter_default,
                 NULL is_nullable,
                 DECODE (data_type,
                         'CHAR', 129,
                         'NCHAR', 129,
                         'DATE', 135,
                         'FLOAT', 139,
                         'LONG', 129,
                         'LONG RAW', 128,
                         'NUMBER', 139,
                         'RAW', 128,
                         'ROWID', 129,
                         'VARCHAR2', 129,
                         'NVARCHAR2', 129,
                         13
                        ) data_type,
                 DECODE (data_type,
                         'CHAR', DECODE (data_length, NULL, 255, data_length),
                         'LONG', 2147483647,
                         'LONG RAW', 2147483647,
                         'ROWID', 18,
                         'RAW', DECODE (data_length, NULL, 255, data_length),
                         'VARCHAR2', DECODE (data_length,
                                             NULL, 2000,
                                             data_length
                                            ),
                         'DATE', NULL,
                         'FLOAT', NULL,
                         'NUMBER', NULL,
                         NULL
                        ) character_maximum_length,
                 DECODE (data_type,
                         'DATE', 19,
                         'FLOAT', 15,
                         'NUMBER', DECODE (data_precision,
                                           NULL, 0,
                                           data_precision
                                          ),
                         'CHAR', NULL,
                         'NCHAR', NULL,
                         'LONG', NULL,
                         'LONG RAW', NULL,
                         'RAW', NULL,
                         'VARCHAR2', NULL,
                         'NVARCHAR2', NULL,
                         NULL
                        ) numeric_precision,
                 DECODE (data_type,
                         'DATE', 0,
                         'NUMBER', DECODE (data_scale, NULL, 0, data_scale),
                         'CHAR', NULL,
                         'NCHAR', NULL,
                         'FLOAT', NULL,
                         'LONG', NULL,
                         'LONG RAW', NULL,
                         'RAW', NULL,
                         'VARCHAR2', NULL,
                         'NVARCHAR2', NULL,
                         NULL
                        ) numeric_scale,
                 NULL description, data_type type_name, overload
            FROM all_arguments
           WHERE data_level = 0 AND data_type IS NOT NULL) procedure_parameters
   WHERE procedure_name = 'CMC_PASSSTATION_CENTER'
ORDER BY 1, 2, 3, 5

语句二，还有一些跟它类似都是查ALL_SYNONYMS 表的，buffer gets都在135578628左右
SELECT /*+ RULE */ TABLE_OWNER, TABLE_NAME FROM ALL_SYNONYMS WHERE OWNER='PUBLIC' AND SYNONYM_NAME='CMC_PASSSTATION_CENTER'

[ 本帖最后由 quckros 于 2010-10-11 15:33 编辑 ]

Yong Huang

Those SQLs are Oracle's, as you expected. But the important thing is *delta* of buffer gets within a period of time, not the absolute value.

Yong Huang

quckros

原帖由 sundog315 于 2010-10-9 13:14 发表
可以考虑在线重定义

在线重定义表有诸多条件限制，比如说一定要有主键等，因不满足条件，我准备找个合适的时间，对CHAIN_CNT值较高的做enable row movement 和shrink space动作

quckros

原帖由 Yong Huang 于 2010-10-12 06:00 发表
Those SQLs are Oracle's, as you expected. But the important thing is *delta* of buffer gets within a period of time, not the absolute value.

Yong Huang

怎样得到“*delta* of buffer gets within a period of time”，做AWR还是？

Yong Huang

> 怎样得到“*delta* of buffer gets within a period of time”，做AWR还是？

I don't remember if AWR does it. It probably does. I think EM (enterprise manager) can do that automatically and graphically. Otherwise, write your own monitoring tool. Pseudo code like this:

select sysdate from dual; --and store it somewhere, in a table, a file, or a shell script variable
select buffer_gets, sql_id from v$sql where buffer_gets > &somevalue; --if it's below this threshold, there's not much use to monitor; also store these values somewhere

Repeat the above and do math to substract on buffer_gets.

Yong Huang

cc59

shrink space也要注意，表大的话会异常慢。会生成N多的log

Yong Huang

> shrink space也要注意，表大的话会异常慢。会生成N多的log

In that case, it may be better to shrink compact first. Then do a full shrink later. It won't reduce redo. But it has less impact on a production database because you have better control over timing.

Yong Huang

quckros

感谢cc59 & Yong Huang。
sfclockproduct shrink之前：BLOCKS 1882      CHAIN_CNT   17657     USER_BLOCKS   1607
1.alter table sfclockproduct shrink space compact;
2.alter table sfclockproduct shrink space cascade;
3.dbms_stats.gather_table_stats('...',tabname=>'SFCLOCKPRODUCT,...);
sfclockproduct shrink之后：BLOCKS 1855      CHAIN_CNT   17657     USER_BLOCKS   1607
问题来拉，CHAIN_CNT根本没有变化？HWM也才降了1882-1855=27？而真正使用的blocks才1607。

目前大的表还不敢做，刚刚中午吃饭时对这张相对小点的表做动作时，发生RX锁及5支function与20多支procedure INVALID~~还好提前知道有这些影响动作还是比较快的，呵呵

[ 本帖最后由 quckros 于 2010-10-14 16:06 编辑 ]