Abdul Hafeez Kalsekar -- Oracle database tuning case studies

    As i am  advancing my skillsets and adding  Postgres to  my profile , thought of writing this blog as i am preparing for  edb  postgres exam  Unfortunately i have  mixed up postgresql and edb-postgresql   but  fundamentals remains same .  Server Process  , Memory Structure , Physical files : ################################################ Server Process  ################################################   postmaster ( pmon   )  -->  background process  -->  bgwritter - background writer   ,   logging collector  ,  stats collector  ,   checkpointer  ,  archiver  , auto-vacuum  , wal writer ,  logical replication  ,  dbms_aq  files   -->   data files  ,  wal  segments   ,   archived wal ,  error  files  global fil...

   During Restore we normally  have  to see progress for  big database  .   Posting  script below used  by me  to check progress .  SELECT sid, serial#, context, sofar, totalwork,  round(sofar/totalwork*100,2) "% Complete"  FROM v$session_longops  WHERE opname LIKE 'RMAN%'  AND opname NOT LIKE '%aggregate%'  AND totalwork != 0  AND sofar != totalwork; select device_type "Device", type, filename, to_char(open_time, 'mm/dd/yyyy hh24:mi:ss') open,  to_char(close_time,'mm/dd/yyyy hh24:mi:ss') close,elapsed_time ET, effective_bytes_per_second EPS  from v$backup_async_io; ##### To check Restore Speed  TTITLE OFF SET HEAD OFF SELECT 'Throughput: '||        ROUND(SUM(v.value/1024/1024/1024),1) || ' Gig so far @ ' ||        ROUND(SUM(v.value     /1024/1024)/NVL((SELECT MIN(elapsed_seconds)             FROM v$...

   We  always  have question  what database  parameters influence optimizer behavior .   Oracle  has  views  v$sys_optimizer_env ,  v$sql_optimizer_env and v$sess_optimizer_env  that reflects  database parameters  that influence database optimizer behavior  Ideally  its useful  when comparing 2 database performance and comparing pre and post migration performance .  Optionally we can  set  Optimizer trace 10053 How to Obtain Tracing of Optimizer Computations (EVENT 10053) (Doc ID 225598.1) SQL> select name, isdefault from v$ses_optimizer_env   2  where sid = 265    3  order by isdefault, name; NAME                                     ISD ---------------------------------------- --- _pga_max_size                   ...