Object dropped along with table in oracle

Objects dropped along with table in oracle

Which objects are dropped along with table and which objects become INVALID.

Below are automatically dropped with table.

1. Table structure
2. Table data
3. Indexes on the table
4. Triggers associated with table

Below objects status becomes invalid.

1. Views
2. Procedures
3. Functions
4. Packages
5. Materialized views

Oracle wait events

free buffer waits

This wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make

free buffers by writing out dirty buffers. Once DBWR finishes writing the dirty buffers to disk, they are free to be reused.

AWR shows significant waits for free buffer waits

Causes and solution

1) It could be due to poor file write performance in some filesystems. Investigate possible I/O performance problems on the filesystem.

2) Due to small buffer cache. Increase db_cache_size parameter manually or implement Automatic memory management (ASMM).

3) Use multiple DBwriters or enable asynchronous I/O if the platform supports.


log buffer space

This event occurs when server processes are writing redo records to the log buffer faster than LGWR can write them out. So, the log buffer fills up and the processes wait for free space. After LGWR writes some buffers out, then those buffers may be reused by other processes.

Causes and solution

1) LOG_BUFFER is too small. Increase the size of redo log buffer.

2) Could be due to slow redo log files (lgwr is writing very slow due to slow disks). Move the redo logs to faster disks.


read by other session

A session wants to pin a block that is currently being read from disk into the buffer cache by another session.

Causes and solution

1) Tune the SQL statement to use less I/O. from 10g, SQL tuning advisor can be used for tuning the query.

2) Due to small buffer cache. Increase db_cache_size parameter manually or implement Automatic memory management (ASMM).

Causes of HARD parsing and solutions

Causes of HARD parsing and solution

Usually hard parses are due to large statements involving partitioned objects. They also causes high CPU usage.

Case 1:

Optimizer is using dymanic sampling which impacts the parse time

Dynamic sampling is performed by the CBO at parse time when it is either requested via hint or parameter, or by default because statistics are missing.

Depending on the level of the dynamic sampling, it may take some time to complete.

This time is reflected in the parse time for the statement

From 10g, use SQL Tuning Advisor to generate a profile for the query and accept it.

Find the hints needed to implement the plan normally generated with dynamic sampling and modify the query with the hints.

Case 2:

Many queries are being HARD parsed

Hard parsing can happen due to unshared SQL statments (by using Literals in the statement)

if the "Misses in the library cache" is close to total number of parses, hard parsing can be sensed.

Identifying and modifying (rewriting) the SQL statements being hard parsed statements to use bind values could solve the

issue.

To make the statement use bind variables, we can se CURSOR_SHARING parameter in init.ora.

The following are the options set based on the desired output.

    EXACT - Leave the statement as it was written with literals (default value).
    FORCE - Substitute all literals with binds (as much as possible).
    SIMILAR - Substitute literals with binds only if the query's execution plan won't change.


Case 3:

Hard parsing due to the shared SQL aged out

If the shared pool is too small, it causes many statements that could be shared to age out of the library cache and later

reloaded.

Each reload requires a hard parse and impacts the CPU and latches.

This can be identified by AWR report in Library cache statistics section. This section shows more "reloads" count.

Increasing the sharedpool size could reduce the number of statements being aged out.

Implement Automatic Shared Memory Management(ASMM) by setting SGA_TARGET value greater than 0. This will manage the shared

pool size automatically.

Keeping the frequently used SQL and PL/SQL statements in the sharedpool (pinning) is one of the method to avoid SQL

statement age out issues.

This can be done using DBMS_SHARED_POOL.KEEP() procedure.

This will reduce reloads and fragmentation since the object doesn't need to keep reentering the shared pool over and over.

VMSTAT for DBA

What to look in vmstat (For DBA's)

A vmstat output can be used to identify CPU bottlenecks.

Below output shows vmstat output of a typical unix database server.

 procs            memory                        swap        io       system    cpu
 r  b   swpd   free   buff  cache         si   so    bi    bo     in    cs       us sy id wa
 2  5 375912  19548  17556 477472    0    1     0     0      1     1        1  0  0  1
 0  4 375912  18700  17556 478264    0    0  1044   0     774  1329   8  1   0  91
 0  5 375912  17664  17556 479168    0    0  1160   0     764  1110   8  1   0  91

In the above output, we should look for first 2 columns. ie. "r" and "b".

The server experiences bottleneck issues when "r" is greater than the number of CPU’s on the server.

In the above output first row shows "r" is 2. ie. if the CPU count is 2 and "r" is 2, there is no much CPU bottleneck. But "b" shows 5. ie. there are some sessions are waiting. 

It needs more investigation at database level if the waiting sessions are database sessions.

Things to remember in vmstat

r: How many processes are waiting for CPU time.
b: Wait Queue - Process which are waiting for I/O (disk, network, user input,etc..)

Watch command in unix

One more very simple command useful and handy to find the load and CPU starvation.

See the below output

root> w

  7:54am  up 12 days, 12:45,  49 users,  load average: 0.09, 0.11, 0.32
User     tty           login@  idle   JCPU   PCPU  what
root     ttyp1         7:01pm  5:47                tee -a /u01/home/pocj
jjuir    ttyp2         2:48pm    20      3      3  runmenu50 pamenu
uunhik   ttyp3         5:29pm    24                runmenu50 pamenu

The load average is an arbitrary number that shows overall resource consumption of the server. 

Most load average displays have three values for the load average. 

The load average display shows the load averages for the past minute, the past 5 minutes, and the past 10 minutes. 

A low load average is ideal, and the load average should stay below zero. 

Whenever the value exceeds “1” there may be a CPU overload problem.