oracle收集统计信息之analyze

浅浅的花香味﹌ 2022-04-10 08:55 714阅读 0赞

oracle收集统计信息之analyze  
1.analyze 收集表，索引的统计信息，现在oracle不推荐用analyze收集统计信息  
 收集表的统计信息Analyze table tablename compute statistics;  
 收集索引聚簇统计信息Analyze index|cluster indexname estimate statistics;  
 对于大表收集统计信息相当于全表扫描，耗费时间和资源，可以抽样估算法来搜集生成统计速度要快，如果不是要求要有精确数据的话，尽量采用抽样分析法。  
 nalyze table tablename estimate statistics sample 20 percent;  
 删除表的统计信息：  
 ANALYZE TABLE tablename DELETE STATISTICS  
实验：  
查询jobs表的统计信息以及最后一次统计时间  
SQL> select TABLE\_NAME,NUM\_ROWS,BLOCKS,LAST\_ANALYZED from user\_tables where table\_name='JOBS';  
TABLE\_NAME                       NUM\_ROWS     BLOCKS LAST\_ANALYZED  
\------------------------------ ---------- ---------- --------------  
JOBS                                   19          5 17-7月 -12  
分析表jobs  
SQL> analyze table jobs compute statistics;  
表已分析。  
可以看到jobs的最后一次统计时间变成了29-1月 -13  
SQL> select TABLE\_NAME,NUM\_ROWS,BLOCKS,LAST\_ANALYZED from user\_tables where table\_name='JOBS';  
TABLE\_NAME                       NUM\_ROWS     BLOCKS LAST\_ANALYZED  
\------------------------------ ---------- ---------- --------------  
JOBS                                   19          5 29-1月 -13  
删除jobs表的统计信息  
SQL> analyze table jobs delete statistics;  
表已分析。  
查看统计信息已经被删除了  
SQL> select TABLE\_NAME,NUM\_ROWS,BLOCKS,LAST\_ANALYZED from user\_table where table\_name='JOBS'

TABLE\_NAME                       NUM\_ROWS     BLOCKS LAST\_ANALYZED  
\------------------------------ ---------- ---------- --------------  
JOBS

利用抽样收集jobs表的统计信息：  
SQL> analyze table jobs estimate statistics sample 20 percent;

表已分析。  
再次查看jobs表的统计信息，统计同样被收集：  
SQL> select TABLE\_NAME,NUM\_ROWS,BLOCKS,LAST\_ANALYZED from user\_tables where table\_name='JOBS';

TABLE\_NAME                       NUM\_ROWS     BLOCKS LAST\_ANALYZED  
\------------------------------ ---------- ---------- --------------  
JOBS                                   19          5 29-1月 -13  
SQL>

用analyze统计表的统计信息会是shared pool缓存的sql失效：  
查询的sql  
SQL>  select count(\*) from jobs;

COUNT(\*)  
\----------  
        19  
查看该sql在相关信息可以看到sql执行1次解析一次  
SQL> select sql\_text,sql\_id,hash\_value,executions exec,loads,invalidations inva  
id from v$sqlarea where sql\_text like '&text%';  
输入 text 的值:  select count  
原值    1: select sql\_text,sql\_id,hash\_value,executions exec,loads,invalidation  
 invalid from v$sqlarea where sql\_text like '&text%'  
新值    1: select sql\_text,sql\_id,hash\_value,executions exec,loads,invalidation  
 invalid from v$sqlarea where sql\_text like 'select count%'

SQL\_TEXT                       SQL\_ID        HASH\_VALUE       EXEC      LOADS  
\------------------------------ ------------- ---------- ---------- ----------  
   INVALID  
\----------  
select count(\*) from jab  8r5sp8cjcpqan  583719252          1          1  
         0  
SQL>  
再次分析表jobs  
SQL> analyze table jobs compute statistics;

表已分析。  
再次执行查询的sql并查看sql在v$sqlarea情况发现sql又被解析了一次，并失效一次这说明用analyze收集表的信息可以使shared pool相关对象的sql失效  
SQL>  select count(\*) from jobs;

COUNT(\*)  
\----------  
        19

SQL> select sql\_text,sql\_id,hash\_value,executions exec,loads,invalidations inva  
id from v$sqlarea where sql\_text like '&text%';  
输入 text 的值:  select count  
原值    1: select sql\_text,sql\_id,hash\_value,executions exec,loads,invalidation  
 invalid from v$sqlarea where sql\_text like '&text%'  
新值    1: select sql\_text,sql\_id,hash\_value,executions exec,loads,invalidation  
 invalid from v$sqlarea where sql\_text like 'select count%'

SQL\_TEXT                       SQL\_ID        HASH\_VALUE       EXEC      LOADS  
\------------------------------ ------------- ---------- ---------- ----------  
   INVALID  
\----------  
select count(\*) from jobs   8r5sp8cjcpqan  583719252          1          2