1 Log File

Every database would have at least 2 log file groups. 

• Noarchivelog mode is similar to ‘truncate log at check point” in Sybase or “Simple recovery mode” in MSSQL
• archivelog mode is similar to “Full recovery mode” in MSSQL

When the LGWR write the log buffer to the log file

• Every 3 secs , the LGWR will check the remaining space in the log buffer. if the free space is less than 1/3, the LGWR would write the log buffer to the online redo log file.The “1/3” threshold is control by _LOG_IO_SIZE
• When log buffer has more than 1 MB data
• Online redo log switch
• User issue commit command
  

Log file status

• current: The Log file is currently used by LGWR.
• active: Check point is ongoing but not yet completed.
• inactive: Checkpoint is completed.
• cleaning: The logfile content is being clean.
• unused: Never used before or clean completed.

When the does the log switch happen?

• Current log file full
• Alter system switch logfile

select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE# from v$log

Below query shows 3 file group. The current log group is 1.

alter system switch logfile;

select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE# from v$log;

After log switch, the File group become current and the sequence number +1. The File group 1 become active as it is undergoing check point process.

After checkpoint completed, the File group 1 become inactive

Clean the logfile group 1. Usually, we only need to clear the log file if the logfile is corrupt.

alter database clear logfile group 1;

Log group 1 become unused and reset the sequence to 0

Lest switch the log file again.

alter system switch logfile;

select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE# from v$log

Becane the logfile group 1 has the smallest sequence number ( 0 ), therefore the log switch would switch to filegroup 1 instead of 3.

Check the log file physical location ( V$LOGFILE)

select * from v$logfile

Add log file group

alter database add logfile group 4 ('/home/oracle/app/oracle/oradata/orcl/redo04.log') size 10M;

Remove log file group

Prerequisites before removing the log file group

• database has at least 2 logfile group after remove.
• Only status is inactive or unused can be removed.
• If db is in the archived mode, the ARC column must be YES.

alter database drop logfile group 3;

Even the logfile group has already been drop but the log file still exist on the file system. To reclaim the disk space, we have to manually delete the file from OS file system ( rm or delete).

Add log file member

The new log file is same size as other log file in the same group. When oracle instance write to the file group, it would write into both file group member. Ideally, the two different log file should be in the 2 different file system/disk to ensure the availability.

 alter database add logfile member '/home/oracle/app/oracle/oradata/orcl/redo04b.log' to group 4;

Remove log file member

Prerequisites before removing the log file group

• The file group member can not be the last one in the file group..
• Only status is inactive or unused can be removed.
• If db is in the archived mode, the ARC column must be YES.

Below demo shows if we try to drop the last file member in the file group, we would get ORA-00361 error.

col member format a50

select GROUP#,member from V$LOGFILE;

select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE#,BYTES from v$log;

alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo04b.log';

select GROUP#,member from V$LOGFILE;

select group#,SEQUENCE#,STATUS,ARCHIVED,MEMBERS,FIRST_CHANGE#,BYTES from v$log;

Even the logfile member has already been drop but the log file still exist on the file system. To reclaim the disk space, we have to manually delete the file from OS file system ( rm or delete).

 

Reference

• http://psoug.org/reference/log_files.html
• http://docs.oracle.com/cd/E14072_01/server.112/e10595/onlineredo002.htm
• OMF: http://docs.oracle.com/cd/B28359_01/server.111/b28310/omf.htm

2 Connection, Session and UGA

When user use the application ( such as Sqlplus ) connect to the database server. The client application would start the client process. Oracle database would respond with Server processes to process use’s request, such as query.  Each connection can have more than 1 sessions. UGA is session memory. In the dedicate server architecture, UGA store in the PGA. In the shared server architecture, UGA store in the SGA( Large Pool).

• V$PROCESS contains all the connections in the database instance.
• V$SESSION contains all the sessions in the database instance.

* image from Oracle

Server process and Client process may not be one to one mapping.

Below demo is from Oracle document . It create 2 sessions in one connections.

First, list the current session own by SYS.

SELECT SID, SERIAL#, PADDR,program FROM V$SESSION WHERE USERNAME ='SYS';

Enable the autotrace which create the another session. As below screenshot shows the SID 148 and 151 has same PADDR( Process address).

disconnect command only terminate the sessions but not process.

open another session and query the process address .As you can see from below screen shot, the PID 27 still exist, but there is no more session ID associate with that process.

SELECT SID, SERIAL#, PADDR,program FROM V$SESSION WHERE USERNAME ='SYS';

SELECT PID,SPID,ADDR,PNAME,USERNAME,PROGRAM FROM V$PROCESS WHERE ADDR = HEXTORAW('5FFDEB88');

1 Cursor

Every query comes into oracle, oracle would assign the cursor(s) in PGA’s private SQL area. OPEN_CURSORS parameter define how many cursors can open by a user sessions.

More cursors open, used more memory in PGA.

SESSION_CACHED_CURSORS

Number of cursors a session can store. 11.2 default value is 50. This is to avoid the soft parse.

Show cursors number by applications and users

    SELECT

      s.sid,

      s.username,

      s.osuser,

      S.program,

      S.SERIAL#,

      S.LOGON_TIME,

      S.STATUS,

      B.class,

      b.name,

      v.value

    from

      V$SESSTAT v,

      v$statname b,

      V$SESSION S,

      v$process p

    where

    v.statistic# = b.statistic#

    and s.paddr = p.addr

    and V.sid=S.sid

    -- and s.username is not null

    and B.name='opened cursors current'

     -- and value > 10 

    order by

     10 desc

 

Show the open cursor’s SQL

SELECT

  s.sid,

  s.username,

  s.osuser,

  S.program,

  S.SERIAL#,

  S.LOGON_TIME,

  S.STATUS,

  B.class,

  b.name,

  V.value,

  sql.SQL_TEXT

FROM

  V$SESSTAT v,

  v$statname b,

  V$SESSION S,

  V$PROCESS P,

  V$SQLTEXT SQL

WHERE

  sql.ADDRESS    = S.SQL_ADDRESS

AND v.statistic# = b.statistic#

AND s.paddr      = p.addr

AND V.sid        =S.sid

AND B.name       ='opened cursors current'

ORDER BY

  p.spid,

  sql.hash_value,

  sql.piece

2 PGA ( Program Global Area)

Pre 9i

* Image from here

• SORT_AREA_SIZE: Use by sort operation
• HASH_AREA_SIZE: Use by hash join operation
• CREATE_BITMAP_AREA_SIZE: use by building bitmap index. Larger value would help speed up create the bitmap index.
• MERGE_BITMAP_AREA_SIZE: use by merge comparison operation. Larger value would help speed up the merge operation.

* Image from here

• Session Memory: User information, such as username, login time.
• Persistent Area: store bind variables.
• Runtime area: store Query execution state

Show parameter pga_aggregate_target

show parameter area_size

Show the current session pga/uga usage

SELECT

  a.name as "NAME",

  b.value as "VALUE"

FROM

  v$statname a, v$sesstat b

WHERE

  a.statistic#=b.statistic#

AND

  (

    a.name LIKE 'session%ga memory%%'

  OR a.name LIKE '%direct temp%'

  )

AND sid=

  (

    SELECT DISTINCT

      sid

    FROM

      v$mystat

  );

Below example show when the sort area size is 64K (65536 byte), the disk sort count is 1.

-- Create test table

create table testpo as select * from dba_objects;

-- Collect statistic on the table

execute DBMS_STATS.GATHER_TABLE_STATS('SYS','testpo');

-- Enable the trace

set autotrace traceonly statistic;

select * from testpo order by 1;

After increase the sort area size to 128K, the disk count is still 1.

alter session set sort_area_size = 131072;

Once I increase the sort_area_size to 12800K, the disk sort become 0. The sort now is all happen in the memory. This also means if we have a lot of process need the sort operation, we have to allocate a lot of memory to each session for the sort area.

alter session set sort_area_size = 13107200;

PGA_AGGREGATE_TARGET

After 9i, Oracle add the PGA_AGGREGATE_TARGET to automatically manage the PGA memory instead it is fixed for the entire PGA. Every operation can use 5% of PGA_AGGREGATE_TARGET. In parallel operation, can use up to 30% memory. Once the process complete, the memory get release back to the pool for next process. So the same session start the next operation, it will need to request the memory again.

alter system set PGA_AGGREGATE_TARGET=150M;

 

PGA_AGGREGATE_TARGET is dynamic parameter. we don’t have to bounce the instance. Once the PGA_AGGREGATE_TARGET set greater  than 0. he WORKAREA_SIZE_POLICY change to auto automatically .

However, if you want to turn off the PGA_AGGREGATE_TARGET, we have to bounce the instance and set to spfile only. Otherwise, we would get the ORA-00093 error.

After we turn on PGA_AGGREGATE_TARGET, the same query we execute before has 0 disk sort . The SORT_AREA_SIZE,SORT_AREA_SIZE,CREATE_BITMAP_AREA_SIZE and MERGE_BITMAP_AREA_SIZE are not effective because of PGA_AGGREGATE_TARGET is enable.. The PGA_AGGREGATE_TARGET would automatically adjust the memory.

V$PGASTAT

Show the current PGA stat.

select * from V$PGASTAT

V$PROCESS

V$PROCESS contain the current active process.

Below query show the current active process and PGA memory usage

select pid,spid,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_MAX_MEM,addr from v$process

Below query list all the user session that use PGA memory  more than 1MB

SELECT

  b.pid,

  b.spid ,

  a.sid,

  a.osuser,

  a.username,

  a.machine,

  a.process,

  a.logon_time,

  a.program,

  b.PGA_USED_MEM,

  b.PGA_ALLOC_MEM

FROM

  V$SESSION a,

  V$PROCESS B

WHERE

  a.PADDR                    = B.ADDR

AND B.PGA_USED_MEM/1024/1024 > 1

ORDER BY

  b.PGA_USED_MEM

 

V$PGA_TARGET_ADVICE

statistics_level needs to be TYPICAL or ALL in order to use V$PGA_TARGET_ADVICE.

select * from v$pga_target_advice

V$STATISTICS_LEVEL

This view shows what advice would be turn on by statistics_level .

select sl.statistics_name,sl.session_status,sl.system_status,sl.activation_level,sl.session_settable  from v$statistics_level sl;

V$SYSSTAT

Below query show the sorrt area status

select * from V$sysstat where name like '%sort%'

Reference

docs.oracle.com/cd/E25054_01/server.1111/e25789/memory.htm#autoId4

1 Streams Pool

Steams pool memory is used by Oracle stream only.

How to check streams pool size?

show parameter streams_pool

select * from v$sgastat where pool='streams pool';

V$streams_pool_advice

select * from v$streams_pool_advice;

Reference

http://docs.oracle.com/cd/E25054_01/server.1111/e25789/memory.htm

88 Java Pool

Java Pool store the session specific code within JVM .

How to check the Java Pool size?

show parameter java_pool

select * from v$sgastat where pool='java pool';

How to change the Java Pool size

alter system set java_pool_size=8M;

java_max_sessionspace_space

Max memory for a java session can use. once it exceed this amount, the application would be aboard.

java_soft_sessionspace_limit.

Similar to the  java_max_sessionspace_space but the application would be aboard but only log into the trace file,

v$java_pool_advice

select * v$java_pool_advice;

Reference

http://docs.oracle.com/cd/E25054_01/server.1111/e25789/memory.htm

0 Large Pool

Large Pool’s purpose is similar to Shared Pool. The major difference is it would be only used in the certain circumstances.

• In MTS( Multi-threaded server) env, use for soft area and hash area.
• Parallel Query
• Large file’s IO Buffer
• RMAN’s IO Buffer

* Image is from Oracle

0 Redo Log Buffer

How to check the Redo Log Buffer size?

V$SGASTAT reports actual memory usage, where V$PARAMETER/show parameter reports the
initial parameter setting.  Base on the below query, it show the difference between show parameter and V$SGASTAT.  What I can find online, there are couple different reasons for the difference

• Guard pages
• Log buffer page headers (16 bytes per log buffer page).

Initial size

show parameter log_buffer

Current size

show sga

select * from v$sgainfo;

select name, bytes from v$sgastat where name = 'log_buffer';

select name, value from v$sga where name = 'Redo Buffers';

How to set the log Buffer size?

Log buffer size is control internal by oracle from 10g and later. Per Meta link note 351857.1, Oracle automatically size the log_buffer. Also, if we use AMM, the log buffer size is part of memory_target algorithm.
If you really need to change the log_buffer, you can still use alter system to change it. The parameter is not dynamic, so we need to set the scope to spfile and bounce the instance to make it effective.

alter system set log_buffer=XXX scope=spfile;

Log block size

Log buffer is divided by continuous serious of blocks. The block size is vary by OS platform. Below Query show the log block size.

select max(lebsz) from sys.x$kccle;

 

When the LGWR write the log buffer to the log file

• Every 3 secs , the LGWR will check the remaining space in the log buffer. if the free space is less than 1/3, the LGWR would write the log buffer to the online redo log file.The “1/3” threshold is control by _LOG_IO_SIZE
• When log buffer has more than 1 MB data
• Online redo log switch

Reference

Log Guard Page: http://www.ixora.com.au/notes/log_buffer_guard_pages.htm

http://www.dbasupport.com/forums/showthread.php?52976-Log-Buffer-Size/page2

0 How to find out what SQL is currently running by particular session ?

There are at least 2 ways to find out what SQL is running by particular session. DBCC INPUTBUFFER and DMV.

DBCC INPUTBUFFER( Session ID)

Use sp_who to find the session ID. Then run

DBCC INPUTBUFFER(148)

4 ERworld 2012 on Demand / ERwin 9

The free ERworld online conference( Oct 22th-24th) just finished. The conference is now available on demand. You can access all the conference online from here until 1/22/2013. Unfortunately, it does not like VMware tech day we can download the video and put in the pocket on the go. They only allow to view it online. It would be nice if they can change that.

 

One of the exciting news is the ERwin 9 release. According to CA tech support, the license is available on request starting this week ( 10/22) if you have existing contract in place. ERwin 9 has complete redefine its architecture. In the earlier version  ( 4, 7, 8), ERwin Data modeler is 2 tier application. The ERwin data modeler connect to the Model mart through the database client and we have to create the login/user in the Model mart database.

 

0 ORA-00600 Internal error code …

After I shutdown my oracle instance and restart, I got the ORA-00600…

This is the Oracle internal error, the error message only can interoperate by Oracle support. The another error ORA-02097 looks specious. It seems to me some parameter in the spfile is not correct cause the database fail to mount.

In order to figure out what is wrong in my spfile, I have to recreate it to pfile.

• Startup nomount
• create pfile from memory;
• startup pfile=’path of the pfile’

Ah… the database mount fine…

Let me recreate the spifle

create spfile from memory

… everything looks good now …