课程第40次

botang · 发表于 2019-8-11 09:41:23

AWR是工具调优，
AWR研究的对象之一是优化器统计信息，而书上详细介绍了AWR是怎么研究它的这个对象的，所以优化器统计信息就属于本质调优。
（农业上播种的机器--->我们只要学会怎么按按钮就行了（你不需要学习植物学）-->但是如果播种机的说明文档里头拼命地介绍要挖多深坑。。。要间隔多少。-->就在直接介绍植物学）

select t.num_rows from dba_Tables t where t.owner='HR' and t.table_name='TBIG';
select * from dba_tab_col_statistics t where t.owner='HR' and t.table_name='TBIG';
select dbms_Stats.get_prefs(pname => 'METHOD_OPT') from dual;
select dbms_Stats.get_prefs(pname => 'STALE_PERCENT') from dual;
select dbms_Stats.get_prefs(pname => 'ESTIMATE_PERCENT') from dual;
select dbms_stats.get_prefs(pname=>'NO_INVALIDATE') from dual;
select * from dba_tab_stat_prefs;
select text from hr.tbig;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG',
method_opt =>'for columns text size 254 for columns size auto');
end;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG');
end;

复制代码

select * from dba_tab_stat_prefs where owner='HR';
select text from hr.tbig;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG',
method_opt =>'for columns text size 254 for columns size auto');
end;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG');
end;
select * from dba_tab_pending_stats where owner='HR';
begin
dbms_stats.set_table_prefs(ownname => 'HR',
tabname => 'TBIG',
pname => 'PUBLISH',pvalue => 'FALSE');
end;
begin
dbms_Stats.gather_table_stats(ownname => 'HR',
tabname => 'TBIG'
);
end;
begin
dbms_stats.publish_pending_stats(ownname => 'HR' ,tabname => 'TBIG');
end;
select * from dba_tab_pending_stats where owner='HR';

复制代码

select * from dba_tables where table_name like 'WR_$\_%SNAPSHOT%' escape '\';

复制代码

OWNER

TABLE_NAME

TABLESPACE_NAME

CLUSTER_NAME

IOT_NAME

STATUS

PCT_FREE

PCT_USED

INI_TRANS

MAX_TRANS

INITIAL_EXTENT

NEXT_EXTENT

MIN_EXTENTS

MAX_EXTENTS

PCT_INCREASE

FREELISTS

FREELIST_GROUPS

LOGGING

BACKED_UP

NUM_ROWS

BLOCKS

EMPTY_BLOCKS

AVG_SPACE

CHAIN_CNT

AVG_ROW_LEN

AVG_SPACE_FREELIST_BLOCKS

NUM_FREELIST_BLOCKS

DEGREE

INSTANCES

CACHE

TABLE_LOCK

SAMPLE_SIZE

LAST_ANALYZED

PARTITIONED

IOT_TYPE

TEMPORARY

SECONDARY

NESTED

BUFFER_POOL

FLASH_CACHE

CELL_FLASH_CACHE

ROW_MOVEMENT

GLOBAL_STATS

USER_STATS

DURATION

SKIP_CORRUPT

MONITORING

CLUSTER_OWNER

DEPENDENCIES

COMPRESSION

COMPRESS_FOR

DROPPED

READ_ONLY

SEGMENT_CREATED

RESULT_CACHE

1

SYS

WRM$_SNAPSHOT

SYSAUX

VALID

10

1

255

65536

1048576

1

2147483645

YES

N

81

5

0

79

0

1

N

ENABLED

81

2019/8/10 14:10:26

NO

N

NO

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

NO

YES

DEFAULT

2

SYS

WRM$_SNAPSHOT_DETAILS

SYSAUX

VALID

10

1

255

65536

1048576

1

2147483645

YES

N

6723

43

0

38

0

1

N

ENABLED

6723

2019/8/10 14:10:27

NO

N

NO

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

NO

YES

DEFAULT