OraFAQ Forum: Performance Tuning » How to reduce the consistent gets of this sql

Home » RDBMS Server » Performance Tuning » How to reduce the consistent gets of this sql (Oracle 10.2.0.4.0 64bit , AIX 5.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to reduce the consistent gets of this sql [message #513370]

Mon, 27 June 2011 02:21

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, guys
How can I reduce the consistents gets of the sql:

select b.OFFER_ID,
        b.OFFER_CODE,
        b.OFFER_NAME,
        b.OFFER_COMMENTS,
        b.BAND_ID,
        b.CAN_BE_BUY_ALONE,
        b.PRICING_PLAN_ID,
        b.PRIORITY,
        b.STATE,
        to_char(b.EFF_DATE, 'yyyymmdd hh24:mi:ss'),
        to_char(b.EXP_DATE, 'yyyymmdd hh24:mi:ss'),
        to_char(b.BEGIN_DATE, 'yyyymmdd hh24:mi:ss'),
        to_char(b.END_DATE, 'yyyymmdd hh24:mi:ss'),
        b.REDOFLAG,
        b.MIN_NUM,
        b.MAX_NUM,
        b.EXP_DATE_OFFSET,
        b.EXP_DATE_OFFSET_UNIT,
        b.USER_RANGE,
        b.OFFER_ATTR_TYPE,
        b.OFFER_TYPE,
        to_char(b.STATE_CHG_DATE, 'yyyymmdd hh24:mi:ss'),
        to_char(b.OFFER_CRT_DATE, 'yyyymmdd hh24:mi:ss'),
        b.GROUP_TYPE_ID,
        nvl(b.GROUP_LIMIT_RULE, 0),
        b.PROD_OFFER_PUBLISHER,
        b.CUSTOM_REQUEST,
        b.OPERATION_MODLE,
        a.GROUP_ID,
        a.REGION_NAME,
        c.pricing_desc,
        d.band_name,
        b.eff_type,
        b.exp_type,
        b.MIN_ORDER_NUM,
        b.MAX_ORDER_NUM,
        b.IF_CUST_PRICE
   from REGION a, PRODUCT_OFFER b, PRICING_PLAN c, BAND d
   where a.offer_id = b.Offer_Id
    and b.pricing_plan_id = c.pricing_plan_id
    and b.band_id = d.band_id
    and b.state = 'A'
    and b.Eff_Date < sysdate and
    b.exp_date > sysdate
    and b.begin_date < sysdate
    and b.end_date > sysdate
    and '#10030#10044#200109#218265#' like '%#' || a.group_id || '#%'
    and to_number(a.Right_Limit) <= 0
    --and (a.Chn_Class_Limit like '%#' ||  || '#%' or
        --trim(:channel_segment) is null)
    and 0 = Offer_Login_Limit_Check(b.offer_id,'nf0S01');

Offer_Login_Limit_Check is a function of check the valid user login.

The function definition :

CREATE OR REPLACE FUNCTION OFFER_LOGIN_LIMIT_CHECK
(
  IN_OFFER_ID PRODUCT_OFFER.OFFER_ID%TYPE,
  IS_LOGIN_NO DLOGINMSG.LOGIN_NO%TYPE
) RETURN NUMBER IS
  RETNUM    NUMBER;
  VN_COUNTS NUMBER;
    
BEGIN

  RETNUM    := 0;
  VN_COUNTS  := 0;
 
  IF is_login_no is NULL then
  
    RETURN 0;
    
  END if;

    SELECT   MAX (cnum) INTO VN_COUNTS
      FROM   (SELECT   COUNT (0) cnum
                FROM   SBANDOFFER
               WHERE       ELEMENT_CODE = IS_LOGIN_NO
                       AND ELEMENT_TYPE = 'gh'
                       AND OFFER_ID = IN_OFFER_ID
                       AND BAND_TYPE = 80
                       AND STATE = '1'
                       AND EFFDATE <= SYSDATE
                       AND EXPDATE >= SYSDATE
              UNION
              SELECT   COUNT (0) cnum
                FROM   dloginrolerel a, sbandoffer b
               WHERE       a.login_no = IS_LOGIN_NO
                       AND a.power_code = b.element_code
                       AND element_type = 'js'
                       AND BAND_TYPE = 80
                       AND OFFER_ID = IN_OFFER_ID
                       AND STATE = '1'
                       AND EFFDATE <= SYSDATE
                       AND EXPDATE >= SYSDATE);
                       
      
     IF VN_COUNTS > 0 THEN
        RETNUM := 1;
     END IF;
  
  
  RETURN RETNUM;
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN - SQLCODE;
  WHEN OTHERS THEN
    RETURN SQLCODE;
    
END OFFER_LOGIN_LIMIT_CHECK;

The execution plan of the sql:

Execution Plan
----------------------------------------------------------
Plan hash value: 894788602
-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |   134 | 36314 |   150   (7)| 00:00:02 |
|*  1 |  HASH JOIN                      |                   |   134 | 36314 |   150   (7)| 00:00:02 |
|   2 |   TABLE ACCESS FULL             | BAND              |    55 |   660 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN                     |                   |   134 | 34706 |   146   (7)| 00:00:02 |
|   4 |    MERGE JOIN                   |                   |   157 | 34540 |   111   (8)| 00:00:02 |
|*  5 |     TABLE ACCESS BY INDEX ROWID | PRODUCT_OFFER     |   156 | 29016 |    96   (8)| 00:00:02 |
|*  6 |      INDEX FULL SCAN            | PRODUCT_OFFER_IDX |   224 |       |    80   (9)| 00:00:01 |
|*  7 |     SORT JOIN                   |                   |   226 |  7684 |    15   (7)| 00:00:01 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| REGION            |   226 |  7684 |    14   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | IDX_REGION_FUN    |   819 |       |     3   (0)| 00:00:01 |
|  10 |    INDEX FAST FULL SCAN         | IDX_REGION_COM_1  | 18191 |   692K|    34   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."BAND_ID"="D"."BAND_ID")
   3 - access("B"."PRICING_PLAN_ID"="C"."PRICING_PLAN_ID")
   5 - filter("B"."STATE"='A' AND "B"."END_DATE">SYSDATE@! AND "B"."EXP_DATE">SYSDATE@! AND
              "B"."EFF_DATE"<SYSDATE@! AND "B"."BEGIN_DATE"<SYSDATE@!)
   6 - filter("OFFER_LOGIN_LIMIT_CHECK"("B"."OFFER_ID",'nf0S01')=0)
   7 - access("A"."OFFER_ID"="B"."OFFER_ID")
       filter("A"."OFFER_ID"="B"."OFFER_ID")
   8 - filter('#10030#10044#200109#218265#' LIKE '%#'||"A"."GROUP_ID"||'#%')
   9 - access(TO_NUMBER("RIGHT_LIMIT")<=0)
Statistics
----------------------------------------------------------
      22524  recursive calls
          0  db block gets
     138064  consistent gets
          0  physical reads
          0  redo size
     264416  bytes sent via SQL*Net to client
       2109  bytes received via SQL*Net from client
        149  SQL*Net roundtrips to/from client
      22525  sorts (memory)
          0  sorts (disk)
       2206  rows processed

The sql trace of this sql:

********************************************************************************

select  b.OFFER_ID,
        b.OFFER_CODE,
        b.OFFER_NAME,
        b.OFFER_COMMENTS,
        b.BAND_ID,
        b.CAN_BE_BUY_ALONE,
        b.PRICING_PLAN_ID,
        b.PRIORITY,
        b.STATE,
        to_char(b.EFF_DATE, 'yyyymmdd hh24:mi:ss'),
        to_char(b.EXP_DATE, 'yyyymmdd hh24:mi:ss'),
        to_char(b.BEGIN_DATE, 'yyyymmdd hh24:mi:ss'),
        to_char(b.END_DATE, 'yyyymmdd hh24:mi:ss'),
        b.REDOFLAG,
        b.MIN_NUM,
        b.MAX_NUM,
        b.EXP_DATE_OFFSET,
        b.EXP_DATE_OFFSET_UNIT,
        b.USER_RANGE,
        b.OFFER_ATTR_TYPE,
        b.OFFER_TYPE,
        to_char(b.STATE_CHG_DATE, 'yyyymmdd hh24:mi:ss'),
        to_char(b.OFFER_CRT_DATE, 'yyyymmdd hh24:mi:ss'),
        b.GROUP_TYPE_ID,
        nvl(b.GROUP_LIMIT_RULE, 0),
        b.PROD_OFFER_PUBLISHER,
        b.CUSTOM_REQUEST,
        b.OPERATION_MODLE,
        a.GROUP_ID,
        a.REGION_NAME,
        c.pricing_desc,
        d.band_name,
        b.eff_type,
        b.exp_type,
        b.MIN_ORDER_NUM,
        b.MAX_ORDER_NUM,
        b.IF_CUST_PRICE
   from REGION a, PRODUCT_OFFER b, PRICING_PLAN c, BAND d
   where a.offer_id = b.Offer_Id
    and b.pricing_plan_id = c.pricing_plan_id
    and b.band_id = d.band_id
    and b.state = 'A'
    and b.Eff_Date < sysdate and
    b.exp_date > sysdate
    and b.begin_date < sysdate
    and b.end_date > sysdate
    and '#10030#10044#200109#218265#' like '%#' || a.group_id || '#%'
    and to_number(a.Right_Limit) <= 0
    --and (a.Chn_Class_Limit like '%#' ||  || '#%' or
        --trim(:channel_segment) is null)
    and 0 = Offer_Login_Limit_Check(b.offer_id,'nf0S01')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      149      5.01       5.70          0       2920          0        2206
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      151      5.05       5.75          0       2920          0        2206

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77  

Rows     Row Source Operation
-------  ---------------------------------------------------
   2206  HASH JOIN  (cr=138064 pr=0 pw=0 time=21087739 us)
     56   TABLE ACCESS FULL BAND (cr=7 pr=0 pw=0 time=226 us)
   2243   HASH JOIN  (cr=138057 pr=0 pw=0 time=21180773 us)
   2243    MERGE JOIN  (cr=137764 pr=0 pw=0 time=5030939 us)
  16318     TABLE ACCESS BY INDEX ROWID PRODUCT_OFFER (cr=136792 pr=0 pw=0 time=16775468 us)
  22524      INDEX FULL SCAN PRODUCT_OFFER_IDX (cr=135218 pr=0 pw=0 time=19303965 us)(object id 176928)
   2243     SORT JOIN (cr=972 pr=0 pw=0 time=1322567 us)
   5294      TABLE ACCESS BY INDEX ROWID REGION (cr=972 pr=0 pw=0 time=1166350 us)
  82473       INDEX RANGE SCAN IDX_REGION_FUN (cr=151 pr=0 pw=0 time=164992 us)(object id 241945)
  20696    INDEX FAST FULL SCAN IDX_REGION_COM_1 (cr=293 pr=0 pw=0 time=41547 us)(object id 241946)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     149        0.00          0.00
  SQL*Net more data to client                    36        0.00          0.00
  SQL*Net message from client                   149        0.00          0.65
********************************************************************************

The execution plan of this function( OFFER_LOGIN_LIMIT_CHECK):

SQL>  select OFFER_LOGIN_LIMIT_CHECK(20097,'nf0S01') from dual;


Execution Plan
----------------------------------------------------------
Plan hash value: 1546270724

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


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

The sql trace of function( OFFER_LOGIN_LIMIT_CHECK) :

SELECT MAX (CNUM) 
FROM
 (SELECT COUNT (0) CNUM FROM SBANDOFFER WHERE ELEMENT_CODE = :B2 AND 
  ELEMENT_TYPE = 'gh' AND OFFER_ID = :B1 AND BAND_TYPE = 80 AND STATE = '1' 
  AND EFFDATE <= SYSDATE AND EXPDATE >= SYSDATE UNION SELECT COUNT (0) CNUM 
  FROM DLOGINROLEREL A, SBANDOFFER B WHERE A.LOGIN_NO = :B2 AND A.POWER_CODE =
   B.ELEMENT_CODE AND ELEMENT_TYPE = 'js' AND BAND_TYPE = 80 AND OFFER_ID = 
  :B1 AND STATE = '1' AND EFFDATE <= SYSDATE AND EXPDATE >= SYSDATE)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute  22524     13.68      13.00          0          0          0           0
Fetch    22524      2.80       2.50          0     135144          0       22524
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    45048     16.48      15.50          0     135144          0       22524

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 77     (recursive depth: 1)

Here are some tables and their records:

region                            has 91,007 records
product_offer                 has  22,523 records
pricing_plan                    has 20,695 records
brand                             has 56 records
sbandoffer                      has 2 records
dloginrolerel(synonyms)  has 28,417 records

I have already added the indexes for the outer sql to reduce the cost from 374 to 150.
But the the consistent gets seems not reduce that much.
I notice that the function will cost 6 consistents gets per execution. It seems it not very high.
But if this function goes with the sql, it will cost a very high consistent gets, if I remove this function, the sql only cost 1903 consistents gets.

So what I am thinking there should be two ways to reduce the consistent gets:
The first one is reduce the recursive call of the sql.
The second is reduce the consistent gets of the function. (but it seems that the consistent gets in this function is very low, only 6.)

Any ideas or suggestions, thanks very much.

BR,
Milo