35.1.2 结果缓存应用演示

您所在的位置：首页 > 读书频道 > 数据库 > Oracle >

35.1.2 结果缓存应用演示

http://book.51cto.com 2008-06-10 16:06 谷长勇王彬陈杰电子工业出版社我要评论(0)

摘要：《Oracle 11数据库权威技术手册》从数据库的基础知识入手，全面系统地介绍了Oracle 11g 数据库管理系统的所有特性，并配以翔实的实际用例，严谨的论述，深入探讨了这些特性的细节内容，同时具有很强的可操作性和实用性。本文进行了应用演示。
标签：Oracle 数据库

35.1.2 应用演示

通过下面例子说明结果缓存的使用。假定相关系统初始化参数是：

·RESULT_CACHE_MAX_SIZE ＝1056k

·RESULT_CACHE_MAX_RESULT＝5

·RESULT_CACHE_MODE＝MANUAL

1．SQL 结果缓存

SQL> --- 首先以系统用户登录，设置数据显示格式，并清除所有缓存
SQL> connect / as sysdba
Connected.
SQL>--设置内容显示格式 
SQL> SET ECHO ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 150
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 1000
SQL> SET SERVEROUTPUT ON
SQL> execute dbms_result_cache.flush
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.

SQL>--- 显示缓存报告，确认结果缓存为空
SQL> execute dbms_result_cache.memory_report
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 1056K bytes (1056 blocks)
Maximum Result Size = 52K bytes (52 blocks)
[Memory]
Total Memory = 5132 bytes [0.003% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
SQL> --- SQL语句带RESULT_CACHE优化提示，查看执行计划
SQL> connect hr/hr
Connected.
SQL> --- (查询名Q1是可选的，非必需)

SQL> explain plan for
2   select /*+ result_cache q_name(Q1) */ * from departments;
Explained.
SQL> set echo off
@$Oracle_HOME/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
Plan hash value: 4167016233
|Id|Operation         | Name                     |Rows|Bytes|Cost (%CPU)|Time      |
------------------------------------------------------------------------------------
|0 |SELECT STATEMENT  |                          | 27 | 540 |    3   (0)| 00:00:01 |
|1 |RESULT CACHE      |ggq8ztnxqw8ft4ucg3art21sjm|    |     |           |          |
|2 | TABLE ACCESS FULL|DEPARTMENTS               | 27 | 540 |    3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=4; dependencies=(HR.DEPARTMENTS); 
name="select /*+ result_cache q_name(Q1) */ * from departments"
14 rows selected.

SQL>---执行另一条查询语句，同样带结果缓存优化提示
SQL> connect hr/hr
Connected.
SQL> --- (查询名Q2可选，非必需)
SQL> explain plan for
2   select department_name, emp_count
3   from (select /*+ result_cache q_name(Q2) */
4        department_id, count(*) emp_count
5        from employees
6        group by department_id) e, departments d
7   where e.department_id = d.department_id;
Explained.

SQL>---显示执行计划 
SQL> @$Oracle_HOME/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 523547400
-------------------------------------------------------------------------------------------
|Id| Operation                  | Name                     |Rows|Bytes|Cost(%CPU)|Time    |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT           |                          | 11 | 462 |   7  (29)|00:00:01|
| 1|  MERGE JOIN                |                          | 11 | 462 |   7  (29)|00:00:01|
| 2| TABLE ACCESS BY INDEX ROWID| DEPARTMENTS              | 27 | 432 |   2   (0)|00:00:01|
| 3|    INDEX FULL SCAN         | DEPT_ID_PK               | 27 |     |   1   (0)|00:00:01|
|*4|   SORT JOIN                |                          | 11 | 286 |   5  (40)|00:00:01|
| 5|    VIEW                    |                          | 11 | 286 |   4  (25)|00:00:01|
| 6|     RESULT CACHE           |7n7dsf9ukwqcv7cwbupmmdntaj|    |     |          |        |
| 7|      HASH GROUP BY         |                          | 11 |  33 |   4  (25)|00:00:01|
| 8|       TABLE ACCESS FULL    | EMPLOYEES                |107 | 321 |   3   (0)|00:00:01|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Result Cache Information (identified by operation id):
------------------------------------------------------
6 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache q_name(Q2)*/
department_id, count(*) emp_count
from employees
group by department_id"
26 rows selected.

SQL> --- 执行两个查询
SQL> connect hr/hr
Connected.
SQL> select /*+ result_cache q_name(Q1) */ * from departments
2  /

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration                        200        1700
20 Marketing                             201        1800
30 Purchasing                            114        1700
40 Human Resources                       203        2400
50 Shipping                              121        1500
60 IT                                    103        1400
70 Public Relations                      204        2700
80 Sales                                 145        2500
90 Executive                             100        1700
100 Finance                               108        1700
110 Accounting                            205        1700
120 Treasury                                         1700
130 Corporate Tax                                    1700
140 Control And Credit                               1700
150 Shareholder Services                             1700
160 Benefits                                         1700
170 Manufacturing                                    1700
180 Construction                                     1700
190 Contracting                                      1700
200 Operations                                       1700
210 IT Support                                       1700
220 NOC                                              1700
230 IT Helpdesk                                      1700
240 Government Sales                                 1700
250 Retail Sales                                     1700
260 Recruiting                                       1700
270 Payroll                                          1700

27 rows selected.

SQL> select department_name, emp_count
2   from (select /*+ result_cache q_name(Q2) */
3        department_id, count(*) emp_count
4        from employees
5        group by department_id) e, departments d
6   where e.department_id = d.department_id
7  /

DEPARTMENT_NAME                 EMP_COUNT
------------------------------ ----------
Administration                          1
Marketing                               2
Purchasing                              6
Human Resources                         1
Shipping                               45
IT                                      5
Public Relations                        1
Sales                                  34
Executive                               3
Finance                                 6
Accounting                              2

11 rows selected.

SQL> 
SQL> --- 通过v$result_cache_statistics视图查看内存分配和统计。
SQL> --- 其中Create Count Success的2表示创建了2个结果缓存
SQL>connect / as sysdba
Connected.
SQL>select * from v$result_cache_statistics

        ID NAME                                                         VALUE
---------- ------------------------------------------------------- ----------
1 Block Size (Bytes)                                            1024
2 Block Count Maximum                                           1056
3 Block Count Current                                             32
4 Result Size Maximum (Blocks)                                    52
5 Create Count Success                                             2
6 Create Count Failure                                             0
7 Find Count                                                       0
8 Invalidation Count                                               0
9 Delete Count Invalid                                             0
10 Delete Count Valid                                               0

10 rows selected.

SQL> ---再次执行两个查询
SQL> connect hr/hr
Connected.
SQL> 
SQL> select /*+ result_cache q_name(Q1) */ * from departments;
……
SQL> select department_name, emp_count
2   from (select /*+ result_cache q_name(Q2) */
3        department_id, count(*) emp_count
4        from employees
5        group by department_id) e, departments d
6   where e.department_id = d.department_id
/
……
SQL> --- 再次查看缓存内部分配和使用统计
SQL> --- 其中Find Count表示找到两个结果缓存（两个查询各一个）
SQL> connect / as sysdba
Connected.
SQL> select *
from v$result_cache_statistics
ID NAME                                                         VALUE
---------- ------------------------------------------------------- ----------
1 Block Size (Bytes)                                            1024
2 Block Count Maximum                                           1056
3 Block Count Current                                             32
4 Result Size Maximum (Blocks)                                    52
5 Create Count Success                                             2
6 Create Count Failure                                             0
7 Find Count                                                       2
8 Invalidation Count                                               0
9 Delete Count Invalid                                             0
10 Delete Count Valid                                               0

10 rows selected.

2．PL/SQL函数结果缓存

Oracle 11g数据库提供了标记 PL/SQL 函数以指明应该缓存其结果的能力。这样，下次使用同一参数值调用函数时就可以进行查找而不必重新计算了。缓存是系统级的，这样调用该函数的所有会话都会因缓存的返回值而收益。可以使用函数声明中的 RESULT_CACHE 选项激活 PL/SQL 函数缓存。例如：

SQL>--- 清空缓存 
SQL> connect / as sysdba
Connected.
SQL>execute dbms_result_cache.flush
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> execute dbms_result_cache.memory_report
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 1056K bytes (1056 blocks)
Maximum Result Size = 52K bytes (52 blocks)
[Memory]
Total Memory = 5132 bytes [0.003% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
SQL> 
SQL> connect hr/hr
Connected.

SQL>--- 创建一个函数，使用RESULT_CACHE表示函数结果缓存
SQL>--- 使用RELIES_ON指定结果依赖的表或视图
SQL> create or replace function EMP_COUNT(dept_no number)
2  return number
3  result_cache relies_on (employees)
4  is
5   v_count number;
6  begin
7   select count(*) into v_count
8   from employees
9   where department_id = dept_no;
11   return v_count;
12  end;
13  /

SQL> --- 执行函数
SQL> select department_name, emp_count(department_id) no_of_emps
2  from departments
3  where department_name = 'Accounting'
4  /
DEPARTMENT_NAME                NO_OF_EMPS
------------------------------ -------------------------------------------
Accounting                              2

1 row selected.

SQL>--- 确认内存分配情况，发现结果缓存被创建
SQL> connect / as sysdba
Connected.
SQL> execute dbms_result_cache.memory_report
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 1056K bytes (1056 blocks)
Maximum Result Size = 52K bytes (52 blocks)
[Memory]
Total Memory = 103528 bytes [0.054% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.051% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 1 blocks
................... PLSQL   = 1 blocks (1 count)

PL/SQL procedure successfully completed.

SQL> ---查询v$result_cache_statistics
SQL> ---Create Count Success为1表示结果缓存被成功创建一次
SQL> connect / as sysdba
Connected.

SQL> select *
2  from v$result_cache_statistics;
ID NAME                                                         VALUE
---------- ------------------------------------------------------- ----------
1 Block Size (Bytes)                                            1024
2 Block Count Maximum                                           1056
3 Block Count Current                                             32
4 Result Size Maximum (Blocks)                                    52
5 Create Count Success                                             1
6 Create Count Failure                                             0
7 Find Count                                                       0
8 Invalidation Count                                               0
9 Delete Count Invalid                                             0
10 Delete Count Valid                                               0

10 rows selected.

SQL> --- 再次调用同一函数
SQL> connect hr/hr
Connected.
SQL> select department_name, emp_count(department_id) no_of_emps
2  from departments
3  where department_name = 'Accounting'
4  /
……

SQL> --- 查看结果缓存的分配和使用情况，Find Count为1表示结果缓存被成功找到一次 
SQL> connect / as sysdba
Connected.
SQL> select *
2  from v$result_cache_statistics;
ID NAME                                                         VALUE
---------- ------------------------------------------------------- ----------
1 Block Size (Bytes)                                            1024
2 Block Count Maximum                                           1056
3 Block Count Current                                             32
4 Result Size Maximum (Blocks)                                    52
5 Create Count Success                                             1
6 Create Count Failure                                             0
7 Find Count                                                       1
8 Invalidation Count                                               0
9 Delete Count Invalid                                             0
10 Delete Count Valid                                               0

10 rows selected.

SQL> --- 查看 v$result_cache_objects
SQL> --- SCAN_COUNT列中1表示结果缓存被使用
SQL> connect / as sysdba
Connected.
SQL> select type, namespace,status, scan_count,name
2  from v$result_cache_objects
3  /

TYPE       NAMES STATUS    SCAN_COUNT NAME
--------- ----- -------- --------- -----------------------------------------------------
Dependency       Published          0 HR.EMP_COUNT
Dependency       Published          0 HR.EMPLOYEES
Result     PLSQL Published          1 "HR"."EMP_COUNT"::8."EMP_COUNT"#fac892c7867b54c6 #1
3 rows selected.

【责任编辑：杜书 TEL：（010）68476606】

回书目 上一节 下一节

上一篇： 35.1.1 结果缓存概述下一篇： 35.2.1 执行计划管理概述