Oracle 11gR1 enhancements to the initialization parameters management

Oracle Database 11.1 now upon startup writes values of initialization parameters to the alert log in such a way that when you loose your SPFILE or PFILE it is now easier to copy and paste them to create a new PFILE.

Also the SPFILE or PFILE that was used to open the instance is now mentioned with an additional line.


..
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletong.ora
System parameters with non-default values:
  processes                = 150
  shared_pool_size         = 144M
  streams_pool_size        = 16M
  control_files            = "/u01/app/oracle/oradata/tong/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/tong/control02.ctl"
  control_files            = "/u01/app/oracle/oradata/tong/control03.ctl"
  db_block_size            = 8192
  db_cache_size            = 220M
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 8G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=tongXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/tong/adump"
  audit_trail              = "DB"
  db_name                  = "tong"
  open_cursors             = 300
  star_transformation_enabled= "TRUE"
  pga_aggregate_target     = 192M
  diagnostic_dest          = "/u01/app/oracle"
  _trace_files_public      = TRUE
Sat Sep 13 11:13:50 2008
PMON started with pid=2, OS id=3199 
..

..
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
  processes                = 250
  sessions                 = 280
  __shared_pool_size       = 222298112
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  nls_language             = TURKISH
  nls_territory            = TURKEY
  sga_target               = 285212672
  control_files            = /oracle/oradata/tong/control01.ctl, /oracle/oradata/tong/control02.ctl, /oracle/oradata/tong/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 50331648
  compatible               = 10.2.0.3.0
  db_file_multiblock_read_count= 32
  db_recovery_file_dest    = /home/oracle/flash_recovery_area
  db_recovery_file_dest_size= 8589934592
  fast_start_mttr_target   = 180
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=tongXDB)
  job_queue_processes      = 10
  background_dump_dest     = /oracle/admin/tong/bdump
  user_dump_dest           = /oracle/admin/tong/udump
  core_dump_dest           = /oracle/admin/tong/cdump
  audit_file_dest          = /oracle/admin/tong/adump
  db_name                  = tong
  open_cursors             = 300
  star_transformation_enabled= TRUE
  pga_aggregate_target     = 94371840
PMON started with pid=2, OS id=5423
..

Also now you can create a pfile or spfile from the active instance’s current values of the initialization parameters.


CREATE PFILE FROM MEMORY;

CREATE SPFILE FROM MEMORY;

This feature gives an opportunity to compare the parameters used at that moment with the last startup ones.

Top Ten Mistakes Found in Oracle Systems – Bad connection management –

In the documentation this is mentioned as: The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. Additionally, simultaneous connections from the same client are also a waste of system and network resources.

Until now I have never experienced a performance problem example similar to this case, but last week one my friends asked me a question and yes as mentioned above I saw how it can make a huge difference. He was doing nearly 20-30 SQL calls over JDBC without connection pooling for each of the business actions, and I advised him to package these calls inside a database function for each business action and call these PL/SQL packages as Callable Statements.

Below is a simple example I tried to set up in order to demonstrate this kind of a case: in test1 we will do 1000 times DUAL calls inside a PL/SQL block where as we will open a connection for each of 1000 DUAL calls in test2.


[oracle@tonghost tmp]$ date ; ./test1.ksh ; date ;
Sat Sep 13 11:40:18 EEST 2008
Sat Sep 13 11:40:19 EEST 2008
[oracle@tonghost tmp]$ cat test1.log

Session altered.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42

in test1 1000 DUAL calls is done under a second inside the database,


[oracle@tonghost tmp]$ date ; ./test2.ksh ; date ;
Sat Sep 13 11:50:29 EEST 2008
Sat Sep 13 11:52:27 EEST 2008
[oracle@tonghost tmp]$ cat test2.log

Session altered.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.00

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

where as in test2 it takes nearly two minutes to do the same task.

Above tests were done on a OEL5 11.1 instance: Bad Connection Management Cost Demo Source Codes

11.1 Flashback Database Test and Flashback Tablespace Off Options Drawback

Oracle’s flashback features are one of the most loved, read posts on my blog. The three studies I have done before were;

Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management

Oracle 10g flashback features’ limitations and restrictions

Flashback database and NOLOGGING operations problem demo

Today I tested flashback database feature on 11.1 and my experiences were as following;


connect / as sysdba

create smallfile tablespace flashtbs
datafile 'flashtbs1.dbf' size 500K
logging
extent management local
segment space management auto;

create user flash identified by flash
default tablespace flashtbs
temporary tablespace temp;

grant connect, resource, dba to flash;

-- check if database is in archivelog mode
archive log list

-- check if flashback option is enabled
select flashback_on from v$database;

-- check for the flashback log files
host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback

connect flash/flash

create table emp
tablespace flashtbs
as select * from hr.employees;

select sum(salary) from emp;

SUM(SALARY)
-----------
     691400

-- note as scn2
select current_scn from v$database;

CURRENT_SCN
-----------
    1713454

select undoblks from v$undostat;

  UNDOBLKS
----------
        49
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                338558976

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08        4726784    1294336    1688064                        0

begin
 for i in 1..10000 loop
   update emp set salary=salary+1;
 end loop;
 commit;
end;
/

select undoblks from v$undostat;

  UNDOBLKS
----------
      6177
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                378716160

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08       55263232    3080192  105243648                        0

-- check for new flashback log files
host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback

[ 14 new 3989504 sized flb files ]

begin
 for i in 1..10000 loop
   update emp set salary=salary+1;
 end loop;
 commit;
end;
/

select undoblks from v$undostat;

  UNDOBLKS
----------
     12328
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                417447936

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08      105086976    4974310  210600960                        0

host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback

[ again 14 new 3989504 sized flb files ]

Undo and Redo stats are very similar for both runs, as expected.

V$UNDOSTAT.UNDOBLKS
1st) 6177 – 49 = 6128
2nd) 12328 – 6177 = 6151

V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE
1st) 378716160 – 338558976 = 40157184
2nd) 417447936 – 378716160 = 38731776

V$FLASHBACK_DATABASE_STAT.FLASHBACK_DATA
1st) 55263232 – 4726784 = 50536448
2nd) 105086976 – 55263232 = 49823744

V$FLASHBACK_DATABASE_STAT.REDO_DATA
1st) 105243648 – 1688064 = 103555584
2nd) 210600960 – 105243648 = 105357312

V$FLASHBACK_DATABASE_STAT.DB_DATA
1st) 3080192 – 1294336 = 1785856
2nd) 4974310 – 3080192 = 1894118

Now lets repeat the test with a tablespace which does not use flashback option.


connect / as sysdba

create smallfile tablespace flashtbs2
datafile 'flashtbs2.dbf' size 500K
logging
extent management local
segment space management auto;

alter tablespace flashtbs2 flashback off;

select name, flashback_on from v$tablespace;

NAME                           FLA
------------------------------ ---
..
FLASHTBS                       YES
FLASHTBS2                      NO
..

connect flash/flash

create table emp2
tablespace flashtbs2
as select * from hr.employees;

select undoblks from v$undostat;

  UNDOBLKS
----------
         6
        11
         0
     12334
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                414031872

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08      106602496  107986944  211301888                        0

begin
 for i in 1..10000 loop
   update emp2 set salary=salary+1;
 end loop;
 commit;
end;
/

select undoblks from v$undostat;

  UNDOBLKS
----------
      6138
        11
         0
     12334
        42

select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1664754 31-MAY-08             1440      516628480                453746688

select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
04-SEP-08 04-SEP-08      157245440  113696768  314953216                        0

[ this time just 5 new 3989504 sized flb files are created ]

Other than the flashback log files undo and Redo stats are again very similar for the third run as well, interesting.

V$UNDOSTAT.UNDOBLKS
3rd) 6138 – 6 = 6132

V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE
3rd) 453746688 – 414031872 = 39714816

V$FLASHBACK_DATABASE_STAT.FLASHBACK_DATA
3rd) 157245440 – 106602496 = 50642944

V$FLASHBACK_DATABASE_STAT.REDO_DATA
3rd) 314953216 – 211301888 = 103651328

but this time DB_DATA stats has a significant increase,

V$FLASHBACK_DATABASE_STAT.DB_DATA
3rd) 113696768 – 107986944 = 5709824

Now lets try to flashback the database with a tablespace which this feature is disabled.


select sum(salary) from emp;

SUM(SALARY)
-----------
    2831400

-- note as scn1
select current_scn from v$database;

CURRENT_SCN
-----------
    1775827

-- temporary action
insert into emp select * from emp;

commit;

select sum(salary) from emp;

SUM(SALARY)
-----------
    5662800

connect / as sysdba

shutdown immediate;

startup mount;

-- flashback to scn1 
flashback database to scn 1775827 ;

*
ERROR at line 1:
ORA-38753: Cannot flashback data file 7; no flashback log data.
ORA-01110: data file 7: '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf'

-- in order to flashback we need to get rid of the datafiles which have not this feature enabled
alter database
datafile '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf' offline ;

-- try again flashback to scn1
flashback database to scn 1775827;

alter database open read only;

select sum(salary) from flash.emp;

SUM(SALARY)
-----------
    2831400

shutdown immediate;

startup mount;

alter database
datafile '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf' offline ;

-- flashback to scn2
flashback database to scn 1713454;

alter database open resetlogs;

select sum(salary) from flash.emp;

SUM(SALARY)
-----------
     691400

So the bottom line is if you think you will be using any flashback options be careful and get prepared about their limitations(mentioned in my previous post linked above), for this case flashback disabled tablespace was the drawback and workaround was to offline it(of course if this is acceptable).

ps: If you want to clean up the mess;


shutdown immediate;

startup 

drop user flash cascade;

drop tablespace flashtbs including contents and datafiles;

-- this one is not needed because of the second flashback database
-- drop tablespace flashtbs2 including contents and datafiles;

MAX_RUN_DURATION attribute of the SCHEDULER to setup a sniper job

Many thanks to Ravi R. of OTN Scheduler Forum for the idea, below is the code for a general purpose sniper job that snipes any job that has exceeded its maximum duration.


--
-- this test was done on 11.1 EE
--
-- ensure that the values of are both non-NULL and sufficiently high for your need
select value 
  from v$parameter 
 where name='job_queue_processes';

VALUE
-----
1000

-- if needed dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 10); can be used to set 
select value 
  from all_scheduler_global_attribute
 where ATTRIBUTE_NAME= 'MAX_JOB_SLAVE_PROCESSES';

VALUE
-----
10 

-- create a table for output
create table job_output (a timestamp with time zone, b varchar2(1000));

-- add an event queue subscriber for this user's messages
exec dbms_scheduler.add_event_queue_subscriber('myagent')

-- create a sniper procedure
create or replace procedure sniper_proc
    (message IN sys.scheduler$_event_info) as
begin

    -- if this is not a JOB_OVER_MAX_DUR message, error out
    if message.event_type != 'JOB_OVER_MAX_DUR' then
      raise PROGRAM_ERROR;
    end if;

    -- stop the job
    dbms_scheduler.stop_job('"'||message.object_owner||'"."'||
      message.object_name ||'"');

    -- insert into job output
    insert into job_output values (systimestamp,
     'sniper job sniped '||'"'||message.object_owner||'"."'||
      message.object_name ||'"');
end;
/

-- create a sniper program
begin
    dbms_scheduler.create_program (
        program_name => 'sniper_prog',
        program_action=> 'sniper_proc',
        program_type => 'stored_procedure',
        number_of_arguments => 1,
        enabled => FALSE) ;

    dbms_scheduler.define_metadata_argument ( 'sniper_prog','event_message',1); 
    dbms_scheduler.enable('sniper_prog');
end;
/

-- create a general purpose sniper job to kill any job that has
-- exceeded its max_run_duration
begin
    dbms_scheduler.create_job('sniper_job',
      program_name=>'sniper_prog',
      event_condition =>
        'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
      queue_spec =>'sys.scheduler$_event_queue,myagent',
      enabled=>true);
end;
/

-- create two jobs to test the sniper job
begin
    dbms_scheduler.create_job
      ( 'first_job', job_action =>
          'insert into job_output values(systimestamp, ''first job begins'');
           commit; dbms_lock.sleep(120);
           insert into job_output values(systimestamp, ''first job ends'');',
        job_type => 'plsql_block',
        enabled => false ) ;
    dbms_scheduler.set_attribute
      ( 'first_job' , 'max_run_duration' , interval '60' second);

    dbms_scheduler.create_job
      ( 'second_job', job_action =>
          'insert into job_output values(systimestamp, ''second job begins'');
          commit; dbms_lock.sleep(120);
          insert into job_output values(systimestamp, ''second job ends'');',
        job_type => 'plsql_block',
      enabled => false ) ;
    dbms_scheduler.set_attribute
      ( 'second_job' , 'max_run_duration' , interval '60' second);

    dbms_scheduler.enable('first_job');
    dbms_lock.sleep(10);
    dbms_scheduler.enable('second_job');
end;
/

SELECT * FROM job_output ORDER BY a ;

A                                                 B
------------------------------------------------- --------------------------------------------------------------------------------
03/09/2008 11:45:29,585914 +03:00                 first job begins
03/09/2008 11:45:40,019922 +03:00                 second job begins
03/09/2008 11:46:34,226044 +03:00                 sniper job sniped "SH"."FIRST_JOB"
03/09/2008 11:46:44,344798 +03:00                 sniper job sniped "SH"."SECOND_JOB"

One limitation of this is that it only works for jobs in the same schema as the sniper job.

Also there is a similar code example of a job e-mail notification package on the OTN Scheduler page, here.

On Extended Transaction Monitoring Statistics After 10g

After 10g when a long-running transaction is rolling back and if the process takes more than six seconds this event is recorded in the view V$SESSION_LONGOPS.


-- to estimate when the monitored rollback process will finish
SELECT TIME_REMAINING, SOFAR / TOTALWORK * 100 PCT
  FROM V$SESSION_LONGOPS
 WHERE SID = :sid
   AND OPNAME = 'Transaction Rollback' ;

Also after 10g addition to the user issued rollback statements, transactions recovered by SMON and parallel instance recovery sessions are monitored. You can view historical information about transaction recovery and transaction rollback and you can calculate average rollback duration to estimate better transaction recovery time and set the FAST_START_PARALLEL_ROLLBACK initialization parameter more appropriately to optimize system performance. Historical information is kept in V$FAST_START_TRANSACTIONS until the next instance shutdown.

V$FAST_START_TRANSACTIONS contains information both for transactions that the Oracle server is recovering(the STATE is RECOVERING) and for transactions that the Oracle server has recovered(the STATE is RECOVERED). New columns are added to this view as;

• XID: Transaction ID of this transaction
• PXID: Transaction ID of the parent transaction
• RCVSERVERS: Number of servers working on this transaction including the coordinator server(It can be 1 if only SMON is doing the recovery).

For example this statement can be used to track transaction recovery after instance startup;


SELECT state, undoblocksdone, undoblockstotal, cputime
  FROM v$fast_start_transactions;

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERING 574 1945 16

SQL> /

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERING 1300 1945 34

SQL> /

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERED 1945 1945 65

SQL> SELECT message FROM v$session_longops;

MESSAGE
---------------------------------------------
Transaction Rollback: xid:0x0001.00a.00000812 : 1945 out of 1945 Blocks done

For more reading on this topic please visit the article Oracle Database 10g: The Top 20 Features for DBAs by Arup Nanda on OTN; Week 2 – How Much Longer?: Rollback Monitoring