Tune SQL that returns TOP N rows ....

I've the following query which runs for more than 4 mintues at first time and i'm looking for options to reduce the timings. There is only one table involved in this  "ss_holding_detail" query and it has 100 millionn rows. Ofcourse this table is range partitioned by eff_date.  

I'm looking for something like this

select * from (select col from ss_holding_detail) where rownum <= 15 (topn) order by value desc.

There is a index on the join columns and stat available.

Optimizer mode = 'ALL_ROWS'
cursor_sharing = EXACT

SELECT  grp_id,
                        '31-JUL-07',
                        ROWNUM,
                        NULL,
                        ROWNUM,
                        NULL,
                        dscfield,
                        rank_value,
                        total_value,
                        pct_value,
                        extrafield,
                        NVL(bmark_id,'7015') ,
                        NVL(bmark_pct_value,0),
                        TO_NUMBER(NULL)
                  FROM  (
                SELECT  tab_acct.grp_id,
                        tab_acct.dscfield,
                        tab_acct.extrafield,
                        tab_acct.rank_value,
                        tab_acct.total_value,
                        tab_acct.pct_value,
                        tab_bmark.grp_id bmark_id,
                        tab_bmark.pct_value bmark_pct_value
               FROM     (SELECT  DISTINCT
                  grp_id,
                  keyfield,
                  dscfield,
                  extrafield,
                  total_value,
                  rank_value,
                  ss_api.calc_pct(NVL(rank_value,0), NVL(total_value,0)) AS pct_value
            FROM
                  (SELECT shd.grp_id,
                        sec_id keyfield,
                        sec_nm || ' ' || sec_dsc  dscfield,
                        ' ' extrafield,
                        sec_subacct_id,
                        sec_typ1,
                        sec_typ2,
                        mat_dt,
                        asset_class,
                        eff_dt,
                        shd.mv_usd AS rank_value,
                        SUM(shd.mv_usd)  OVER () AS total_value
                  FROM  ss_holding_detail shd
                  WHERE shd.grp_id = :p_in_grp_id
                  AND   shd.eff_dt = :v_eff_dt  
                  ORDER BY  11  DESC )  WHERE 1=1   and sec_typ2 NOT IN ('REPO','REOP')    AND sec_typ1 <> 'CASH'
          ) tab_acct,
                        (SELECT  DISTINCT
                  grp_id,
                  keyfield,
                  dscfield,
                  extrafield,
                  total_value,
                  rank_value,
                  ss_api.calc_pct(NVL(rank_value,0), NVL(total_value,0)) AS pct_value
            FROM
                  (SELECT shd.grp_id,
                        sec_id keyfield,
                        sec_nm || ' ' || sec_dsc  dscfield,
                        ' ' extrafield,
                        sec_subacct_id,
                        sec_typ1,
                        sec_typ2,
                        mat_dt,
                        asset_class,
                        eff_dt,
                        shd.mv_usd AS rank_value,
                        SUM(shd.mv_usd)  OVER () AS total_value
                  FROM  ss_holding_detail shd
                  WHERE shd.grp_id = :p_in_grp_id
                  AND   shd.eff_dt = :v_eff_dt  
                  ORDER BY  11  DESC )  WHERE 1=1   and sec_typ2 NOT IN ('REPO','REOP')    AND sec_typ1 <> 'CASH'
          ) tab_bmark
               WHERE    tab_acct.keyfield = tab_bmark.keyfield (+)
               ORDER    BY tab_acct.rank_value DESC ) allrecs
                  WHERE ROWNUM <=  15
                  ORDER by allrecs.rank_value DESC


Explain plan from TkPROF

Rows     Row Source Operation
-------  ---------------------------------------------------
     15  COUNT STOPKEY (cr=3049 pr=2094 pw=0 time=24773902 us)
     15   VIEW  (cr=3049 pr=2094 pw=0 time=24773898 us)
     15    SORT ORDER BY STOPKEY (cr=3049 pr=2094 pw=0 time=24773893 us)
   2541     HASH JOIN OUTER (cr=3049 pr=2094 pw=0 time=24770436 us)
   2541      VIEW  (cr=2545 pr=1612 pw=0 time=18413120 us)
   2541       HASH UNIQUE (cr=2545 pr=1612 pw=0 time=18410576 us)
   2541        VIEW  (cr=2545 pr=1612 pw=0 time=18379289 us)
   2543         WINDOW SORT (cr=2545 pr=1612 pw=0 time=18376744 us)
   2543          PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=2545 pr=1612 pw=0 time=14072544 us)
   2543           TABLE ACCESS BY LOCAL INDEX ROWID SS_HOLDING_DETAIL PARTITION: KEY KEY (cr=2545 pr=1612 pw=0 time=14067450 us)
   2543            INDEX RANGE SCAN SS_HOLDING_DETAIL_PK PARTITION: KEY KEY (cr=12 pr=12 pw=0 time=44751 us)(object id 1658137)
    500      VIEW  (cr=504 pr=482 pw=0 time=6352103 us)
    500       HASH UNIQUE (cr=504 pr=482 pw=0 time=6351600 us)
    500        VIEW  (cr=504 pr=482 pw=0 time=6345223 us)
    500         WINDOW SORT (cr=504 pr=482 pw=0 time=6344211 us)
    500          PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=504 pr=482 pw=0 time=4880910 us)
    500           TABLE ACCESS BY LOCAL INDEX ROWID SS_HOLDING_DETAIL PARTITION: KEY KEY (cr=504 pr=482 pw=0 time=4879896 us)
    500            INDEX RANGE SCAN SS_HOLDING_DETAIL_PK PARTITION: KEY KEY (cr=4 pr=3 pw=0 time=42665 us)(object id 1658137)

LVL 18
sventhanAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
that function can be safely (and should be) declared deterministic.

However, there's not really a reason to call that function again and again as the built in function ratio_to_report will do it for you...
Using the built-in saves on the sql to pl/sql context switching

SELECT grp_id, '31-JUL-07', rownum, NULL, rownum, NULL, dscfield, rank_value,
       total_value, pct_value, extrafield, nvl(grp_id, '7015'),
       nvl(pct_value, 0), to_number(NULL)
FROM (SELECT shd.grp_id, sec_id keyfield, sec_nm || ' ' || sec_dsc dscfield,
             ' ' extrafield, sum(shd.mv_usd) OVER () AS total_value,
             shd.mv_usd AS rank_value,
             nvl(ratio_to_report(shd.mv_usd) OVER (), 0) AS pct_value
      FROM ss_holding_detail shd
     WHERE 1 = 1
       AND shd.grp_id = :grp_id
       AND shd.eff_dt = :eff_date
       AND sec_typ2 NOT IN ('REPO', 'REOP')
       AND sec_typ1 <> 'CASH'
  ORDER BY rank_value DESC)
WHERE rownum <= 15

Open in new window

0
 
yuchingConnect With a Mentor Commented:
Hi,
I dont understand why you have to requery table ss_holding_detail ? The condition for tab_acct and tab_bmark is the same. Try this, it should be getting the same result

SELECT  grp_id,'31-JUL-07',ROWNUM,
    NULL,ROWNUM,NULL,dscfield,
    rank_value,total_value,pct_value,
    extrafield,NVL(bmark_id,'7015') ,NVL(bmark_pct_value,0),
    TO_NUMBER(NULL)
FROM  (
  SELECT  tab_acct.grp_id, tab_acct.dscfield, tab_acct.extrafield,
    tab_acct.rank_value,tab_acct.total_value, tab_acct.pct_value,
    tab_acct.grp_id bmark_id, tab_acct.pct_value bmark_pct_value
  FROM     (
    SELECT  Distinct grp_id, keyfield, dscfield,
        extrafield,total_value,rank_value,
        ss_api.calc_pct(NVL(rank_value,0), NVL(total_value,0)) AS pct_value
    From (
        SELECT shd.grp_id, sec_id keyfield, sec_nm || ' ' || sec_dsc  dscfield,
           ' ' extrafield, sec_subacct_id, sec_typ1,
           sec_typ2, mat_dt, asset_class,
           eff_dt,shd.mv_usd AS rank_value,
           SUM(shd.mv_usd)  OVER () AS total_value
         FROM  ss_holding_detail shd
         WHERE shd.grp_id = :p_in_grp_id AND   shd.eff_dt = :v_eff_dt  
         ORDER BY  11  DESC
     )  WHERE 1=1   and sec_typ2 NOT IN ('REPO','REOP')    AND sec_typ1 <> 'CASH'
   ) tab_acct
   ORDER    BY tab_acct.rank_value DESC
) allrecs
WHERE ROWNUM <=  15
ORDER by allrecs.rank_value DESC
0
 
DauheeConnect With a Mentor Commented:
you should ensure all stats are up to date. Also because the same table is being queried multiple times, how about creating a view and put a "with" clause in it - that clause stops oracle from doing separate reads of the same table

http://www.dba-oracle.com/t_with_clause.htm

Also it would be interesting to see autotrace output "set autotrace on"
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
sventhanAuthor Commented:
Thanks Yuching.

I've even narrow down the Query like below...

SELECT grp_id,
       '31-JUL-07',
       ROWNUM,
       NULL,
       ROWNUM,
       NULL,
       dscfield,
       rank_value,
       total_value,
       pct_value,
       extrafield,
       NVL(grp_id, '7015'),
       NVL(pct_value, 0),
       TO_NUMBER(NULL)
  FROM (SELECT shd.grp_id,
               sec_id keyfield,
               sec_nm || ' ' || sec_dsc dscfield,
               ' ' extrafield,
               SUM(shd.mv_usd) OVER() AS total_value,
               shd.mv_usd AS rank_value,
               ss_api.calc_pct(NVL(shd.mv_usd, 0),
                               NVL(SUM(shd.mv_usd) OVER(), 0)) AS pct_value
          FROM ss_holding_detail shd
         WHERE 1 = 1
           AND shd.grp_id = :grp_id           AND shd.eff_dt = :eff_date
           and sec_typ2 NOT IN ('REPO', 'REOP')
           AND sec_typ1 <> 'CASH'
           order by rank_value desc
           )
WHERE ROWNUM <= 15.

The indexes  are

alter table WMCDM.SS_HOLDING_DETAIL
  add constraint SS_HOLDING_DETAIL_PK primary key (EFF_DT, GRP_ID, ISR_ID, SEC_ID);

-- Create/Recreate indexes
create bitmap index WMCDM.SS_HOLDING_DETAIL_BIDX1 on WMCDM.SS_HOLDING_DETAIL (GRP_ID);
create bitmap index WMCDM.SS_HOLDING_DETAIL_BIDX2 on WMCDM.SS_HOLDING_DETAIL (ISR_ID);
create bitmap index WMCDM.SS_HOLDING_DETAIL_BIDX3 on WMCDM.SS_HOLDING_DETAIL (SEC_ID);
create bitmap index WMCDM.SS_HOLDING_DETAIL_BIDX4 on WMCDM.SS_HOLDING_DETAIL (EFF_DT);
create bitmap index WMCDM.SS_HOLDING_DETAIL_BIDX5 on WMCDM.SS_HOLDING_DETAIL (ASSET_CLASS);
create bitmap index WMCDM.SS_HOLDING_DETAIL_BIDX6 on WMCDM.SS_HOLDING_DETAIL (SEC_TYP1);
create bitmap index WMCDM.SS_HOLDING_DETAIL_BIDX7 on WMCDM.SS_HOLDING_DETAIL (SEC_TYP2);
create bitmap index WMCDM.SS_HOLDING_DETAIL_BIDX8 on WMCDM.SS_HOLDING_DETAIL (PARENT_ISR_ID);

is the anything more I could do in this to get the better performance?
0
 
sventhanAuthor Commented:
Thanks Dauhee:
I'll get you the autotrace output.

If I wanted to keep the original query I'll think about with clause. But I've already modified the query into much simpler one and posted it above. Do you have any other thoughts?

0
 
sdstuberConnect With a Mentor Commented:
is the ss_api.calc_pct function deterministic?  Is it declared to be so?

if it's not declared to be so, try wrapping it....

  (select   ss_api.calc_pct(NVL(shd.mv_usd, 0) from dual)
0
 
sventhanAuthor Commented:
Thanks again SD for your valuable time.

This function calc_pct is very simple as below. If I do not call the function in the SQL i see the performance gain. I'll try to handle this function in SQL itself and see if it helps.

  FUNCTION calc_pct
  (
    p_in_numerator       NUMBER DEFAULT 0,
    p_in_denominator     NUMBER DEFAULT 0
  ) RETURN NUMBER
  AS
  BEGIN

      IF (p_in_denominator = 0) THEN
        return 0;
      ELSE
        return (p_in_numerator / p_in_denominator);
      END IF;

  END calc_pct;
0
 
DauheeConnect With a Mentor Commented:
Hi sventhan,

There are no "hard and fast" rules - the only real way to determine if changes being made improve things is to get the consistent gets (from autotrace) as sometimes a modified query may appear to run quicker/slower but that could just because overall system activity has changed. I would be interested to see the consistent gets for the original sql in comparison with the rewrite. Certainly as sventhan suggested changing the function to deterministic will help.

Also the explain plan for the new sql - even though no full tables scans are being used it may be beneficial to have a different index i.e. multi-column index
0
 
DauheeCommented:
oops - sdstuber's suggestion for deterministic :)
0
 
sventhanAuthor Commented:
Thanks for all your help.

Congrats!!!  Sdstuber on your Million Points achivement.
Good Luck to you...
0
 
sdstuberCommented:
glad I could help,  and thanks!  I didn't know I was getting close until about 980,000 and then realized it was just 10 questions (or so) away.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.