oracle table nocache,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