Home » RDBMS Server » Performance Tuning » Explain Plan - number of rows returned.
Explain Plan - number of rows returned. [message #122767] Wed, 08 June 2005 08:13 Go to next message
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 #122921 is a reply to message #122767] Thu, 09 June 2005 04:09 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

Can I see your query? What is the optimizer mode?
Re: Explain Plan - number of rows returned. [message #122996 is a reply to message #122767] Thu, 09 June 2005 09:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Explain Plan - number of rows returned. [message #123005 is a reply to message #122996] Thu, 09 June 2005 09:42 Go to previous messageGo to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
I'll check this again Smartin. Thanks for your input.
Lee
Re: Explain Plan - number of rows returned. [message #123187 is a reply to message #122767] Fri, 10 June 2005 08:08 Go to previous message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
Sorry.. being an idiot here. Optimizer is COST BASED.
Looked at statisitcs and table data did not change significantly. It is also being run on the same test system.
Previous Topic: Index Question
Next Topic: Constraint state in DWH environment (rely disable novalidate)
Goto Forum:
  


Current Time: Sun Sep 20 15:09:02 CDT 2020