OraFAQ Forum: Performance Tuning » High Consistent gets

Home » RDBMS Server » Performance Tuning » High Consistent gets

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

High Consistent gets [message #110788]

Thu, 10 March 2005 05:59

vipin
Messages: 40
Registered: May 2002

Member

Hi,

I am running the same sql against the same datasets in two different boxes. I get two different timings(slower in a high spec env.) though the explain plan is same. Can you please tell me the reason? I can see high consistent gets in the slower one.

Thanks,
Elapsed: 00:00:00.28

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1315 Card=1 Bytes=38
)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=1315 Card=205 Bytes=7790)
3 2 NESTED LOOPS (Cost=2 Card=6 Bytes=162)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'RPL_USERADMIN' (Co
st=1 Card=1 Bytes=22)

5 4 INDEX (RANGE SCAN) OF 'PK_RPL_USERADMIN' (UNIQUE)
(Cost=1 Card=1)

6 3 INDEX (FAST FULL SCAN) OF 'PAMPGPM_0PK' (UNIQUE) (Co
st=1 Card=593 Bytes=2965)

7 2 TABLE ACCESS (BY INDEX ROWID) OF 'RPT_RECEIVABLEINTERE
ST' (Cost=1312 Card=7265 Bytes=79915)

8 7 INDEX (RANGE SCAN) OF 'RPT_RECEIVABLEINTEREST_IDX1'
(NON-UNIQUE) (Cost=66 Card=7265)

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
6461 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Elapsed: 00:00:14.19

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=46)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=8 Card=25 Bytes=1150)
3 2 NESTED LOOPS (Cost=2 Card=1 Bytes=30)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'RPL_USERADMIN' (Co
st=1 Card=1 Bytes=25)

5 4 INDEX (RANGE SCAN) OF 'PK_RPL_USERADMIN' (UNIQUE)
(Cost=1 Card=1)

6 3 INDEX (RANGE SCAN) OF 'PAMPGPM_0PK' (UNIQUE) (Cost=1
Card=148 Bytes=740)

7 2 TABLE ACCESS (BY INDEX ROWID) OF 'RPT_RECEIVABLEINTERE
ST' (Cost=6 Card=2540 Bytes=40640)

8 7 INDEX (RANGE SCAN) OF 'RPT_RECEIVABLEINTEREST_IDX1'
(NON-UNIQUE) (Cost=2 Card=2540)

Statistics
----------------------------------------------------------
42 recursive calls
0 db block gets
685128 consistent gets
0 physical reads
60 redo size
481 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

Report message to a moderator