落落优化脚本

1、取消直方图
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=> 'TEST',
estimate_percent => 100,
no_invalidate=>false,
block_sample=>FALSE,
method_opt=>'FOR ALL COLUMNS SIZE 1',
granularity => 'ALL',cascade=> TRUE);

2、收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'T223',tabname=> 'T23',no_invalidate=>false,method_opt=>'FOR ALL COLUMNS SIZE repeat',granularity => 'ALL',cascade=> TRUE);

method_opt=>'FOR ALL COLUMNS SIZE 1 代表禁止直方图

3、前几次使用auto
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=> 'TEST',
estimate_percent => 100,
no_invalidate=>false,
block_sample=>FALSE,
method_opt=>'FOR ALL COLUMNS SIZE auto',
granularity => 'ALL',cascade=> TRUE);

4、使用几次之后使用repeat收集
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=> 'TEST',
estimate_percent => 100,
no_invalidate=>false,
block_sample=>FALSE,
method_opt=>'FOR ALL COLUMNS SIZE repeat',
granularity => 'ALL',cascade=> TRUE);

5、查看统计信息是否过久
exec dbms_stats.flush_database_monitoring_info;

set line 1000
select owner,table_name,object_type,stale_stats,to_char(last_analyzed,'yyyy-mm-dd') from dba_tab_statistics
where table_name in ('&1') and owner ='&2'
and (stale_stats='YES' or last_analyzed is null);

select owner,table_name,PARTITION_NAME,object_type,stale_stats,last_analyzed from dba_tab_statistics
where table_name in ('&1') and owner ='&2' and PARTITION_NAME='&3'
and (stale_stats='YES' or last_analyzed is null);

select owner,table_name,object_type,stale_stats,last_analyzed from dba_tab_statistics
where table_name in ('&1') and owner ='&2'
;

select owner,table_name,object_type,stale_stats,last_analyzed from dba_tab_statistics
where table_name in ('&1')
and (stale_stats='YES' or last_analyzed is null);

select 'exec dbms_stats.unlock_table_stats ('||''''||owner||''''||','||''''||table_name||''''||');'
from dba_tab_statistics where table_name in ('&1') and owner ='&2'
and (stale_stats='YES' or last_analyzed is null);
;

SELECT S.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) "执行时间'S'",
S.EXECUTIONS "执行次数",
S.OPTIMIZER_COST "COST",
S.SORTS,
S.MODULE, --连接模式（JDBC THIN CLIENT：程序）
-- S.LOCKED_TOTAL,
S.PHYSICAL_READ_BYTES "物理读",
-- S.PHYSICAL_READ_REQUESTS "物理读请求",
S.PHYSICAL_WRITE_REQUESTS "物理写",
-- S.PHYSICAL_WRITE_BYTES "物理写请求",
S.ROWS_PROCESSED "返回行数",
S.DISK_READS "磁盘读",
S.DIRECT_WRITES "直接路径写",
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME
FROM GV$SQLAREA S
WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) > 5 --100 0000微秒=1S
AND S.PARSING_SCHEMA_NAME = USER
AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') =
TO_CHAR( SYSDATE, 'YYYY-MM-DD' )
AND S.COMMAND_TYPE IN (2 , 3, 5, 6 , 189)
ORDER BY "执行时间'S'" DESC;

6、修改最多的表
col owner for a10
col table_name for a40
col PARTITION_NAME for a20
col SUBPARTITON_NAME for a20
select * from
(
select * from
(
select * from
(
select u.name owner,o.name table_name,null partition_name, null subpartition_name,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.tab$ t,sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name,o.name,o.subname,null,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type# = 19
union all
select u.name,o.name,o2.subname,o.subname,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.tabsubpart$ tsp,sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# =u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like 'XDB'
union all
select * from
(
select u.name owner,o.name table_name,null partition_name,null subpartition_name,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.tab$ t,sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name,o.name,o.subname,null,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type# = 19
union all
select u.name,o.name,o2.subname,o.subname,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.tabsubpart$ tsp,sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like '%XDB%'
) order by inserts desc
) where rownum<=20;

select * from dba_tab_modifications where table_name=upper('TEST');

7、索引跳跃扫描（skip scan出现就是有问题的，就重建索引）
--单块读，组合索引，引导列没有包含在where列中！

索引全扫描（单块）--如果是大表就有问题了！需要使用hint
索引快速全扫描（多块）--扫描结果无序

(外连接)left/right/out/inner join 走nl 无法使用hint改变驱动表

使用hash连接的时候 swap_join_inputs

not in/not exist 反连接（ANTI） （注意去null值）

8、查看数据更新信息
select owner,
table_name,
partition_name,
subpartition_name,
stats_update_time,
stats_update_time - lag(stats_update_time,1,null) over(partition by owner,table_name order by stats_update_time) interval
from DBA_TAB_STATS_HISTORY
where owner = '&1'
and table_name = '&2'
order by owner,table_name,stats_update_time desc;


col owner for a10
col table_name for a20
col PARTITION_NAME for a20
col SUBPARTITON_NAME for a20
select * from
(
select * from
(
select * from
(
select u.name owner,o.name table_name,null partition_name, null subpartition_name,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.tab$ t,sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name,o.name,o.subname,null,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type# = 19
union all
select u.name,o.name,o2.subname,o.subname,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.tabsubpart$ tsp,sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# =u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like 'XDB'
union all
select * from
(
select u.name owner,o.name table_name,null partition_name,null subpartition_name,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.tab$ t,sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name,o.name,o.subname,null,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type# = 19
union all
select u.name,o.name,o2.subname,o.subname,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.tabsubpart$ tsp,sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like '%XDB%'
) order by inserts desc
) where rownum<=20 and table_name=upper('&1') and owner=UPPER('&2');

select * from dba_tab_modifications where table_name=upper('TEST');

9、表数据增长较多的情况
set line 1000
col owner for a10
col table_name for a40
col PARTITION_NAME for a20
col SUBPARTITON_NAME for a20
select * from
(
select * from
(
select * from
(
select * from
(
select u.name owner,o.name table_name,null partition_name, null subpartition_name,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.tab$ t,sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name,o.name,o.subname,null,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type# = 19
union all
select u.name,o.name,o2.subname,o.subname,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.tabsubpart$ tsp,sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# =u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like 'XDB'
union all
select * from
(
select u.name owner,o.name table_name,null partition_name,null subpartition_name,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.tab$ t,sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name,o.name,o.subname,null,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type# = 19
union all
select u.name,o.name,o2.subname,o.subname,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.tabsubpart$ tsp,sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like '%XDB%'
) order by inserts desc
) where to_char(TIMESTAMP,'yyyy-mm-dd')='2016-06-23' ) where rownum<=10;


set line 1000
col owner for a10
col table_name for a20
col PARTITION_NAME for a20
col SUBPARTITON_NAME for a20
select * from
(
select * from
(
select * from
(
select u.name owner,o.name table_name,null partition_name, null subpartition_name,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.tab$ t,sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name,o.name,o.subname,null,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type# = 19
union all
select u.name,o.name,o2.subname,o.subname,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m,sys.obj$ o,sys.tabsubpart$ tsp,sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# =u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like 'XDB'
union all
select * from
(
select u.name owner,o.name table_name,null partition_name,null subpartition_name,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.tab$ t,sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name,o.name,o.subname,null,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type# = 19
union all
select u.name,o.name,o2.subname,o.subname,
m.inserts,m.updates,m.deletes,m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m,sys.obj$ o,sys.tabsubpart$ tsp,sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like '%XDB%'
) order by inserts desc
) where rownum<=20 and to_char(TIMESTAMP,'yyyy-mm-dd') >= '2016-08-20';