Home » RDBMS Server » Performance Tuning » High Consistent gets
High Consistent gets [message #110788] Thu, 10 March 2005 05:59 Go to next message
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
Re: High Consistent gets [message #110803 is a reply to message #110788] Thu, 10 March 2005 08:14 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
THe statistics are different.
Analyze the tables/indexes again in both databases and try again.
Previous Topic: ora-02287 sequnce not allowed here
Next Topic: Want sql executed session wise
Goto Forum:
  


Current Time: Fri Mar 29 04:08:02 CDT 2024