DBA : Oracle Database

Showing posts with label Oracle Database. Show all posts

Thursday, May 29, 2014

Shared Pool Internals

In this post, we will see shared pool architecture and how it works internally since Oracle 7 through Oracle 11gR2.

Defining the Shared Pool

Shared pool is the most important component in System Global Area (SGA) which is the RAM of database and it is the second largest in SGA memory area. It contains several key performance related memory areas. If the size of the shared pool is sized either less or more, then the entire database performance will suffer.

Purpose of Shared Pool

Many users run SQL or PL/SQL statements and it goes through 3 phases.

a. Parse – Translate and optimize the query

b. Execute – Lay down the execution plan and run the query

c. Fetch – Pull back data from oracle objects based on the execution plan.

When SQL queries are executed, the shared pool caches the executable versions of SQL and PL/SQL statements. So, when users/applications execute same SQL or PL/SQL code, it does multiple executions without doing hard parse which significantly results in reduction in CPU, memory and latches.

What does Shared Pool Contains

Shared pool contains following substructures

1. Fixed area/Permanent area

· This is allocated during instance startup

· Contains structures such as

i. Processes - using PROCESSES paramenter

ii. Sessions - using SESSIONS parameter

iii. Segmented Arrays - used to store of objects. It may grow dynamically.

2. Variable area

· It is handled by oracle’s internal algorithm

· Contains

i. Library cache

ii. Data dictionary cache ,etc

Shared pool acts like a repository for storing the SQL and Pl/sql code which was executed successfully such that if we receive similar statements, it can reduce the speed of the parsing. Much of the shared pool usage is to support the execution of the shared SQL and Pl/SQL packages, but in order to build the cursor or compile PL/Sql package, we need to about all the database objects (like tables, procedures, indexes, etc) referenced by the pl/sql package and their optimizer statistics. All these information are stored in the shared pool independent of the cursors/program unit.

Metadata are stored independently and hence it is easy to build the cursors. Few executions using shared server, parallel query, RMAN used large memory allocations in the shared pool.

Fixed area is permanent during instance startup after setup by DBA and not much to deal with these areas.

Now, we will the dynamic memory allocation areas

Data Dictionary Cache

The data dictionary cache contains information like table definitions, referential integrities, index informations, column definitions, users, passwords, privileges, etc. This is like the buffer cache except it stores dictionary information instead of user information. Since the dictionary informations are buffered, when parsing SQL cursors or during the compilation of PL/SQL program, it will be quick as these are in RAM. It is also known as ROW CACHE.

V$ROWCACHE will give details on the hit ratio for Data dictionary. The tuning of data dictionary cache is done by changing shared_pool_size parameter.

select sum(gets) "Gets", sum(getmisses) "Get Misses", (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;

The value of the Hit Ratio should be over 90%

Library Cache

The primary responsibility of Library Cache is to collect, parse and execute the SQL statements that are going against the database. It contains the following.

a. SQL/cursors – Executable representation of SQL statements that may be used repeatedly by many sessions.

b. PL/SQL – Executable representation of PL/SQL packages, procedures, functions thaty may be used repeatedly by many sessions.

c. Objects of various types required to parse and execute a SQL statement which includes tables, indexes, types, methods, etc.

Library cache will maintain the relation between tables and SQL statements (specifically child cursors) and if any change is done to tables, then oracle will know which cursors needs to invalidate and which one to keep in the cache.

We need to understand that Oracle will not keep all objects like synonyms, tables, indexes, etc in the cache. It keeps only the objects which are recently referenced.

Statistics about the library cache activity is available in V$LIBRARYCACHE.

If you want to know the objects in cache, we have to use V$DB_OBJECT_CACHE.

Result Cache

This cache holds the result sets and the query fragments. When a user queries again, the results will fetched from this cache and the response will be quick.

Internal Working of Shared Pool

As we have seen from above topics, we now know that shared pool is a collection of objects like tables, cursors, views, procedures, functions or packages (Pl/SQL package).

Size of single object will be small but when you have a PL/SQL package, the size would grow in MB as it includes many objects and its attributes. Example: A package body will have many procedures which in turn will occupy more space when we run a simple SQL statement.

Each shared pool object is not a single allocation unit (AU) and it is partitioned into independent memory allocations called “HEAPS”. Number of Heaps for the object varies depends upon the type of objects. Eg: for a SQL cursor, there will be 2 heaps a) Smaller Heap for library cache metadata and b) Larger Heap for executable representation of the cursor.

Each HEAP is comprised of 1 or more chunks of memory of Standard Allocation Units (AU) and these reduce the problem of memory fragmentation.

When memory is allocated for objects, the memory is not allocated in contiguous fashion. i.e., the chunks will not be contiguous as you can see from the figure.

But the memory (free lists – Pointers to memory chunks) must be contiguous.

Eg. Consider, we need 5KB size of Heap memory and each chunk is 4K and we need 1K to complete memory allocation, so the remaining memory is allocated to the heap from another memory chunk and hence the memory chunks are not contiguous.

When the chunks are not contiguous and if they work this way, it will avoid fragmentation.

Generally, the chunk sizes are of 1k and 4K creating more uniform memory allocations. So, when same objects are allocated and aged out, same size of chunks are allocated and aged out. By doing so, we can avoid memory fragmentation and memory usage effectively.

LARGE POOL

RMAN, parallel query and shared servers uses allocation of more than 5KB and they need large allocation of memory. Hence the use other memory pool called LARGE POOL.

RESERVED POOL

Sometimes SQL, PL/SQL packages, if they are over 5KB, they might require larger contiguous chunks of memory. Default settings of reserved pool is 4,400 bytes

If there is not enough free space in the shared pool, then oracle must search for free enough memory to satisfy the request. Oracle might even have to age out the old objects to satisfy it. In such cases, oracle will hold the latch resource for a period until it tries to find the memory. Till then, it will cause a minor disruption to the other requests (concurrent request) at memory allocation.

So, what oracle does is, it internally configures a small space called RESERVERED POOL so that when we have operations involving PL/SQL and trigger compilations or to load any java objects, this will be used. When the operation is over, the memory freed will be returned back to reserved pool.

When ORA-4031 error occurs

ORA-4031 occurs in any of the memory pools in the SGA when oracle cannot find a memory chunk large enough to satisfy the internal allocation request on behalf of users operation.

1. A user executes a query.

2. An object needs to be allocated in shared pool.

3. The memory allocated for chunks to be contiguous and this will be allocated to the objects.

4. If it is small memory size, then the Heap manager scans through freelists and if it is small, it will allocated.

5. If the requested amount of contiguous memory is not available for chunks, then Heap Manager iterates through the shared pool’s LRU list and it attempts to create a contiguous chunk of requested size (this is done by aging out the LRU objects).

6. But, ORA-4031 error occurs even if the large/free space created by the heap manager is NOT CONTIGUOUS

In case if ASMM/AMM is enabled, an additional granule of memory is requested if sufficient contiguous memory can’t be found.

References :

http://oracleinaction.com/shared-pool-arch/

http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm

http://narashimreddy.wordpress.com/2009/10/30/internals-of-shared-pool/
http://orainternals.wordpress.com/2009/08/06/ora-4031-and-shared-pool-duration/

Oracle database Architecture - Part 2

PGA – Program Global Area

It is the memory region which contains the data and control information for a server process. PGA memory is not shared and it is created by oracle when a server process is started.

PGA is a memory heap that contains session-dependent variables required by dedicated or shared server processes. Server process allocates memory that it requires in the PGA.

Two components of 2 PGA are.

1. Stack Space and

2. User Global Area.

Stack Space: It holds the bind variables, plsql array,etc.

User Global Area: UGA contains the following.

1. Session Memory

2. SQL work Area

3. Private SQL area.

Private SQL area: Whenever a user initiates a session and issues a SQL statement, it will have Private SQL area to store bind variable values, query execution state information and query execution work areas, i.e., in short it uses a single shared SQL area. Thus many private SQL areas can be associated with the same shared SQL area.

But do not confuse that private SQL area is in UGA, while the shared SQL area which stores the execution plan in SGA.

Eg. 50 executions of Select * from test_table in one session and 30 executions of same query in a different session can share the same plan.

Private shared SQL area are not shared and may contain different values and data

Cursor

Cursor on the other side is a name or pointer to the private SQL area. Cursor is like a pointer on the client side and as a state on the server side.

Private SQL area is divided into following areas.

1. Run-time area – Contains query execution state information. It also tracks the number of rows retrieved so far when the SQL is going for Full Table Scan (FTS). This is the area oracle creates initially when the execute request is passed. For DML statements, the run-time area is freed when the SQL statement is closed.

2. Persistent Area – It contains the bind variables. When the user executes the statement, the bind variable is supplied at run time. This area is freed only when the cursor is closed.

The allocation and deallocation of private SQL areas mainly dependent on the application design, eventhough the private sql areas for client process is limited by OPEN_CURSORS.

SQL Work Area

This private allocation of PGA memory is used for memory-intensive operations like sorting, has joins, bitmap merge join,etc.

Ø Sort area is used for sorting operation.

Ø Hash area is used by hash join for building the hash table

Ø Bitmap merge area is used by bitmap merge operation which uses to merge data retrieved from scans of multiple bitmap indexes.

When we use automatic memory management for PGA using PGA_AGGREGATE_TARGET , these work areas are automatically managed by oracle itself.

The bigger the work areas, the more the performance of the operator but with higher cost in memory consumption.

We have to allocate size of the work area size such that it should be big enough to accommodate the input data and auxillary memory structures allocated by its SQL operator. If not, the response time increases, because the input data has to spill to temporary disk storage. In extreme cases, when the size of sort area size is too small, multiple passes over the data pieces must be performed. This would increase the response time drastically.

We can see these with more examples in PGA tuning post.

Oracle database Architecture - Part 1

Oracle database is widely used RDBMS for nearly 2 decades and has become most successful database. In this article, we will see how the oracle database architecture is designed and how db works both at memory and OS level.

Oracle database server is categorized into 3 parts.

1. Oracle memory structures

2. Oracle background structures and

3. Oracle disk utilization structures.

Oracle memory structures consist of two main components.

i. System Global Area (SGA)

ii. Program Global Area (PGA)

SGA – It is a RAM of the database and this is the first and most important component. Whenever DBA speaks about memory of the database, it means that it is SGA.

SGA stores different components of memory usage that are designed to fetch the data quickly for users and to maximize the number of concurrent users to access the oracle instance.

SGA consists of 3 mandatory parts.

a. Buffer cache

b. Redo log buffer

c. Shared pool

Buffer cache consists of buffers which are in the size of database blocks. These are designed to store data blocks recently used by the SQL statements issued by the user. If the recently used blocks are used in the buffer cache, the performance will improvise on subsequent fetching of the data using select queries.

Redo log buffer allows the user processes to write their redo log entries to the memory area in order to increase the speed on tracking the database changes. It is important to remember that every user processes that makes a change to database must write an entry to the redo log in order to allow the database to recover the changes. If the database set up to archive redo logs, the database changes are kept in order to rebuild the database objects in the event of disk failure. The main purpose of having the redo information in memory is that it avoids the need for the user process to spend extra time to write directly on the physical files (redo log files on the disk). By doing so, oracle database avoids contention on the disk usage which would in-turn would slow down the database.

Shared pool is a another most important mandatory component of Oracle memory. It consists of

1. Library cache

2. Data Dictionary cache (also known as “row” cache)

Shared SQL library cache is designed to store the parse information of SQL queries executed against the database. Parse information includes the set of database operations that SQL execution performs to obtain the data (i.e., Parse phase,execute phase and fetch phase). This information is a shared resource in the library cache. When another user session/process executes for same query again, oracle will first check whether the query is existing in the shared pool (library cache) and if it is available, then the parse information in the shared pool will be used. But, the data returned to the user will not be shared in the shared pool, because sharing data between applications represent a integrity/security issue.

Data dictionary cache or “row” cache as referred is other mandatory component of shared pool. This is used to store data from data dictionary in order to improve response time on the data dictionary views. Since all user processes and oracle database internal processes use data dictionary, the database benefits a lot in performance when data dictionary objects are cached in memory.

We will see more about shared pool architecture and its working in a different post.

Index on Foreign Keys to avoid locks

In this article we will see how the locks are on formed on the tables when we do not have an index on the foreign key colum

Example, we will see the following scenario.

1. Let us create a primary table “P” with primary key.

SQL> create table p (x int primary key);

Table created.

2. Create a child table with column “x” referencing to the parent table “P”

SQL> create table c (x references p);

Table created.

3. Structure of the tables are as below.

SQL> desc p;

Name Null? Type

-------------------------------------- -------- --------------------------

X NOT NULL NUMBER(38)

SQL> desc c;

Name Null? Type

-------------------------------------- -------- --------------------------

X NUMBER(38)

4. Let us insert records into the parent table “P” and commit the changes.

SQL> insert into p select rownum from dual connect by level < 10000;

9999 rows created.

SQL> commit;

Commit complete.

5. Insert the same from table to “P” into table “C”

SQL> insert into c select * from p;

9999 rows created.

SQL> commit;

Commit complete.

6. Now, update the child table “C” for a value of row and pause the session without commiting. This implies that the session is not ended.

SQL> update c set x=2 where x=1;

1 row updated.

SQL> pause

7. In another session, update the child record of another row and do not commit.

SQL> update c set x=2 where x=3;

1 row updated.

8. Next, in same session (2^nd session), try to delete a row from parent table “P”

SQL> delete from p where x=10;

9. Now, you can the session is not ending the hanging.

10. Let us see whether do we have any locks on the tables .

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;

2 3 4 5 6 7

BLOCKING_STATUS

------------------------------------------------------------------------------------------------------------------------------------------------------

BCTEST@vm1 ( SID=145 ) is blocking BCTEST@vm1 ( SID=145 )

BCTEST@vm1 ( SID=133 ) is blocking BCTEST@vm1 ( SID=145 )

SQL> select a.sid,a.serial#,c.object_name,c.object_type from V$session a, V$locked_object b, dba_objects c

where a.sid=b.session_id

and b.object_id=c.object_id; 2 3

SID SERIAL# OBJECT_NAME OBJECT_TYPE

---------- ---------- --------------- -------------------

145 61 P TABLE

133 11 P TABLE

145 61 C TABLE

133 11 C TABLE

SQL> @pid.sql

Enter Oracle SID: 145

=====================================================================

SID/Serial : 145,61

Foreground : PID: 6794 - sqlplus@vm1 (TNS V1-V3)

Shadow : PID: 6797 - oracle@vm1 (TNS V1-V3)

Terminal : pts/2/ UNKNOWN

OS User : oracle on vm1

Ora User : BCTEST

Status Flags: ACTIVE DEDICATED USER

Tran Active : 000000006D487318

Last Call : Fri 15:52:26 - 6.2 min

Lock/ Latch : 000000006E39F108/ NONE

Latch Spin : NONE

Current SQL statement:

delete from p where x=:"SYS_B_0"

Previous SQL statement:

update c set x=:"SYS_B_0" where x=:"SYS_B_1"

Session Waits:

WAITING: enq: TM - contention

Connect Info:

: Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

: Oracle Advanced Security: authentication service for Linux: Version 11.2.0.3.0 - Production

: Oracle Advanced Security: encryption service for Linux: Version 11.2.0.3.0 - Production

: Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production

Locks:

TRANSAC ENQ H: X R: NONE - RS+SLOT#983048 WRP#3823

DML/DATA ENQ H: RX R: RSX - C

DML/DATA ENQ H: RX R: NONE - P

TYPE=AE H: S R: NONE - ID1=100 ID2=0

=====================================================================

SQL> @pid.sql

Enter Oracle SID: 133

=====================================================================

SID/Serial : 133,11

Foreground : PID: 6880 - sqlplus@vm1 (TNS V1-V3)

Shadow : PID: 6881 - oracle@vm1 (TNS V1-V3)

Terminal : pts/1/ UNKNOWN

OS User : oracle on vm1

Ora User : BCTEST

Status Flags: INACTIVE DEDICATED USER

Tran Active : 000000006D4C5558

Last Call : Fri 15:51:50 - 7.0 min

Lock/ Latch : NONE/ NONE

Latch Spin : NONE

Current SQL statement:

Previous SQL statement:

update c set x=:"SYS_B_0" where x=:"SYS_B_1"

Session Waits:

WAITING: SQL*Net message from client

Connect Info:

: Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

: Oracle Advanced Security: authentication service for Linux: Version 11.2.0.3.0 - Production

: Oracle Advanced Security: encryption service for Linux: Version 11.2.0.3.0 - Production

: Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production

Locks:

TYPE=AE H: S R: NONE - ID1=100 ID2=0

DML/DATA ENQ H: RX R: NONE - P

TRANSAC ENQ H: X R: NONE - RS+SLOT#851992 WRP#3894

DML/DATA ENQ H: RX R: NONE - C

=====================================================================

SQL>

1. From the above results, you can see the sessions are locked one another on both table “P” and “C”.

2. Session 145 is blocked itself as it has both update and delete statement within itself and not continuing due to references between the columns in both tables.

11. Next, we will see what happens when we create index an index on foreign key (in child table).

SQL> create index c_idx on c(x);

Index created.

12. Let us try to update and pause it in 1 session.

SQL> update c set x=2 where x=1;

1 row updated.

SQL> pause

In another session, let us update another row of child table and try to delete from Parent table.

SQL> update c set x=2 where x=3;

1 row updated.

SQL> delete from p where x=10;

delete from p where x=10

ERROR at line 1:

ORA-02292: integrity constraint (BCTEST.SYS_C0011326) violated - child record found

Here, we see that the lock is disappeared and we get an integrity constraint error.

So, we need to first delete the record from child table “C” and then from parent table “P”.

In case, if you want to delete both the parent and child records when we execute a delete a record from parent table, then we have to create foreign key as below.

alter table sample1
add foreign key (col1)
references sample (col2)
on delete cascade;

Another example is below.

1. Create a table supplier and insert some records.

CREATE TABLE supplier

( supplier_id number(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50),

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

);

INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1');

INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2');

COMMIT;

2. Create another table Product . Please note on delete cascade.

CREATE TABLE product

( product_id number(10) not null,

product_name varchar2(50) not null,

supplier_id number(10) not null,

CONSTRAINT fk_supplier

FOREIGN KEY (supplier_id)

REFERENCES supplier(supplier_id)

ON DELETE CASCADE );

INSERT INTO product VALUES (1, 'Product 1', 1);

INSERT INTO product VALUES (2, 'Product 2', 1);

INSERT INTO product VALUES (3, 'Product 3', 2);

COMMIT;

3. Delete some records from supplier (parent table ) in Session 1

SQL> DELETE supplier WHERE supplier_id = 1;

1 row deleted.

In another session, try to delete another record from same parent table

SQL> DELETE supplier WHERE supplier_id = 2;

In 3^rd session, try to insert a record into same Parent table.

INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');

4. We can see, sessions 2 and 3 will hung and will have enq:TM-contention issues from below query.

SQL> SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type

FROM v$lock l, dba_objects o, v$session s

WHERE UPPER(s.username) = UPPER('&User')

AND l.id1 = o.object_id (+)

AND l.sid = s.sid

ORDER BY sid, type; 2 3 4 5 6

Enter value for user: BCTEST

old 3: WHERE UPPER(s.username) = UPPER('&User')

new 3: WHERE UPPER(s.username) = UPPER('BCTEST')

SID BLOCKER EVENT TY LMODE REQUEST OBJECT_NAME OBJECT_TYPE

---------- ---------- ------------------------------ -- ---------- ---------- --------------- -------------------

133 SQL*Net message from client AE 4 0 ORA$BASE EDITION

133 SQL*Net message from client TM 3 0 SUPPLIER TABLE

133 SQL*Net message from client TM 3 0 PRODUCT TABLE

133 SQL*Net message from client TX 6 0

145 133 enq: TM - contention AE 4 0 ORA$BASE EDITION

145 133 enq: TM - contention TM 3 0 C TABLE

145 133 enq: TM - contention TM 3 0 P TABLE

145 133 enq: TM - contention TM 3 0 SUPPLIER TABLE

145 133 enq: TM - contention TM 0 5 PRODUCT TABLE

145 133 enq: TM - contention TX 6 0

152 145 enq: TM - contention AE 4 0 ORA$BASE EDITION

152 145 enq: TM - contention TM 0 3 PRODUCT TABLE

152 145 enq: TM - contention TM 3 0 SUPPLIER TABLE

13 rows selected.

5. Now, we will see which foreign keys are not having indexes.

SQL> col table_name format a15

SQL> col column_name format a35

SQL> SELECT * FROM (

2 SELECT c.table_name, cc.column_name, cc.position column_position

FROM user_constraints c, user_cons_columns cc

WHERE c.constraint_name = cc.constraint_name

AND c.constraint_type = 'R' and c.table_name='PRODUCT'

MINUS

SELECT i.table_name, ic.column_name, ic.column_position

FROM user_indexes i, user_ind_columns ic

WHERE i.index_name = ic.index_name

)

ORDER BY table_name, column_position;

TABLE_NAME COLUMN_NAME COLUMN_POSITION

--------------- ----------------------------------- ---------------

PRODUCT SUPPLIER_ID 1

6. Now, create an index on foreign key in child table.

SQL> CREATE INDEX fk_supplier ON product (supplier_id);

Index created.

7. Now, we can see all sessions are completing their statements without any issues.

Session 1

SQL> DELETE supplier WHERE supplier_id = 1;

1 row deleted

Session 2

SQL> DELETE supplier WHERE supplier_id = 2;

1 row deleted.

Session 3

SQL> INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');

1 row created.

Thursday, May 22, 2014

Datafiles contains uncommitted data

We are aware that LGWR writes both committed and uncommitted data from redo log buffer to redo log files not only when we commit but also when the log buffer is 10MB full, 1/3 full , every 3 seconds or every commit –whichever is first.

But nowadays, DBWR also does continuous checkpointing and flush the dirty buffers to disk (i.e., writes both committed and uncommitted data to datafiles) because of the following reasons.

a. When we do transactions greater than the available memory, we need some free buffers for transactions to complete.

b. When DBWR flush dirty blocks to disk, redo logs will have enough space and can be reused.

c. Limits the time to recovery after a crash using the parameter FAST_START_MTTR_TARGET.

Consider, we are doing a bulk update.

Then commit.

Next, the system suddenly crashes ,

If we have all data left in the cache by DBWR, then while recovery, all the data needs to be reapplied and would take long time.

DBWR flush dirty buffers to datafiles when one of the following occurs.

a. Checkpoint occurs

b. Dirty buffers reach threshold / Flush buffer cache

c. No free buffers

d. Before logfile gets overwritten/Before log switch

e. Tablespace offline

f. Tablespace read only

g. Table drop or truncate

h. Tablespace begin backup

In this post, let us see a demonstration that datafiles contains even the uncommitted data. This is carried out in single instance.

1. Create a new tablespace TEST_UNCOMMITTBS

SQL> create tablespace test_uncommittbs datafile '/data1/noasmdb/datafile/test_uncommit01.dbf' size 1M autoextend on next 512K;

Tablespace created.

2. Create a table TEST_UNCOMMIT_TBL under the tablespace created above.

Create table test_uncommit_tbl (sampletext varchar2(30)) tablespace test_uncommittbs

3. Insert a record in to the table and do not commit.

SQL> insert into test_uncommit_tbl values ('testdata_uncommit');

1 row created.

4. C heck for the data in the datafile and we do not file as we have not performed any checkpoint/flush of dirty buffer/tablespace offline/tablespace read only/tablespace begin backup.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

5. Now commit and do a checkpoint

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered

6. Note down the checkpoint change number

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4708155

7. We have the data in group 1 (CURRENT) redolog files too. So, let us do a log file switch now to have fresh CURRENT redo log file with no data above.

[oracle@vm1 redofiles]$ strings redo_g01a.log |grep testdata

testdata_uncommit

[oracle@vm1 redofiles]$ strings redo_g01b.log |grep testdata

testdata_uncommit

SQL> select group#,status from v$log;

GROUP# STATUS

---------- ----------------

1 CURRENT

2 INACTIVE

3 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS

---------- ----------------

1 ACTIVE

2 CURRENT

3 INACTIVE

[oracle@vm1 redofiles]$ strings redo_g02a.log |grep testdata

[oracle@vm1 redofiles]$ strings redo_g02b.log |grep testdata

Scenario 1 : Manual Checkpoint

In this scenario, we will do a manual checkpoint which would initiate uncommitted and committed data (dirty buffers) to write to disk.

1. Find the current checkpoint number.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4708155

2. Update the record we inserted before and do not commit

SQL> update test_uncommit_tbl set sampletext='testdata_uncommit_ckpt' where sampletext='testdata_uncommit';

1 row updated.

3. Check for the data in the datafile and it should not be available.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit

Only old data is available.

4. Now, perform a manual checkpoint in the database.

SQL> alter system checkpoint;

System altered.

5. Check for the data available in datafiles.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit_ckpt,

testdata_uncommit

[oracle@vm1 datafile]$

6. Find the current checkpoint number

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4709165

Conclusion

a. Uncommitted data is present in datafiles when a checkpoint occurs.

b. Checkpoint of the database is incremented.

Scenario 2 : Flush buffer cache

In this scenario, we will see that buffer cache containing the dirty buffers getting flushed to disk without checkpoint.

1. Find the current checkpoint

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4709314

2. Update the record as test_uncommit_flush and do not commit

SQL> update test_uncommit_tbl set sampletext='testdata_uncommit_flush' where sampletext='testdata_uncommit';

1 row updated.

3. Note that the updated record is still not available in the datafile

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit,

testdata_uncommit_ckpt,

testdata_uncommit

4. Now, flush the buffer cache .

SQL> alter system flush buffer_cache;

System altered.

5. Note that the uncommitted record is available in the datafile.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit_flush,

testdata_uncommit,

testdata_uncommit_ckpt,

testdata_uncommit

6. Also, check that checkpoint number is the same as before.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4709314

Scenario 3: Taking tablespace offline/Tablespace Read only/Tablespace online backup

1. Check the current checkpoint number of the database.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4709314

2. Check the current checkpoint value of the datafile of the tablespace TEST_UNCOMMITTBS

SQL> select name,checkpoint_change# from v$datafile where name like '%test_uncommit%';

NAME CHECKPOINT_CHANGE#

------------------------------------------------------------ ------------------

/data1/noasmdb/datafile/test_uncommit01.dbf 4709314

3. Update the record of the table and do not commit.

SQL> update test_uncommit_tbl set sampletext='testdata_uncommit_tbsoff' where sampletext='testdata_uncommit';

1 row updated

4. Check the datafile does not contain the data testdata_uncommit_tbsoff

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit_flush,

testdata_uncommit,

testdata_uncommit_ckpt,

testdata_uncommit

5. Now, take the tablespace ‘test_uncommittbs ‘ offline

SQL> alter tablespace test_uncommittbs offline;

Tablespace altered.

6. Check for the data in the datafile, it will be available.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit_tbsoff,

testdata_uncommit,

testdata_uncommit_flush,

testdata_uncommit,

testdata_uncommit_ckpt,

testdata_uncommit

7. Now the checkpoint number of the datafile is changed/incremented but the database checkpoint is same.

SQL> select name,checkpoint_change# from v$datafile where name like '%test_uncommit%';

NAME CHECKPOINT_CHANGE#

------------------------------------------------------------ ------------------

/data1/noasmdb/datafile/test_uncommit01.dbf 4712883

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4709314

8. Now bring the tablespace online

SQL> alter tablespace test_uncommittbs online;

Tablespace altered.

9. Check for the checkpoint of the database and tablespace. We will notice only the tablespace checkpoint would have got incremented.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4709314

SQL> select name,checkpoint_change# from v$datafile where name like '%test_uncommit%';

NAME CHECKPOINT_CHANGE#

------------------------------------------------------------ ------------------

/data1/noasmdb/datafile/test_uncommit01.dbf 4712952

10. Check that data will still be the ‘Updated value’ as the transaction is not yet committed.

SQL> select * from test_uncommit_tbl;

SAMPLETEXT

------------------------------

testdata_uncommit_tbsoff

Scenario 4: Before log switch/ before log files are overwrriten

1. We have seen previously that we have 3 red log groups each with 2 members. However, just displaying the result again. The CURRENT redo log group is Group 3.

SQL> select group#,members,status from v$log;

GROUP# MEMBERS STATUS

---------- ---------- ----------------

1 2 INACTIVE

2 2 INACTIVE

3 2 CURRENT

2. Note down the checkpoint number of the database.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4709314

3. Update the record in the table.

SQL> update test_uncommit_tbl set sampletext='testdata_uncommit_logswitch' where sampletext='testdata_uncommit';

1 row updated.

4. Check in the datafile and the updated record should not be available.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit_tbsoff,

testdata_uncommit,

testdata_uncommit_flush,

testdata_uncommit,

testdata_uncommit_ckpt,

testdata_uncommit

5. Now , perform log switch such that group 3 gets overwritten .

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered

6. Now, we can noticed that data is existing in the datafile before the log switch to overwritten the log.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit_logswitch,

testdata_uncommit,

testdata_uncommit_tbsoff,

testdata_uncommit,

testdata_uncommit_flush,

testdata_uncommit,

testdata_uncommit_ckpt,

testdata_uncommit

7. Note the checkpoint number incremented.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4713768

Scenario 5 : Buffers reaching threshold

In this scenario, we will see how the committed/uncommitted data (dirty buffers) are flushed to disk when the server processes do not find free buffers.

1. Note the current checkpoint of the database.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4713768
2. Find the current size of the database buffer cache.

SQL> select component,current_size/1024/1024,min_Size/1024/1024,max_size/1024/1024 from v$sga_dynamic_components where component='DEFAULT buffer cache';

COMPONENT CURRENT_SIZE/1024/1024 MIN_SIZE/1024/1024 MAX_SIZE/1024/1024

------------------------ ---------------------- ------------------ ------------------

DEFAULT buffer cache 44 44 44

3. Let us update the record in the table and do not commit.

SQL> update test_uncommit_tbl set sampletext='testdata_uncommit_threshold' where sampletext='testdata_uncommit';

1 row updated.

4. Now check for the data in datafile and you will not find the data.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit_logswitch,

testdata_uncommit,

testdata_uncommit_tbsoff,

testdata_uncommit,

testdata_uncommit_flush,

testdata_uncommit,

5. Let us query another table without committing the last update statement.

I just ran the statement.

Select * from dba_objects;

6. Once I did this, I could see the uncommitted data ‘testdata_uncommit_threshold’ is available in the datafile.

[oracle@vm1 datafile]$ strings test_uncommit01.dbf |grep testdata

testdata_uncommit_threshold,

testdata_uncommit,

testdata_uncommit_logswitch,

7. Checkpoint is not incremented .

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

4713768

Conclusion on this scenario :

Checkpoint is not changing when there is just a flush of dirty buffers to disk. This is due to LRU/TCH algorithm which only flushes from the cold end of LRUlist which may not necessary to have the recent SCN i.e.,, they may contain the oldest SCN.

References

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1670195800346464273

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=40952&DestinationA=RSS

http://oracleinaction.com/uncommitted-data-in-datafiles/

DBA

Pages

Thursday, May 29, 2014

Shared Pool Internals

Oracle database Architecture - Part 2

Oracle database Architecture - Part 1

Index on Foreign Keys to avoid locks

Thursday, May 22, 2014

Datafiles contains uncommitted data

Pages

Blog Archive