Solved

Tune SQL that returns TOP N rows ....

Posted on 2008-06-14
11
1,208 Views
Last Modified: 2010-04-21
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)

0
Comment
Question by:sventhan
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 11

Assisted Solution

by:yuching
yuching earned 100 total points
Comment Utility
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
 
LVL 7

Assisted Solution

by:Dauhee
Dauhee earned 150 total points
Comment Utility
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
 
LVL 18

Author Comment

by:sventhan
Comment Utility
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
 
LVL 18

Author Comment

by:sventhan
Comment Utility
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Author Comment

by:sventhan
Comment Utility
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
 
LVL 7

Assisted Solution

by:Dauhee
Dauhee earned 150 total points
Comment Utility
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
 
LVL 7

Expert Comment

by:Dauhee
Comment Utility
oops - sdstuber's suggestion for deterministic :)
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
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
 
LVL 18

Author Closing Comment

by:sventhan
Comment Utility
Thanks for all your help.

Congrats!!!  Sdstuber on your Million Points achivement.
Good Luck to you...
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now