ORACLE cache table一例

针对TOP SQL中占用BUFFER GETS消耗最多的一条SQL，我们来进行分析，发现其执行次数在6天时间里达到了3000万次，BUFFER_GETS的累计占用达到了6亿个db block：

SQL> select sql_text,buffer_gets,executions,first_load_time from v$sqlarea order by buffer_gets desc;
SQL_TEXT  BUFFER_GETS EXECUTIONS FIRST_LOAD_TIME
—————   ———————  ——————-  —————————
SELECT COUNT (*) FROM (SELECT COUNTRYID FROM V_BAS_AIRPORTINT WHERE AIRPORTID IN (:b1,:b2) GROUP BY COUNTRYID)
655838041
30171278
2010-04-29/00:20:55
1、首先来看这条SQL的执行计划：
SQL> explain plan for SELECT COUNT (*) FROM (SELECT COUNTRYID FROM V_BAS_AIRPORTINT WHERE AIRPORTID IN (:b1,:b2) GROUP BY COUNTRYID);
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————————–
| Id  | Operation                            |  Name          | Rows  | Bytes |
——————————————————————————–
|   0 | SELECT STATEMENT                     |                |     1 |     2 |
|   1 |  SORT AGGREGATE                      |                |     1 |     2 |
|   2 |   VIEW                               |                |     2 |     4 |
|   3 |    SORT GROUP BY                     |                |     2 |    72 |
|   4 |     NESTED LOOPS                     |                |     2 |    72 |
|   5 |      NESTED LOOPS                    |                |     2 |    68 |
|   6 |       NESTED LOOPS                   |                |     2 |    58 |
|   7 |        NESTED LOOPS OUTER            |                |     2 |    52 |
|   8 |         NESTED LOOPS                 |                |     2 |    42 |
|   9 |          INLIST ITERATOR             |                |       |       |
|  10 |           TABLE ACCESS BY INDEX ROWID| T_BAS_AIRPORT  |     2 |    16 |
|  11 |            INDEX RANGE SCAN          | SYS_C0015543   |     2 |       |
|  12 |          TABLE ACCESS BY INDEX ROWID | T_BAS_CITY     |     1 |    13 |
|  13 |           INDEX UNIQUE SCAN          | SYS_C0015538   |     1 |       |
|  14 |         INDEX UNIQUE SCAN            | SYS_C0027246   |     1 |     5 |
|  15 |        INDEX UNIQUE SCAN             | SYS_C0015532   |     1 |     3 |

PLAN_TABLE_OUTPUT
——————————————————————————–
|  16 |       TABLE ACCESS BY INDEX ROWID    | T_BAS_AREA     |     1 |     5 |
|  17 |        INDEX UNIQUE SCAN             | SYS_C0015527   |     1 |       |
|  18 |      INDEX UNIQUE SCAN               | SYS_C0015525   |     1 |     2 |
——————————————————————————–
2、查看其统计信息，每次的execute有35次的递归调用和16次的逻辑读：
——————————————————————————–
35  recursive calls
0  db block gets
16  consistent gets
0  physical reads
0  redo size
194  bytes sent via SQL*Net to client
235  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
1  rows processed
3、 查看3张基础小表的行数，发现记录数都非常少：
SQL> select count(*) from T_BAS_AIRPORT;
COUNT(*)
———-
3874
SQL> select count(*) from T_BAS_CITY;
COUNT(*)
———-
3396
SQL> select count(*) from T_BAS_AREA;
COUNT(*)
———-
12
4、查看相关表的平均使用空间AVG_SPACE，理想：
SQL> select owner,blocks,a
vg_space from dba_tables
where table_name in(‘T_BAS_AIRPORT’,'T_BAS_CITY’,'T_BAS_AREA’);

OWNER                              BLOCKS  AVG_SPACE
—————————— ———- ———-
MU                                     13          0
MU                                    133          0
MU                                     88          0
5、查看相关索引的CLUSTERING_FACTOR，对比三张小表的行数和BLOCKS数，发现有3个索引稀疏程度较高：
SQL> select index_name,index_type,table_name,clustering_factor from dba_indexes
where owner=’MU’ and table_name in(‘T_BAS_AIRPORT’,'T_BAS_CITY’,'T_BAS_AREA’)
and clustering_factor is not null order by clustering_factor desc;

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     CLUSTERING_FACTOR
—————————— ————————— —————————— —————–
SYS_C0015543                   NORMAL                      T_BAS_AIRPORT                               2198
FK_BAS_AIRPORT_INX1            NORMAL                      T_BAS_AIRPORT                               2193
SYS_C0015538                   NORMAL                      T_BAS_CITY                                  1768
FK_CITY_COUNTRYID_INX1         NORMAL                      T_BAS_CITY                                   194
FK_BAS_AREA_INX1               NORMAL                      T_BAS_AREA                                     1
SYS_C0015527                   NORMAL                      T_BAS_AREA                                     1
6、确定优化策略为：
a、将三张小表缓存；
b、将clustering_factor高的index重建。
7、缓存该3张小表进buffer pool，并且长期keep在LRU热端：
这里BUFFER_POOL和CACHE同时使用，是用BUFFER_POOL来指定存贮的缓冲池，而CACHE来指定存储的方式。
SQL> alter table T_BAS_AREA cache;
SQL> alter table T_BAS_CITY cache;
SQL> alter table T_BAS_AIRPORT cache;
SQL> alter table T_BAS_AREA storage(buffer_pool keep);
SQL> alter table T_BAS_CITY storage(buffer_pool keep);
SQL> alter table T_BAS_AIRPORT storage(buffer_pool keep);
8、查看三张小表目前的缓存情况：
SQL> select table_name,cache,buffer_pool from user_TABLES where table_name in(‘T_BAS_AIRPORT’,'T_BAS_CITY’,'T_BAS_AREA’);
TABLE_NAME                     CACHE      BUFFER_POOL
——————————    ———-    ——————-
T_BAS_AIRPORT                      Y      KEEP
T_BAS_CITY                         Y      KEEP
T_BAS_AREA                         Y      KEEP
9、索引重建，采用drop and create的方式，而非rebuild，来彻底消除稀疏：
SQL> drop index SYS_C0015543;
SQL> drop index FK_BAS_AIRPORT_INX1;
SQL> drop index SYS_C0015538;
SQL> create index SYS_C0015543 ….;
SQL> create index FK_BAS_AIRPORT_INX1 ….;
SQL> create index SYS_C0015538 ….;
10、查看现在的统计信息，有了明显改善，获得了约5倍的性能提升：
——————————————————————————–
0  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
208  bytes sent via SQL*Net to client
236  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
1  rows processed

11、查看TOP SQL，该SQL从TOP SQL中消失

若原SQL运行时间为0.01秒
则调整后该SQL执行时间为0.01/5=0.002秒
该SQL的单次执行时间节省了0.008秒
以该SQL执行了3000万次计
则调整后该SQL实际为数据库节省了
0.008*30000000=240000秒=4000分钟=66小时

Popularity: 9% [?]

转载之：http://www.stonemoya.com/archives/cache-table%E4%B8%80%E4%BE%8B.html