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  |
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 executionWITH
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  |
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 #133532 is a reply to message #133377] |
Fri, 19 August 2005 08:31 |
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 |
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> SELECT DBMS_RANDOM.VALUE(1,100) first_r
, DBMS_RANDOM.VALUE(1,100) second_r
FROM t
/
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> |
|
|
|
| Re: Hints on a poor performing view [message #133548 is a reply to message #133377] |
Fri, 19 August 2005 10:31 |
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 16:52:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
