| Explain Plan - number of rows returned. [message #122767] |
Wed, 08 June 2005 08:13  |
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
|
Junior Member |
|
|
Hello,
I have run the same batch process a few days apart and there has been a very significant performance downgrade for one sql script. There is little difference in table data and indexes have not been changed. This is running on Oracle 8.1.7.
The Explain Plan on the first run is as follows:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 2 0 0
Execute 1 0.01 0.05 0 0 3 0
Fetch 21 2.46 2.97 1183 14249 12 923
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 2.48 3.03 1183 14251 15 923
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 248
Rows Row Source Operation
------- ---------------------------------------------------
923 SORT ORDER BY
923 NESTED LOOPS OUTER
924 NESTED LOOPS OUTER
924 NESTED LOOPS OUTER
924 HASH JOIN
923 TABLE ACCESS FULL TABLE3
67974 VIEW VIEW12
67974 FILTER
67974 HASH JOIN
56056 TABLE ACCESS FULL TABLE2
82230 TABLE ACCESS FULL TABLE1
923 TABLE ACCESS BY INDEX ROWID TABLE4
1846 INDEX UNIQUE SCAN (object id 8414)
923 TABLE ACCESS BY INDEX ROWID TABLE5
1846 INDEX UNIQUE SCAN (object id 8418)
923 TABLE ACCESS BY INDEX ROWID TABLE6
1846 INDEX UNIQUE SCAN (object id 8237)
The Explain Plan on the second is as follows:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.01 0.06 0 0 3 0
Fetch 22 1038.07 1055.67 534572 1922975 7468 933
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 1038.09 1055.74 534572 1922975 7471 933
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 248
Rows Row Source Operation
------- ---------------------------------------------------
933 SORT ORDER BY
933 NESTED LOOPS OUTER
934 NESTED LOOPS OUTER
934 NESTED LOOPS OUTER
934 NESTED LOOPS
934 TABLE ACCESS FULL TABLE3
1866 VIEW VIEW12
63210750 FILTER
63210750 HASH JOIN
52194819 TABLE ACCESS FULL TABLE2
76495737 TABLE ACCESS FULL TABLE1
933 TABLE ACCESS BY INDEX ROWID TABLE4
1866 INDEX UNIQUE SCAN (object id 8414)
933 TABLE ACCESS BY INDEX ROWID TABLE5
1866 INDEX UNIQUE SCAN (object id 8418)
933 TABLE ACCESS BY INDEX ROWID TABLE6
1866 INDEX UNIQUE SCAN (object id 8237)
It appears the full table scans are to blame. What I do not understand is why 76495737 and 52194819 rows are returned when there are only approx 82500 and 67500 in recently ANALYSED tables TABLE1 and TABLE2.
I have noticed one thing - but it could be a shot inthe dark. If you divide 76495737 by 82500 the result is 927. Likewise 52194819/927 = 56305 (close to result of TABLE2 first run) AND 63210750/927 = 68188 (again close to result of FILTER first run).
Has anyone else experienced this? If so, what can be done to prevent this from happening? Thanks for your time.
Lee
|
|
|
|
|
|
| Re: Explain Plan - number of rows returned. [message #122996 is a reply to message #122767] |
Thu, 09 June 2005 09:18  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Sorry, I doubt this will help, but the only thing I can come up with is that a) data in table1 and table2 really did change significantly or b) one of these runs is from one system (like dev or test) and the other is from a different system (like prod) such that the systems have widely different data volumes.
Please post back if you find the solution on this one as I'm curious as to what it could be. The only thing I can suggest is to re-analyze and re-double check everything.
|
|
|
|
| Re: Explain Plan - number of rows returned. [message #123004 is a reply to message #122767] |
Thu, 09 June 2005 09:41 |
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
|
Junior Member |
|
|
Hi,
The optimizer is CHOOSE. The sql below is part of Report 6 module.
select ah.acch_formatted_account_no,
afm.authmv_to_subacc_no,
bd.bank_sort_code,
ba.bankac_accno,
afm.authmv_reference,
fe.fundex_date,
fe.fundex_amount,
afm.authmv_freq_code,
afm.authmv_freq_interval,
fe.fundex_authmv_seqno,
fe.fundex_tracer_no,
fe.fundex_soc_seqno
from funds_move_extracts fe, -- TABLE3
auth_funds_moves afm, -- VIEW12
account_headers ah, -- TABLE6
bank_accounts ba, -- TABLE4
bank_details bd -- TABLE5
where ((fe.fundex_soc_seqno (+) = afm.authmv_soc_seqno
and fe.fundex_authmv_seqno (+) = afm.authmv_seqno
and afm.authmv_scheduled = 'Y'
and fe.fundex_authmv_eff_date_from (+) = afm.authmv_eff_date_from)
and (afm.authmv_soc_seqno = ah.acch_soc_seqno (+)
and afm.authmv_to_account_no = ah.acch_account_no (+))
and (afm.authmv_from_bankac_seqno = ba.bankac_seqno (+))
and (ba.bankac_bank_seqno = bd.bank_seqno (+))
and fe.fundex_soc_seqno = '1'
and fe.fundex_pay_method = 'DD'
and fe.fundex_posted_date is null
and fe.fundex_bacs_serial = '000417')
AND (:HEADER_SOC_SEQNO = fe.fundex_soc_seqno)
order by fe.fundex_date,
ah.acch_account_no,
afm.authmv_to_subacc_no,
fe.fundex_authmv_seqno
:HEADER_SOC_SEQNO is an input parameter and happens to be value 1. The only difference between this sql and the one run earlier is the fundex_bacs_serial number.
Thanks for looking at this.
Regards
Lee
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
