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.
Quanwen Zhao <quanwenzhao@xxxxxxxxx> 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
