Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FTS has different no of rows in a plan

2745940Mar 21 2016 — edited Apr 5 2016

Hello Gurus,

I have encountered unique problem where a sql plan has 4-5 full table scan for one table. Out of them a few FTS has correct number of rows fetched while others have incorrect. Query plan is given below, a tkprof output:

TKPROF: Release 11.2.0.3.0 - Development on Fri Mar 18 13:56:24 2016

Trace file: OLGR2D3_ora_27139.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

select a14.USER_ID USER_ID,

a11.FK_LAYER_ID LAYER_ID,

a16.FK_COMPANY_ID COMPANY_ID,

a13.COMPANY_POLICY_TYPE_NAME COMPANY_POLICY_TYPE_NAME,

a16.STATUS_ID STATUS_ID,

EXTRACT(YEAR FROM a15.EFFECTIVE_DATE) UNDER_WRITING_YEAR

from EREPOSIT.V_GCIN_PROG_QUOTES

a11

join

EREPOSIT.V_ERE_IN_PROG_LAYER_BY_STATUS

a12

(a11.FK_LAYER_ID = a12.LAYER_ID)

join

EREPOSIT.ERE_IN_PROG_SUBCLASS

a13

(a12.RISK_HEADER_ID = a13.FK_RISK_HEADER_ID)

join

EREPOSIT.V_GCIN_BROKERS

a14

(a12.RISK_HEADER_ID = a14.FK_RISK_HEADER_ID)

join

EREPOSIT.V_ERE_PROGRAM

a15

(a12.RISK_HEADER_ID = a15.RISK_HEADER_ID)

join

EREPOSIT.V_GCIN_PROG_MARKETS

a16

(a11.FK_MARKET_ID = a16.MARKET_ID)

join

REVENUE_DM.GCMP_RPT_SECURITY

a17

(a15.PROGRAM_ID = a17.GCMP_PROGRAM_ID)

where ((a17.SEC_ROLE not in ('C')

or a15.STATUS_ID not in (2))

and a15.STATUS_ID not in (0)

and a17.SEC_ROLE in ('C')

and LOWER(a17.LDAP_USERNAME) = LOWER('Jgili'))

group by a14.USER_ID,

a11.FK_LAYER_ID,

a16.FK_COMPANY_ID,

a13.COMPANY_POLICY_TYPE_NAME,

a16.STATUS_ID,

EXTRACT(YEAR FROM a15.EFFECTIVE_DATE)

having max(a11.AUTH_IND_CLIENT_VISIBLE) = 'Y'

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.51 0.51 0 12 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 52.61 52.61 1 288815 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 53.12 53.12 1 288827 0 0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 481

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

---------- ---------- ---------- ---------------------------------------------------

0 0 0 FILTER (cr=288815 pr=1 pw=0 time=52611996 us)

155 155 155 HASH GROUP BY (cr=288815 pr=1 pw=0 time=52611993 us cost=159 size=320 card=1)

215 215 215 HASH JOIN (cr=288815 pr=1 pw=0 time=52610868 us cost=158 size=320 card=1)

215 215 215 NESTED LOOPS (cr=288813 pr=1 pw=0 time=26217299 us)

215 215 215 NESTED LOOPS (cr=288598 pr=1 pw=0 time=26210961 us cost=156 size=308 card=1)

215 215 215 NESTED LOOPS (cr=288594 pr=1 pw=0 time=26203219 us cost=155 size=293 card=1)

445 445 445 NESTED LOOPS (cr=2553 pr=1 pw=0 time=149835 us cost=65 size=265 card=1)

968 968 968 HASH JOIN OUTER (cr=1972 pr=1 pw=0 time=85821 us cost=63 size=254 card=1)

968 968 968 NESTED LOOPS (cr=1972 pr=1 pw=0 time=71183 us)

968 968 968 NESTED LOOPS (cr=1117 pr=1 pw=0 time=63066 us cost=61 size=239 card=1)

173 173 173 NESTED LOOPS (cr=1016 pr=1 pw=0 time=71822 us cost=57 size=207 card=1)

74 74 74 NESTED LOOPS (cr=794 pr=1 pw=0 time=69242 us cost=54 size=193 card=1)

78 78 78 NESTED LOOPS (cr=661 pr=1 pw=0 time=66738 us cost=53 size=179 card=1)

35 35 35 NESTED LOOPS (cr=452 pr=0 pw=0 time=61459 us cost=49 size=165 card=1)

35 35 35 HASH JOIN (cr=389 pr=0 pw=0 time=60442 us cost=47 size=136 card=1)

35 35 35 NESTED LOOPS (cr=294 pr=0 pw=0 time=49348 us)

35 35 35 NESTED LOOPS (cr=267 pr=0 pw=0 time=49122 us cost=34 size=107 card=1)

35 35 35 HASH JOIN (cr=229 pr=0 pw=0 time=48116 us cost=32 size=73 card=1)

49 49 49 HASH JOIN (cr=132 pr=0 pw=0 time=4562 us cost=16 size=47 card=1)

11 11 11 TABLE ACCESS BY INDEX ROWID GCMP_RPT_SECURITY (cr=37 pr=0 pw=0 time=874 us cost=4 size=13 card=1)

44 44 44 INDEX SKIP SCAN GCMP_RPT_SEC$LDAP_ROLE (cr=5 pr=0 pw=0 time=895 us cost=3 size=0 card=1)(object id 11805711)

4943 4943 4943 TABLE ACCESS FULL ERE_IN_PROG (cr=95 pr=0 pw=0 time=2992 us cost=11 size=167212 card=4918)

3988 3988 3988 VIEW (cr=97 pr=0 pw=0 time=43016 us cost=16 size=127868 card=4918)

4025 4025 4025 HASH UNIQUE (cr=97 pr=0 pw=0 time=41275 us cost=16 size=201638 card=4918)

4943 4943 4943 WINDOW SORT (cr=97 pr=0 pw=0 time=44633 us cost=16 size=201638 card=4918)

4943 4943 4943 HASH JOIN (cr=97 pr=0 pw=0 time=8448 us cost=14 size=201638 card=4918)

6 6 6 TABLE ACCESS FULL ERE_PROG_STATUS (cr=2 pr=0 pw=0 time=71 us cost=2 size=90 card=6)

4943 4943 4943 TABLE ACCESS FULL ERE_IN_PROG (cr=95 pr=0 pw=0 time=3959 us cost=11 size=127868 card=4918)

35 35 35 INDEX RANGE SCAN PK_ERE_IN_PROG (cr=38 pr=0 pw=0 time=447 us cost=1 size=0 card=1)(object id 11757507)

35 35 35 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG (cr=27 pr=0 pw=0 time=223 us cost=2 size=34 card=1)

4256 4256 4256 VIEW (cr=95 pr=0 pw=0 time=9255 us cost=12 size=142622 card=4918)

4256 4256 4256 HASH GROUP BY (cr=95 pr=0 pw=0 time=7999 us cost=12 size=137704 card=4918)

4943 4943 4943 TABLE ACCESS FULL ERE_IN_PROG (cr=95 pr=0 pw=0 time=2730 us cost=11 size=137704 card=4918)

35 35 35 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_SUBCLASS (cr=63 pr=0 pw=0 time=743 us cost=2 size=29 card=1)

35 35 35 INDEX RANGE SCAN ERE_IN_PROG_SUBCLS$RH_ID (cr=37 pr=0 pw=0 time=397 us cost=1 size=0 card=1)(object id 11724077)

78 78 78 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_USER (cr=209 pr=1 pw=0 time=4111 us cost=4 size=28 card=2)

292 292 292 INDEX RANGE SCAN ERE_IN_PROG_USER$RH_ID (cr=39 pr=1 pw=0 time=3044 us cost=1 size=0 card=5)(object id 11731190)

74 74 74 TABLE ACCESS BY INDEX ROWID ERE_IN_USER (cr=133 pr=0 pw=0 time=868 us cost=1 size=14 card=1)

78 78 78 INDEX UNIQUE SCAN PK_ERE_IN_USER (cr=51 pr=0 pw=0 time=342 us cost=0 size=0 card=1)(object id 11707506)

173 173 173 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_LAYER (cr=222 pr=0 pw=0 time=1678 us cost=3 size=28 card=2)

181 181 181 INDEX RANGE SCAN ERE_IN_PROG_LAYER$RH_ID (cr=70 pr=0 pw=0 time=653 us cost=1 size=0 card=2)(object id 11724075)

968 968 968 INDEX RANGE SCAN ERE_IN_PROG_MARKET_QUOTE$RH_ID (cr=101 pr=0 pw=0 time=1283 us cost=1 size=0 card=3)(object id 11724086)

968 968 968 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_MARKET_QUOTE (cr=855 pr=0 pw=0 time=4347 us cost=4 size=96 card=3)

0 0 0 TABLE ACCESS FULL ERE_PROG_QUOTES_TXT (cr=0 pr=0 pw=0 time=10 us cost=2 size=15 card=1)

445 445 445 VIEW PUSHED PREDICATE (cr=581 pr=0 pw=0 time=58264 us cost=2 size=11 card=1)

968 968 968 SORT GROUP BY (cr=581 pr=0 pw=0 time=53801 us cost=2 size=14 card=1)

2244 2244 2244 INDEX RANGE SCAN PK_ERE_IN_PROG_MARKET_QUOTE (cr=581 pr=0 pw=0 time=22242 us cost=2 size=28 card=2)(object id 11757508)

215 215 215 VIEW PUSHED PREDICATE V_GCIN_PROG_MARKETS (cr=286041 pr=0 pw=0 time=52449466 us cost=90 size=28 card=1)

215 215 215 SORT GROUP BY (cr=286041 pr=0 pw=0 time=52448007 us cost=90 size=275 card=1)

1703 1703 1703 NESTED LOOPS (cr=286041 pr=0 pw=0 time=50391314 us)

1703 1703 1703 NESTED LOOPS (cr=285986 pr=0 pw=0 time=50381232 us cost=89 size=275 card=1)

1703 1703 1703 NESTED LOOPS OUTER (cr=285670 pr=0 pw=0 time=50362621 us cost=87 size=263 card=1)

1703 1703 1703 NESTED LOOPS OUTER (cr=275018 pr=0 pw=0 time=50297996 us cost=86 size=229 card=1)

1703 1703 1703 NESTED LOOPS OUTER (cr=264366 pr=0 pw=0 time=50232858 us cost=85 size=195 card=1)

1654 1654 1654 NESTED LOOPS OUTER (cr=254257 pr=0 pw=0 time=50187301 us cost=84 size=161 card=1)

1630 1630 1630 HASH JOIN (cr=244268 pr=0 pw=0 time=50050033 us cost=80 size=127 card=1)

972 972 972 HASH JOIN (cr=2633 pr=0 pw=0 time=119329 us cost=7 size=188 card=2)

972 972 972 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_MARKET (cr=1743 pr=0 pw=0 time=33997 us cost=4 size=152 card=2)

972 972 972 INDEX RANGE SCAN PK_ERE_IN_PROG_MARKET (cr=771 pr=0 pw=0 time=15122 us cost=2 size=0 card=2)(object id 11757509)

4895 4895 4895 TABLE ACCESS FULL ERE_PROG_MARKET_STATUS (cr=890 pr=0 pw=0 time=32295 us cost=2 size=198 card=11)

5730710 5730710 5730710 VIEW (cr=241635 pr=0 pw=0 time=51869772 us cost=72 size=908721 card=27537)

5730710 5730710 5730710 HASH JOIN (cr=241635 pr=0 pw=0 time=49800741 us cost=72 size=3139218 card=27537)

2199635 2199635 2199635 HASH JOIN (cr=128605 pr=0 pw=0 time=26891445 us cost=42 size=229021 card=2899)

652815 652815 652815 HASH JOIN (cr=86330 pr=0 pw=0 time=23636441 us cost=30 size=57553 card=859)

1791125 1791125 1791125 VIEW (cr=43165 pr=0 pw=0 time=19469149 us cost=16 size=127868 card=4918)

1791125 1791125 1791125 SORT UNIQUE (cr=43165 pr=0 pw=0 time=18804549 us cost=16 size=201638 card=4918)

2199635 2199635 2199635 WINDOW SORT (cr=43165 pr=0 pw=0 time=15846432 us cost=16 size=201638 card=4918)

2199635 2199635 2199635 HASH JOIN (cr=43165 pr=0 pw=0 time=3198427 us cost=14 size=201638 card=4918)

2670 2670 2670 TABLE ACCESS FULL ERE_PROG_STATUS (cr=890 pr=0 pw=0 time=16565 us cost=2 size=90 card=6)

2199635 2199635 2199635 TABLE ACCESS FULL ERE_IN_PROG (cr=42275 pr=0 pw=0 time=881341 us cost=11 size=127868 card=4918)

2199635 2199635 2199635 HASH JOIN (cr=43165 pr=0 pw=0 time=3495069 us cost=14 size=201638 card=4918)

2670 2670 2670 TABLE ACCESS FULL ERE_PROG_STATUS (cr=890 pr=0 pw=0 time=46234 us cost=2 size=90 card=6)

2199635 2199635 2199635 TABLE ACCESS FULL ERE_IN_PROG (cr=42275 pr=0 pw=0 time=894094 us cost=11 size=127868 card=4918)

2199635 2199635 2199635 TABLE ACCESS FULL ERE_IN_PROG (cr=42275 pr=0 pw=0 time=836159 us cost=11 size=59016 card=4918)

1458710 1458710 1458710 VIEW (cr=113030 pr=0 pw=0 time=19816396 us cost=30 size=484785 card=13851)

1458710 1458710 1458710 SORT GROUP BY (cr=113030 pr=0 pw=0 time=19182425 us cost=30 size=443232 card=13851)

6266935 6266935 6266935 HASH JOIN (cr=113030 pr=0 pw=0 time=8179610 us cost=29 size=443232 card=13851)

2199635 2199635 2199635 TABLE ACCESS FULL ERE_IN_PROG (cr=42275 pr=0 pw=0 time=712728 us cost=11 size=59016 card=4918)

5897585 5897585 5897585 TABLE ACCESS FULL ERE_IN_PROG_MARKET (cr=70755 pr=0 pw=0 time=2422136 us cost=17 size=264240 card=13212)

147 147 147 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_MARKET (cr=9989 pr=0 pw=0 time=80873 us cost=4 size=34 card=1)

12149 12149 12149 INDEX RANGE SCAN ERE_IN_PROG_MARKET$RH_ID (cr=1594 pr=0 pw=0 time=28878 us cost=1 size=0 card=3)(object id 11724076)

250 250 250 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_MARKET (cr=10109 pr=0 pw=0 time=45420 us cost=1 size=34 card=1)

12341 12341 12341 INDEX RANGE SCAN ERE_IN_PROG_MARKET$RH_ID (cr=1594 pr=0 pw=0 time=15931 us cost=1 size=0 card=3)(object id 11724076)

155 155 155 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_MARKET (cr=10652 pr=0 pw=0 time=48242 us cost=1 size=34 card=1)

13042 13042 13042 INDEX RANGE SCAN ERE_IN_PROG_MARKET$RH_ID (cr=1640 pr=0 pw=0 time=16565 us cost=1 size=0 card=3)(object id 11724076)

250 250 250 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG_MARKET (cr=10652 pr=0 pw=0 time=48559 us cost=1 size=34 card=1)

13042 13042 13042 INDEX RANGE SCAN ERE_IN_PROG_MARKET$RH_ID (cr=1640 pr=0 pw=0 time=15675 us cost=1 size=0 card=3)(object id 11724076)

1703 1703 1703 INDEX RANGE SCAN PK_ERE_IN_PROG (cr=316 pr=0 pw=0 time=9184 us cost=1 size=0 card=1)(object id 11757507)

1703 1703 1703 TABLE ACCESS BY INDEX ROWID ERE_IN_PROG (cr=55 pr=0 pw=0 time=4424 us cost=2 size=12 card=1)

215 215 215 INDEX UNIQUE SCAN PK_ERE_PROG_STATUS (cr=4 pr=0 pw=0 time=4134 us cost=0 size=0 card=1)(object id 11775165)

215 215 215 TABLE ACCESS BY INDEX ROWID ERE_PROG_STATUS (cr=215 pr=0 pw=0 time=6087 us cost=1 size=15 card=1)

6 6 6 TABLE ACCESS FULL ERE_PROG_STATUS (cr=2 pr=0 pw=0 time=102 us cost=2 size=72 card=6)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 1 0.00 0.00

Disk file operations I/O 2 0.00 0.00

db flash cache single block physical read

1 0.00 0.00

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.51 0.51 0 12 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 52.61 52.61 1 288815 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 53.12 53.12 1 288827 0 0

Misses in library cache during parse: 1

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 1 15.28 15.28

Disk file operations I/O 2 0.00 0.00

db flash cache single block physical read

1 0.00 0.00

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 6 0.00 0.00 0 0 0 0

Execute 6 0.00 0.00 0 0 0 0

Fetch 6 0.00 0.00 0 12 0 6

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 18 0.00 0.00 0 12 0 6

Misses in library cache during parse: 0

1 user SQL statements in session.

1 internal SQL statements in session.

2 SQL statements in session.

********************************************************************************

Trace file: OLGR2D3_ora_27139.trc

Trace file compatibility: 11.1.0.7

Sort options: default

1 session in tracefile.

1 user SQL statements in trace file.

1 internal SQL statements in trace file.

2 SQL statements in trace file.

2 unique SQL statements in trace file.

249 lines in trace file.

53 elapsed seconds in trace file.

fdsf

The file is attached to thread. Please take not of

4943

TABLE ACCESS FULL ERE_IN_PROG (cr=95 pr=0 pw=0 time=2730 us cost=11 size=137704 card=4918)

and

2199635

TABLE ACCESS FULL ERE_IN_PROG (cr=42275 pr=0 pw=0 time=712728 us cost=11 size=59016 card=4918)

See how rows changed and cr increased which makes this query run very slow.

Please help.

Regards,

Virendra

This post has been answered by John Brady - UK on Mar 21 2016

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on May 3 2016

Added on Mar 21 2016

#general-database-discussions

7 comments

524 views