Hi there,

I have been looking at the soft parse figures and that points to 99.97%. This is near enough the target (which probably means there is no much hard parsing going on?). Non-parse CPU% is 96.15 which is quite high. This indicates Oracle utilizes the CPU mostly for statement execution but not for parsing. Hence, you were right in what you said about 'High CPU usage might be an indicative of a different problem where in some cases, the process might be actually waiting on the IO (full scan) to complete'


This is one of the problem sql (which is part of the a search utility) along with its execution path:

SELECT "CAMERAGROUP"."GROUPNAME", "BCAPTURE"."CAPTUREDATE", "CAMERAGROUP"."URN", "CAMERA"."URN", "CAMERA"."SHORTNAME", "BCAPTURE"."URN", "BCAPTURE"."VRM", "CAMERAGROUP"."DELETED", "CAMERAGROUPCAMERA"."CAMERAGROUPURN", "CAMERA"."FEEDIDENTIFIER", "CAMERA"."SOURCEIDENTIFIER", "CAMERA"."CAMERAID", "CAMERA"."DELETED", "HOTLISTMATCH"."URN", "HOTLISTMATCH"."VRM"
FROM "BOF2"."CAMERAGROUP" "CAMERAGROUP", "BOF2"."CAMERAGROUPCAMERA" "CAMERAGROUPCAMERA", "BOF2"."CAMERA" "CAMERA", "BOF2"."BCAPTURE" "BCAPTURE", "BOF2"."HOTLISTMATCH" "HOTLISTMATCH"
WHERE ("CAMERAGROUP"."URN"="CAMERAGROUPCAMERA"."CAMERAGROUPURN" (+))
AND ("CAMERAGROUPCAMERA"."CAMERAURN"="CAMERA"."URN" (+))
AND ((("CAMERA"."FEEDIDENTIFIER"="BCAPTURE"."FEEDIDENTIFIER" (+))
AND ("CAMERA"."SOURCEIDENTIFIER"="BCAPTURE"."SOURCEIDENTIFIER" (+)))
AND ("CAMERA"."CAMERAID"="BCAPTURE"."CAMERAIDENTIFIER" (+)))
AND ("BCAPTURE"."URN"="HOTLISTMATCH"."CAPTUREID" (+))
AND ("BCAPTURE"."CAPTUREDATE") >=TO_DATE ('19-03-2007','dd-mm-yyyy');

15771400 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=143079 Card=222252
Bytes=31559784)

1 0 HASH JOIN (OUTER) (Cost=143079 Card=222252 Bytes=31559784)
2 1 HASH JOIN (Cost=107774 Card=222252 Bytes=27114744)
3 2 HASH JOIN (OUTER) (Cost=10 Card=237 Bytes=20856)
4 3 HASH JOIN (OUTER) (Cost=5 Card=237 Bytes=10191)
5 4 TABLE ACCESS (FULL) OF 'CAMERAGROUP' (TABLE) (Cost
=3 Card=31 Bytes=1054)

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

7 3 TABLE ACCESS (FULL) OF 'CAMERA' (TABLE) (Cost=5 Card
=278 Bytes=12510)

8 2 TABLE ACCESS (FULL) OF 'BCAPTURE' (TABLE) (Cost=107
685 Card=4726370 Bytes=160696580)

9 1 TABLE ACCESS (FULL) OF 'HOTLISTMATCH' (TABLE) (Cost=3231
5 Card=1016632 Bytes=20332640)





Statistics
----------------------------------------------------------
1764 recursive calls
0 db block gets
666466 consistent gets
855697 physical reads
0 redo size
1140230272 bytes sent via SQL*Net to client
11566198 bytes received via SQL*Net from client
1051428 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15771400 rows processed

As can be seen its generating a high cost, particaularly when performing the hash joins the one hash join (takes up most of the cost!). From what you say, is this taking the huge table into memory and that is what is resulting in the high cost?

Am going to analze data using dbms_stats. Am thinking that the frequancy should be once a week.

Also for shared servers, is there a parameter setting whereby i can say and n number of processes will be able to use a shared server?

Thanks for much for your feedback.
Chucks