How to avoid using the execution plan with parallel on oracle rac 19.13?

11:40:01 AM CPU %user %nice %system %iowait %steal
> %idle
> 11:50:01 AM all 5.96 0.00 2.90
> *46.53* 0.43 44.18
> 12:00:01 PM all 6.03 0.00 3.04 *48.79*
> 0.44 41.70
> 12:10:01 PM all 6.23 0.00 3.08 *50.25*
> 0.44 40.00
> 12:20:01 PM all 6.13 0.00 2.92 *42.71*
> 0.45 47.78
> 12:30:01 PM all 5.79 0.00 2.96 *45.31*
> 0.45 45.49
> 12:40:01 PM all 5.90 0.00 3.00 *47.26*
> 0.44 43.40
> 12:50:01 PM all 5.90 0.00 2.92 *45.98*
> 0.44 44.76
> 01:00:01 PM all 5.97 0.00 3.04 *44.85*
> 0.44 45.70
> 01:10:01 PM all 6.47 0.00 3.22 *51.91*
> 0.45 37.95

At the same time the SQL seems to take about *15 to 20 mins* executing completely. The following is detailed stuff.

WITH SQLAREA_PLAN_HASH AS( SELECT DISTINCT INST_ID,SQL_ID,PLAN_HASH_VALUE
> FROM (SELECT INST_ID,SQL_ID,PLAN_HASH_VALUE, row_number() over (partition
> by inst_ID order by ELAPSED_TIME desc nulls last) rn1, row_number() over
> (partition by inst_ID order by ROUND((ELAPSED_TIME / 1000) /
> DECODE(EXECUTIONS, 0, 1, EXECUTIONS), 0) desc nulls last) rn2 FROM
> *GV$SQLAREA_PLAN_HASH* WHERE LAST_ACTIVE_TIME >= sysdate - 6/(24*60) AND
> PARSING_SCHEMA_NAME NOT IN
> ('SYSMAN','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','MGMT_VIEW','SYS','SYSTEM','OUTLN','DIP','ORACLE_OCM','XS$NULL','SQLTXPLAIN'))
> WHERE RN1 <=60 OR RN2 <=60 ) SELECT VSPH.INST_ID, VSPH.SQL_ID,
> SUBSTR(VSPH.SQL_TEXT, 0, 50) AS SQL_TEXT_BRIEF, VSPH.SQL_FULLTEXT,
> VSPH.SHARABLE_MEM, VSPH.PERSISTENT_MEM, VSPH.RUNTIME_MEM, VSPH.SORTS,
> VSPH.VERSION_COUNT, VSPH.LOADED_VERSIONS, VSPH.OPEN_VERSIONS,
> VSPH.USERS_OPENING, VSPH.FETCHES, VSPH.EXECUTIONS,
> VSPH.PX_SERVERS_EXECUTIONS, VSPH.END_OF_FETCH_COUNT, VSPH.USERS_EXECUTING,
> VSPH.LOADS, VSPH.FIRST_LOAD_TIME, VSPH.INVALIDATIONS, VSPH.PARSE_CALLS,
> VSPH.DISK_READS, VSPH.DIRECT_WRITES, VSPH.BUFFER_GETS,
> VSPH.APPLICATION_WAIT_TIME / 1000 AS APPLICATION_WAIT_TIME,
> VSPH.CONCURRENCY_WAIT_TIME / 1000 AS CONCURRENCY_WAIT_TIME,
> VSPH.CLUSTER_WAIT_TIME / 1000 AS CLUSTER_WAIT_TIME, VSPH.USER_IO_WAIT_TIME
> / 1000 AS USER_IO_WAIT_TIME, VSPH.PLSQL_EXEC_TIME / 1000 AS
> PLSQL_EXEC_TIME, VSPH.JAVA_EXEC_TIME / 1000 AS JAVA_EXEC_TIME,
> VSPH.ROWS_PROCESSED, VSPH.COMMAND_TYPE, VSPH.OPTIMIZER_MODE,
> VSPH.OPTIMIZER_COST, VSPH.OPTIMIZER_ENV_HASH_VALUE, VSPH.PARSING_USER_ID,
> VSPH.PARSING_SCHEMA_ID, VSPH.PARSING_SCHEMA_NAME, VSPH.KEPT_VERSIONS,
> VSPH.HASH_VALUE, VSPH.PLAN_HASH_VALUE, VSPH.MODULE, VSPH.ACTION,
> VSPH.SERIALIZABLE_ABORTS, VSPH.OUTLINE_CATEGORY, VSPH.CPU_TIME / 1000 AS
> CPU_TIME, VSPH.ELAPSED_TIME / 1000 AS ELAPSED_TIME, VSPH.OUTLINE_SID,
> VSPH.REMOTE, VSPH.OBJECT_STATUS, VSPH.LITERAL_HASH_VALUE,
> VSPH.LAST_LOAD_TIME, VSPH.SQL_PROFILE, VSPH.PROGRAM_ID, VSPH.PROGRAM_LINE#
> AS PROGRAM_LINE, VSPH.LAST_ACTIVE_TIME, VSPH.TYPECHECK_MEM,
> VSPH.IO_CELL_OFFLOAD_ELIGIBLE_BYTES, VSPH.IO_INTERCONNECT_BYTES,
> VSPH.PHYSICAL_READ_REQUESTS, VSPH.PHYSICAL_READ_BYTES,
> VSPH.PHYSICAL_WRITE_REQUESTS, VSPH.PHYSICAL_WRITE_BYTES,
> VSPH.OPTIMIZED_PHY_READ_REQUESTS, VSPH.IO_CELL_UNCOMPRESSED_BYTES,
> VSPH.IO_CELL_OFFLOAD_RETURNED_BYTES FROM *GV$SQLAREA_PLAN_HASH*
> VSPH,SQLAREA_PLAN_HASH SPH WHERE VSPH.INST_ID=SPH.INST_ID AND
> VSPH.SQL_ID=SPH.SQL_ID AND VSPH.PLAN_HASH_VALUE=SPH.PLAN_HASH_VALUE;
By the way I've found out it always uses the *2 number of parallels* to run, so weird? Although I've set *parallel_force_local* to *TRUE* on two nodes (the original value is *FALSE*) respectively.

Now, how to avoid using the execution plan with parallel on my oracle rac?

Could you help me troubleshoot this complex case? Thanks beforehand.

Best Regards
Quanwen Zhao

--
http://www.freelists.org/webpage/oracle-l