Shared server memory usage

[Chucks_k]

Hi,

Just an update: I ran an analyze on the schema and re-ran the query

The execution plan is below:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=152723 Card=364221
Bytes=51355161)

1 0 HASH JOIN (RIGHT OUTER) (Cost=152723 Card=364221 Bytes=513
55161)

2 1 TABLE ACCESS (FULL) OF 'HOTLISTMATCH' (TABLE) (Cost=3298
9 Card=1031856 Bytes=20637120)

3 1 HASH JOIN (Cost=115838 Card=364221 Bytes=44070741)
4 3 HASH JOIN (OUTER) (Cost=10 Card=237 Bytes=20619)
5 4 HASH JOIN (OUTER) (Cost=5 Card=237 Bytes=9954)
6 5 TABLE ACCESS (FULL) OF 'CAMERAGROUP' (TABLE) (Cost
=3 Card=33 Bytes=1089)

7 5 INDEX (FULL SCAN) OF 'SYS_C004035' (INDEX (UNIQUE)
) (Cost=1 Card=237 Bytes=2133)

8 4 TABLE ACCESS (FULL) OF 'CAMERA' (TABLE) (Cost=5 Card
=283 Bytes=12735)

9 3 TABLE ACCESS (FULL) OF 'BCAPTURE' (TABLE) (Cost=115
711 Card=7050830 Bytes=239728220)





Statistics
----------------------------------------------------------
1751 recursive calls
0 db block gets
667153 consistent gets
798674 physical reads
0 redo size
1156230391 bytes sent via SQL*Net to client
11611496 bytes received via SQL*Net from client
1055546 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15833169 rows processed

As can be seen the cost has gone up along with the execution plan changing as well!! The query took 4 mins longer to complete. Alto there were less physical reads the no of rows processed increased. Is the higher cost/longer time due to the fact that running the stats made sure all the stats were up to date and hence Oracle taking more time as data volume had grown since the last valid analyze? Any advice will be greatly appreciated on why Oracle has decided to take a different and slower route will be much appreciated

Thanks in advance,
Chucks