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

There looks to be opportunity to tune that SQL, you’re going to the same gv$ view in your with clause (with a distinct and analytics) as you’re joining to later, presumably you should be able to just do the analytics in one go.

Either way, this is a gv$ so needs to check each instance, so you get a parallel thread per instance.

Why is a query against a gv$ view (which should be in memory on each instance) consuming IO?

Thanks,
Andy

On Fri, May 26, 2023 at 6:41 AM, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:

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