Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Tune SQL that returns TOP N rows ....

Posted on 2008-06-14
11
Medium Priority
?
1,230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 11

Assisted Solution

by:yuching
yuching earned 400 total points
ID: 21788240
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 600 total points
ID: 21788342
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
ID: 21788426
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 18

Author Comment

by:sventhan
ID: 21788440
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 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 21788947
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
 
LVL 18

Author Comment

by:sventhan
ID: 21788995
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 600 total points
ID: 21791515
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
ID: 21791520
oops - sdstuber's suggestion for deterministic :)
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 21792745
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
ID: 31467226
Thanks for all your help.

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

Expert Comment

by:sdstuber
ID: 21819097
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

722 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