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