PL/SQL函数缓存测试

一、创建测试的表,并初始化测试数据

CREATE TABLE tmp_sales_orders(sales_year INTEGER, amount NUMBER(10,2));

INSERT INTO tmp_sales_orders VALUES('2010', 500);
INSERT INTO tmp_sales_orders VALUES('2013', 1500);
INSERT INTO tmp_sales_orders VALUES('2014', 1000);
INSERT INTO tmp_sales_orders VALUES('2015', 2000);
INSERT INTO tmp_sales_orders VALUES('2016', 3000);

COMMIT;

二、创建带缓存的函数

create or replace function fn_get_sales_amount_with_cache(in_sales_year varchar2)
return number
result_cache
is
  Result number;
begin
  SELECT amount
  INTO Result
  FROM tmp_sales_orders
  WHERE sales_year = in_sales_year;

  return(Result);
end fn_get_sales_amount_with_cache;

三、执行结果和统计信息

select fn_get_sales_amount_with_cache('2014') from dual;

第一次:

Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

第二次查询:

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

三、表新增或更新数据,缓存将失效

update tmp_sales_orders set amount = 5000 where sales_year = '2014';
commit;

再次运行同样的语句,发现consistent gets不为零,说明缓存失效,oracle重新从表读取了数据

select fn_get_sales_amount_with_cache('2014') from dual;
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Comments are closed.