Category Archives: Database

Create Histograms On Columns That Already Have One

The default value of METHOD_OPT from  10g onwards is ‘FOR ALL COLUMNS SIZE AUTO’.

The definition of AUTO as per Oracle documentation is  :
AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

This basically implies that Oracle will automatically  create histograms on those  columns which have skewed data distribution and there are  SQL statements  referencing those columns.

However, this gives rise to the problem is that Oracle generates too many  unnecessary histograms .

Let’s demonstrate:

– Create a table with skewed data distribution in two columns

SQL>drop table hr.skewed purge;

create table hr.skewed
( empno number,
job_id varchar2(10),
salary number);

insert into hr.skewed select employee_id, job_id, salary
from hr.employees;

– On gathering statistics for the table using default options, it can be seen that histogram is not gathered on any column although data
distribution in columns JOB_ID and SALARY is skewed

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED');

col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID NONE
SKEWED EMPNO NONE

– Let’s now issue some queries querying the table based on  the  three columns in the table followed by statistics gathering to verify that histograms get automatically created only on columns with skewed data distribution.

– No histogram gets created if column EMPNO is queried which
has data distributed uniformly

SQL>select * from hr.skewed where empno = 100;
exec dbms_stats.gather_table_stats('HR','SKEWED');

col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID NONE
SKEWED EMPNO NONE

– A histogram gets created on JOB_ID column as soon as we search  for records with a JOB_ID as data distribution is non-uniform in JOB_ID column

SQL>select * from hr.skewed where job_id = 'CLERK';
exec dbms_stats.gather_table_stats('HR','SKEWED');

col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

– A histogram gets created on SALARY column when search is made for  employees drawing salary more than 10000 as data distribution is non-uniform in SALARY column.

SQL>select * from hr.skewed where salary < 10000;
exec dbms_stats.gather_table_stats('HR','SKEWED');

col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY FREQUENCY
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

Thus gathering statistics using default options, manually or as part of the automatic maintenance task,  might lead to creation of histograms  on all such columns  which have  skewed data distribution and  had been  part of the search clause even once. That is, Oracle  makes even the histograms you didn’t ask for.  Some of the histograms might not be needed by the application and hence are undesirable as computing histograms is a resource intensive operation and moreover they might  degrade the performance as a result of their interaction with bind peeking.

Solution
Employ FOR ALL COLUMNS SIZE REPEAT option of METHOD_OPT parameter  which prevents deletion of existing histograms and collects histograms only on the columns that already have histograms.

First step is to eliminate unwanted histograms and have histograms only on the desired columns.

Well, there are two options:

OPTION-I: Delete histograms from unwanted columns and use REPEAT option henceforth which Collects histograms only on the columns that already have histograms.

– Delete unwanted histogram for SALARY column

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED', -
METHOD_OPT => 'for columns salary size 1');

-- Verify that histogram for salary column has been deleted

col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

– Issue a SQL with  salary column in where clause and verify that gathering  stats using repeat  option retains histogram on JOB_ID column and does not cause histogram to be created on salary column.

SQL>select * from hr.skewed where salary < 10000;

exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns salary size REPEAT');

col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
 SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

OPTION-II:   Wipe out all histograms and manually add only the desired ones. Use REPEAT option henceforth which Collects histograms only on the columns that already have one.

– Delete histograms on all columns 

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for all columns size 1');

– Verify that histograms on all columns have been dropped

SQL>col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
 SKEWED JOB_ID NONE
 SKEWED EMPNO NONE

– Create histogram only on the desired JOB_ID column

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns JOB_ID size AUTO');

– Verify that histogram has been created on JOB_ID

SQL>col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

- Verify that gathering  stats using repeat  option creates histogram only on JOB_ID column on which it already exists

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns salary size REPEAT');

SQL>col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

That is, now Oracle will no longer make histograms you didn’t ask for.

– Finally, change the preference for METHOD_OPT parameter of automatic stats gathering job from default value of AUTO to REPEAT so that it will gather histograms only for the columns already having one.

–  Get Current value –

SQL> select dbms_stats.get_prefs ('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
-----------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

– Set preference to REPEAT–

SQL> exec dbms_stats.set_global_prefs ('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

– Verify –

SQL> select dbms_stats.get_prefs ('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
-----------------------------------------------------------------------
FOR ALL COLUMNS SIZE REPEAT

From  now onwards, gathering  of statistics, manually or automatically will not create any new histograms while retaining  all the existing ones.

I hope this post is useful.

Happy reading….

References:

https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt
http://www.pythian.com/blog/stabilize-oracle-10gs-bind-peeking-behaviour/
https://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/

———————————————————————————————–

Related Links:

Home

Database Index
Tuning Index

———————————————————————————————

Change Parameter Value In Another Session

The values of initialization parameters in another session can be changed  using procedures SET_BOOL_PARAM_IN_SESSION and SET_INT_PARAM_IN_SESSION provided in DBMS_SYSTEM package.

Let’s demonstrate:

SQL>conn / as sysdba

SYS> grant dba to hr;

– Currently parameter HASH_AREA_SIZE is set to 131073 in HR session

HR>> sho parameter hash_area_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hash_area_size                       integer     131073

– Find out SID, SERIAL# for HR session

SYS> select sid, serial#, username from v$session where username=’HR';

SID    SERIAL# USERNAME
———- ———- ——————————
54        313 HR

– Set value of parameter HASH_AREA_SIZE to 131072 in HR session

SYS> exec dbms_system.SET_INT_PARAM_IN_SESSION(54, 313, ‘HASH_AREA_SIZE’,131072);

– Verify that the value of parameter HASH_AREA_SIZE has been changed in HR session

HR> sho parameter hash_area_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hash_area_size                       integer     131072

Similary Boolean initialization parameters can be modified using  dbms_system.SET_BOOL_PARAM_IN_SESSION.

– Let’s find out the value of parameter SKIP_UNUSABLE_INDEXES in HR session

HR> sho parameter skip_unusable indexes

NAME TYPE VALUE
———————————— ———– ——————————
skip_unusable_indexes boolean TRUE

– Modify the  value of parameter SKIP_UNUSABLE_INDEXES to FALSE in HR session

SYS> exec dbms_system.SET_BOOL_PARAM_IN_SESSION(54, 313, ‘skip_unusable_indexes’,FALSE);

– Verify that the value of parameter SKIP_UNUSABLE_INDEXES  has been changed to FALSE in HR session

HR> sho parameter skip_unusable indexes

NAME TYPE VALUE
———————————— ———– ——————————
skip_unusable_indexes boolean FALSE

References:

http://dbaspot.com/oracle-server/143210-how-query-sessions-active-initialization-parameters-other-session.html

————————————————————————————————–

Related links:

Home

Database Index 

Find Values Of Another Session’s Parameters

 

—————————-

ORA-09925: Unable to create audit trail file

I received this error message when I started my virtual machine and tried to logon to my database as sysdba to startup the instance.
[oracle@node1 ~]$ sqlplus / as sysdba

ERROR:
ORA-09925: Unable to create audit trail file
Linux Error: 30: Read-only file system
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux Error: 30: Read-only file system
Additional information: 9925

- I rebooted my machine and got following messages which pointed to some errors encountered during filesystem check and instructed to run fsck manually.

[root@node1 ~]# init 6

Checking filesystems

/: UNEXPECTED INCONSISTENCY; RUN fsck MANUALLY.
(i.e., without -a or -p options)
*** An error occurred during the filesystem check.
*** Dropping you to a shell; the system will reboot
*** when you leave the shell.
Give root password for maintenance
(or type Control-D to continue):

– I entered password for root to initiate filesystem check. As a result I was prompted multiple no. of times to allow fixing of  various filesystem errors.

(Repair filesystem) 1 # fsck
Fix(y)?

- After all the errors had been fixed, filesystem check was restarted

Restarting e2fsck from the beginning...

/: ***** FILE SYSTEM WAS MODIFIED *****
/: ***** REBOOT LINUX *****

- After filesystem had been finally checked to be correct, I exited for reboot to continue.

(Repair filesystem) 2 # exit

– After the reboot, I could successfully connect to my database as sysdba .

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 12 09:21:52 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL>

I hope this post was useful.

Your comments and suggestions are always welcome.

—————————————————————————————–

Related Links:

Home

Database Index

 

————-

 

PARALLEL_ADAPTIVE_MULTI_USER

One of the  initialization parameters that influence the number of slave processes assigned to a server process is parallel_adaptive_multi_user.

It accepts two values:

FALSE (default value in Oracle9i): If the pool is not exhausted, the server process is assigned the requested number of slave processes .

TRUE (default value from Oracle Database 10g onwards): As the number of  slave processes already in use increases, the requested degree of parallelism is automatically reduced, even if there are still enough servers in the pool to satisfy the required degree of parallelism.

Let’s demonstrate ..

Overview:

– parallel_max_servers = 40

Set parallel_adaptive_multi_user = false
– Execute a query requesting  DOP of 8 concurrently in 1, 2, 3, 4, 5 and 6 sessions and note down the no. of slave processes allocated in each session for each concurrent execution.
– Verify that
. for no.  of concurrent sessions <= 5, each session gets requested no. of slave processes i.e. 8
. for no.  of concurrent sessions > 5, first 5 sessions get 8 slave processes each but query in any subsequent session is executed serially i.e. it is silently downgraded.

Set parallel_adaptive_multi_user = true
– Execute a query requesting  DOP of 8 concurrently in 1, 2, 3, and 4 sessions and note down the no. of slave processes allocated in each session for each concurrent
– Verify that
. for no.  of concurrent sessions  <= 2, each session gets requested no. of slave processes i.e. 8
. for no.  of concurrent sessions  =  3,
– first 2 sessions get 8 slave processes each
-Third session  gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
. for no.  of concurrent sessions > 3,
– first 2 sessions get 8 slave processes each
-Third session  gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
– The query in any subsequent session is executed serially i.e. it is silently downgraded.

Implementation :
———— PARALLEL_ADAPTIVE_MULTIUSER = FALSE —————————
– Create test table temp_sales with dictionary DOP = 4

SQL> drop table sh.temp_sales purge;
create table sh.temp_sales as select *    from sh.sales;
alter table sh.temp_sales parallel 4;

– Set parallel_min_percent=0 and restart the database

SQL> alter system set parallel_min_percent=0 scope=spfile;
              shu immediate;              startup SQL> select * from v$px_process_sysstat               where statistic like '%Server%'; STATISTIC                           VALUE  ------------------------------ ----------  Servers In Use                          0  Servers Available                       8  Servers Started                         0  Servers Shutdown                        0  Servers Highwater                       0  Servers Cleaned Up                      0  Server Sessions                         0

– Check that parallel_max_servers = 40

SQL> sho parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
parallel_max_servers                 integer     40

– check that parallel_adaptive_multi_user   =    FALSE

SQL> sho parameter parallel_adaptive

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
parallel_adaptive_multi_user         boolean     FALSE

– Issue the following query requesting DOP = 8 in one session --

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

– Check that the query gets requested no. of slave processes (8)

SQL> select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

-- check the highwater  of parallel processes started = 8–

select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                         0
Servers Shutdown                        0
Servers Highwater                       8
Servers Cleaned Up                      0
Server Sessions                         8

-- Issue the following query requesting DOP = 8 in two sessions –
-- Check that both the sessions get requested no. of slave processes (8)

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– check that
      . 8 more parallel processes have started (Servers Started  =  8)
      . 16 parallel processes are available now (Servers Available = 16)          
      . Servers highwater has been updated to 16

select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      16
Servers Started                         8
Servers Shutdown                        0
Servers Highwater                      16
Servers Cleaned Up                      0
Server Sessions                        24

– Issue the following query requesting DOP = 8 in three sessions –
– Check that all the sessions get requested no. of slave processes (8)

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– check that
      .8 more parallel processes have started (Servers Started  =  16)
      . 24 parallel processes are available now (Servers Available = 24)          
      . Servers highwater has been updated to 24

SQL>select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      24
Servers Started                        16
Servers Shutdown                        0
Servers Highwater                      24
Servers Cleaned Up                      0
Server Sessions                        48

– Issue the following query requesting DOP = 8 in four sessions –
– Check that all the sessions get requested no. of slave processes (8)

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– check that
      .8 more parallel processes have started (Servers Started  =  24)
      . 32 parallel processes are available now (Servers Available = 32)          
      . Servers highwater has been updated to 32

SQL>select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      32
Servers Started                        24
Servers Shutdown                        0
Servers Highwater                      32
Servers Cleaned Up                      0
Server Sessions                        80

– Issue the following query requesting DOP = 8 in five sessions –
– Check that all the sessions get requested no. of slave processes (8)

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– check that
      . 8 more parallel processes have started (Servers Started  =  32)
      . 40 parallel processes are available now (Servers Available = 40)          
      . Servers highwater has been updated to40

SQL>select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      40
Servers Started                        32
Servers Shutdown                        0
Servers Highwater                      40
Servers Cleaned Up                      0
Server Sessions                       120

– Issue the following query requesting DOP = 8 in five sessions –
– In first 5 sessions  Server process gets requested no. of slaves (8):

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

-- In 6th session, the query gets executed serially

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          0

– check that
      . No more parallel processes have started (Servers Started  =  32 as  earlier)
      . 40 parallel processes are available as earlier (Servers Available = 40)          
      . Servers highwater  is at the same value as earlier i.e. 40

SQL>select * from v$px_process_sysstat
    where statistic like '%Server%'; STATISTIC                           VALUE ------------------------------ ---------- 
Servers In Use                          0
Servers Available                      40 
Servers Started                        32 
Servers Shutdown                        0 
Servers Highwater                      40 
Servers Cleaned Up                      0 
Server Sessions                       160

The observations for PARALLEL_DAPTIVE_MULTI_USER = FALSE can be tabulated as follows:

PARALLEL_ADAPTIVE_MULTI_USER = FALSE
|————– No. of slave processes assigned ————–|
No. of sessions        sess1         sess2         sess3      sess4      sess5      sess6
———————-            ———        ——–        ———      ——–      ——–       ——–

          1                                    8                   –                   –                 –                –                 -

          2                                    8                   8                   –                 –                –                 -

          3                                    8                   8                   8                 –                –                 -

          4                                    8                   8                   8                 8                –                 -

          5                                    8                   8                   8                 8                8                 -

          6                                    8                   8                   8                 8                8                 0

  Hence, it can be seen that
. for no.  of concurrent sessions <= 5, each session gets requested no. of slave processes i.e. 8 so that total px servers allocated =  parallel_max_servers = 40
. for no.  of concurrent sessions > 5, first 5 sessions get 8 slave processes each but query in any subsequent session is executed serially i.e. it is silently downgraded as maximum no. of px slaves have already been used.
Hence, when PARALLEL_ADAPTIVE_MULTI_USER = FALSE, as many px slaves are allocated as are requested as long as they are available.

————- PARALLEL_ADAPTIVE_MULTI_USER = TRUE —————————

– Set parallel_adaptive_multi_user = true and restart the database

SQL>Alter system set parallel_adaptive_multi_user = true;
    shu immediate; 
    startup             
    sho parameter parallel_adaptive NAME                                 TYPE        VALUE  -------------------------------  ----------- -----------------  
parallel_adaptive_multi_user         boolean     TRUE

– Issue the following query requesting 8 px slaves in one session –
– Check that the  Server process gets requested no. of slaves (8)
– While the query is executing , go to next step and check that Servers in  use = 8 and servers highwater = 8

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          8

SQL>select * from v$px_process_sysstat
      where statistic like '%Server%';

STATISTIC                           VALUE
 ------------------------------ ----------
 Servers In Use                          8
 Servers Available                       0
 Servers Started                         0
 Servers Shutdown                        0
 Servers Highwater                       8
 Servers Cleaned Up                      0
 Server Sessions                         8

– Issue the following query requesting 8 px slaves in two sessions –
– Check that the  both the sessions get requested no. of slaves (8)

– While the query is executing , go to next step and check that Servers in  use = 16 and servers highwater = 16

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          8

SQL>select * from v$px_process_sysstat
      where statistic like '%Server%';

STATISTIC                           VALUE
 ------------------------------ ----------
 Servers In Use                         16
 Servers Available                       0
 Servers Started                         8
 Servers Shutdown                        0
 Servers Highwater                      16
 Servers Cleaned Up                      0
 Server Sessions                        24

– Issue the following query requesting 8 px slaves in three sessions –
– Check that the first two sessions get requested no. of slaves (8)
                                the third session get 6 slaves only

– While the query is executing , go to next step and check that Servers in  use = 22 and servers highwater = 22 (8 + 8 + 6)

– In first 2 sessions :

SQL> conn sh/sh
      select count(*) from sh.temp_sales s1, sh.temp_sales s2
      where rownum < 10000000;

    select statistic, last_query from v$pq_sesstat
           where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          8

In third session :

SQL> conn sh/sh
      select count(*) from sh.temp_sales s1, sh.temp_sales s2
      where rownum < 10000000;

    select statistic, last_query from v$pq_sesstat
           where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          6

SQL>select * from v$px_process_sysstat
            where statistic like '%Server%';

STATISTIC                           VALUE
 ------------------------------ ----------
 Servers In Use                         22
 Servers Available                       0
 Servers Started                        14
 Servers Shutdown                        0
 Servers Highwater                      22
 Servers Cleaned Up                      0
 Server Sessions                        46

– Issue the following query requesting 8 px slaves in four sessions –
  – Check that the first two sessions get requested no. of slaves (8)
                                the third session get 6 slaves only 
                               the fourth session does not get any slaves

– While the query is executing , go to next step and check that Servers in  use = 22 and servers highwater = 22 (8 + 8 + 6) as earlier
– In first 2 sessions :

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– in 3rd session:

SQL> conn sh/sh
      select count(*) from sh.temp_sales s1, sh.temp_sales s2
      where rownum < 10000000;

     select statistic, last_query from v$pq_sesstat
          where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          6

– In 4th session:

SQL> conn sh/sh
      select count(*) from sh.temp_sales s1, sh.temp_sales s2
      where rownum < 10000000;

     select statistic, last_query from v$pq_sesstat
          where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
Server Threads                          0

SQL>select * from v$px_process_sysstat
      where statistic like '%Server%';

STATISTIC                           VALUE
 ------------------------------ ----------
 Servers In Use                         22
 Servers Available                       0
 Servers Started                        14
 Servers Shutdown                        0
 Servers Highwater                      22
 Servers Cleaned Up                      0
 Server Sessions                        68

The observations for PARALLEL_DAPTIVE_MULTI_USER = FALSE can be tabulated as follows:

PARALLEL_ADAPTIVE_MULTI_USER = FALSE
                                      |—- No. of slave processes assigned –|
No. of sessions        sess1         sess2         sess3      sess4     
———————-            ———        ——–        ———      ——–     

          1                                    8                   –                   –                 –

          2                                    8                   8                   –                 –

          3                                    8                   8                  6                 –

          4                                    8                   8                  6                 0              

  Hence, it can be seen that if same query is issued in all the sessions,
. for no.  of concurrent sessions  <= 2, each session gets requested no. of slave processes i.e. 8
. for no.  of concurrent sessions  =  3,
– first 2 sessions get 8 slave processes each
-Third session  gets 6 slave processes i.e DOP gets silently downgraded even requested no. of slaves can be provided
. for no.  of concurrent sessions > 3,
– first 2 sessions get 8 slave processes each
-Third session  gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
– The query in any subsequent session is executed serially i.e. it is silently downgraded.

 

Hence, when PARALLEL_ADAPTIVE_MULTI_USER = true, DOP of the a SQL statements is downgraded automatically  so that px slaves can be assigned to other SQL statements which might be issued in other sessions.

References:

Troubleshooting Oracle Performance by Christian Antognini

—————————————————————————————

Related Links :

Home

Automatic Degree Of Parallelism – Part – I
Automatic Degree Of Parallelism – Part – II

——————————————————————————————–

PARALLEL_MIN_PERCENT

The Oracle server maintains a “pool” of parallel slave processes available for parallel operations. The Database configuration parameters PARALLEL_MIN_SERVERS and PARALLEL_MAX_SERVERS determine the initial and maximum size of the pool.  The number of slave processes provided to a query coordinator may be less than requested by it, due to system load or other parallel processes which are using the parallel server pool. For example, if the maximum number of slave processes is set to 40 and an  execution plan requires eight slave processes, only 5 concurrent SQL statements (40/8) can be executed with the required degree of parallelism. When the limit is reached, there are two possibilities:

– either the degree of parallelism is downgraded (in other words, reduced) or

– an error (ORA-12827: insufficient parallel query slaves available) is returned to the server process.

To configure which one of these two possibilities is used, you have to set the initialization parameter parallel_min_percent. It can be set to an integer value ranging from 0 to 100. There are three main situations:

0: This value (which is the default) specifies that if requested no. of slave processes are not available,  the degree of parallelism of the statement can be silently downgraded.  If less than two slave processes are available, the statement might even execute serially. The error ORA-12827 is never raised .

1–99: The values ranging from 1 to 99 define a limit for the downgrade. If no. of available slave processes is less than the specified percentage of the requested slave processes , the error ORA-12827 is raised.
For example, if it is set to 25 and 16 slave processes are requested, at least 4 (16*25/100) must be provided to avoid the error.

100: With this value, If no. of available slave processes is less than the no. of  requested slave processes , the error ORA-12827 is raised.

Let’s demonstrate …

– Find out the value of parallel_max_servers

SQL> sho parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
parallel_max_servers                 integer     40

– Let’s  set parallel_min_servers to a value < parallel_max_servers i.e. 8

SQL> alter system set parallel_min_servers=8;

– Restart the database

SQL>shu immediate;
startup

– Initially only 8 px servers (= parallel_min_servers) are started

SQL>  select * from v$px_process_sysstat
           where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                         0
Servers Shutdown                        0
Servers Highwater                       0
Servers Cleaned Up                      0
Server Sessions                         0

– Initially all the statistics related to parallel execution show values
   as 0

SQL>select name, value from v$sysstat
where upper(name) like '%PARALLEL OPERATIONS%'
or upper(name) like '%PARALLELIZED%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
queries parallelized                                                      0
DML statements parallelized                                               0
DDL statements parallelized                                               0
DFO trees parallelized                                                    0
Parallel operations not downgraded                                        0
Parallel operations downgraded to serial                                  0
Parallel operations downgraded 75 to 99 pct                               0
Parallel operations downgraded 50 to 75 pct                               0
Parallel operations downgraded 25 to 50 pct                               0
Parallel operations downgraded 1 to 25 pct                                0

-—————- PARALLEL_MIN_PERCENT = 0 ————————

– Check that currently, parallel_min_percent is set to its default value of 0

SQL> sho parameter parallel_min_p

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent                 integer     0

– Let’s create a test table  –

SQL> drop table sh.temp_sales purge;
create table sh.temp_sales as select *    from sh.sales;

– Let’s modify dictionary DOP of temp_sales to a value > parallel_max_servers (40)

SQL>alter table sh.temp_sales parallel 50;

– Let’s execute a query on temp_sales

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/

– check that the query uses 40 px processes = parallel_max_servers
   i.e. DOP of the statement has been silently downgraded from requested value
   of 50 to 40 as parallel_min_percent= 0

SQL> select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                         40

– Note that 1 query has been parallelized so far and its parallelization
   operation has been downgraded (1 to 25) % (50 to 40 i.e. 20%)

SQL>select name, value from v$sysstat
where upper(name) like '%PARALLEL OPERATIONS%'
or upper(name) like '%PARALLELIZED%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
queries parallelized                                                      1
DML statements parallelized                                               0
DDL statements parallelized                                               0
DFO trees parallelized                                                    1
Parallel operations not downgraded                                        0
Parallel operations downgraded to serial                                  0
Parallel operations downgraded 75 to 99 pct                               0
Parallel operations downgraded 50 to 75 pct                               0
Parallel operations downgraded 25 to 50 pct                               0
Parallel operations downgraded 1 to 25 pct                                1

– check that 32 more  px servers were started in addition earlier 8 (servers started has increasesd    from 8 to 32)  and 40 of them are available (servers available = 40).    Servers highwater has been updated to 40

SQL>  select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      40
Servers Started                        32
Servers Shutdown                        0
Servers Highwater                      40
Servers Cleaned Up                      0
Server Sessions                        40

----------------- PARALLEL_MIN_PERCENT = 50 ------------------------

– Let’s set parallel_min_percent = 50

SQL> alter system set parallel_min_percent=50 scope=spfile;

shu immediate;
startup

SQL> sho parameter parallel_min_percent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent                 integer     50

– check that query executes with DOP = 40 as
   requested DOP * (parallel_min_percent/100) = 50 *(50/100) = 25
   and upto maximum of 40 servers  (parallel_max_servers)    are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/

select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

– Let’s change dictionary DOP of temp_sales to 82

SQL> alter table sh.temp_sales parallel 82;

– Let’s re execute the earlier query
– It can be seen that we get error message ORA-12827 as
   requested DOP * (parallel_min_percent/100) = 82 *(50/100) = 41
   but maximum of only 40 servers  (parallel_max_servers)   are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
begin
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves available
ORA-06512: at line 2

– Let’s change dictionary DOP of temp_sales to 80

SQL> alter table sh.temp_sales parallel 80;

— Let’s re execute the earlier query
— It can be seen that query executes with DOP of 40  as
requested DOP * (parallel_min_percent/100) = 80 *(50/100) = 40
and maximum of 40 servers  (parallel_max_servers)    are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

Thus PARALLEL_MIN_PERCENT = 1 to 99  ensures that
if specified percent of the requested px servers are available
        a SQL statement will be parallelized  
Else,
   ORA-12827 will be returned

-—————- PARALLEL_MIN_PERCENT = 100 ————————
– Let’s set parallel_min_percent to 100 so that query will be parallelized only if available px servers >= requested no. of px servers

SQL>alter system set parallel_min_percent=100 scope=spfile;
shu immediate;
startup;

– Currently  Dictionary DOP = 80, parallel_max_servers = 40

– Let’s execute the same query again

– Since maximum no. of available px servers (40) < requested no. (80), we get ORA-12827

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
begin
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves available
ORA-06512: at line 2

– Let’s change dictionary DOP of temp_sales to 40 (=parallel_max_servers) so that available servers = requested

SQL> alter table sh.temp_sales parallel 40;

– Let’s re execute the earlier query
– It can be seen that the query executes with DOP = 40
   requested DOP * (parallel_min_percent/100) = 40 *(100/100) = 40
   and upto maximum of 40 servers  (parallel_max_servers)    are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

References:

Troubleshooting Oracle Performance by Christian Antognini

—————————————————————————————

Related Links :

Home

Automatic Degree Of Parallelism – Part – I
Automatic Degree Of Parallelism – Part – II

——————————————————————————————–

 

PARALLEL_MIN_SERVERS

PARALLEL_MIN_SERVERS specifies the number of slave processes that are started at instance startup. These slave processes are always available and don’t need to be started when a server process requires them. The slave processes exceeding this minimum are dynamically started when required and, once returned to the pool, stay idle for five minutes. If they are not reused in that period, they are shut down. By default, this initialization parameter is set to 0. This means that no slave processes are created at startup. When some SQL statements need slave processes, they wait for the startup of the slave processes. The wait event related to this operation is os thread startup

Let’s demonstrate …

– Check that currently parallel_min_servers is set to its default value of 0

SQL> conn / as sysdba
sho parameter parallel_min_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers                 integer     0

– Let’s restart the database

SQL> shu immediate;
startup

– check that no px processes are started automatically    (Servers Started = 0)

SQL> set line 80
set tab off
col statistic for a30

select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       0
Servers Started                         0
Servers Shutdown                        0
Servers Highwater                       0
Servers Cleaned Up                      0
Server Sessions                             0

– Let’s check the maximum no. of servers that can be started –

SQL> sho parameter parallel_max_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     40

– Let’s try to set parallel_min_servers to a value > parallel_max_servers
— We immediately get an error message

SQL> alter system set parallel_min_servers=41;

alter system set parallel_min_servers=41
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-12811: PARALLEL_MIN_SERVERS must be less than or equal to
PARALLEL_MAX_SERVERS, 40

– Let’s  set parallel_min_servers to a value < parallel_max_servers i.e. 8

SQL> alter system set parallel_min_servers=8;

– Verify that 8 Px servers have been spawned (Servers Started =8)
   and all of them are available (Servers Available = 8)

SQL>  select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                         8
Servers Shutdown                        0
Servers Highwater                       0
Servers Cleaned Up                      0
Server Sessions                         0

– Let’s create a table with dictionary DOP = 4 –

SQL> drop table sh.temp_sales purge;
create table sh.temp_sales as select *    from sh.sales;

alter table sh.temp_sales parallel 4;

– Issue a query on temp_sales so that px servers are put to use

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/

– Check that the query used 4 Px servers

SQL> select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          4

- check that Server highwater has been modified to 4 as maximum of 4 servers have    been allocated by this instance yet

SQL>  select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                         8
Servers Shutdown                        0
Servers Highwater                       4
Servers Cleaned Up                      0
Server Sessions                         4

– Let’s modify dictionary DOP of temp_sales to 16 and issue the same query again

SQL>alter table sh.temp_sales parallel 16;

begin
execute immediate 'select * from sh.temp_sales';
end;
/

– check that the query uses 16 px processes

SQL> select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                         16

– check that 8 new px servers have been assigned (servers started has increasesd    from 8 to 16) and all of them are available (servers available = 16).
   Servers highwater has been updated to 16

SQL>  select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      16
Servers Started                        16
Servers Shutdown                        0
Servers Highwater                      16
Servers Cleaned Up                      0
Server Sessions                        20

– Let’s wait for 5 minutes to check  that px servers idle for 5 minutes are  shut down automatically

SQL> exec dbms_lock.sleep(300);

– check that 8 px servers have been shutdown automatically after 5 minutes
   (Servers Shutdown = 8) as they were idle for that time. Now available
   servers are back to 8)

SQL> select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                        16
Servers Shutdown                        8
Servers Highwater                      16
Servers Cleaned Up                      0
Server Sessions                        20

– Let’s change the dictionary DOP of temp_sales back to 4

SQL>alter table sh.temp_sales parallel 4;

– Let’s run the earlier query repeatedly in another session and while the query is
   still running, issue the query in the next step

SQL> begin
for i in 1..1000 loop
execute immediate 'select * from sh.temp_sales';
end loop;
end;
/

– It can be seen that 4 servers are in use in another session (Servers in use=4)
   and only 4 servers are available now

SQL> select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          4
Servers Available                       4
Servers Started                        16
Servers Shutdown                        8
Servers Highwater                      16
Servers Cleaned Up                      0
Server Sessions                      4420

– Let’s check again the status of px servers after the query has completed
   in the other session
– It can be seen that all the 8 servers are available again now

SQL> select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                        16
Servers Shutdown                        8
Servers Highwater                      16
Servers Cleaned Up                      0
Server Sessions                      8020

Reference :

Troubleshooting Oracle Performance by Christian Antognini

—————————————————————————————-

Related Links :

Home

————————————————————————————————

UNDO AND REDO IN ORACLE

In this post, I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reasons for duplicating the storage of undo.

First, let’s demonstrate that

– oracle redo contains undo
– Checkpointing causes
. dirty blocks to be written to datafiles
. buffers containing undo to be written to undo tablespace

Overview:

– Create a tablespace test_undo
– Create a table test_undo_tab in tablespace test_undo
– Insert two rows with txt – teststring1, teststring2 in test_undo_tab and perform manual checkpoint
– Switch logs so that new log does not contain string teststring1
– Switch undo tablespace to undotbs2
– Update the column value from teststring1 to teststring_uncommitted and do not commit
– In another session, update the column value from teststring2 to
teststring_committed and commit
– check that both new and old (undo) values have been written to current redo log
– check that undo tablespace does not contain pre update values i.e.
teststring1 and teststring2 as undo information is still in buffer cache and checkpoint has not taken place yet
– Perform manual checkpoint so that buffers containing undo information are flushed to disk
– check that undo tablespace contains pre update values i.e. teststring1 and teststring2

Implementation:

– Find out name of the current undo tablespace –

sql>sho parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- 

------------------------------
undo_tablespace                      string      UNDOTBS1

– create an undo tablespace undotbs2

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/undotbs2.dbf' 

size 100m;

– Create a tablespace test_undo

SQL> drop tablespace test_undo including contents and datafiles;
     create tablespace test_undo datafile '/u01/app/oracle/test_undo.dbf' size 128k;

– Create a test table test_undo_tab in the tablespace created above

SQL> drop table test_undo_tab purge;
     create table test_undo_tab(txt char(1000)) tablespace test_undo;

– Insert two rows in test_undo_tab and commit –

SQL> insert into test_undo_tab values ('teststring1');
     insert into test_undo_tab values ('teststring2');
     commit;

– Perform manual checkpoint so that above changes are written to the datafile

SQL>alter system checkpoint;

- Set undotbs2 as current undo_tablespace

SQL>alter system set undo_tablespace = undotbs2;
    sho parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- 

------------------------------
undo_tablespace                      string      UNDOTBS2

– Switch logs so that the current log does not contain string teststring

SQL>alter system switch logfile;

– Find out name of current redo log

SQL> col member for a30
     select member, l.status from v$log l, v$logfile f where l.group# = f.group# and l.status = 'CURRENT';

MEMBER                         STATUS
------------------------------ ----------------
/u01/app/oracle/redo04.log     CURRENT

– In one session Update one row and do not commit –

SQL>  update test_undo_tab set txt = 'teststring_uncommitted'
      where txt = 'teststring1';

– In another session, Update the second row and commit –

SQL>  update test_undo_tab set txt = 'teststring_committed'
      where txt = 'teststring2';
      commit;

– Commit and then check that redo log contains both redo and undo for both the DML’s (committed and uncommitted)–

[oracle@node1 oracle]$ strings /u01/app/oracle/redo04.log|grep teststring

teststring_uncommitted                                                                                  
teststring1                                                          

teststring_committed                                                 

teststring2

– check that datafile does not contain updated values i.e. teststring_committed and teststring_uncommitted as checkpointing has not taken place yet and dirty buffers have not been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_undo.dbf|grep teststring

teststring2                                                                                                                              ,
teststring1

– check that undo tablespace does not contain pre update value i.e. teststring1 and teststring2 as undo information is in buffer cache and checkpointing has not taken place yet

[oracle@node1 oracle]$ strings /u01/app/oracle/undotbs2.dbf|grep teststring

– Perform manual checkpoint so that buffers containing undo information are flushed to disk

SQL> alter system checkpoint;

– check that datafile does contains updated values (both committed and uncommitted) i.e. teststring_committed and teststring_uncommitted as checkpointing has taken place and dirty buffers have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_undo.dbf|grep teststring

teststring_committed                                                                                                               ,
teststring_uncommitted

– check that undo tablespace contains pre update values i.e. teststring1 and teststring2

[oracle@node1 oracle]$ strings /u01/app/oracle/undotbs2.dbf| grep teststring

teststring2                                                                  
teststring1

– Cleanup –

SQL>drop tablespace test_undo including contents and datafiles;
    alter system set undo_tablespace=undotbs1;
    drop tablespace undotbs2 including contents and datfiles;

Thus, it can be concluded that :

– Log writer writes both undo and redo for every DML in a transaction (committed/uncommitted) irrespective of whether checkpoint has taken place or not.
– On checkpoint,
. Undo present in buffers in database buffer cache is written to undo tablespace .
. Dirty buffers containing committed/uncommitted data are written to datafiles.

It implies that in a running database,

– datafiles may contain uncommitted data (buffers dirtied by uncommitted transaction and checkpoint takes place)

– committed data may not be there in datafiles (checkpointing not done after commit)

– redo/undo for committed changes will always be there in redo logs (Lgwr writes on commit).

– redo/undo for Uncommitted changes may be there in redo logs(Lgwr writes every 3 seconds)

– In addition to redo logs, undo information

  • . may be present in buffer cache (checkpoint has not taken place after change)
  • . will be written to undo tablespace on checkpoint
  • . will never be overwritten until a transaction is active

 

Now the question arises why undo information is being stored both in undo tablespace and redo logs.

Let’s see what will happen if undo is stored in redo logs only.

A redo log can be reused once changes protected by it have been written to datafiles (and archivelogs if database is in archivelog mode).

It implies that if I make a change and do not commit it
– Change is written to a redo log
– checkpoint takes place
– uncommitted change is written to datafile
– I decide to rollback the change
– If redo log has not been overwritten
. search entire redo log for the undo and then rollback
else (redo log has been overwritten)
. undo information is not available for rollback.

One might argue that if somehow a redo log is not allowed to be overwritten until it contains active undo, we might be able to manage with undo stored in redo logs only. This solution is not feasible as
– size of redo logs will grow enormously large very soon as thet contain both undo and redo (a user might decide not to end a transaction for months)
– to rollback a change, enormous amount of data in redo logs (both redo and undo) will have to be searched leading to degraded performance
– there will be contention on redo logs as they are being used for both
. writing redo and undo
. reading to rollback a change

Hence, undo information has to be stored separately from redo and is used for rolling back uncommited transactions . The undo stored in undo buffers/undo tablespace is additionally used for
– read consistency
– flashback query
– flashback version query

Now, let’s see what will happen if undo is stored in undo buffers and undo tablespace only.

SCENARIO – I

– I make a change and do not commit
– The redo for the change is present in redo logs
– The undo for the change is present in buffer cache
– The undo for the change is not present in undo tablespace as checkpoint has not taken place
– The instance crashes
– undo information present in buffer cache is wiped out
– As part of instance recovery, redo is applied and datafiles contain uncommitted data
– The database cannot be opened as undo information needed to rollback uncommitted change is not available leading to an inconsistent database.

SCENARIO – II

– I take hot backup of a tablespace.
– The datafile for the tablespace is lost or corrupted.
– I take the tablespace offline and restore the datafile from backup.
– I recover the datafile
– The redo logs and archivelogs contain redo data for both committed and uncommitted transactions
– The redo logs and archivelogs do not contain undo data (as per our
assumption).
– As part of recovery, redo for all the committed/uncommitted changes is read from online/archive redo logs and applied
– The tablespace cannot be brought online as the undo information needed to rollback uncommitted changes is not available

Hence, to resolve above problems, undo is stored in redo logs also. During roll forward phase of instance/media recovery, as changes (redo in redo logs) are applied to datafiles, undo stored in redo logs is used to generate undo segments. These segments are subsequently used to rollback uncommitted changes during rollback phase of instance/media recovery.

References:

Expert Oracle Database Architecture by Tom Kyte

http://oraclenz.wordpress.com/2008/06/22/differences-between-undo-and-redo/
https://forums.oracle.com/forums/thread.jspa?threadID=1016183&start=0&tstart=0
https://forums.oracle.com/forums/thread.jspa?threadID=672861

https://jonathanlewis.wordpress.com/2015/04/20/manuals-2/
Does Select Statement Generate Redo?
Hot Backup Mode Causes Excessive Redo
https://www.dbametrix.com/download/Redo-Log-In-depth.pdf
Delayed Block Cleanout In Oracle

—————————————————————————————-

Related links:

Home

Database Index

Consistent Reads In Oracle : Part-I
Consistent Reads In Oracle : Part-II
Flush Buffer Cache
Oracle Checkpoints
Uncommitted Data In Datafiles

 

—————————————————————————————-