Other sga checks

 

##################
Bufer Pool stats 
##################


col name format a46 heading 'DBWR Statistic'
rem col stat format 999,999,999,999,999 heading 'Statistic Value'
set pages 40
@title80 'DBWR Statistic Report'
spool rep_out\&db\dbwr_stat
select a.name,a.stat 
from  (select name, value stat from v$sysstat 
       where name not like '%redo%' and name not like '%remote%') a
where (a.name like 'DBWR%' or a.name like '%buffer%' or a.name like '%write%' or name like '%summed%') 
union
select class name, count stat from v$waitstat where class='data block'
union 
select name||' hit ratio' name, 
   round((1 - (a.physical_reads / greatest((a.db_block_gets + a.consistent_gets),1)) * 100),3) stat
from V$buffer_pool_statistics a
union 
select name||' free buffer wait' name,free_buffer_wait stat
from V$buffer_pool_statistics
union
select name||' buffer busy wait' name,buffer_busy_wait stat
from V$buffer_pool_statistics
union
select name||' write complete wait' name,write_complete_wait stat
from V$buffer_pool_statistics
/





##################
SGA component history usage 
##################

col snap_time form a30
set lines 300  pages 300
break on snap_time page
select a.instance_number, end_interval_time snap_time, category, round(used_total/1024/1024) used_total_mbs, round(allocated_total/1024/1024) alloc_tot_mbs, 
       round(ALLOCATED_AVG/1024/1024) allocated_avg_mbs, round(ALLOCATED_MAX/1024/1024) allocated_mx_mbs 
from dba_hist_snapshot a, dba_hist_process_mem_summary b 
where a.snap_id=b.snap_id and a.instance_number=b.instance_number   and a.end_interval_time>sysdate-7 order by 1,2
/



prompt**  **   Database modified parameetrs    **
prompt**==================================================================


set line 100
col instance format 99
col snap_id format 9999999
col parameter_name format a20
col old_value format a10
col new_value  format a10
select instance_number instance, snap_id, time, parameter_name, old_value, new_value from (
select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI') TIME, a.instance_number, parameter_name, value new_value,
lag(parameter_name,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_pname,
lag(value,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_value ,
decode(substr(parameter_name,1,2),'__',2,1) calc_flag
from dba_hist_parameter a, dba_Hist_snapshot b , gv$instance v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and lower(parameter_name) not like nvl('undo%',parameter_name)
and a.instance_number in (select instance_number from v$instance ) 
)
where
new_value != old_value
order by 1,2;
 


--  and a.begin_interval_time  between to_date('&start_dt','YYYY-MM-DD HH24:MI_SS')  
-- and to_date('&end_dt','YYYY-MM-DD HH24:MI_SS')



Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more