Abdul Hafeez Kalsekar -- Oracle database tuning case studies

  1.  Increase the Size of Log Buffer. --Check the Size of log buffer Show parameter log_buffer --Increase the size of log_buffer if dynamic allocation then set minimum size  alter system set log_buffer=4194304 scope=spfile; --Restart the Oracle Database Shutdown immediate; Startup; 2. Increase the Archive Process for faster writing --Check the current log archive process show parameter log_archive_max_process --It show active status of process get in above parameter select process,status from v$archive_processes; --Increase the archive process alter system set log_archive_max_processes=10; 3. Reduce redo generation of unneeded tables like Logs table --Disable logging alter table scott.emp nologging; --Enable logging alter table scott.emp logging;   4. Tune the following parameter also help LOG_CHECKPOINT_TIMEOUT LOG_CHECKPOINT_INTERVAL  FAST_START_IO_TARGET FAST_START_MTTR_TARGET 5. Increase the size of redo logs Online redo log files should be sizes to perform...

ASM information :  kfod disks=all -- space  used by each database in asm  set lines 200 column DATABASE format a25 col "GB" format 99,999.9 set pagesize 90 SELECT disk_group_name,        SUBSTR(alias_path, 2, INSTR(alias_path, '/', 1, 2) - 2) DATABASE,        ROUND(SUM(alloc_bytes)/1024/1024/1024, 1) "GB",        ROUND(SUM(alloc_bytes)/1024/1024, 1) "MB" FROM (   SELECT SYS_CONNECT_BY_PATH(alias_name, '/') alias_path,          alloc_bytes,          disk_group_name   FROM (     SELECT g.name disk_group_name,            a.parent_index pindex,            a.name alias_name,            a.reference_index rindex,            f.space alloc_bytes,            f.type type     FROM v$asm_file f ...

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

  In this Document Purpose Scope Details References APPLIES TO: Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Cloud Service - Version N/A and later Oracle Database - Enterprise Edition - Version 11.2.0.3 and later Oracle Database Backup Service - Version N/A and later Oracle Database Cloud Schema Service - Version N/A and later Information in this document applies to any platform. PURPOSE The goal of this note is to provide best practices and recommendations to users of Oracle Real Application Clusters (RAC) databases using very large SGA (e.g. 100GB) per instance (note that RAC assumes homogeneously sized SGAs across the cluster). This document is compiled and maintained based on Oracle's experience with its global RAC customer base. This is not meant to replace or supplant the Oracle Documentation set, but rather, it is meant as a supplement to the same. It is imperative that the Oracle Documentation be read, understood, and referenced to provide a...

 REM srdc_db_ora4031.sql - Collect information for ORA-4031 analysis  define SRDCNAME='DB_ORA4031SP' SET MARKUP HTML ON PREFORMAT ON set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||        to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance; set TERMOUT on MARKUP html preformat on REM spool &SRDCSPOOLNAME..htm select '+----------------------------------------------------+' from dual union all select '| Diagnostic-Name: '||'&&SRDCNAME' from dual union all select '| Timestamp:       '||        to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual union all select '| Machine:         '||host_name from v$instance union all select '| Version:         '||version from v$instance union all select...

 select name , owner              , type              , referenced_name                , referenced_type    from all_dependencies    where referenced_name in   ( select distinct  object_name  from dba_hist_sql_plan where sql_id='&&sql_id'  and object_type='TABLE'  ) ;   select name , owner              , type              , referenced_name                , referenced_type    from all_dependencies    where  name in   ( select distinct  object_name  from dba_hist_sql_plan where sql_id='&&sql_id'  and object_type='TABLE'  ) ; -- For Table exec dbms_utility.get_dependency('TABLE','HR','EMPLOYEES'); Check Tables Accessed by Pocedure : select pname...

Advantages for optimizing query performance and system efficiency. Enhancing Performance:  Manually running the SQL Tuning Advisor can identify query bottlenecks and suggest improvements. This could result in faster execution times and improved user experience. Query Optimization:  This tool analyzes query execution plans and proposes alternate solutions based on factors such as indexes, stats, and database settings. Cost Savings:  Poorly performing queries that use up too many resources can be identified and optimized, leading to savings through better hardware utilization. Proactive Maintenance:  Check for potential performance issues before they hit your production environment by regularly analyzing and tuning queries. Learning Opportunities:  Find out about query optimization techniques and best practices by studying the SQL Tuning Advisor’s recommendations. 1) Creating SQL Tuning Sets SQL Tuning Sets (STS) are essential for storing SQL statements along with...

   There may be times when a normal SQL trace of a PLSQL package is not sufficient. For example, when the SQL statements seem to be running fast, but the procedure takes a long time. It may be useful to understand which calls in the PLSQL package are taking the time. With this in mind, this document provides a step by step guide to tracing PLSQL using DBMS_HPROF. DBMS_HPROF. create directory PROF_DIR as '/home/oracle/pl'; grant all on directory plshprof_dir to pltest; grant execute on dbms_hprof to pltest; -- as user PLTEST  execute dbms_hprof.create_tables(); With the last call the tables DBMSHP_TRACE_DATA, DBMSHP_RUNS, DBMSHP_FUNCTION_INFO and DBMSHP_PARENT_CHILD_INFO are created. They store the information in the database schema  and can be queried afterwards.  Create the DBMS_HRPOF objects if not present . Log on to the PLTEST  that owns the PLSQL package being profiled. sqlplus <user>/<password> SQL> @$ORACLE_HOME/rdbms/admin/dbmshp...