Abdul Hafeez Kalsekar -- Oracle database tuning case studies

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

  alter session set tracefile_identifier='PX_serial'; alter session set events 'trace[PX_Messaging]disk high'; alter session set events 'trace[SQL_Parallel_Compilation | SQL_Parallel_Optimization]disk high'; alter session set events 'trace [Parallel_Execution.*] disk high'; alter session set events '10053 trace name context forever, level 1'; alter session set events 'trace[SQL_Code_Generator] disk high'; alter session set events 'trace[SQL_Compiler]disk high'; . < Execute the problematic SELECT stmt > . alter session set events 'trace[SQL_Parallel_Compilation | SQL_Parallel_Optimization] off'; alter session set events 'trace [Parallel_Execution.*] off'; alter session set events '10053 trace name context off'; alter session set events 'trace[SQL_Code_Generator] off'; alter session set events 'trace[SQL_Compiler] off'; alter session set events 'trace[PX_Messaging] off';

   There were many   request  coming in for Reorg  Hence  thought of documenting handy article to check fragmentation and perform reorg . After performing Reorganization we need to also gather fresh statistics  .   Check invalid objects pre and post Reorganization .  Article  will have 2  main topics mainly  1)  Ways to check Fragmentation  2)  Performing Reorganization . For Lob    i have  documented in my  previous Blog  below  https://abdul-hafeez-kalsekar.blogspot.com/2024/01/oracle-database-lob-maintenance.html Ways to  check Fragmentations :  ==> High water mark(HWM)  check query : set verify off      column owner format a10      column alcblks heading 'Allocated|Blocks' just c      column usdblks heading 'Used|Blocks'      just c      column hgwtr heading 'High|Water'...

   There were many   request  coming in for Reorg  Hence  thought of documenting handy article to check fragmentation and perform reorg . After performing Reorganization we need to also gather fresh statistics  .   Check invalid objects pre and post Reorganization .  Article  will have 2  main topics mainly  1)  Ways to check Fragmentation  2)  Performing Reorganization . For Lob    i have  documented in my  previous Blog  below  https://abdul-hafeez-kalsekar.blogspot.com/2024/01/oracle-database-lob-maintenance.html Ways to  check Fragmentations :  ==> High water mark(HWM)  check query : set verify off      column owner format a10      column alcblks heading 'Allocated|Blocks' just c      column usdblks heading 'Used|Blocks'      just c      column hgwtr heading 'High|Water'...

  Things to  verify while creating JOb  : -- Job is  not auto Drop  -- Instance stickiness of job  -- jOBS IS restart able after stop    Known Issues :  1) NEXT_RUN_DATE is in the past.   to Fix  we  need to alter start date attribute of job and disable and  enable job   2)  Scheduler jobs are not triggering .  For that we can reset job queue process and   disable/enable scheduler  Disable Scheduler :   alter system set job_queue_processes=0 sid='*';  exec dbms_scheduler.set_scheduler_attribute ('SCHEDULER_DISABLED','TRUE'); Dropping  job : exec DBMS_SCHEDULER.drop_JOB (job_name => ‘DEMO.HELOS_TEST_JOBS’,force=>true); Stooping Jobs  exec DBMS_SCHEDULER.stop_JOB (job_name => 'SERV_STG.HRI_USER_JOB' , force=>true) ; exec dbms_job.broken(42, TRUE); Manually Running Job :    BEGIN     DBMS_SCHEDULER.run_job(job_name => 'SQLHC.TE...