Home » RDBMS Server » Performance Tuning » Hints on a poor performing view
Hints on a poor performing view [message #133377] Thu, 18 August 2005 15:25 Go to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
I am having some problems with a view that won't run in a reasonable time and I was wondering if anybody had any suggestions. The first series of commands below runs in less than a second combined and shows the correct results. If, however, I run it as a single query (second code block below), it takes nearly an hour to complete. So my question is simple. Are there any hints I can give it to force it to execute completely the subqueries in order and then to the joins, just like I did manually by creating the tables first?

I think few notes are appropriate before anybody reads any further. The explain plan doesn't help. It just says a bunch of stuff about table TEMP TABLE TRANSFORMATION and RECURSIVE EXECUTION, but it doesn't tell me anything about what indexes it is using or the order in which it is accessing the tables. Even if it did, I would have the same question, that is, how do I control that order. (I have included the explain plan at the bottom just so nobody will reply with "Post the explain plan".) Also, I hope nobody wastes their time trying to figure out what the queries actually do or if they are accurate. They are. The only thing of relevance is that I have three subqueries and I need to control their execution order.

Thanks in advance.

Really fast execution
SQL> DROP TABLE short_posn;

Table dropped

SQL> CREATE TABLE short_posn AS
  2  SELECT fip.acct_id, fip.instr_master_id, fim.ticker_symbol, fip.sub_acct_cd, fip.cur_trdt_posn_qty
  3  FROM   fl_instn_posn fip
  4  JOIN   fi_instr_master fim ON fim.instr_master_id = fip.instr_master_id
  5  JOIN   (SELECT DISTINCT acct_id FROM short_accts_v) acct ON acct.acct_id = fip.acct_id;

Table created

SQL> DROP TABLE short_lots;

Table dropped

SQL> CREATE TABLE short_lots AS
  2  SELECT fl.acct_id,
  3         fim.ticker_symbol,
  4         fl.instr_master_id,
  5         fl.sub_acct_cd,
  6         SUM(fl.trd_dt_qty) fpx_qty,
  7         SUM(fl.td_fed_cost_basis) fpx_cost
  8  FROM   fl_lot fl
  9  JOIN   fi_instr_master fim ON fim.instr_master_id = fl.instr_master_id
 10  JOIN   (SELECT DISTINCT acct_id FROM short_accts_v) acct ON acct.acct_id = fl.acct_id
 11  GROUP  BY fl.acct_id,
 12            fim.ticker_symbol,
 13            fl.instr_master_id,
 14            fl.sub_acct_cd;

Table created

SQL> WITH
  2    joined AS (SELECT nvl(lot.acct_id, ip.acct_id) acct_id,
  3                      NVL(lot.instr_master_id, ip.instr_master_id) instr,
  4                      lot.sub_acct_cd lot_sub,
  5                      lot.fpx_qty lot_qty,
  6                      ip.sub_acct_cd ip_sub,
  7                      ip.cur_trdt_posn_qty ip_qty
  8               FROM   short_lots lot
  9               FULL JOIN short_posn ip ON  lot.acct_id = ip.acct_id
 10                                       AND lot.instr_master_id = ip.instr_master_id
 11                                       AND lot.sub_acct_cd = ip.sub_acct_cd
 12              )
 13  SELECT j.*
 14  FROM   joined j
 15  JOIN   fi_instr_master fim ON fim.instr_master_id = j.instr
 16  WHERE NOT (lot_qty = 0 AND ip_qty IS NULL)
 17  AND   NOT (nvl(lot_sub, '$$') = nvl(ip_sub, '$$') AND nvl(lot_qty, 0) = nvl(ip_qty, 0))
 18  AND   instr != 1
 19  ORDER  BY acct_id, fim.ticker_symbol, instr, decode(lot_sub, 'CASH', 1, NULL, 2, 3);

   ACCT_ID      INSTR LOT_SUB    LOT_QTY IP_SUB                     IP_QTY
---------- ---------- ------- ---------- ------ --------------------------
    438766       6085                    MRGN                    50.000000
    438766       6085 SHRT          -150 SHRT                  -200.000000
    438766      16979                    MRGN                   250.000000
    438766      16979 SHRT           -50 SHRT                  -300.000000
    438766       1368 CASH      11546.92 CASH                 12318.380000
    438766      29461 CASH           400 CASH                   200.000000
    438766      29461 MRGN          -200 MRGN                   200.000000
    438766      29461 SHRT          -100 SHRT                  -300.000000
    538448       1368 CASH         -4.62        
    563878      48417 CASH           105 CASH                   207.000000
    563878      48417 MRGN           102        
    563878      59166 CASH             2        
    595551       1368 CASH     278531.34 CASH                280624.360000

13 rows selected



Really slow execution
WITH 
short_posn AS ( SELECT fip.acct_id, fip.instr_master_id, fim.ticker_symbol, fip.sub_acct_cd, fip.cur_trdt_posn_qty
                FROM   fl_instn_posn fip
                JOIN   fi_instr_master fim ON fim.instr_master_id = fip.instr_master_id
                JOIN   (SELECT DISTINCT acct_id FROM short_accts_v) acct ON acct.acct_id = fip.acct_id
              ),
short_lots AS ( SELECT fl.acct_id,
                       fim.ticker_symbol,
                       fl.instr_master_id,
                       fl.sub_acct_cd,
                       SUM(fl.trd_dt_qty) fpx_qty,
                       SUM(fl.td_fed_cost_basis) fpx_cost
                FROM   fl_lot fl
                JOIN   fi_instr_master fim ON fim.instr_master_id = fl.instr_master_id
                JOIN   (SELECT DISTINCT acct_id FROM short_accts_v) acct ON acct.acct_id = fl.acct_id
                GROUP  BY fl.acct_id,
                          fim.ticker_symbol,
                          fl.instr_master_id,
                          fl.sub_acct_cd
              ),
  joined AS (SELECT nvl(lot.acct_id, ip.acct_id) acct_id,
                    NVL(lot.instr_master_id, ip.instr_master_id) instr,
                    lot.sub_acct_cd lot_sub, 
                    lot.fpx_qty lot_qty,
                    ip.sub_acct_cd ip_sub,
                    ip.cur_trdt_posn_qty ip_qty
             FROM   short_lots lot
             FULL JOIN short_posn ip ON  lot.acct_id = ip.acct_id 
                                     AND lot.instr_master_id = ip.instr_master_id 
                                     AND lot.sub_acct_cd = ip.sub_acct_cd
            )
SELECT j.*, fim.ticker_symbol, (SELECT COUNT(*) FROM ft_trns_input WHERE acct_id = j.acct_id) ttl
FROM   joined j
JOIN   fi_instr_master fim ON fim.instr_master_id = j.instr
WHERE NOT (lot_qty = 0 AND ip_qty IS NULL)
AND   NOT (nvl(lot_sub, '$$') = nvl(ip_sub, '$$') AND nvl(lot_qty, 0) = nvl(ip_qty, 0))
AND   instr != 1
ORDER  BY acct_id, fim.ticker_symbol, instr, decode(lot_sub, 'CASH', 1, NULL, 2, 3)


SELECT STATEMENT, GOAL = CHOOSE			Cost=43	Cardinality=11128	Bytes=1001520
RECURSIVE EXECUTION		Object name=SYS_LE_3_0			
RECURSIVE EXECUTION		Object name=SYS_LE_3_1			
 TEMP TABLE TRANSFORMATION					
  VIEW	Object owner=SYS		Cost=43	Cardinality=11128	Bytes=1001520
   UNION-ALL					
    HASH JOIN OUTER			Cost=24	Cardinality=10822	Bytes=973980
     VIEW	Object owner=HSWMPRD2		Cost=14	Cardinality=10822	Bytes=486990
      TABLE ACCESS FULL	Object owner=SYS	Object name=SYS_TEMP_0FD9D6AB0_993DEC37	Cost=14	Cardinality=10822	Bytes=476168
     VIEW	Object owner=HSWMPRD2		Cost=3	Cardinality=1528	Bytes=68760
      TABLE ACCESS FULL	Object owner=SYS	Object name=SYS_TEMP_0FD9D6AAF_993DEC37	Cost=3	Cardinality=1528	Bytes=33616
    HASH JOIN ANTI			Cost=19	Cardinality=306	Bytes=23562
     VIEW	Object owner=HSWMPRD2		Cost=3	Cardinality=1528	Bytes=68760
      TABLE ACCESS FULL	Object owner=SYS	Object name=SYS_TEMP_0FD9D6AAF_993DEC37	Cost=3	Cardinality=1528	Bytes=33616
     VIEW	Object owner=HSWMPRD2		Cost=14	Cardinality=10822	Bytes=346304
      TABLE ACCESS FULL	Object owner=SYS	Object name=SYS_TEMP_0FD9D6AB0_993DEC37	Cost=14	Cardinality=10822	Bytes=476168
Re: Hints on a poor performing view [message #133385 is a reply to message #133377] Thu, 18 August 2005 16:39 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Ok, some of this is a blatent guess, just something I would try to see what happened. But perhaps if you remove the queries from the with clause and make them regular inline views instead, that will help clarify the explain plan results?

Possibly at the same time, or separately, you could try the rownum trick to materialize the results of some of your subqueries.

Perhaps also try putting your first two with subqueries directly inside the "joined" subquery as inline views.

Also, if you can place any of those final where conditions you have at the bottom of the main query inside the individual subqueries, even if you have to repeat them in more than one subquery, I'd give that a shot.

Your style is also different, but of course that is a preference sort of thing. For instance on big queries I like to reference column names with table.column just so I don't run the risk of confusing myself. I also don't really use the with clause unless I plan on referring to a subquery more than once, and unless I missed it, you are only referring to each one once. I also don't use the join on syntax, which of course is purely style, but slows me down when trying to look at a query such as yours.
Re: Hints on a poor performing view [message #133395 is a reply to message #133385] Thu, 18 August 2005 18:41 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
What is the rownum trick? I am not familiar with that one.
Re: Hints on a poor performing view [message #133426 is a reply to message #133395] Fri, 19 August 2005 00:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
with subquery as
  (select 
   ...
   where rownum > 0)
select ... ;

Re: Hints on a poor performing view [message #133532 is a reply to message #133377] Fri, 19 August 2005 08:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Barbara (or anyone really), have you noticed a difference in placing the rownum in the where clause as shown above vs putting it in the select clause?

I would guess (but only a guess) that both would do the same thing, ie cause the subquery to be executed so that the rownum pseudocolumn could then be generated and then referenced.
Re: Hints on a poor performing view [message #133547 is a reply to message #133532] Fri, 19 August 2005 10:12 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
ROWNUM in the WHERE clause of an in-line view materializes that view, preventing predicate-pushing.

First, I'm going to just dummy up a table with five rows in it.
CREATE TABLE t (x VARCHAR2(1));
INSERT INTO t VALUES ('A');
INSERT INTO t VALUES ('B');
INSERT INTO t VALUES ('C');
INSERT INTO t VALUES ('D');
INSERT INTO t VALUES ('E');

Now, I have an in-line view that selects five random numbers from 1 to 100. Outside the in-line view, I select this same value twice.
SQL> SELECT a.r    first_r
  2  ,      a.r    second_r
  3  FROM
  4  (
  5  SELECT DBMS_RANDOM.VALUE(1,100) r
  6  FROM   t
  7  ) a
  8  /
 
   FIRST_R   SECOND_R
---------- ----------
30.0327328 4.26939405
77.3999469  19.382956
52.8165329 75.5643653
68.7761851 39.6915307
40.1392967 50.1017614
 
SQL>
As you can see, without "the ROWNUM trick", the optimizer pushes the predicate, behind the scenes converting this query to
SELECT DBMS_RANDOM.VALUE(1,100)    first_r
,      DBMS_RANDOM.VALUE(1,100)    second_r
FROM   t
/
But by introducing ROWNUM to the in-line view, those results are "materialized" (i.e., made "material", evaluated) before their results are passed to the outer SQL, preventing the predicate-pushing we've just seen in the example without ROWNUM.
SQL> SELECT r    first_r
  2  ,      r    second_r
  3  FROM
  4  (
  5  SELECT DBMS_RANDOM.VALUE(1,100) r
  6  FROM   t
  7  WHERE  ROWNUM > 0
  8  )
  9  /
  
   FIRST_R   SECOND_R
---------- ----------
26.6963882 26.6963882
90.0265163 90.0265163
23.1941336 23.1941336
16.5104589 16.5104589
36.0958909 36.0958909
 
SQL>
I hope that helps. For more information on this phenomenon, click here.
Re: Hints on a poor performing view [message #133548 is a reply to message #133377] Fri, 19 August 2005 10:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Thanks Art for the example, it offers a perfect way to test it.

Looks like putting rownum in the select or the where is just a matter of style.

MYDBA@ORCL > create table t as select rownum a from all_objects where rownum <= 5;

Table created.

MYDBA@ORCL > desc t;
 Name                                               Null?    Type
 -------------------------------------------------- -------- -----------------------

 A                                                           NUMBER

MYDBA@ORCL > select count(*) from t;

  COUNT(*)
----------
         5

1 row selected.

MYDBA@ORCL > select r v1, r v2 from (
  2  select dbms_random.value(1,100) r, rownum rn from t);

        V1         V2
---------- ----------
31.7544102 31.7544102
43.2373741 43.2373741
 77.372078  77.372078
68.9641104 68.9641104
62.3042573 62.3042573

5 rows selected.

MYDBA@ORCL > select r v1, r v2 from (
  2  select dbms_random.value(1,100) r from t where rownum > 0);

        V1         V2
---------- ----------
91.6186359 91.6186359
7.01379603 7.01379603
52.8649657 52.8649657
73.3895573 73.3895573
89.5364383 89.5364383

5 rows selected.

Re: Hints on a poor performing view [message #133559 is a reply to message #133377] Fri, 19 August 2005 11:41 Go to previous message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
smartin, Art, Barbara,

You all are the best. That rownum trick worked like a charm. 1.24 seconds to execute the view. Thanks so much.
Previous Topic: index performing bad
Next Topic: How to rewrite a query
Goto Forum:
  


Current Time: Mon Nov 28 05:51:12 CST 2022