IN ORACLE MILIEU …

When a SQL is submitted by a user to Oracle database, it never happens that Oracle will execute the SQL continuously at one go. Oracle process never get to work on the execution of statement without any interruptions. Often the process has to pause or wait for some event or some other resource to be [...]

There are several ways to check the IO statistics and load on the devices. We have OS level utilities like iostat and sar. We will check out first the OS utilities and then we will move to check IO stats using database query. Example of using sar -d for IO stats: (appmgr01) appmgr – -bash [...]

System Activity Report (SAR) -bash-2.05b$ sar -u 10 5 Linux 2.4.21-37.0.0.1.4.ELhugemem (ap6188rt)    06/12/2007 10:48:24 PM       CPU     %user     %nice   %system   %iowait     %idle 10:48:34 PM       all     22.07      0.00     14.36      0.03     63.54 10:48:44 PM       all     16.70      0.00     13.93      0.17     69.20 10:48:54 PM       all      8.80      0.00      8.15      0.25     82.80 10:49:04 PM       all      2.52      0.00      3.55      0.00     93.92 10:49:14 PM       [...]

Correct size of PGA You can get the correct size of PGA using V$PGA_TARGET_ADVICE, dynamic performance view. SQL> SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb, 2 estd_pga_cache_hit_percentage cache_hit_perc, 3 estd_overalloc_count 4 FROM V$PGA_TARGET_ADVICE; TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT ———- ————– ——————– 512 98 909 1024 100 0 2048 100 0 3072 100 0 4096 100 0 4915 100 0 5734 100 [...]

For tuning buffer cache, we need to understand following point very closely. Physical reads: These are the data blocks that Oracle reads from disk. Reading data from disk is much more expensive than reading data that’s already in Oracle’s memory. When you issue a query, Oracle always first tries to retrieve the data from memory—the [...]

Shared Pool The most important part of memory resource when considering the production system is “shared pool”. The shared pool is a part of the SGA that holds almost all the necessary elements for execution of the SQL statements and PL/SQL programs. In addition to caching program code, the shared pool caches the data dictionary [...]

Introduction For tuning oracle database 10g we need to following some of the standard steps, which I call as protocol for performance tuning. To begin with as a DBA, we need to know at first that there is a problem with our database. Sounds weird !! but a fact. Many a times some DBA dont [...]

Many a times we face some issue, that a perticular sql query is holding a lock(s) on certain object. This will impact concurrency issue also for other DMLs. Some other user may complaint that he is using a DML query(Insert/delete/update) and qurey is taking lot of time. One of the reason for this might be [...]