Oracle性能优化-3-提示

最新推荐文章于 2021-04-07 11:27:45 发布

香辣素毛肚

最新推荐文章于 2021-04-07 11:27:45 发布

阅读量866

点赞数 1

分类专栏： oracle 文章标签： oracle PL/SQL性能优化 hint

本文链接：https://blog.csdn.net/qq_34153210/article/details/115398235

版权

oracle 专栏收录该内容

19 篇文章 3 订阅

订阅专栏

我们在 Oracle性能优化-1-索引中第五节索引价值第一次用到了提示，提示可以否决优化器的决定，以其获取更佳的查询性能。

3.1FIRST_ROWS

最短时间获取前N行，n不能超过1000

select /*+ first_rows(1) */
 *
  from mtl_system_items_b msi
 where msi.organization_id = 204
   and msi.inventory_item_id = 45

3.2ALL_ROWS

检索大量数据，比如报表环境

select /*+ all_rows */
 *
  from mtl_system_items_b msi
 where msi.organization_id = 204
   and msi.inventory_item_id = 45

3.3FULL

FULL执行全表扫描，能提升性能在于更改了驱动表

select /*+ full(msi) */
 *
  from mtl_system_items_b msi
 where msi.organization_id = 204
   and msi.inventory_item_id = 45

上面这个例子只是给出用法

3.4INDEX

强制走某个索引

select /*+ index (msi MTL_SYSTEM_ITEMS_B_N3 )*/ * from mtl_system_items_b msi where msi.inventory_item_status_code='Active'

3.5NO_INDEX

禁止使用某索引

select /*+ no_index (msi MTL_SYSTEM_ITEMS_B_N3 )*/ * from mtl_system_items_b msi where msi.inventory_item_status_code='Active'

3.6INDEX_JOIN

如果两列分别创建了索引，使用索引合并，合并后使用索引的结果，而不会访问表

创建表和索引

create table test2(year , state  , quantity   ) as
select round(dbms_random.value(1990, 2020)),
       dbms_random.string('x', 5),
       round(dbms_random.value(0, 100))
  from dual
connect by level <= 1000000;

create index year_idx on test2(year);
create index state_idx on test2(state);

我们看一下解释计划

创建了两个索引，却只使用到了state_idx

我们使用display查看一下解释计划（这个存疑）参考DBMS_XPLAN

  explain plan for 
    select state, year
  from test2
 where state = '90UI6'
   and year = 1998;


select * from table(dbms_xplan.display(null,null,'BASIC'));

使用display_cursor查看一下

SELECT /* TOTO */     state, year
  from test2
 where state = '90UI6'
   and year = 1998
   
SELECT t.*
  FROM v$sql s,
       table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
 WHERE sql_text LIKE '%TOTO%';

现在我们使用索引合并再次尝试一下

SELECT /*+ index_join(t YEAR_IDX STATE_IDX) */     state, year
  from test2 t
 where state = '90UI6'
   and year = 1998

IO次数从34涨到74

还不如为这两个字段建立复合索引，执行索引快速全扫描来的好，那么索引合并到底有什么用呢？

让我们从“什么是索引联接？”开始探讨“ SQL Server中的索引联接”主题。索引联接是一种使用具有两个或多个索引的索引交集完全完成查询的技术。如果一个表具有多个小索引而不是一个大覆盖索引，那么优化器可以在这些小索引上选择索引交集以满足查询的需要。在索引交集中，它必须对用于服务查询的所有索引执行逻辑读取。

https://sqlrelease.com/index-join-in-sql-server

3.7INDEX_COMBINE

index_combine用于合并单个表上的位图索引，用法同上

3.8INDEX_FFS

在第一篇文章中同样提到了索引快速全扫描，当查询的列和where条件全部是索引列的时候，可以使用，全部索引将不经过排序就被读出来。如果查询列含有其它列，则使用索引全扫描

3.9ORDERED

from子句最后的表是驱动表

我们通过采购订单的表连接看看驱动顺序

当pll表在最后：

当ph表在最后：

我们查看第一个nested loops

第一张图是pla在上，所以是pla驱动ph,第二张图是ph驱动pla。

这样看起来好像性能上没有区别，但是驱动的原则是小表驱动大表（或者是查找的行数小的驱动行数大的）

所以正确的做法应该是ph驱动pla

尝试下列的写法

优化器自动将ph作为驱动表，我们手动设置一下，按顺序驱动

select /*+ ordered */ ph.po_header_id, pll.line_location_id,pla.po_line_id
  from   po_line_locations_all pll,po_lines_all pla,po_headers_all ph
 where ph.po_header_id = pla.po_header_id
   and pla.po_line_id = pll.po_line_id
   and ph.po_header_id=11382

性能那是直线下降

3.10LEADING

手动指定驱动表

select /*+ leading(pll)*/ph.po_header_id, pll.line_location_id, pla.po_line_id
  from po_line_locations_all pll, po_lines_all pla, po_headers_all ph
 where ph.po_header_id = pla.po_header_id
   and pla.po_line_id = pll.po_line_id
   and pla.po_line_id = 5947

3.11NO_EXPAND

禁止优化器使用“or扩展”，什么情况下禁止，这个答案我给不出来，因此略过

3.12DRIVING_SITE

略

3.13USE_MERGE

两个表进行merge join 操作，分别进行排序，再合并，用于需要返回大量数据，当use_hash不可用时，使用use_merge

select  /*+ use_merge(pla,pll)*/
 ph.po_header_id, pll.line_location_id, pla.po_line_id
  from po_line_locations_all pll, po_lines_all pla, po_headers_all ph
 where ph.po_header_id = pla.po_header_id
   and pla.po_line_id = pll.po_line_id
   and pla.po_line_id = 5947

3.14USE_NL

use_nl嵌套查询，是返回单行最快的方式，接收的第一个表名应该是返回行数比较小的表，作为驱动表，第二个表作为查询表

select  /*+ USE_NL(pla,ph)*/
 ph.po_header_id, pll.line_location_id, pla.po_line_id
  from po_line_locations_all pll, po_lines_all pla, po_headers_all ph
 where ph.po_header_id = pla.po_header_id
   and pla.po_line_id = pll.po_line_id
   and pla.po_line_id = 5947

3.15USE_HASH

use_nl第一个作为驱动表，应该是小表。use_hash同样第一个表是驱动表，但是第二个表需要放在内存中，因此第二个表应该更小。我们需要查询PGA_AGGRATE_TARGET和HASH_AREA_SIZE，保证内表全部装入内存中，否装会造成较大的IO瓶颈

select  /*+ use_hash(ph,pla)*/
 ph.po_header_id, pll.line_location_id, pla.po_line_id
  from po_line_locations_all pll, po_lines_all pla, po_headers_all ph
 where ph.po_header_id = pla.po_header_id
   and pla.po_line_id = pll.po_line_id
   and pla.po_line_id = 5947

这里我将ph放在前面，仍然是将pla作为驱动表，将ph表放入了内存，所以ph扫描了全部索引，不过这个cpu耗费也太夸张了。

3.16QB_NAME

为子查询取一个块名，然后可以在语句前列为该块指定提示，比如我将子查询的pla指定为全表扫描

select /*+ full(@db pla)*/*
  from po_headers_all ph
 where exists (select /*+   qb_name(db) */ 1
          from po_lines_all pla
         where pla.po_header_id = ph.po_header_id
           and pla.po_line_id = 5947)

3.17PUSH_SUBQ

当子查询能山筛选出更少的行时。使用push_subq使得子查询更早的评估

select /*+ push_subq(@QB1) */
 *
  from mtl_material_transactions mmt
 where 1 = 1
   and mmt.transaction_date between sysdate - 3000 and sysdate - 2500
   and exists (select /*+ QB_NAME(QB1)*/ 1
          from po_headers_all ph, rcv_transactions rt
         where ph.po_header_id = rt.po_header_id
           and rt.transaction_id = mmt.rcv_transaction_id
           and ph.po_header_id = 1)

3.18APPEND

浪费了数据库存储空间，以获取更快的插入数据的速度。如果从来不删除数据，一定要使用append

创建表

create table  test_insert(a number)

插入数据

insert into test_insert(a)
select 1 from dual  connect by level <=2000000

使用append

是快了那么一点，但是不能排除是不是缓存的原因

3.19CACHE

将小表的全表内容缓存到内存里

select /*+ CACHE */ * from po_headers_all

3.20RESULT_CACHE

在函数中使用：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/RESULT_CACHE-clause.html#GUID-7B0FFFDF-C953-46E5-9FD6-C41DFBDE1B0B

在查询中使用：http://www.dba-oracle.com/oracle11g/oracle_11g_result_cache_sql_hint.htm

result_cache提示旨在用于相对静态（不变）的数据，使其成为数据仓库和DSS应用程序或在批处理加载期间加载数据并在非DML环境中运行查询的任何系统的理想解决方案。通常，结果缓存的好处包括：

删除重复的表连接-该RESULT_CACHE 提示是伟大的非规范化旧的数据库。请记住，在Oracle7时代，磁盘的价格是今天的一百多倍，IT部门故意将其架构设计为具有尽可能少的冗余（第三标准格式或3NF）。所述RESULT_CACHE暗示可用于预加入3NF表在一起成为冗余1NF表示，并且0NF是可能的，如果你定义结果集与VARRAY，重复列。

缓存确定性函数输出-对于PL / SQL确定性函数，使用result_cache可以大大减少重复重新计算静态值的开销。

减少CPU开销-如果没有结果缓存来存储预先加入或预先汇总的结果，则必须从数据缓冲区（db_cache_size）中获取原始数据，从而导致CPU消耗增加。

减少磁盘I / O-与物化视图（存储在磁盘上）相比，结果集可立即在RAM中使用，而无需任何物理I / O。

我们试试这个函数输出，我准备手写一个排序函数，效果类似order by

首先了解一下嵌套表，我们排序的元素放在数组里，我们使用嵌套表创建数组

来源：https://www.oracletutorial.com/plsql-tutorial/plsql-nested-tables/

嵌套表的索引从1开始，而不是从0开始

当然我们也可以使用关联数组

参考：https://www.oracletutorial.com/plsql-tutorial/plsql-associative-array/

当然plsql还有有界非稀疏数组：https://www.oracletutorial.com/plsql-tutorial/plsql-varray/

测试一下嵌套表

declare
  type nested_table_type is table of number ;--not null;--限制元素的是否为空，但是只用extend扩展，仍会产开空元素
  n nested_table_type;
begin
  n := nested_table_type();
  n.EXTEND;
  n(n.last) := 1;
  --n.EXTEND;
 -- n(n.last) := '';
  n.EXTEND;
  n(n.last) := 2;
  for i in n.first .. n.last loop
    dbms_output.put_line(n(i));
  end loop;
end;

我创建了一个数据类型为number,且按顺序存放1和2，最后将1和2输出

测试一下嵌套表作为表的字段

--创建嵌套表类型
CREATE  TYPE price_table_type
    IS TABLE OF number ;
--创建表 
create table goods(
id number,
prices price_table_type
)nested table prices store as price_table;
  
--插入数据
DECLARE
  price_t price_table_type := price_table_type();
BEGIN
  price_t.extend;
  price_t(price_t.last) := 1;
  price_t.extend(3, 1);--将索引为1的嵌套表复制三遍，也就是现在嵌套表有四个1
  INSERT INTO goods
  VALUES
    (0,
     price_t);
 COMMIT;
END;
--读取数据
DECLARE
  price_t price_table_type := price_table_type();
BEGIN
  SELECT prices
    INTO price_t
    FROM goods
   WHERE id = 0;
  FOR i IN price_t.first .. price_t.last LOOP
    dbms_output.put_line(price_t(i));
  END LOOP;
END;

结果正好是1，1，1，1

现在我们测试一下in out

DECLARE
  TYPE nested_table_type IS TABLE OF NUMBER;
  l_price nested_table_type := nested_table_type(); 
  PROCEDURE test(p_price IN OUT nested_table_type) IS
  BEGIN
    p_price(1) := 0;
  END;
BEGIN
  l_price.extend();
  l_price(1) := 1;
  test(l_price);
  dbms_output.put_line(l_price(1));
END;

DECLARE
 l_price number;
  PROCEDURE test(p_price IN OUT number) IS
  BEGIN
    p_price := 0;
  END;
BEGIN
 
  l_price:= 1;
  test(l_price);
  dbms_output.put_line(l_price);
END;

in out 类型都将值做了改变，那么为什么要使用nocopy呢

Oracle有两种在PL / SQL代码中传递传递OUT和IN OUT参数的方法：

按值传递：默认操作是创建一个临时缓冲区（正式参数），将数据从参数变量（实际参数）复制到该缓冲区，并在该过程的生命周期内使用该临时缓冲区。成功完成该过程后，会将临时缓冲区的内容复制回参数变量中。如果发生异常，则不会执行回写操作。
引用传递：使用NOCOPY提示告诉编译器使用引用传递，因此不需要临时缓冲区，也不会进行正向复制和向后复制操作。相反，对参数值的任何修改都将直接写入参数变量（实际参数）。

在正常情况下，您可能不会注意到这两种方法之间的区别，但是一旦您开始传递大型或复杂的数据类型（LOB，XMLTYPE，集合等），两种方法之间的区别就会变得相当可观。临时缓冲区的存在意味着按值传递需要每个参数OUT和IN OUT参数两倍的内存，这在使用大参数时可能是一个问题。此外，将数据复制到临时缓冲区并返回到参数变量所花费的时间可能非常长。

由此我们可以得出以下结论：

经过的时间：当我们使用IN OUT参数传递这个大集合时，执行一次内存分配，向前复制和向后复制需要花费一秒钟的时间。相比之下，使用该IN OUT NOCOPY参数进行调用所花费的时间无法在百分之一秒内测量，因为没有临时缓冲区的管理。因此，对大型参数使用按引用传递可以显着提高性能。
内存使用情况：如预期的那样，当IN OUT按值传递大参数时，会话需要额外的内存用于临时缓冲区。当参数定义为时IN OUT NOCOPY，因为没有临时缓冲区，所以不需要额外的内存。因此，对大型参数使用按引用传递将减少会话所需的内存。

下面是测试脚本

CREATE OR REPLACE PACKAGE test_nocopy AS

PROCEDURE in_out_time;
PROCEDURE in_out_memory;
PROCEDURE in_out_nocopy_time;
PROCEDURE in_out_nocopy_memory;

END;



CREATE OR REPLACE PACKAGE BODY test_nocopy AS

  TYPE t_tab IS TABLE OF VARCHAR2(32767);
  g_tab   t_tab := t_tab();
  g_start NUMBER;

  FUNCTION get_stat(p_stat IN VARCHAR2) RETURN NUMBER;
  PROCEDURE in_out(p_tab IN OUT t_tab);
  PROCEDURE in_out_nocopy(p_tab IN OUT NOCOPY t_tab);

  -- Function to return the specified statistics value.
  FUNCTION get_stat(p_stat IN VARCHAR2) RETURN NUMBER AS
    l_return NUMBER;
  BEGIN
    SELECT ms.value
      INTO l_return
      FROM v$mystat   ms,
           v$statname sn
     WHERE ms.statistic# = sn.statistic#
       AND sn.name = p_stat;
    RETURN l_return;
  END get_stat;

  -- Basic test procedures.
  PROCEDURE in_out(p_tab IN OUT t_tab) IS
    l_count NUMBER;
  BEGIN
    l_count := p_tab.count;
  END in_out;

  PROCEDURE in_out_nocopy(p_tab IN OUT NOCOPY t_tab) IS
    l_count NUMBER;
  BEGIN
    l_count := p_tab.count;
  END in_out_nocopy;

  -- Time a single call using IN OUT.
  PROCEDURE in_out_time IS
  BEGIN
    g_start := dbms_utility.get_time;
  
    in_out(g_tab);
  
    dbms_output.put_line('IN OUT Time         : ' || (dbms_utility.get_time - g_start) || ' hsecs');
  END in_out_time;

  -- Check the memory used by a single call using IN OUT.
  PROCEDURE in_out_memory IS
  BEGIN
    g_start := get_stat('session pga memory');
  
    in_out(g_tab);
  
    dbms_output.put_line('IN OUT Memory       : ' || (get_stat('session pga memory') - g_start) || ' bytes');
  END in_out_memory;

  -- Time a single call using IN OUT NOCOPY.
  PROCEDURE in_out_nocopy_time IS
  BEGIN
    g_start := dbms_utility.get_time;
  
    in_out_nocopy(g_tab);
  
    dbms_output.put_line('IN OUT NOCOPY Time  : ' || (dbms_utility.get_time - g_start) || ' hsecs');
  END in_out_nocopy_time;

  -- Check the memory used by a single call using IN OUT NOCOPY.
  PROCEDURE in_out_nocopy_memory IS
  BEGIN
    g_start := get_stat('session pga memory');
  
    in_out_nocopy(g_tab);
  
    dbms_output.put_line('IN OUT NOCOPY Memory: ' || (get_stat('session pga memory') - g_start) || ' bytes');
  END in_out_nocopy_memory;

-- Initialization block to populate test collection.
BEGIN
  g_tab.extend;
  g_tab(g_tab.last) := '1234567890123456789012345678901234567890';
  g_tab.extend(999999, 1); -- Copy element 1 into 2..1000000
END;

来源：https://oracle-base.com/articles/misc/nocopy-hint-to-improve-performance-of-parameters-in-plsql

搞清楚了这些问题，回过头来继续看我们的快排

我们使用嵌套表，用快排写一个排序，快排的算法参考：https://blog.csdn.net/qq_34153210/article/details/104685252

CREATE OR REPLACE PACKAGE public_pkg IS
  TYPE tab_t IS TABLE OF NUMBER;
  PROCEDURE main;
END public_pkg;

CREATE OR REPLACE PACKAGE BODY public_pkg IS

  FUNCTION PARTITION(a      IN OUT NOCOPY tab_t,
                     p_low  IN NUMBER,
                     p_high IN NUMBER) RETURN NUMBER IS
    mid   NUMBER;
    tem   NUMBER;
    pivot NUMBER;
    low   NUMBER := p_low;
    high  NUMBER := p_high;
  BEGIN
  
    mid := floor((low + high) / 2);
    tem := a(mid);
    a(mid) := a(low);
    a(low) := tem;
    pivot := a(low);
    WHILE (low < high) LOOP
      WHILE (low < high AND (a(high) >= pivot OR pivot IS NULL)) LOOP
        high := high - 1;
      END LOOP;
      a(low) := a(high);
      WHILE (low < high AND (a(low) < pivot OR a(low) IS NULL)) LOOP
        low := low + 1;
      END LOOP;
      a(high) := a(low);
    END LOOP;
    a(low) := pivot;
    RETURN low;
  END;

  PROCEDURE sort(a      IN OUT NOCOPY tab_t,
                 p_low  IN NUMBER,
                 p_high IN NUMBER) IS
    pivotpos NUMBER;
  BEGIN
    IF (p_low < p_high) THEN
      pivotpos := PARTITION(a, p_low, p_high);
      sort(a, p_low, pivotpos - 1);
      sort(a, pivotpos + 1, p_high);
    END IF;
  END;
  PROCEDURE traverse(a IN tab_t) IS
  BEGIN
    FOR i IN a.first .. a.last LOOP
      dbms_output.put_line(a(i));
    END LOOP;
  END;

  PROCEDURE test IS
    a tab_t;
  BEGIN
    a := tab_t();
    a.extend(6);
    a(1) := 2;
    a(2) := 3;
    a(3) := 1;
    a(4) := NULL;
    a(5) := 1;
    a(6) := NULL;
    sort(a, a.first, a.last);
    traverse(a);
  END;
  PROCEDURE main IS
  BEGIN
    test;
  END;
END public_pkg;

在test函数中，我们放入2 3 1 null 1 null

执行mian函数排完序如下：

测试函数没有问题了，回到我们的正题，使用函数缓存，result_catch不能提升函数运行的效率，而是将函数的结果缓存到了内存中，不用每次都去计算，我们传入item_id，传出最高价格，将最高价格对应的line_id打印出来

首先查出来item_id为75的所有价格和对应的po_line_id

SELECT pla.po_line_id,
       pla.item_id,
       pla.unit_price
  FROM po_lines_all pla
 WHERE 1 = 1
  -- AND pla.unit_price = test_result_catch_p.get_max_price(pla.item_id)
   AND pla.item_id = 75
   order by pla.unit_price desc

那么我们打开注释，返回的结果应该是9771和9770两行

花费12秒之久（求最大值可以用SQL实现，这里只是为了演示函数结果缓存的效果，我们研究的情况是，真实情况中必须要对函数优化时，该怎么做的问题）

然后我们加上result_cache关键词修饰

再次查看结果

只需要0.14秒，我们换一个查询

这时注释掉我们关键词，并使用

alter system flush shared_pool

清除缓存后

完整的test_result_catch_p如下：

CREATE OR REPLACE PACKAGE test_result_catch_p IS
  FUNCTION get_max_price(p_item_id IN NUMBER) RETURN NUMBER result_cache;
END test_result_catch_p;

CREATE OR REPLACE PACKAGE BODY test_result_catch_p IS

  FUNCTION get_max_price(p_item_id IN NUMBER) RETURN NUMBER result_cache IS
    n public_pkg.tab_t := public_pkg.tab_t();
  BEGIN
  
    FOR a_rec IN (SELECT pla.unit_price
                    FROM po_lines_all pla
                   WHERE pla.item_id = p_item_id) LOOP
      n.extend;
      n(n.last) := a_rec.unit_price;
    END LOOP;
  
    public_pkg.sort(n, n.first, n.last);
    RETURN n(n.last);
  END;
END test_result_catch_p;

当然我们也可以建立函数索引，如果这个方法会再各个地方使用，最好还是采用result_cache方式

如果result_cache提示失效，应该怎么做

show parameter result_cache_max_size

SELECT dbms_result_cache.status FROM dual;

结果应该是enabled

apps用户没有权限

SELECT *
  FROM user_tab_privs u
 WHERE u.privilege = 'EXECUTE'
   AND u.table_name = 'DBMS_RESULT_CACHE'

result_cache_max_size是从shared_pool_size中取得，查询共享池大小

2.注意函数声明和函数体都需要result_cache声明，且SQL需要result_cache提示

3.查询状态是否有效

 select * from v$result_cache_objects

3.21视图中使用hint

以3.15use_hash举例说明

  CREATE view test_view_hint AS
    SELECT ph.po_header_id,
           pll.line_location_id,
           pla.po_line_id
      FROM po_line_locations_all pll,
           po_lines_all          pla,
           po_headers_all        ph
     WHERE ph.po_header_id = pla.po_header_id
       AND pla.po_line_id = pll.po_line_id
       AND pla.po_line_id = 5947;

下面的sql以nest_loop连接，改为hash连接

select /*+ use_hash(tvh.ph,tvh.pla)*/  *
from test_view_hint tvh

香辣素毛肚

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Oracle性能优化-3-提示

我们在 Oracle性能优化-1-索引中第五节索引价值第一次用到了提示，提示可以否决优化器的决定，以其获取更佳的查询性能。3.1FIRST_ROWS最短时间获取前N行，n不能超过1000select /*+ first_rows(1) */ * from mtl_system_items_b msi where msi.organization_id = 204 and msi.inventory_item_id = 453.2ALL_ROWS检索大量数据，比如报表环境
复制链接

扫一扫

专栏目录