Calculate Background Process and User Process Memory Consumption for Oracle 10g R2 (10.2.0.3)

Here is a very simple formula and observation on what is the memory consumption of Oracle Process and User Connections on Linux (SLES10) for Oracle 10.2.0.3

SGA_TARGET is set to sga_target=3100 M (Approx 3 Gb)

USER DSIZ VSZ COMMAND COMMAND
oracle 3219786 3319072 oracle ora_pmon_DB1
oracle 3266114 3365400 oracle ora_dbw0_DB1
oracle 3233590 3332876 oracle ora_lgwr_DB1
oracle 3223238 3322524 oracle ora_ckpt_DB1
oracle 3223326 3322612 oracle ora_smon_DB1
oracle 3220182 3319468 oracle ora_reco_DB1
oracle 3234162 3333448 oracle ora_arc0_DB1

oracle 3219806 3319092 oracle oracleDB1 (LOCAL=NO)
oracle 3218706 3317992 oracle oracleDB1 (LOCAL=NO)
oracle 3218706 3317992 oracle oracleDB1 (LOCAL=NO)
oracle 3218702 3317988 oracle oracleDB1 (LOCAL=NO)
oracle 3219226 3318512 oracle oracleDB1 (LOCAL=NO)
oracle 3219786 3319072 oracle oracleDB1 (LOCAL=NO)
oracle 3218702 3317988 oracle oracleDB1 (LOCAL=NO)
oracle 3219738 3319024 oracle oracleDB1 (LOCAL=NO)

So, based on the above, it is approx. 1 % of the SGA.

Let me know if you disagree or if you have similar observations / calculations.
Note : There are system parameter that will have an impact too.

Diagnostic Destination in 11g

Introduced in 11g, DIAGNOSTIC_DEST initialization parameter can be used to set user, core, background dump destination. These had to be individually set or would default to $ORACLE_HOME/dbs.

DIAGNOSTIC_DEST now contains all the ADR (Automatic Diagnostic Repository) files which includes alerts, trace, core dumps etc.

It follows the following structure.

{diagnostic_dest}/diag/rdbms/{dbname}/{instname}

and breaks down further into

{diagnostic_dest}/diag/rdbms/{dbname}/{instname}/alert
{diagnostic_dest}/diag/rdbms/{dbname}/{instname}/incident
{diagnostic_dest}/diag/rdbms/{dbname}/{instname}/trace
and so on...


Alert : Contains XML LOG (alert)
Incident : Core dumps and other incident dumps
Trace : Background (bdump) and alert_$ORACLE_SID.log

Enable SQL Trace

What is involved in enabling SQL Trace and what INIT parameters should be set ? Here are my thoughts...

STATISTIC_LEVEL – Default Typical
(can be changed to ALL)
TIMED_STATISTICS
If STATISTICS_LEVEL set to basic then false
If STATISTICS_LEVEL set to typical or all then TRUE.

In order to enable SQL Trace
TIMED_STATISTICS should be TRUE.
MAX_DUMP_FILE_SIZE
– Default Unlimited in 11g (ranges from 0 to unlimited)
USER_DUMP_DEST (In 11g DIAGNOSTIC_DEST can be set)
– Set in init, can be changed.

Trace files are generated in USER_DUMP_DEST.
OR
$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/

To enable sql trace within a session
Alter session set SQL_TRACE = TRUE;
To disable sql trace from that session
Alter session set SQL_TRACE = FALSE;

Oracle Recyclebin

Recyclebin : A new feature of RECYCLEBIN was introduced in Oracle 10g.

Enabled : By Default RECYCBLEBIN is enabled. It can be disabled.
There are two SYS owned views USER_RECYCLEBIN and DBA_RECYCLEBIN.
RECYCLEBIN$ is the table owned by SYS. Query DBA_OBJECTS for RECYCLEBIN and you will find these and others.
Basically, in simple terms it works similar to recyclebin in Windows. When you delete an object it goes to recyclebin, either you can recover it from there or PURGE recyclebin and get rid of it.
PURGE :
1. DROP TABLE PURGE;
· This will drop the table and purge at the same time and will not move it to recyclebin. It is similar to dropping a table in earlier versions (9i and below)

2. PURGE TABLESPACE tablespace_name USER username;
Substitute tablespace_name and username.
· All objects in a particular tablespace for a user that have been dropped and are still in recyclebin can be purged.

3. PURGE TABLESPACE tablespace_name;
Substitute tablespace_name.
· All objects in a particular tablespace that have been dropped and are still in recyclebin can be purged.

4. PURGE RECYCLEBIN;
· This can be used to purge user recyclebin. · Note : USER_RECYCBLEBIN synonym refers to RECYCLEBIN

5. PURGE DBA_RECYCLEBIN;
· As a dba you can clear entire recyclebin and clean (purge) all the dropped objects.
You can also purge a particular object in the recyclebin after it is dropped.
Objects in recycle bin are purged, if there are space constraints (running out of space in the tablesapce)
Some of the constraints and limitations

1. Would not preserve table's integrity constraints in recycble bin.
2. Would not restore the indexes or triggers automatically when table from recycble bin is restored.

How to see contents of Recyclebin : SHOW RECYCLEBIN;
It is not recommended to disable recyblebin.
· Disable recyblebin globally (for the database)
o alter system set recyclebin = off;· Disable recyclebin for a session
o alter session set recycblebin = off;

Why OR DBA ?

Well, i am not sure. I am still searching for a real good reason. Why work as a Professional / Consultant DBA and also carry the on-call responsibility. Why would one want to work at odd hours and get called anytime of day or night ?

I think it is challenging, interesting and fun job.

Why did you choose to be an Oracle DBA or even work on Oracle Technology or Oracle Stack ?