# DCdbm.ctl:203:Collects Oracle RDBMS Memory Information # $Id: DCdbm.ctl,v 1.8 2015/10/20 13:17:29 RDA Exp $ # ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/DCdbm.ctl,v 1.8 2015/10/20 13:17:29 RDA Exp $ # # Change History # 20151020 JJU Enhance cursor sharability reports. =head1 NAME DB:DCdbm - Collects Oracle RDBMS Memory Information =head1 DESCRIPTION This module collects the Oracle RDBMS memory-related information. The following report can be generated and are regrouped under C: =cut if !${I_DBC/E} return echo tput('bold'),'Processing DB.DBM module ...',tput('off') # Initialization var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' pretoc '^1:RDBMS' pretoc '1+:RDBMS Memory' =head2 no_privileges - Insufficient Privileges This module must be run as a SYSDBA user. =cut if !${B_SYSDBA/P} {report no_privileges write 'The RDBMS Memory module requires you to connect as a SYSDBA user. \ This can be set up during the DB module setup.' toc '2:[[',getFile(),'][rda_report][Insufficient Privileges]]' toc '-:RDBMS' return } # Load the common macros run DB:DBinfo() # Get database version var $ver = get_db_version() =head2 respool - Reserved Pool Information Collects information about Reserved Pool (or Reserved Area) inside the Shared Pool. =cut debug ' Inside DBM module, gathering Reserved Pool information' report respool var $TTL = '---+!! Reserved Pool Information' var @TTL = ('',\ '---+ Reserved Pool Percentage Set',\ '---+ Reserved Pool Information') var @HDR = ('',\ '|*Parameter* |*Session Value* |*Instance Value* |',\ '|*Instance* | *Free Space*| *Average Free Space*| *Free Count*\ | *Max Free Size*| *Used Space*| *Average Used Size*\ | *Used Count*| *Max Used Size*| *Requests*| *Request Misses*\ | *Last Miss Size*| *Max Miss Size*| *Request Failures*\ | *Last Failure Size*| *Aborted Request Threshold*\ | *Aborted Requests*| *Last Aborted Size*|') set $sql {SELECT '|' || " a.ksppinm || '| ' || " b.ksppstvl || '| ' || " c.ksppstvl || '|' " FROM sys.x$ksppi a,sys.x$ksppcv b,sys.x$ksppsv c " WHERE a.indx = b.indx " AND a.indx = c.indx " AND a.ksppinm = '_shared_pool_reserved_pct'; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " p.inst_id || ' | ' || " p.free_space || '| ' || " p.avg_free_size || '| ' || " p.free_count || '| ' || " p.max_free_size || '| ' || " p.used_space || '| ' || " p.avg_used_size || '| ' || " p.used_count || '| ' || " p.max_used_size || '| ' || " s.requests || '| ' || " s.request_misses || '| ' || " s.last_miss_size || '| ' || " s.max_miss_size || '| ' || " s.request_failures || '| ' || " s.last_failure_size || '| ' || " s.aborted_request_threshold || '| ' || " s.aborted_requests || '| ' || " s.last_aborted_size || '|' " FROM (SELECT ROUND(AVG(x$ksmspr.inst_id),2) inst_id, " SUM(DECODE(ksmchcls,'R-free',ksmchsiz,0)) free_space, " ROUND(AVG(DECODE(ksmchcls,'R-free',ksmchsiz,0)),2) avg_free_size, " SUM(DECODE(ksmchcls,'R-free',1,0)) free_count, " MAX(DECODE(ksmchcls,'R-free',ksmchsiz,0)) max_free_size, " SUM(DECODE(ksmchcls,'R-free',0,ksmchsiz)) used_space, " ROUND(AVG(DECODE(ksmchcls,'R-free',0,ksmchsiz)),2) avg_used_size, " SUM(DECODE(ksmchcls,'R-free',0,1)) used_count, " MAX(DECODE(ksmchcls,'R-free',0,ksmchsiz)) max_used_size " FROM x$ksmspr " WHERE ksmchcom not like '%reserved sto%') p, " (SELECT SUM(kghlurcn) requests, " SUM(kghlurmi) request_misses, " MAX(kghlurmz) last_miss_size, " MAX(kghlurmx) max_miss_size, " SUM(kghlunfu) request_failures, " MAX(kghlunfs) last_failure_size, " MAX(kghlumxa) aborted_request_threshold, " SUM(kghlumer) aborted_requests, " MAX(kghlumes) last_aborted_size " FROM x$kghlu) s; } call separator(1) call writeSql($sql) call separator(0,'Reserved Pool Information') =head2 subpool - Number of Subpools Collects the number of subpools. =cut debug ' Inside DBM module, gathering number of subpools' report subpool prefix {write '---+ Number of Subpools' write '|*Parameter* |*Session Value* |*Instance Value* |' } set $sql {SELECT '|' || " a.ksppinm || ' | ' || " b.ksppstvl || '| ' || " c.ksppstvl || '|' " FROM sys.x$ksppi a,sys.x$ksppcv b,sys.x$ksppsv c " WHERE a.indx = b.indx " AND a.indx = c.indx " AND a.ksppinm LIKE '%kghdsidx%'; } call writeSql($sql) if isCreated(true) {write $TOP toc '2:[[',getFile(),'][rda_report][Number of Subpools]]' } =head2 spresmal - Shared Pool Reserved Minimum Allocation Collects shared pool reserved minimum allocation information. =cut debug ' Inside DBM module, gathering shared pool reserved minimum \ allocation' report spresmal prefix {write '---+ Shared Pool Reserved Minimum Allocation' write '|*Name* |*Value* |' } set $sql {SELECT '|' || " nam.ksppinm || ' | ' || " val.ksppstvl || '|' " FROM x$ksppi nam,x$ksppsv val " WHERE nam.indx = val.indx " AND nam.ksppinm LIKE '%shared%' " ORDER BY nam.ksppinm; } call writeSql($sql) if isCreated() {write $TOP toc '2:[[',getFile(),'][rda_report][Shared Pool Reserved Minimum Allocation]]' } =head2 lchitrat - Library Cache Hit Ratio Collects the amount of times a statement must be parsed instead of being reused. =cut debug ' Inside DBM module, gathering library cache hit ratio information' report lchitrat prefix {write '---+ Library Cache Hit Ratio' write '| *Executions*| *Cache Misses While Executing*|' } set $sql {SELECT '| ' || " SUM(pins) || '| ' || " SUM(reloads) || '|' " FROM v$librarycache; } call writeSql($sql) if isCreated(true) {write $TOP toc '2:[[',getFile(),'][rda_report][Library Cache Hit Ratio]]' } =for stopwords HighWaterMark =head2 hwm - HighWaterMark Information Collects statistics about code in the library cache. =cut debug ' Inside DBM module, gathering HighWaterMark information' report hwm prefix {write '---+ HighWaterMark Information' write '| *Max Invalidations*| *Max Versions Loaded*| *Versions HighWaterMark*\ | *Largest Memory object*|' } set $sql {SELECT '| ' || " MAX(invalidations) || '| ' || " MAX(loaded_versions) || '| ' || " MAX(version_count) || '| ' || " MAX(sharable_mem) || '|' " FROM v$sqlarea; } call writeSql($sql) if isCreated(true) {write $TOP toc '2:[[',getFile(),'][rda_report][HighWaterMark Information]]' } =head2 sgacomp - SGA Component Information Collects information about the auto-tuned components in the SGA and shows activity moving memory around in the SGA. (Oracle 9i Release 2 and later.) =cut if match($ver,'^(92|10|11|12)') {debug ' Inside DBM module, gathering SGA component information' report sgacomp var $TTL = '---+!! SGA Component Information' var @TTL = ('',\ '---+ SGA Components Size',\ '---+ SGA Components Last Operation',\ '---+ SGA Components Resize Information',\ '---+ Explicit Settings For Components') var @HDR = ('',\ '|*Component* | *Current Size*| *Min Size*| *Max Size*\ | *Granule Size*|',\ '|*Component* |*Operation Type* |*Operation Mode* \ |*Timestamp* |',\ '|*Component* |*Parameter* | *Initial Size*| *Final Size*\ |*Status* |*Changed At* |',\ '|*Component* | *Low Size*| *Low Size (MiB)*| *High Size*\ | *High Size (MiB)*|') if match($ver,'^(92|10)') {var $sep = 5 set $sql {SELECT '|' || " component || ' | ' || " current_size || '| ' || " min_size || '| ' || " max_size || '| ' || " granule_size || '|' " FROM v$sga_dynamic_components; "SELECT '| | ' || " SUM(current_size) || '| | | |' " FROM v$sga_dynamic_components; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " component || ' |' || " last_oper_type || ' |' || " last_oper_mode || ' |' || " TO_CHAR(last_oper_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM v$sga_dynamic_components; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " component || ' |' || " parameter || ' | ' || " initial_size || '| ' || " final_size || '|' || " status || ' |' || " TO_CHAR(end_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM v$sga_resize_ops; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " component || ' | ' || " MIN(final_size) || '| ' || " MIN(final_size / 1048576) || '| ' || " MAX(final_size) || '| ' || " MAX(final_size / 1048576) || '|' " FROM v$sga_resize_ops " GROUP BY component; } } else {call push(@TTL,'---+ Last modified Explicit Settings For Components') call push(@HDR,'|*Component* |*Last Modified* | *Size*|') var $sep = 6 set $sql {SELECT '|' || " component || ' | ' || " current_size || '| ' || " min_size || '| ' || " max_size || '| ' || " granule_size || '|' " FROM v$memory_dynamic_components; "SELECT '| | ' || " SUM(current_size) || '| | | |' " FROM v$sga_dynamic_components; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " component || ' |' || " last_oper_type || ' |' || " last_oper_mode || ' |' || " TO_CHAR(last_oper_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM v$memory_dynamic_components; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " component || ' |' || " parameter || ' | ' || " initial_size || '| ' || " final_size || '|' || " status || ' |' || " TO_CHAR(end_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM v$memory_resize_ops; "PROMPT ___Macro_separator(4)___ "SELECT '|' || " component || ' | ' || " MIN(final_size) || '| ' || " MIN(final_size/1048576) || '| ' || " MAX(final_size) || '| ' || " MAX(final_size/1048576) || '|' " FROM v$memory_resize_ops " GROUP BY component; "PROMPT ___Macro_separator(5)___ "SELECT '|' || " component || ' |' || " lasttime || '| ' || " setting || '| ' " FROM (SELECT component, " TO_CHAR(end_time,'DD-Mon-YYYY HH24:MI:SS') lasttime, " MAX(final_size) setting " FROM v$memory_resize_ops " GROUP BY component,end_time " UNION " SELECT component, " TO_CHAR(end_time,'DD-Mon-YYYY HH24:MI:SS') lasttime, " MIN(final_size) setting " FROM v$memory_resize_ops " GROUP BY component,end_time) " ORDER BY component,setting; } if match($ver,'^(112|12)') {incr $sep call push(@TTL,'---+ Historical Memory Resizing Operations') call push(@HDR,'|*Start Time* |*End Time* |*Component* |*Operation Type* \ |*Operation Mode* | *Initial Size*| *Target Size*\ | *Final Size*|*Status* |') append $sql {PROMPT ___Macro_separator(6)___ "SELECT '|' || " TO_CHAR(start_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(end_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " component || ' |' || " oper_type || ' |' || " oper_mode || ' | ' || " initial_size || '| ' || " target_size || '| ' || " final_size || '|' || " status || ' |' " FROM dba_hist_memory_resize_ops " ORDER BY start_time,end_time; } } } if match($ver,'^(10|11|12)') {call push(@TTL,'---+ SGA Information') call push(@HDR,'|*Name* | *Bytes*|*Auto* |') append $sql {PROMPT ___Macro_separator(:1)___ "SELECT '|' || " name || ' | ' || " bytes || '|' || " resizeable || ' |' " FROM v$sgainfo; } } call separator(1) call writeSql(bindSql($sql,$sep)) call separator(0,'SGA Component Information') } =head2 sgastat - Top SGA Components Collects top SGA components by size. =cut debug ' Inside DBM module, gathering top SGA components' report sgastat if match($ver,'^7') {prefix {write '---+ Top SGA Components' write '|*Component* | *Size*|' } set $sql {SELECT '|' || " name || ' | ' || " bytes || '|' " FROM v$sgastat " ORDER BY bytes DESC; } } else {prefix {write '---+ Top SGA Components' write '|*Pool* |*Component* | *Size*|' } set $sql {SELECT '|' || " pool || ' |' || " name || ' | ' || " bytes || '|' " FROM v$sgastat " ORDER BY bytes DESC; } } var $max = ${N_TOPSGA:20} if match($max,'^\d+$') decr $max else var $max = 19 if loadSql($sql) call writeLastSql(0,$max) if isCreated(true) {write $TOP toc '2:[[',getFile(),'][rda_report][Top SGA Components]]' } =head2 libcache - Library Cache Information Collects overview information about the shared pool and the library cache usage. (Oracle 9i Release 2 and later.) =cut if match($ver,'^(92|10|11|12)') {debug ' Inside DBM module, gathering library cache information' report libcache prefix {write '---+ Library Cache Information' write ' * If database started recently, this data is not as useful.' if match($ver,'^12') write ' * Some information, like the reserved shared pool details, are \ typically not available in a pluggable database.' write '%BR%' } if match($ver,'^(92|101)') {set $sql {PROMPT ___Set_VMS___ "SET define on "COL val1 NEW_VAL x_sgasize NOPRINT "COL val2 NEW_VAL x_sp_size NOPRINT "COL val3 NEW_VAL x_lp_size NOPRINT "COL val4 NEW_VAL x_jp_size NOPRINT "COL val5 NEW_VAL x_bc_size NOPRINT "COL val6 NEW_VAL x_other_size NOPRINT "SELECT val1, " val2, " val3, " val4, " val5, " val6 " FROM (SELECT SUM(bytes) val1 " FROM v$sgastat) s1, " (SELECT NVL(SUM(bytes),0) val2 " FROM v$sgastat " WHERE pool='shared pool') s2, " (SELECT NVL(SUM(bytes),0) val3 " FROM v$sgastat " WHERE pool='large pool') s3, " (SELECT NVL(SUM(bytes),0) val4 " FROM v$sgastat " WHERE pool='java pool') s4, " (SELECT NVL(SUM(bytes),0) val5 " FROM v$sgastat " WHERE name='buffer_cache') s6, " (SELECT NVL(SUM(bytes),0) val6 " FROM v$sgastat " WHERE name IN ('log_buffer','fixed_sga')) s7; " "COL val1 NEW_VAL x_sp_used NOPRINT "COL val2 NEW_VAL x_sp_used_shr NOPRINT "COL val3 NEW_VAL x_sp_used_per NOPRINT "COL val4 NEW_VAL x_sp_used_run NOPRINT "COL val5 NEW_VAL x_sp_no_stmts NOPRINT "COL val6 NEW_VAL x_sp_vers NOPRINT "SELECT SUM(sharable_mem+persistent_mem+runtime_mem) val1, " SUM(sharable_mem) val2, " SUM(persistent_mem) val3, " SUM(runtime_mem) val4, " COUNT(*) val5, " MAX(version_count) val6 " FROM v$sqlarea; " "COL val1 NEW_VAL x_1time_sum NOPRINT "COL val2 NEW_VAL x_1time_ttl NOPRINT "SELECT SUM(sharable_mem+persistent_mem+runtime_mem) val1, " COUNT(*) val2 " FROM v$sqlarea " WHERE executions=1; " "COL val2 NEW_VAL x_sp_no_obj NOPRINT "SELECT COUNT(*) val2 " FROM v$db_object_cache; " "COL val2 NEW_VAL x_sp_no_kept_chks NOPRINT "COL val3 NEW_VAL x_sp_sz_kept_chks NOPRINT "SELECT DECODE(COUNT(*),'',0,COUNT(*)) val2, " DECODE(SUM(sharable_mem),'',0,SUM(sharable_mem)) val3 " FROM v$db_object_cache " WHERE kept='YES'; " "COL val2 NEW_VAL x_sp_free_chks NOPRINT "SELECT SUM(bytes) val2 " FROM v$sgastat " WHERE name='free memory' " AND pool='shared pool'; " "COL val2 NEW_VAL x_sp_no_pins NOPRINT "SELECT COUNT(*) val2 " FROM v$session a, " v$sqltext b " WHERE a.sql_address||a.sql_hash_value = b.address||b.hash_value; " "COL val2 NEW_VAL x_sp_sz_pins NOPRINT "SELECT SUM(sharable_mem+persistent_mem+runtime_mem) val2 " FROM v$session a, " v$sqltext b, " v$sqlarea c " WHERE a.sql_address||a.sql_hash_value = b.address||b.hash_value " AND b.address||b.hash_value = c.address||c.hash_value; " "COL val3 NEW_VAL x_tot_lc NOPRINT "SELECT SUM(lc_inuse_memory_size)+SUM(lc_freeable_memory_size) val3 " FROM v$library_cache_memory; " "COL val2 NEW_VAL x_sp_avail NOPRINT "SELECT &x_sp_size-(&x_tot_lc*1048576)-&x_sp_used val2 " FROM dual; " "COL val2 NEW_VAL x_sp_other NOPRINT "SELECT &x_sp_size-(&x_tot_lc*1048576) val2 " FROM dual; " "COL val1 NEW_VAL x_trend_4031 NOPRINT "COL val2 NEW_VAL x_trend_size NOPRINT "COL val3 NEW_VAL x_trend_rS NOPRINT "COL val4 NEW_VAL x_trend_rs_size NOPRINT "SELECT request_misses val1, " DECODE(request_misses,0,0,last_Miss_Size) val2, " request_failures val3, " DECODE(request_failures,0,0,last_failure_size) val4 " FROM v$shared_pool_reserved; " "SET serveroutput on size 1000000 "SELECT '|*Database Started*| ' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || '|' || CHR(10) || " '|*Instance Name/Number*| ' || instance_name || '/' || " instance_number || '|' " FROM v$instance; "BEGIN " dbms_output.put_line('| ||'); " dbms_output.put_line('|*Breakdown of SGA*| ' || " ROUND(&x_sgasize / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Shared Pool*| ' || " ROUND(&x_sp_size / 1048576,2) || ' MiB (' || " 100*ROUND(&x_sp_size / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Large Pool*| ' || " ROUND(&x_lp_size / 1048576,2) || ' MiB (' || " 100*ROUND(&x_lp_size / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Java Pool*| ' || " ROUND(&x_jp_size / 1048576,2) || ' MiB (' || " 100*ROUND(&x_jp_size / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Buffer Cache*| ' || " ROUND(&x_bc_size / 1048576,2) || ' MiB (' || " 100*ROUND(&x_bc_size / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Other Areas in SGA*| ' || " ROUND(&x_other_size / 1048576,2) || ' MiB (' || " 100*ROUND(&x_other_size / &x_sgasize,2) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line('|*High Level Breakdown of Memory* ||'); " dbms_output.put_line('|*Sharable*| ' || " ROUND(&x_sp_used_shr / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Persistent*| ' || " ROUND(&x_sp_used_per / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Runtime*| ' || " ROUND(&x_sp_used_run / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*SQL Memory Usage (Total)*| ' || " ROUND(&x_sp_used / 1048576,2) || 'MiB (' || " 100*ROUND(&x_sp_used / &x_sp_size,0) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line('|*No Guidelines on SQL in Library Cache%BR%' || " '(If a lot of pinned code, may need larger Shared Pool)* ||'); " dbms_output.put_line( " '|*Number of SQL Statements*| &x_sp_no_stmts|'); " dbms_output.put_line( " '|*Number of Pinned SQL Statements*| &x_sp_no_pins|'); " dbms_output.put_line( " '|*Number of Programmatic Constructs*| &x_sp_no_obj|'); " dbms_output.put_line( " '|*Number of Programmatic Construct Chunks*| &x_sp_no_kept_chks|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*Efficiency Analysis%BR%High versions (100s) could be bug* ||'); " dbms_output.put_line('|*Max Child Cursors found*| &x_sp_vers|'); " dbms_output.put_line('|*Programmatic Construct Memory Size*| ' || " ROUND(&x_sp_sz_kept_chks / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Pinned SQL Statements Memory Size*| ' || " ROUND(&x_sp_sz_pins / 1048576,2) || ' MiB|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*Library Cache at 50% or 60% of Shared Pool not uncommon*||'); " dbms_output.put_line('|*Estimated Total Library Cache Memory Usage*| ' || " 1.1*&x_tot_lc || ' MiB (' || " 100*ROUND((1.1*&x_tot_lc) / (&x_sp_size / 1048576),2) || '%)|'); " dbms_output.put_line('|*Other Shared Pool Memory *| ' || " ROUND(&x_sp_other / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Shared Pool Free Memory Chunks*| ' || " ROUND(&x_sp_free_chks / 1048576,2) || ' MiB (' || " 100*ROUND(&x_sp_free_chks / &x_sp_size,2) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*Ideal percentages for one time executions is 20% or lower* ||'); " dbms_output.put_line('|*Number of Objects Executed Only One Time*| ' || " &x_1time_ttl || ' (' || " 100*ROUND(&x_1time_ttl / &x_sp_no_stmts,2) || '%)|'); " dbms_output.put_line('|*Memory for 1 Time Executions*| ' || " ROUND(&x_1time_sum / 1048576,2) || ' MiB (' || " 100*ROUND(&x_1time_sum / &x_sp_used,2) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*0 misses is ideal, but if growing value points to memory issues*||'); " dbms_output.put_line( " '|*Number of Misses for Memory*| &x_trend_rs|'); " dbms_output.put_line( " '|*Size of Last Miss*| &x_trend_rs_size|'); " dbms_output.put_line( " '|*Number of Misses for Reserved Area*| &x_trend_4031|'); " dbms_output.put_line( " '|*Size of Last Miss for Reserved Area*| &x_trend_size|'); "END; "/ } } else {set $sql {PROMPT ___Set_VMS___ "SET define on "COL val1 NEW_VAL x_sgasize NOPRINT "COL val2 NEW_VAL x_sp_size NOPRINT "COL val3 NEW_VAL x_lp_size NOPRINT "COL val4 NEW_VAL x_jp_size NOPRINT "COL val5 NEW_VAL x_bc_size NOPRINT "COL val6 NEW_VAL x_other_size NOPRINT "COL val7 NEW_VAL x_str_size NOPRINT "COL val8 NEW_VAL x_KGH NOPRINT "SELECT val1, " val2, " val3, " val4, " val5, " val6, " val7, " val8 " FROM (SELECT SUM(bytes) val1 " FROM v$sgastat) s1, " (SELECT NVL(SUM(bytes),0) val2 " FROM v$sgastat " WHERE pool='shared pool') s2, " (SELECT NVL(SUM(bytes),0) val3 " FROM v$sgastat " WHERE pool='large pool') s3, " (SELECT NVL(SUM(bytes),0) val4 " FROM v$sgastat " WHERE pool='java pool') s4, " (SELECT NVL(SUM(bytes),0) val5 " FROM v$sgastat " WHERE name='buffer_cache') s5, " (SELECT NVL(SUM(bytes),0) val6 " FROM v$sgastat " WHERE name IN ('log_buffer','fixed_sga')) s6, " (SELECT NVL(SUM(bytes),0) val7 " FROM v$sgastat " WHERE pool='streams pool') s7, " (SELECT NVL(SUM(bytes),0) val8 " FROM v$sgastat " WHERE pool='shared pool' " AND name='KGH: NO ACCESS') s8; " "COL val1 NEW_VAL x_sp_used NOPRINT "COL val2 NEW_VAL x_sp_used_shr NOPRINT "COL val3 NEW_VAL x_sp_used_per NOPRINT "COL val4 NEW_VAL x_sp_used_run NOPRINT "COL val5 NEW_VAL x_sp_no_stmts NOPRINT "COL val6 NEW_VAL x_sp_vers NOPRINT "SELECT SUM(sharable_mem+persistent_mem+runtime_mem) val1, " SUM(sharable_mem) val2, " SUM(runtime_mem) val4, " SUM(persistent_mem) val3, " COUNT(*) val5, " MAX(version_count) val6 " FROM v$sqlarea; " "COL val1 NEW_VAL x_1time_sum NOPRINT "COL val2 NEW_VAL x_1time_ttl NOPRINT "SELECT SUM(sharable_mem+persistent_mem+runtime_mem) val1, " COUNT(*) val2 " FROM v$sqlarea " WHERE executions=1; " "COL val1 NEW_VAL x_ra NOPRINT "SELECT ROUND(NVL((used_space+free_space),0),2) val1 " FROM v$shared_pool_reserved; " "COL val2 NEW_VAL x_sp_no_obj NOPRINT "SELECT COUNT(*) val2 " FROM v$db_object_cache; " "COL val2 NEW_VAL x_sp_no_kept_chks NOPRINT "COL val3 NEW_VAL x_sp_sz_kept_chks NOPRINT "SELECT DECODE(COUNT(*),'',0,COUNT(*)) val2, " DECODE(SUM(sharable_mem),'',0,SUM(sharable_mem)) val3 " FROM v$db_object_cache " WHERE kept='YES'; " "COL val2 NEW_VAL x_sp_free_chks NOPRINT "SELECT SUM(bytes) val2 " FROM v$sgastat " WHERE name='free memory' " AND pool='shared pool'; " "COL val2 NEW_VAL x_sp_no_pins NOPRINT "SELECT COUNT(*) val2 " FROM v$session a, " v$sqltext b " WHERE a.sql_address||a.sql_hash_value = b.address||b.hash_value; " "COL val2 NEW_VAL x_sp_sz_pins NOPRINT "SELECT SUM(sharable_mem+persistent_mem+runtime_mem) val2 " FROM v$session a, " v$sqltext b, " v$sqlarea c " WHERE a.sql_address||a.sql_hash_value = b.address||b.hash_value " AND b.address||b.hash_value = c.address||c.hash_value; " "COL val3 NEW_VAL x_tot_lc NOPRINT "SELECT SUM(lc_inuse_memory_size)+SUM(lc_freeable_memory_size) val3 " FROM v$library_cache_memory; " "COL val2 NEW_VAL x_sp_avail NOPRINT "SELECT &x_sp_size-(&x_tot_lc*1048576)-&x_sp_used val2 " FROM dual; " "COL val2 NEW_VAL x_sp_other NOPRINT "SELECT &x_sp_size-(&x_tot_lc*1048576) val2 " FROM dual; " "COL val1 NEW_VAL x_trend_4031 NOPRINT "COL val2 NEW_VAL x_trend_size NOPRINT "COL val3 NEW_VAL x_trend_rS NOPRINT "COL val4 NEW_VAL x_trend_rs_size NOPRINT "SELECT request_misses val1, " DECODE(request_misses,0,0,last_Miss_Size) val2, " request_failures val3, " DECODE(request_failures,0,0,last_failure_size) val4 " FROM v$shared_pool_reserved; " "SET serveroutput on size 1000000 "SELECT '|*Database Started*| ' || " TO_CHAR(startup_time,'DD-Mon-YYYY HH24:MI:SS') || '|' || CHR(10) || " '|*Instance Name/Number*| ' || instance_name || '/' || " instance_number || '|' " FROM v$instance; "BEGIN " dbms_output.put_line('| ||'); " dbms_output.put_line('|*Breakdown of SGA*| ' || " ROUND(&x_sgasize / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Shared Pool*| ' || " ROUND(&x_sp_size / 1048576,2) || ' MiB (' || " 100*ROUND(&x_sp_size / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Reserved Shared Pool*| ' || " ROUND(TO_NUMBER('&x_ra') / 1048576,2) || ' MiB (' || " 100*ROUND(TO_NUMBER('&x_ra') / &x_sp_size,2) || '%)|'); " dbms_output.put_line('|*Large Pool*| ' || " ROUND(TO_NUMBER('&x_lp_size') / 1048576,2) || ' MiB (' || " 100*ROUND(TO_NUMBER('&x_lp_size') / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Java Pool*| ' || " ROUND(TO_NUMBER('&x_jp_size') / 1048576,2) || ' MiB (' || " 100*ROUND(TO_NUMBER('&x_jp_size') / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Buffer Cache*| ' || " ROUND(TO_NUMBER('&x_bc_size') / 1048576,2) || ' MiB (' || " 100*ROUND(TO_NUMBER('&x_bc_size') / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Streams Pool*| ' || " ROUND(TO_NUMBER('&x_str_size') / 1048576,2) || ' MiB (' || " 100*ROUND(TO_NUMBER('&x_str_size') / &x_sgasize,2) || '%)|'); " dbms_output.put_line('|*Other Areas in SGA*| ' || " ROUND(TO_NUMBER('&x_other_size') / 1048576,2) || ' MiB (' || " 100*ROUND(TO_NUMBER('&x_other_size') / &x_sgasize,2) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line('|*High Level Breakdown of Memory* ||'); " dbms_output.put_line('|*Sharable*| ' || " ROUND(&x_sp_used_shr / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Persistent*| ' || " ROUND(&x_sp_used_per / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Runtime*| ' || " ROUND(&x_sp_used_run / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*SQL Memory Usage (Total)*| ' || " ROUND(&x_sp_used / 1048576,2) || 'MiB (' || " 100*ROUND(&x_sp_used / &x_sp_size,0) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line('|*No Guidelines on SQL in Library Cache%BR%' || " '(If a lot of pinned code, may need larger Shared Pool)* ||'); " dbms_output.put_line( " '|*Number of SQL Statements*| &x_sp_no_stmts|'); " dbms_output.put_line( " '|*Number of Pinned SQL Statements*| &x_sp_no_pins|'); " dbms_output.put_line( " '|*Number of Programmatic Constructs*| &x_sp_no_obj|'); " dbms_output.put_line( " '|*Number of Programmatic Construct Chunks*| &x_sp_no_kept_chks|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*Efficiency Analysis%BR%High versions (100s) could be bug* ||'); " dbms_output.put_line('|*Max Child Cursors Found*| &x_sp_vers|'); " dbms_output.put_line('|*Programmatic Construct Memory Size*| ' || " ROUND(&x_sp_sz_kept_chks / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Pinned SQL Statements Memory Size*| ' || " ROUND(&x_sp_sz_pins / 1048576,2) || ' MiB|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*Library Cache at 50% or 60% of Shared Pool not uncommon*||'); " dbms_output.put_line('|*Estimated Total Library Cache Memory Usage*| ' || " 1.1*&x_tot_lc || ' MiB (' || " 100*ROUND((1.1*&x_tot_lc) / (&x_sp_size / 1048576),2) || '%)|'); " dbms_output.put_line('|*Other Shared Pool Memory *| ' || " ROUND(&x_sp_other / 1048576,2) || ' MiB|'); " dbms_output.put_line('|*Shared Pool Free Memory Chunks*| ' || " ROUND(&x_sp_free_chks / 1048576,2) || ' MiB (' || " 100*ROUND(&x_sp_free_chks / &x_sp_size,2) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*Ideal percentages for one time executions is 20% or lower* ||'); " dbms_output.put_line('|*Number of Objects Executed Only One Time*| ' || " &x_1time_ttl || ' (' || " 100*ROUND(&x_1time_ttl / &x_sp_no_stmts,2) || '%)|'); " dbms_output.put_line('|*Memory For 1 Time Executions*| ' || " ROUND(&x_1time_sum / 1048576,2) || ' MiB (' || " 100*ROUND(&x_1time_sum / &x_sp_used,2) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*If these chunks are growing, SGA_TARGET may be too low*||'); " dbms_output.put_line( " '|*Current KGH: NO ACCESS Allocations*| ' || " ROUND(&x_KGH / 1048576,2) || ' MiB (' || " 100*ROUND(&x_KGH / &x_sp_size,2) || '%)|'); " dbms_output.put_line('| ||'); " dbms_output.put_line( " '|*0 misses is ideal, but if growing value points to memory issues*||'); " dbms_output.put_line( " '|*Number of Misses for Memory*| &x_trend_rs|'); " dbms_output.put_line( " '|*Size of Last Miss*| &x_trend_rs_size|'); " dbms_output.put_line( " '|*Number of Misses for Reserved Area*| &x_trend_4031|'); " dbms_output.put_line( " '|*Size of Last Miss for Reserved Area*| &x_trend_size|'); "END; "/ } } call writeSql($sql) if isCreated(true) {write $TOP toc '2:[[',getFile(),'][rda_report][Library Cache Information]]' } } =head2 high_version_count - High Version Count Information Gathers high version count information. =cut debug ' Inside DBM module, gathering high version count information' report high_version_count if match($ver,'^12') {set $sql {SELECT '[[[' || CHR(10) || '| ' || " version_count || '|' || " sql_id || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(sql_text), " '|', '|'), " '<', '<'), " '>', '>') || ' |' || CHR(10) || ']]]' " FROM v$sqlarea " WHERE con_id = sys_context('USERENV', 'CON_ID') " AND version_count > :1 " ORDER BY version_count; } } else {set $sql {SELECT '[[[' || CHR(10) || '| ' || " version_count || '|' || " sql_id || ' |' || " REPLACE(REPLACE(REPLACE(LTRIM(sql_text), " '|', '|'), " '<', '<'), " '>', '>') || ' |' || CHR(10) || ']]]' " FROM v$sqlarea " WHERE version_count > :1 " ORDER BY version_count; } } var $lim = ${N_VERSION_LIMIT:3000} if loadSql(bindSql($sql,$lim)) {prefix {write '---+ High Version Count Information' write '---## Using Version Count Limit: ',$lim write '| *Version Count*|*SQL ID*|*SQL Text*|' } call writeLastSql() if hasOutput(true) write $TOP } if isCreated(true) toc '2:[[',getFile(),'][rda_report][High Version Count Information]]' =head2 Child Cursors Information Identifies the reason codes to determine the cause for high number of child cursors. =cut debug ' Inside DBM module, gathering child cursors information' pretoc '2:Child Cursors Information' if match($ver,'^12') {set $sql {SELECT '|UNBOUND_CURSOR| ' || " SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SQL_TYPE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OPTIMIZER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OUTLINE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|STATS_ROW_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LITERAL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FORCE_HARD_PARSE| ' || " SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))) || '|' || " CHR(10) || '|EXPLAIN_PLAN_CURSOR| ' || " SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BUFFERED_DML_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PDML_ENV_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INST_DRTLD_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SLAVE_QC_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TYPECHECK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|AUTH_CHECK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|DESCRIBE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LANGUAGE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TRANSLATION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_EQUIV_FAILURE| ' || " SUM(TO_NUMBER(DECODE(bind_equiv_failure,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INSUFF_PRIVS| ' || " SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INSUFF_PRIVS_REM| ' || " SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))) || '|' || " CHR(10) || '|REMOTE_TRANS_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGMINER_SESSION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|INCOMP_LTRL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OVERLAP_TIME_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|EDITION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|MV_QUERY_GEN_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|USER_BIND_PEEK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|TYPCHK_DEP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|NO_TRIGGER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_CURSOR| ' || " SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ANYDATA_TRANSFORMATION| ' || " SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PDDL_ENV_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pddl_env_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TOP_LEVEL_RPI_CURSOR| ' || " SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|DIFFERENT_LONG_LENGTH| ' || " SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' || " SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))) || " '|' || CHR(10) || '|DIFF_CALL_DURN| ' || " SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_UACS_DIFF| ' || " SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PLSQL_CMP_SWITCHS_DIFF| ' || " SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))) || " '|' || CHR(10) || '|CURSOR_PARTS_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|STB_OBJECT_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|CROSSEDITION_TRIGGER_MISMATCH | ' || " SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0'))) " || '|' || CHR(10) || '|PQ_SLAVE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TOP_LEVEL_DDL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MULTI_PX_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_PEEKED_PQ_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MV_REWRITE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ROLL_INVALID_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|OPTIMIZER_MODE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PX_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|MV_STALEOBJ_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_TABLE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LITREP_COMP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PLSQL_DEBUG| ' || " SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LOAD_OPTIMIZER_STATS| ' || " SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ACL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_ARCHIVE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOCK_USER_SCHEMA_FAILED| ' || " SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))) || " '|' || CHR(10) || '|REMOTE_MAPPING_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOAD_RUNTIME_HEAP_FAILED| ' || " SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))) || " '|' || CHR(10) || '|HASH_MATCH_FAILED| ' || " SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PURGED_CURSOR| ' || " SUM(TO_NUMBER(DECODE(purged_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_LENGTH_UPGRADEABLE| ' || " SUM(TO_NUMBER(DECODE(bind_length_upgradeable,'Y',1,'N','0'))) || '|' " || '|' || CHR(10) || '|USE_FEEDBACK_STATS| ' || " SUM(TO_NUMBER(DECODE(use_feedback_stats,'Y',1,'N','0'))) || '|' " FROM v$sql_shared_cursor " WHERE address IN (SELECT address " FROM v$sqlarea " WHERE sql_id = ':1'); } } elsif match($ver,'^112') {set $sql {SELECT '|UNBOUND_CURSOR| ' || " SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SQL_TYPE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OPTIMIZER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OUTLINE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|STATS_ROW_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LITERAL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FORCE_HARD_PARSE| ' || " SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))) || '|' || " CHR(10) || '|EXPLAIN_PLAN_CURSOR| ' || " SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BUFFERED_DML_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PDML_ENV_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INST_DRTLD_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SLAVE_QC_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TYPECHECK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|AUTH_CHECK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|DESCRIBE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LANGUAGE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TRANSLATION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_EQUIV_FAILURE| ' || " SUM(TO_NUMBER(DECODE(bind_equiv_failure,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INSUFF_PRIVS| ' || " SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INSUFF_PRIVS_REM| ' || " SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))) || '|' || " CHR(10) || '|REMOTE_TRANS_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGMINER_SESSION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|INCOMP_LTRL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OVERLAP_TIME_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|EDITION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|MV_QUERY_GEN_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|USER_BIND_PEEK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|TYPCHK_DEP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|NO_TRIGGER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_CURSOR| ' || " SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ANYDATA_TRANSFORMATION| ' || " SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))) || " '|' || CHR(10) || '|INCOMPLETE_CURSOR| ' || " SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TOP_LEVEL_RPI_CURSOR| ' || " SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|DIFFERENT_LONG_LENGTH| ' || " SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' || " SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))) || " '|' || CHR(10) || '|DIFF_CALL_DURN| ' || " SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_UACS_DIFF| ' || " SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PLSQL_CMP_SWITCHS_DIFF| ' || " SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))) || " '|' || CHR(10) || '|CURSOR_PARTS_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|STB_OBJECT_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|CROSSEDITION_TRIGGER_MISMATCH | ' || " SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0'))) " || '|' || CHR(10) || '|PQ_SLAVE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TOP_LEVEL_DDL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MULTI_PX_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_PEEKED_PQ_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MV_REWRITE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ROLL_INVALID_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|OPTIMIZER_MODE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PX_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|MV_STALEOBJ_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_TABLE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LITREP_COMP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PLSQL_DEBUG| ' || " SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LOAD_OPTIMIZER_STATS| ' || " SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ACL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_ARCHIVE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOCK_USER_SCHEMA_FAILED| ' || " SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))) || " '|' || CHR(10) || '|REMOTE_MAPPING_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOAD_RUNTIME_HEAP_FAILED| ' || " SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))) || " '|' || CHR(10) || '|HASH_MATCH_FAILED| ' || " SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PURGED_CURSOR| ' || " SUM(TO_NUMBER(DECODE(purged_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_LENGTH_UPGRADEABLE| ' || " SUM(TO_NUMBER(DECODE(bind_length_upgradeable,'Y',1,'N','0'))) || '|' " FROM v$sql_shared_cursor " WHERE address IN (SELECT address " FROM v$sqlarea " WHERE sql_id = ':1'); } } elsif match($ver,'^111') {set $sql {SELECT '|UNBOUND_CURSOR| ' || " SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SQL_TYPE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OPTIMIZER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OUTLINE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|STATS_ROW_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LITERAL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FORCE_HARD_PARSE| ' || " SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))) || '|' || " CHR(10) || '|EXPLAIN_PLAN_CURSOR| ' || " SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BUFFERED_DML_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PDML_ENV_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INST_DRTLD_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SLAVE_QC_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TYPECHECK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|AUTH_CHECK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|DESCRIBE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LANGUAGE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TRANSLATION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ROW_LEVEL_SEC_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(row_level_sec_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|INSUFF_PRIVS| ' || " SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INSUFF_PRIVS_REM| ' || " SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))) || '|' || " CHR(10) || '|REMOTE_TRANS_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGMINER_SESSION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|INCOMP_LTRL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OVERLAP_TIME_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|EDITION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|MV_QUERY_GEN_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|USER_BIND_PEEK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|TYPCHK_DEP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|NO_TRIGGER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_CURSOR| ' || " SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ANYDATA_TRANSFORMATION| ' || " SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))) || " '|' || CHR(10) || '|INCOMPLETE_CURSOR| ' || " SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TOP_LEVEL_RPI_CURSOR| ' || " SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|DIFFERENT_LONG_LENGTH| ' || " SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' || " SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))) || " '|' || CHR(10) || '|DIFF_CALL_DURN| ' || " SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_UACS_DIFF| ' || " SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PLSQL_CMP_SWITCHS_DIFF| ' || " SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))) || " '|' || CHR(10) || '|CURSOR_PARTS_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|STB_OBJECT_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|CROSSEDITION_TRIGGER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0'))) " || '|' || CHR(10) || '|PQ_SLAVE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TOP_LEVEL_DDL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MULTI_PX_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_PEEKED_PQ_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MV_REWRITE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ROLL_INVALID_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|OPTIMIZER_MODE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PX_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|MV_STALEOBJ_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_TABLE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LITREP_COMP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PLSQL_DEBUG| ' || " SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LOAD_OPTIMIZER_STATS| ' || " SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ACL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_ARCHIVE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOCK_USER_SCHEMA_FAILED| ' || " SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))) || " '|' || CHR(10) || '|REMOTE_MAPPING_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOAD_RUNTIME_HEAP_FAILED| ' || " SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))) || " '|' || CHR(10) || '|HASH_MATCH_FAILED| ' || " SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))) || '|' " FROM v$sql_shared_cursor " WHERE address IN (SELECT address " FROM v$sqlarea " WHERE sql_id = ':1'); } } elsif match($ver,'^102') {set $sql {SELECT '|UNBOUND_CURSOR| ' || " SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SQL_TYPE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OPTIMIZER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OUTLINE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|STATS_ROW_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LITERAL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SEC_DEPTH_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(sec_depth_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|EXPLAIN_PLAN_CURSOR| ' || " SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BUFFERED_DML_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PDML_ENV_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INST_DRTLD_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|SLAVE_QC_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TYPECHECK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|AUTH_CHECK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|DESCRIBE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|LANGUAGE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TRANSLATION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ROW_LEVEL_SEC_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(row_level_sec_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|ROW_LEVEL_SEC_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))) || '|' || " CHR(10) || '|INSUFF_PRIVS_REM| ' || " SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))) || '|' || " CHR(10) || '|REMOTE_TRANS_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGMINER_SESSION_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|INCOMP_LTRL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|OVERLAP_TIME_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|SQL_REDIRECT_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(sql_redirect_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MV_QUERY_GEN_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|USER_BIND_PEEK_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|TYPCHK_DEP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|NO_TRIGGER_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_CURSOR| ' || " SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ANYDATA_TRANSFORMATION| ' || " SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))) || " '|' || CHR(10) || '|INCOMPLETE_CURSOR| ' || " SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TOP_LEVEL_RPI_CURSOR| ' || " SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))) || '|' || " CHR(10) || '|DIFFERENT_LONG_LENGTH| ' || " SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' || " SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LOGICAL_STANDBY_APPLY| ' || " SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_UACS_DIFF| ' || " SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PLSQL_CMP_SWITCHS_DIFF| ' || " SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))) || " '|' || CHR(10) || '|CURSOR_PARTS_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|STB_OBJECT_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ROW_SHIP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(row_ship_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|PQ_SLAVE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|TOP_LEVEL_DDL_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MULTI_PX_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|BIND_PEEKED_PQ_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|MV_REWRITE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|ROLL_INVALID_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|OPTIMIZER_MODE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|PX_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|MV_STALEOBJ_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))) || '|' || " CHR(10) || '|FLASHBACK_TABLE_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))) || " '|' || CHR(10) || '|LITREP_COMP_MISMATCH| ' || " SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))) || '|' " FROM v$sql_shared_cursor " WHERE address IN (SELECT address " FROM v$sqlarea " WHERE sql_id = ':1'); } } var %qid = () loop $lin (grepLastSql('^\|\s\d+\|')) incr $qid{field('\s*\|\s*',2,$lin)} loop $qid (keys(%qid)) {report concat('rc_',$qid) prefix {write '---+ Reason Codes for Non-Sharable Child Cursors' write '---## For SQL_ID: ',$qid write '|*Reason Code*| *Value*|' } call writeSql(bindSql($sql,$qid)) if isCreated(true) {write $TOP toc '3:[[',getFile(),"][rda_report]['",$qid,"' Query Identifier]]" } } unpretoc # Disable the group title in next index if isTocCreated() toc '-:RDBMS' =head1 SEE ALSO L, L =head1 COPYRIGHT NOTICE Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved. =head1 TRADEMARK NOTICE Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. =cut