Recently i stuck with SAP performance issues So maximum time its related to Database so Here i have oracle and i am sharing a report and script through which you can find out the optimum Oracle database Parameters :
Steps:
1) Log-in to SAP system
2) Go to SA38/Se38
3) Give report name RSORADJV
4) Paste this script in provide window ( Script is for 10.2.0.2 only)
select
name,
substr(order_recommendation,3) recommendation,
remark,
"SET",
is_value,
should_be_value,
substr(order_recommendation,1,1) "ID",
inst_id
from
(
select
decode(substr(pis.name,1,3),
'***',-1,pis.inst_id) inst_id,
decode(substr(pis.name,1,3),
'***',upper(pis.name),
decode(pis.subname,
' ',pis.name,
pis.name||' ('||pis.subname||')')) name,
decode(substr(pis.name,1,3),
'***','* '||
decode(substr(pis.name,17,2),
' 1','Last change: March, 10th, 2009',
' 2','Note/Version: 830576/179',
' 3','Evaluation Date/Time: '||to_char(sysdate,'YYYY-MM-DD/HH24:MI:SS'),
' 4','DB Startup Date/Time: '||to_char(ext_startup,'YYYY-MM-DD/HH24:MI:SS')
||decode(ext_rac_enabled,'TRUE',' (data of instance '||pis.inst_id||')',''),
' 5','OLAP: '||decode(substr(upper(''),1,1),'Y','TRUE','FALSE'),
' 6','Patchset: '||ext_version
||decode(ext_rac_enabled,'TRUE',' (data of instance '||pis.inst_id||')',''),
' 7','Mergefix: '||ext_mergefix
||decode(ext_rac_enabled,'TRUE',' (data of instance '||pis.inst_id||')',''),
' 8','RAC: '||ext_rac_enabled
||decode(ext_rac_enabled,' (data of other instances at the end of the list)',''),
' 9','Platform: '||ext_platform_name
||decode(ext_rac_enabled,'TRUE',' (data of instance '||pis.inst_id||')',''),
'10','Event Check: '||decode(ext_event_contains_colon,
0,'reliable',
'not reliable [event separator ":" unsupported; see note 830576]')),
decode(pis.isdefault,
'TRUE',decode(pshould.value,
null,
'O ok (not set; mentioned with other prerequisites/not mentioned in note)',
decode(substr(pshould.value,1,5),
'-man-',
'D check if default value "'||pis.value||'" is suitable ('||substr(pshould.value,6)||')',
'-aut-',
'G automatic check ok; doublecheck if default value "'||pis.value||'" is suitable ('||substr(pshould.value,6)||')',
'-any-',
'N ok (not set; any value recommended)',
'-del-',
'J ok (not set; not to be set as explicitly mentioned in note)',
decode(upper(pis.value),
upper(pshould.value),
'I add explicitly with default value "'||pshould.value||'"',
'A add with value "'||pshould.value||'"'))),
decode(pshould.value,
null,
'F check why set but mentioned with other prerequisites/not mentioned in note',
decode(substr(pshould.value,1,5),
'-man-',
'E check if value "'||pis.value||'" is suitable ('||substr(pshould.value,6)||')',
'-aut-',
'H automatic check ok; doublecheck if value "'||pis.value||'" is suitable ('||substr(pshould.value,6)||')',
'-any-',
'M ok (set; any value recommended)',
'-del-',
'B delete (set; not to be set as explicitly mentioned in note)',
decode(
decode(
substr(replace(upper(pis.value),' ',''),1,length(
substr(replace(upper(pshould.value),' ',''),1,
instr(replace(upper(pshould.value),' ',''),'[')-1))),
substr(replace(upper(pshould.value),' ',''),1,
instr(replace(upper(pshould.value),' ',''),'[')-1),'X',
' ')||
decode(
substr(replace(upper(pis.value),' ',''),-length(
substr(replace(upper(pshould.value),' ',''),
instr(replace(upper(pshould.value),' ',''),']')+1))),
substr(replace(upper(pshould.value),' ',''),
instr(replace(upper(pshould.value),' ',''),']')+1),'X',
' '),
'XX',
'K ok (set correctly =)',
decode(sign(
decode(rpad('>=',length(pshould.value),'X'),
translate(pshould.value,'1234567890','XXXXXXXXXX'),
to_number(pis.value)-to_number(substr(pshould.value,3))+1,
0)),
1,
'L ok (set correctly >=)',
'C change value to "'||pshould.value||'"')))))) order_recommendation,
decode(substr(pis.name,1,3),
'***',' ',
decode(pis.isdefault,
'TRUE','NO',
'YES')) "SET",
pis.value is_value,
decode(substr(pshould.value,1,5),
'-man-',substr(pshould.value,6),
'-aut-',substr(pshould.value,6),
'-any-','any value',
'-del-','deleted '||chr(102)||'rom parameter file',
pshould.value) should_be_value,
pshould."comment" remark
from
(
select
inst_id,
name,
subname,
value,
vs,
"comment",
hide,
ext_mergefix,
ext_rac_enabled,
ext_version,
ext_startup,
ext_platform_name,
ext_event_contains_colon
from
(
select
original.inst_id,
lower(decode(instr(original.n,','),0,original.n,substr(original.n,1,instr(original.n,',')-1))) name,
lower(decode(instr(original.n,','),0,' ',substr(original.n,instr(original.n,',')+1))) subname,
replace(decode(original.n,
'PARALLEL_MAX_SERVERS',decode(ext_cpu_count.cpu_count*10-ext_para_max.para_max,
0,'-aut-'||substr(original.w,6),original.w),
'PGA_AGGREGATE_TARGET',decode(sign(round(ext_pgastat.MAX_since_start/ext_pgastat.limit_value*100)-90),
-1,decode(sign(round(ext_pgastat.MAX_since_start/ext_pgastat.limit_value*100)-75),
1,'-aut-'||substr(original.w,6),
original.w),
original.w),
'PROCESSES',decode(sign(round(ext_resource_limit.MAX_UTILIZATION/ext_resource_limit.limit_value*100)-75),
-1,'-aut-'||substr(original.w,6),original.w),
'SESSIONS',decode(sign(round(ext_resource_limit.MAX_UTILIZATION/ext_resource_limit.limit_value*100)-75),
-1,'-aut-'||substr(original.w,6),original.w),
'SHARED_POOL_SIZE',decode(sign(ext_shared_pool_size.shared_pool_size-400*1000*1000),
-1,'>= 400 MB',
decode(sign(ext_shared_pool_size.shared_pool_size-4*1024*1024*1024),
1,'<= 4GB, if no special reason for the high setting',
decode(sign(round(ext_pools_free.min_from_history/ext_shared_pool_size.shared_pool_size*100)-90),
-1,decode(sign(round(ext_pools_free.min_from_history/ext_shared_pool_size.shared_pool_size*100)-75),
1,'-aut-'||substr(original.w,6),
original.w),
original.w))),
'UNDO_RETENTION',decode(ext_undostat.max_stolen,
0,'-aut-'||substr(original.w,6),original.w),
original.w),'[SID]',ext_database.name) value,
original.v vs,
decode(original.n,
'PARALLEL_MAX_SERVERS','Max used (gv$resource_limit): '||ext_resource_limit.MAX_UTILIZATION
||' ('||round(ext_resource_limit.MAX_UTILIZATION/ext_para_max.para_max*100)
||'%); '
||replace(original.c,'[CPU_COUNT]',to_char(ext_cpu_count.cpu_count)),
'PGA_AGGREGATE_TARGET','Max used MB (gv$pgastat): '||round(ext_pgastat.MAX_since_start/1024/1024)
||' ('||round(ext_pgastat.MAX_since_start/ext_pgastat.limit_value*100)
||'%) ',
'PROCESSES','Max used (gv$resource_limit): '||ext_resource_limit.MAX_UTILIZATION
||' ('||round(ext_resource_limit.MAX_UTILIZATION/ext_resource_limit.limit_value*100)
||'%)',
'SESSIONS','Max used (gv$resource_limit): '||ext_resource_limit.MAX_UTILIZATION
||' ('||round(ext_resource_limit.MAX_UTILIZATION/ext_resource_limit.limit_value*100)
||'%); '
||replace(original.c,'[PROCESSES]',to_char(ext_processes.processes)),
'SHARED_POOL_SIZE',
'Max used MB (dba_hist_sgastat): '||
round((ext_shared_pool_size.shared_pool_size-ext_pools_free.min_from_history)/1024/1024)
||' ('||round((ext_shared_pool_size.shared_pool_size-ext_pools_free.min_from_history)/
ext_shared_pool_size.shared_pool_size*100)
||'%)',
'UNDO_RETENTION','Max unexpired stolen blocks (gv$undostat): '||ext_undostat.max_stolen,
original.c) "comment",
decode(instr(lower(original.n),'_fix_control'),0,'FALSE',decode(ext_fix_control.bugno,null,'TRUE','FALSE')) hide,
ext_mergefix.mergefix_at_least ext_mergefix,
ext_rac.enabled ext_rac_enabled,
original.version ext_version,
original.startup_time ext_startup,
ext_database.platform_name ext_platform_name,
ext_event.contains_colon ext_event_contains_colon
from
(
select
i.inst_id inst_id,
startup_time,
version,
n,w,v,r,c
from
(
select
'*** INFORMATION '||lpad(rownum,2)||' ***' n,
'' w,
'XXXXXX' v,
'' r,
'' c from gv$parameter2 where rownum < 11 union ( select
'BACKGROUND_DUMP_DEST' n,
'/oracle/[SID]/saptrace/background' w,
'XXXXXX' v,
'OS_FAMILY[UNIX]' r,
'' c from dual ) union ( select
'BACKGROUND_DUMP_DEST' n,
'[DRIVE]:\oracle\[SID]\saptrace\background' w,
'XXXXXX' v,
'OS_FAMILY[WINDOWS]' r,
'' c from dual ) union ( select
'CLUSTER_DATABASE' n,
'-any-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'COMMIT_WRITE' n,
'-del-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'COMPATIBLE' n,
'10.2.0' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'CONTROL_FILES' n,
'-any-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'CONTROL_FILE_RECORD_KEEP_TIME' n,
'>=30' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'CORE_DUMP_DEST' n,
'/oracle/[SID]/saptrace/background' w,
'XXXXXX' v,
'OS_FAMILY[UNIX]' r,
'' c from dual ) union ( select
'CORE_DUMP_DEST' n,
'[DRIVE]:\oracle\[SID]\saptrace\background' w,
'XXXXXX' v,
'OS_FAMILY[WINDOWS]' r,
'' c from dual ) union ( select
'DB_BLOCK_BUFFERS' n,
'-del-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'DB_BLOCK_SIZE' n,
'8192' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'DB_CACHE_SIZE' n,
'-man-appropriately set' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'DB_FILE_MULTIBLOCK_READ_COUNT' n,
'-del-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'DB_FILES' n,
'-any-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'DB_NAME' n,
'[SID]' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'DB_WRITER_PROCESSES' n,
'-man-change default in case of dbwr problems only' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'EVENT,10027' n,
'10027 trace name context forever, level 1' w,
'XXXXXX' v,
'' r,
'avoid process state dump at deadlock' c from dual ) union ( select
'EVENT,10028' n,
'10028 trace name context forever, level 1' w,
'XXXXXX' v,
'' r,
'do not wait while writing deadlock trace' c from dual ) union ( select
'EVENT,10049' n,
'-man-set with level 2 if minipatch 5-9 is installed' w,
' X ' v,
'OS_FAMILY[WINDOWS]' r,
'avoid ORA-07445/ORA-00600 as of minipatch 5' c from dual ) union ( select
'EVENT,10091' n,
'10091 trace name context forever, level 1' w,
' X ' v,
'' r,
'avoid CU Enqueue during parsing' c from dual ) union ( select
'EVENT,10091' n,
'10091 trace name context forever, level 1' w,
' X ' v,
'MF[00,01,02]' r,
'avoid CU Enqueue during parsing' c from dual ) union ( select
'EVENT,10142' n,
'10142 trace name context forever, level 1' w,
' XXX' v,
'' r,
'avoid Btree Bitmap Conversion plans' c from dual ) union ( select
'EVENT,10162' n,
'10162 trace name context forever, level 1' w,
' X ' v,
'' r,
'avoid wrong values' c from dual ) union ( select
'EVENT,10183' n,
'10183 trace name context forever, level 1' w,
'XXXXXX' v,
'' r,
'avoid rounding during cost calculation' c from dual ) union ( select
'EVENT,10191' n,
'10191 trace name context forever, level 1' w,
'XXXXXX' v,
'' r,
'avoid high CBO memory consumption' c from dual ) union ( select
'EVENT,10411' n,
'10411 trace name context forever, level 1' w,
' XXX' v,
'' r,
'fixes int-does-not-correspond-to-number bug' c from dual ) union ( select
'EVENT,10629' n,
'10629 trace name context forever, level 32' w,
'XXXXXX' v,
'' r,
'influence rebuild online error handling' c from dual ) union ( select
'EVENT,10891' n,
'10891 trace name context forever, level 1' w,
'XXXXXX' v,
'' r,
'avoid high parsing times joining many tables' c from dual ) union ( select
'EVENT,14532' n,
'-man-set with level 1 if fix 5618049 is installed' w,
' X ' v,
'' r,
'avoid massive shared pool consumption' c from dual ) union ( select
'EVENT,14532' n,
'14532 trace name context forever, level 1' w,
' XXXX' v,
'' r,
'avoid massive shared pool consumption' c from dual ) union ( select
'EVENT,38068' n,
'38068 trace name context forever, level 100' w,
'XXXXXX' v,
'' r,
'long raw statistic; implement note 948197' c from dual ) union ( select
'EVENT,38085' n,
'38085 trace name context forever, level 1' w,
' XXX' v,
'' r,
'consider cost adjust for index fast full scan' c from dual ) union ( select
'EVENT,38087' n,
'-man-set with level 1 if fix 5842686 is installed' w,
' X ' v,
'' r,
'avoid ora-600 at star transformation' c from dual ) union ( select
'EVENT,38087' n,
'38087 trace name context forever, level 1' w,
' XXXX' v,
'' r,
'avoid ora-600 at star transformation' c from dual ) union ( select
'EVENT,44951' n,
'-man-set with level 1024 if fix 6376915 is installed' w,
' X ' v,
'' r,
'avoid HW enqueues during LOB inserts' c from dual ) union ( select
'EVENT,44951' n,
'44951 trace name context forever, level 1024' w,
' XXX' v,
'' r,
'avoid HW enqueues during LOB inserts' c from dual ) union ( select
'FILESYSTEMIO_OPTIONS' n,
'SETALL' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'HPUX_SCHED_NOAGE' n,
'178' w,
'XXXXXX' v,
'RAC[FALSE]' r,
'performance' c from dual ) union ( select
'LOG_ARCHIVE_DEST' n,
'-del-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'LOG_ARCHIVE_DEST_1' n,
'LOCATION=/oracle/[SID]/oraarch/[SID]arch' w,
'XXXXXX' v,
'OS_FAMILY[UNIX]' r,
'note 966073' c from dual ) union ( select
'LOG_ARCHIVE_DEST_1' n,
'LOCATION=[drive]:\oracle\[SID]\oraarch\[SID]arch' w,
'XXXXXX' v,
'OS_FAMILY[WINDOWS]' r,
'note 966073' c from dual ) union ( select
'LOG_ARCHIVE_FORMAT' n,
'%t_%s_%r.dbf' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'LOG_BUFFER' n,
'>=1048576' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'LOG_CHECKPOINTS_TO_ALERT' n,
'TRUE' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'MAX_DUMP_FILE_SIZE' n,
'20000' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'OPEN_CURSORS' n,
'>=800' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'OPTIMIZER_DYNAMIC_SAMPLING' n,
'6' w,
' XXX' v,
'' r,
'est. % of index cached (inlist, nested loop)' c from dual ) union ( select
'OPTIMIZER_FEATURES_ENABLE' n,
'-del-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'OPTIMIZER_INDEX_CACHING' n,
'-del-' w,
'XXXXXX' v,
'' r,
'est. % of index cached (inlist, nested loop)' c from dual ) union ( select
'OPTIMIZER_INDEX_COST_ADJ' n,
'20' w,
'RRRRRR' v,
'' r,
'' c from dual ) union ( select
'OPTIMIZER_INDEX_COST_ADJ' n,
'-del-' w,
'BBBBBB' v,
'' r,
'' c from dual ) union ( select
'OPTIMIZER_MODE' n,
'-del-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'PARALLEL_EXECUTION_MESSAGE_SIZE' n,
'16384' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'PARALLEL_MAX_SERVERS' n,
'-man-#DB-CPU-CORES*10' w,
'XXXXXX' v,
'' r,
'CPU_COUNT=[CPU_COUNT]' c from dual ) union ( select
'PARALLEL_THREADS_PER_CPU' n,
'1' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'PGA_AGGREGATE_TARGET' n,
'-man-appropriately set' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'PROCESSES' n,
'-man-formula how to set in parameter note' w,
'XXXXXX' v,
'' r,
'dependent: SESSIONS' c from dual ) union ( select
'QUERY_REWRITE_ENABLED' n,
'FALSE' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'RECYCLEBIN' n,
'OFF' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'REMOTE_OS_AUTHENT' n,
'TRUE' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'REPLICATION_DEPENDENCY_TRACKING' n,
'-any-' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'SESSIONS' n,
'-man-2*PROCESSES' w,
'XXXXXX' v,
'' r,
'PROCESSES=[PROCESSES]' c from dual ) union ( select
'SHARED_POOL_SIZE' n,
'-man-appropriately set' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'STAR_TRANSFORMATION_ENABLED' n,
'TRUE' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'UNDO_MANAGEMENT' n,
'AUTO' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'UNDO_TABLESPACE' n,
'PSAPUNDO' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'UNDO_RETENTION' n,
'-man-appropriately set' w,
'XXXXXX' v,
'' r,
'' c from dual ) union ( select
'USER_DUMP_DEST' n,
'/oracle/[SID]/saptrace/usertrace' w,
'XXXXXX' v,
'OS_FAMILY[UNIX]' r,
'' c from dual ) union ( select
'USER_DUMP_DEST' n,
'[DRIVE]:\oracle\[SID]\saptrace\usertrace' w,
'XXXXXX' v,
'OS_FAMILY[WINDOWS]' r,
'' c from dual ) union ( select
'_B_TREE_BITMAP_PLANS' n,
'FALSE' w,
' X ' v,
'' r,
'avoid b*tree conversion to bitmap' c from dual ) union ( select
'_BLOOM_FILTER_ENABLED' n,
'FALSE' w,
' X ' v,
'' r,
'avoid wrong values' c from dual ) union ( select
'_DB_BLOCK_NUMA' n,
'-man-without CLM: =1, OTHERWISE: do not set' w,
'XXXXXX' v,
'OS[HP-UX IA (64-bit),HP-UX (64-bit)]' r,
'performance; note 1225732' c from dual ) union ( select
'_ENABLE_NUMA_OPTIMIZATION' n,
'-man-without CLM: =FALSE, OTHERWISE: do not set' w,
'XXXXXX' v,
'OS[HP-UX IA (64-bit),HP-UX (64-bit)]' r,
'performance; note 1225732' c from dual ) union ( select
'_FIX_CONTROL,4728348' n,
'4728348:OFF' w,
' X ' v,
'MF[00,01,02]' r,
'avoid wrong values; notes 964858 and 997889' c from dual ) union ( select
'_FIX_CONTROL,5705630' n,
'5705630:ON' w,
' X XXX' v,
'' r,
'use optimal OR concatenation; note 176754' c from dual ) union ( select
'_FIX_CONTROL,5765456' n,
'5765456:3' w,
' XXX' v,
'' r,
'no further information available' c from dual ) union ( select
'_FIX_CONTROL,6120483' n,
'6120483:OFF' w,
' X ' v,
'MF[00,01,02,03,04,05]' r,
'avoid wrong plan in simple query;notes 981875,1165319' c from dual ) union ( select
'_FIX_CONTROL,6120483' n,
'6120483:OFF' w,
' X ' v,
'MF[13,14]' r,
'avoid wrong plan in simple query;notes 981875,1165319' c from dual ) union ( select
'_FIX_CONTROL,6221403' n,
'6221403:ON' w,
' XXX' v,
'' r,
'correct selectivity; note 1165319' c from dual ) union ( select
'_FIX_CONTROL,6329318' n,
'6329318:OFF' w,
' X ' v,
'MF[01,02,03,04,05]' r,
'avoid between costs lower than =; note 1165319' c from dual ) union ( select
'_FIX_CONTROL,6329318' n,
'6329318:ON' w,
' X ' v,
'MF[06,07,08,09,10,11,12,13,14,15,16,17,18]' r,
'avoid between costs lower than =; note 1165319' c from dual ) union ( select
'_FIX_CONTROL,6430500' n,
'6430500:ON' w,
' X XXX' v,
'' r,
'avoid that unique index not chosen' c from dual ) union ( select
'_FIX_CONTROL,6440977' n,
'6440977:ON' w,
' X XXX' v,
'' r,
'consider redundant predicates in join; note 981875' c from dual ) union ( select
'_FIX_CONTROL,6626018' n,
'6626018:ON' w,
' X XXX' v,
'' r,
'avoid to low filter costs; note 981875' c from dual ) union ( select
'_FIX_CONTROL,6660162' n,
'6660162:ON' w,
' X ' v,
'' r,
'choose right index; note 981875' c from dual ) union ( select
'_FIX_CONTROL,6670551' n,
'6670551:ON' w,
' X ' v,
'' r,
'calculate stats on empty table; note 1165319' c from dual ) union ( select
'_FIX_CONTROL,6972291' n,
'6972291:ON' w,
' XXX' v,
'' r,
'use column group selectivity with hgrm;note 1165319' c from dual ) union ( select
'_FIX_CONTROL,7325597' n,
'7325597:ON' w,
' X XXX' v,
'' r,
'Avoid wrong Index after optimizer mergefix' c from dual ) union ( select
'_FIX_CONTROL,7692248' n,
'7692248:ON' w,
' XXX' v,
'' r,
'collect always histogram information' c from dual ) union ( select
'_FIRST_SPARE_PARAMETER' n,
'-man-set to 1 if fix 6904068 is installed' w,
' XXX' v,
'' r,
'Avoid high CPU consumption for Mutex requests' c from dual ) union ( select
'_INDEX_JOIN_ENABLED' n,
'FALSE' w,
' X ' v,
'' r,
'avoid wrong values; workaround for note 981875' c from dual ) union ( select
'_IN_MEMORY_UNDO' n,
'FALSE' w,
' X ' v,
'' r,
'avoid corruptions' c from dual ) union ( select
'_OPTIM_PEEK_USER_BINDS' n,
'FALSE' w,
'XXXXXX' v,
'' r,
'avoid bind value peaking' c from dual ) union ( select
'_OPTIMIZER_BETTER_INLIST_COSTING' n,
'OFF' w,
' RRR' v,
'' r,
'avoid preference of index supporting inlist' c from dual ) union ( select
'_OPTIMIZER_MJC_ENABLED' n,
'FALSE' w,
'XXXXXX' v,
'' r,
'avoid cartesean merge joins in general' c from dual ) union ( select
'_PUSH_JOIN_UNION_VIEW' n,
'-man-set to false if fix 7155655 is not installed' w,
' X ' v,
'MF[00,01,02,03,04,05,06]' r,
'avoid wrong values' c from dual ) union ( select
'_SORT_ELIMINATION_COST_RATIO' n,
'10' w,
'XXXXXX' v,
'' r,
'use non-order-by-sorted indexes (first_rows)' c from dual ) union ( select
'_TABLE_LOOKUP_PREFETCH_SIZE' n,
'0' w,
' X ' v,
'' r,
'avoid wrong values; note 1109753' c from dual )
),
gv$instance i
)
original,
( select
instance_number inst_id,
decode(pool,
'shared pool','SHARED_POOL_SIZE',
'java pool','JAVA_POOL_SIZE') pool,
min(bytes) min_from_history
from
dba_hist_sgastat
where
pool in ('shared pool', 'java pool') and
name = 'free memory'
group by
instance_number,
pool
)
ext_pools_free,
( SELECT
inst_id,
upper(RESOURCE_NAME) RESOURCE_NAME,
limit_value,
MAX_UTILIZATION
FROM
gv$RESOURCE_LIMIT
WHERE
RESOURCE_NAME IN (
'processes',
'sessions',
'parallel_max_servers')
)
ext_resource_limit,
( select
pga.inst_id,
pga.value max_since_start,
param.value limit_value
from
gv$pgastat pga,
gv$parameter2 param
where
pga.inst_id=param.inst_id and
pga.name='maximum PGA allocated' and
param.name='pga_aggregate_target')
ext_pgastat,
( select inst_id, value processes from gv$parameter2 where name = 'processes')
ext_processes,
( select inst_id, value para_max from gv$parameter2 where name = 'parallel_max_servers')
ext_para_max,
( select inst_id, value cpu_count from gv$parameter2 where name = 'cpu_count')
ext_cpu_count,
( select inst_id, value shared_pool_size from gv$parameter2 where name = 'shared_pool_size')
ext_shared_pool_size,
( select inst_id, max(UNXPBLKRELCNT+UNXPBLKREUCNT) max_stolen from gv$undostat group by inst_id)
ext_undostat,
( select
nvl(max(bf.nr),0) mergefix_at_least
from
( select bg, nr, '10.2.0.2' patchset from
( select
5648287 bg, 16 nr from dual union ( select
6399597 bg, 16 nr from dual ) union ( select
6430500 bg, 16 nr from dual ) union ( select
7325597 bg, 16 nr from dual ) union ( select
6440977 bg, 14 nr from dual ) union ( select
6120483 bg, 13 nr from dual ) union ( select
6626018 bg, 13 nr from dual ) union ( select
6660162 bg, 12 nr from dual ) union ( select
6239971 bg, 12 nr from dual ) union ( select
4567767 bg, 12 nr from dual ) union ( select
6251917 bg, 11 nr from dual ) union ( select
6062266 bg, 10 nr from dual ) union ( select
6087237 bg, 9 nr from dual ) union ( select
6007259 bg, 9 nr from dual ) union ( select
6151963 bg, 9 nr from dual ) union ( select
5882954 bg, 8 nr from dual ) union ( select
4708389 bg, 8 nr from dual ) union ( select
5944076 bg, 7 nr from dual ) union ( select
6051211 bg, 7 nr from dual ) union ( select
6082745 bg, 7 nr from dual ) union ( select
5705630 bg, 7 nr from dual ) union ( select
6070954 bg, 7 nr from dual ) union ( select
5976822 bg, 7 nr from dual ) union ( select
5838613 bg, 7 nr from dual ) union ( select
5949981 bg, 7 nr from dual ) union ( select
5680702 bg, 6 nr from dual ) union ( select
5884780 bg, 3 nr from dual ) union ( select
5084239 bg, 2 nr from dual ) union ( select
5912195 bg, 1 nr from dual ) union ( select
4273361 bg, 1 nr from dual ) union ( select
4483240 bg, 1 nr from dual ) union ( select
4712638 bg, 1 nr from dual ) union ( select
5449488 bg, 1 nr from dual ) union ( select
5483301 bg, 1 nr from dual ) union ( select
5547058 bg, 1 nr from dual ) union ( select
5694984 bg, 1 nr from dual ) union ( select
5705257 bg, 1 nr from dual ) union ( select
5741121 bg, 1 nr from dual ) union ( select
5762598 bg, 1 nr from dual )
) union (
select bg, nr, '10.2.0.4' patchset from
( select
7272039 bg,11 nr from dual union ( select
7692248 bg,11 nr from dual ) union ( select
5099019 bg,10 nr from dual ) union ( select
7295298 bg, 8 nr from dual ) union ( select
7430474 bg, 7 nr from dual ) union ( select
5648287 bg, 6 nr from dual ) union ( select
6430500 bg, 6 nr from dual ) union ( select
7236148 bg, 6 nr from dual ) union ( select
7325597 bg, 6 nr from dual ) union ( select
7138405 bg, 5 nr from dual ) union ( select
6845871 bg, 5 nr from dual ) union ( select
6399597 bg, 4 nr from dual ) union ( select
6221403 bg, 3 nr from dual ) union ( select
6440977 bg, 3 nr from dual ) union ( select
6972291 bg, 3 nr from dual ) union ( select
5714944 bg, 1 nr from dual ) union ( select
6120483 bg, 1 nr from dual ) union ( select
6329318 bg, 1 nr from dual ) union ( select
6626018 bg, 1 nr from dual ) union ( select
6670551 bg, 1 nr from dual )
))
) bf,
v$system_fix_control fc,
v$instance i
where
bf.bg=fc.bugno and
substr(i.version,1,instr(i.version,'.',1,4)-1)=bf.patchset)
ext_mergefix,
v$system_fix_control
ext_fix_control,
( select decode(instr(value,'/'),0,'WINDOWS','UNIX') os_family from
v$parameter2 where name = 'control_files' and rownum < 2)
ext_os_family,
v$database
ext_database,
( select value enabled from v$parameter2 where name = 'cluster_database' )
ext_rac,
( select count(*) contains_colon from gv$parameter2 where name = 'event' and instr(value,':')>0)
ext_event
where
substr(original.v,decode(
replace(substr(original.version,1,instr(original.version,'.',1,4)-1),'.',null),
'10201',1,'10202',2,'10203',3,'10204',4,'10205',5,'10206',6),1)
in ('X', decode(substr(upper(''),1,1),'Y','B','R')) and
original.inst_id = ext_pools_free.inst_id(+) and
original.n = ext_pools_free.pool(+) and
original.inst_id = ext_resource_limit.inst_id(+) and
original.n = ext_resource_limit.RESOURCE_NAME(+) and
original.inst_id = ext_pgastat.inst_id and
original.inst_id = ext_processes.inst_id and
original.inst_id = ext_para_max.inst_id and
original.inst_id = ext_cpu_count.inst_id and
original.inst_id = ext_shared_pool_size.inst_id and
original.inst_id = ext_undostat.inst_id and
(
instr(' '||original.r,'MF[')=0 or
instr(original.r,'MF[')>0 and
instr(
substr(original.r,
instr(original.r,'MF['),
instr(original.r,']',
instr(original.r,'MF['))-
instr(original.r,'MF[')+1),
lpad(ext_mergefix.mergefix_at_least,2,'0')) >0
) and
lower(decode(instr(original.n,','),0,' ',substr(original.n,instr(original.n,',')+1)))=
to_char(ext_fix_control.bugno(+)) and
decode(ext_fix_control.bugno(+),
null, decode(instr(lower(original.n),'_fix_control'),
0,'OK',
'HIDE'),
'OK')='OK' and
(
instr(' '||original.r,'OS_FAMILY[')=0 or
instr(original.r,'OS_FAMILY[')>0 and instr(original.r,ext_os_family.os_family)>0
) and
(
instr(' '||original.r,'OS[')=0 or
instr(original.r,'OS[')>0 and instr(original.r,ext_database.platform_name)>0
) and
(
instr(' '||original.r,'RAC[')=0 or
instr(original.r,'RAC[TRUE]')>0 and ext_rac.enabled='TRUE' or
instr(original.r,'RAC[FALSE]')>0 and ext_rac.enabled='FALSE'
)
)
where hide='FALSE'
) pshould,
(
select
inst_id,
name,
subname,
trim(substr(max(sort_string),1,5)) isdefault,
substr(max(sort_string),6)||
decode(count(*),1,'',
decode(name,'event','',
decode(substr(name,1,1),'_','',
', ...'))) value
from
(
select
inst_id,
lower(name) name,
' ' subname,
concat(lpad(isdefault,5),value) sort_string
from
gv$parameter2
where
name not in ('event','_fix_control')
union
(
select
inst_id,
'_fix_control' name,
substr(trim(value),1,instr(trim(value),':')-1) subname,
concat('FALSE',trim(value)) sort_string
from
gv$parameter2
where
name='_fix_control'
)
union
(
select
inst_id,
decode(substr(lower(n),1,5),
'event','event',
'_fix_','_fix_control',
lower(n)) name,
decode(substr(lower(n),1,5),
'event',substr(n,7),
'_fix_',substr(n,14),
' ') subname,
' TRUE ' sort_string
from
(
select
inst_id,
'*** INFORMATION '||lpad(rownum,2)||
' ***' n
from
gv$mystat
where
rownum < 11
union
(
select
inst_id,
n
from
(
select '_B_TREE_BITMAP_PLANS' n from dual
union ( select '_BLOOM_FILTER_ENABLED' n from dual )
union ( select '_DB_BLOCK_NUMA' n from dual )
union ( select '_ENABLE_NUMA_OPTIMIZATION' n from dual )
union ( select '_FIRST_SPARE_PARAMETER' n from dual )
union ( select '_FIX_CONTROL 4728348' n from dual )
union ( select '_FIX_CONTROL 5705630' n from dual )
union ( select '_FIX_CONTROL 5765456' n from dual )
union ( select '_FIX_CONTROL 6120483' n from dual )
union ( select '_FIX_CONTROL 6221403' n from dual )
union ( select '_FIX_CONTROL 6329318' n from dual )
union ( select '_FIX_CONTROL 6430500' n from dual )
union ( select '_FIX_CONTROL 6440977' n from dual )
union ( select '_FIX_CONTROL 6626018' n from dual )
union ( select '_FIX_CONTROL 6660162' n from dual )
union ( select '_FIX_CONTROL 6670551' n from dual )
union ( select '_FIX_CONTROL 6972291' n from dual )
union ( select '_FIX_CONTROL 7325597' n from dual )
union ( select '_FIX_CONTROL 7692248' n from dual )
union ( select '_INDEX_JOIN_ENABLED' n from dual )
union ( select '_IN_MEMORY_UNDO' n from dual )
union ( select '_OPTIM_PEEK_USER_BINDS' n from dual )
union ( select '_OPTIMIZER_BETTER_INLIST_COSTING' n from dual )
union ( select '_OPTIMIZER_MJC_ENABLED' n from dual )
union ( select '_PUSH_JOIN_UNION_VIEW' n from dual )
union ( select '_SORT_ELIMINATION_COST_RATIO' n from dual )
union ( select '_TABLE_LOOKUP_PREFETCH_SIZE' n from dual )
union ( select 'EVENT 10027' n from dual )
union ( select 'EVENT 10028' n from dual )
union ( select 'EVENT 10049' n from dual )
union ( select 'EVENT 10091' n from dual )
union ( select 'EVENT 10142' n from dual )
union ( select 'EVENT 10162' n from dual )
union ( select 'EVENT 10183' n from dual )
union ( select 'EVENT 10191' n from dual )
union ( select 'EVENT 10411' n from dual )
union ( select 'EVENT 10629' n from dual )
union ( select 'EVENT 10891' n from dual )
union ( select 'EVENT 14532' n from dual )
union ( select 'EVENT 38068' n from dual )
union ( select 'EVENT 38085' n from dual )
union ( select 'EVENT 38087' n from dual )
union ( select 'EVENT 44951' n from dual )
),
gv$instance)
) underscore
)
union
(
select
inst_id,
'event' name,
substr(trim(translate(value,
chr(10)||chr(13)||chr(9),' ')),1,5) subname,
concat('FALSE',trim(translate(value,
chr(10)||chr(13)||chr(9),' '))) sort_string
from
(
select
vparam.inst_id,
substr(':'||vparam.value, instr(':'||vparam.value,':',1,vcnt.cnt)+1,
decode(instr(':'||vparam.value,':',1,vcnt.cnt+1),0,
length(':'||vparam.value),instr(':'||vparam.value,':',1,vcnt.cnt+1)-1)-
decode(instr(':'||vparam.value,':',1,vcnt.cnt ),0,
length(':'||vparam.value),instr(':'||vparam.value,':',1,vcnt.cnt))) value
from
gv$parameter2 vparam,
(select rownum cnt from gv$parameter2 where rownum <= 20) vcnt
where
vparam.name='event'
)
where
value is not null
)
)
group by
inst_id,
name,
subname
) pis
where
pis.inst_id=pshould.inst_id(+) and
pis.name=pshould.name(+) and
pis.subname=pshould.subname(+)
)
order by
inst_id,
id,
name;
After This execute and you will be provided with current and expected value of DB parameters.
No comments:
Post a Comment