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

Hello Quanwen,

> 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.

That is not weird as you are querying a GV$ and using RAC - this is works as designed. This behavior is very very old as MOS ID #734139.1 proves :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Quanwen Zhao <quanwenzhao_at_gmail.com> hat am 26.05.2023 15:40 CEST geschrieben:
>
> Hello forks :-),
>
> My oracle rac 19.13 (test environment) with two nodes has been consuming plenty of disk IO because of a SQLchecking top sql from GV$SQLAREA_PLAN_HASH. As I can see %iowait is always 45%-50% when using the linux command "sar" to observe.
>
> 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