性能調整 OLTP 查詢

我每天執行數百萬次以下 SQL，單次執行不是問題，但執行數百萬次會導致一些性能問題..提前感謝您的幫助！

注意：數據可以在一天中的任何時間更改任何表中的數據。不能減少執行次數，這是 Oracle 11g 環境。

SELECT COUNT (*)
   INTO v_cnt
 FROM t1 cpr, t2 brl
WHERE cpr.cp_id = :1
    AND cpr.brl_id = brl.brl_id
    AND brl.ranking > 0
    AND cpr.security_enabled_ind='Y'
    AND cpr.brl_id NOT IN (
                  SELECT rpr.brl_id
                    FROM t3 rpr, t4 ppr, t5 plcy
                   WHERE rpr.ppr_id = ppr.ppr_id
                     AND ppr.plcy_id = plcy.plcy_id
                     AND plcy.db_object_name = :2);
IF v_cnt = 0
THEN

SELECT where_clause
 FROM
   (SELECT where_clause
      FROM t4 ppr_o
    WHERE ppr_o.ppr_id IN (
                   SELECT ppr.ppr_id
                     FROM t1 cpr,
                          t3 rpr,
                          t4 ppr,
                          t5 plcy
                    WHERE cpr.cp_id = :1 
                       AND cpr.brl_id = rpr.brl_id
                       AND rpr.ppr_id = ppr.ppr_id
                       AND ppr.plcy_id = plcy.plcy_id
                       AND cpr.security_enabled_ind='Y'
                       AND plcy.db_object_name = :2
                       AND plcy.statement_type = :3)
   ORDER BY NVL (ppr_o.ranking, 0) DESC)
WHERE ROWNUM = 1;
END IF;

記錄數

• t1 -> 205101
• t2 -> 42
• t3 -> 142
• t4 -> 57
• t5 -> 13

我首先會考慮嘗試將這兩個查詢合併為一個。如果可以將第二個查詢重構為null在第一個查詢返回零的情況下返回，那麼您可以使用它嗎？

例如，

試驗台：

create table t1( cp_id integer, 
                brl_id integer, 
                security_enabled_ind char(1) );
create table t2( brl_id integer, 
                ranking number );
create table t3( ppr_id integer,
                brl_id integer );
create table t4( ppr_id integer, 
                plcy_id integer, 
                where_clause varchar(1000), 
                ranking number );
create table t5( plcy_id integer, 
                db_object_name varchar(100), 
                statement_type integer );

重構查詢：

with w as ( select rpr.brl_id, ppr_id, plcy.statement_type
           from t3 rpr join t4 ppr using(ppr_id) join t5 plcy using(plcy_id)
           where plcy.db_object_name=:2 )
select where_clause
from( select where_clause
     from t4 ppr_o
     where not exists ( select *
                        from t1 cpr join t2 brl using(brl_id)
                        where cpr.cp_id = 1 and brl.ranking > 0
                              and cpr.security_enabled_ind='Y'
                              and brl_id not in (select brl_id from w) )
           and ppr_o.ppr_id in ( select ppr_id
                                 from t1 cpr join w using(brl_id)
                                 where cpr.cp_id=:1
                                       and cpr.security_enabled_ind='Y'
                                       and w.statement_type=:3 )
   order by ppr_o.ranking desc nulls last )
where rownum=1;

如果在數據更改之間發生具有相同參數的多次執行，您可以考慮使用Server Results Cache。它將使用一些記憶體，但是當它可以使用記憶體時，它應該會降低性能損失。

引用自：https://dba.stackexchange.com/questions/2990