Re: --> outcome each time query ran slow

On Mon, 06 Jan 2003 20:42:34 GMT, "Stephan" <test_at_test.com> wrote:

>select id_district district_id, district_name
>FROM district, district_street where
>(district_id=id_district) AND (place_street_id=707)
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ---------- ------
>----
>Parse 2 0.00 0.00 0 0 0
>0
>Execute 1 0.00 1.55 1 3 9
>0
>Fetch 1 0.01 0.24 7 6 4
>3
>------- ------ -------- ---------- ---------- ---------- ---------- ------
>----
>total 4 0.01 1.79 8 9 13
>3
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 20 (PREVENT)
>
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 HASH JOIN [:Q407002]
> SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */ A1.C0,A1.C1 FROM
> :Q407001 A1,:Q407000 A2 WHERE A2.C0=A1.C0
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DISTRICT' [:Q407001]
> SELECT /*+ Q407001 NO_EXPAND ROWID(A1) */ A1."ID_DISTRICT" C0,
> A1."DISTRICT_NAME" C1 FROM "DISTRICT" PX_GRANULE(0,
> BLOCK_RANGE, DYNAMIC) A1
> 0 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'DISTRICT_STREET_4'
> (NON-UNIQUE) [:Q407000]
Finally we are talking...
Someway or another you have enabled parallel query, and you really don't have sufficient CPUs (most likely 2). I'm just too tired right now to find out which parameters to disable, you could try to issue alter table ... noparallel and see whether this helps or specify a hint in your select to disable parallel query. Every parallel query needs at least 3 processes: 2 slave processes and 1 process to coordinate them. If you have only 2 CPUs you are toast and enabling parallel query will severely aggravate the situation.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jan 06 2003 - 15:54:11 CST