Hi there,

Thank for your response. I do not think parallel queries have been enabled. I know one can enable it at table level. In this scenario, is it best to enable it on tables where there are full tbs scans + high number of rows? and to what degree? I am also wondering if there is an overhead associated with this considering that we are already utilizing near maximum memory?

This is the explain plan (using dbms_xplain) for that particalur query:

SQL> explain plan for
2 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" (+))
3 4 5 6 AND ((("CAMERA"."FEEDIDENTIFIER"="BCAPTURE"."FEEDIDENTIFIER" (+))
7 AND ("CAMERA"."SOURCEIDENTIFIER"="BCAPTURE"."SOURCEIDENTIFIER" (+)))
8 AND ("CAMERA"."CAMERAID"="BCAPTURE"."CAMERAIDENTIFIER" (+)))
9 AND ("BCAPTURE"."URN"="HOTLISTMATCH"."CAPTUREID" (+))
10 AND ("BCAPTURE"."CAPTUREDATE") >=TO_DATE ('19-03-2007','dd-mm-yyyy');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3661953572

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

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CP
U)| Time |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 364K| 48M| | 152K (
2)| 00:30:33 |

|* 1 | HASH JOIN RIGHT OUTER| | 364K| 48M| 31M| 152K (
2)| 00:30:33 |

| 2 | TABLE ACCESS FULL | HOTLISTMATCH | 1031K| 19M| | 32989 (
1)| 00:06:36 |

|* 3 | HASH JOIN | | 364K| 42M| | 115K (
3)| 00:23:11 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 4 | HASH JOIN OUTER | | 237 | 20619 | | 10 (1
0)| 00:00:01 |

|* 5 | HASH JOIN OUTER | | 237 | 9954 | | 5 (2
0)| 00:00:01 |

| 6 | TABLE ACCESS FULL| CAMERAGROUP | 33 | 1089 | | 3 (
0)| 00:00:01 |

| 7 | INDEX FULL SCAN | SYS_C004035 | 237 | 2133 | | 1 (

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0)| 00:00:01 |

| 8 | TABLE ACCESS FULL | CAMERA | 283 | 12735 | | 5 (
0)| 00:00:01 |

|* 9 | TABLE ACCESS FULL | BCAPTURE | 7050K| 228M| | 115K (
3)| 00:23:09 |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("BCAPTURE"."URN"="HOTLISTMATCH"."CAPTUREID"(+))
3 - access("CAMERA"."FEEDIDENTIFIER"="BCAPTURE"."FEEDIDENTIFIER" AND
"CAMERA"."SOURCEIDENTIFIER"="BCAPTURE"."SOURCEIDENTIFIER" AND
"CAMERA"."CAMERAID"="BCAPTURE"."CAMERAIDENTIFIER")
4 - access("CAMERAGROUPCAMERA"."CAMERAURN"="CAMERA"."URN"(+))
5 - access("CAMERAGROUP"."URN"="CAMERAGROUPCAMERA"."CAMERAGROUPURN"(+))
9 - filter("BCAPTURE"."CAPTUREDATE">=TO_DATE('2007-03-19 00:00:00', 'yyyy-

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
mm-ddhh24:mi:ss'))

28 rows selected.

SQL> select count(*) from BCAPTURE;

COUNT(*)
----------
38408590

SQL> select count(*) from hotlistmatch;

COUNT(*)
----------
1034996

SQL> select num_rows,blocks,degree from user_tables where table_name='BCAPTURE';

NUM_ROWS BLOCKS DEGREE
---------- ---------- ----------
38287440 517657 1

SQL> select num_rows,blocks,degree from user_tables where table_name='HOTLISTMATCH';

NUM_ROWS BLOCKS DEGREE
---------- ---------- ----------
1031856 149892 1

As you can see the bcapture table has grown by another 200k since i ran the last analyze yesterday. The xplain plan shows the huge amount of cpu used when doing the full tbs scans and also the time taken ( how accurate is this as when i ran the query yesterday it took about 6-7 mins in total). I have been speaking to the developers and they have mentioned that they have optimised most of these queries and switched to dedicated server mode for the next version of the application. However, to upgrade to this they need to run a verication check on the current dataset. Because of the high cpu usage this is taking too long and is bound to fall over. Now the goal is to reduce the cpu load and apart from not running these heavy searches i have these ideas:

1/ set shared servers to 5 and max_servers to 25. This would mean only 5 are started when the db is started. Reducing overhead?
2/ Set cursor_sharing to similar. They have some queries in the current version using literals.
3/ Explore adding indexes to the bcapture table. Investigating now.

Any other ideas/advice will be highly appreciated.

Thanks for all your help so far.

Chucks