Monthly Archives: May 2013

IDENTIFY THE DATABASE OWNING A CPU INTENSIVE PROCESS

On a linux/Unix server with multiple database instances running , if one of the databases has a process which is CPU intensive, we want to  identify the database which is the owner of  that process.

Currently, I have two databases – AMER and EURO running on the same server.
To simulate the environment, I needed to run a process in a database which consumes a lot of CPU. Since hash joins consume CPU, I wrote the following query and checked its explain plan

SQL> explain plan for
sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id;

select * from table(dbms_xplan.display);<

PLAN_TABLE_OUTPUT
————————————————————————————-
Plan hash value: 864676608

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 665 | 36575 | 409 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 665 | 36575 | 409 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| ORDERS | 105 | 3885 | 204 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ORDER_ITEMS | 665 | 11970 | 204 (0)| 00:00:01 |
———————————————————————

The explain clearly shows that hash join will be used. I executed this query repeatedly against EURO database using the following script :

SQL>conn / as sysdba

begin
for i in 1..100000000 loop
execute immediate 'sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id';
end loop;
end;
/

In another terminal, I issued “top” linux command to find out top CPU consuming processes. This is the output I got

 [root@node1 ~]# top

top – 21:54:39 up 2 days, 22:08, 6 users, load average: 4.75, 4.29, 4.16
Tasks: 244 total, 4 running, 240 sleeping, 0 stopped, 0 zombie
Cpu(s): 44.6%us, 44.9%sy, 0.0%ni, 0.0%id, 0.0%wa, 9.8%hi, 0.7%si, 0.0%st
Mem: 1667320k total, 1654112k used, 13208k free, 130188k buffers
Swap: 8193140k total, 643728k used, 7549412k free, 1133656k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14305 oracle 20 0 632m 66m 55m R 26.1 4.1 0:03.25 oracle
14665 root 20 0 22772 1548 1216 R 23.8 0.1 0:42.47 perl
21119 oracle 20 0 194m 3152 1500 D 15.2 0.2 164:15.69 mgr
142 root 20 0 0 0 0 R 13.5 0.0 132:57.93 kjournald
19272 oracle 20 0 166m 2292 1700 S 6.3 0.1 52:30.99 extract
3685 root 20 0 5924 544 488 D 5.6 0.0 52:14.51 syslogd

Here, It can be seen that the process with PID 14305 owned by oracle user is consuming most of the CPU.
But we don’t know which database does this process belong to. Let’s try to see the status of process with PID 14305

#[root@node1 ~]# ps -p 14305 -f

UID PID PPID C STIME TTY TIME CMD
oracle 14305 9918 2 21:44 ? 00:00:18 oracleeuro (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

It can be seen that process is running against euro database. Since local connection was made to the database, LOCAL= YES is present in the connection string.

— Now I will run the same script after making remote connection to euro database.

SQL>conn sys/oracle@euro as sysdba

begin
for i in 1..100000000 loop
execute immediate 'sELECT /*+ USE_HASH(l h) */ * FROM oe.orders h,oe.order_items l WHERE l.order_id = h.order_id';
end loop;
end;
/

In another terminal, I issued “top” linux command to find out top CPU consuming processes.

[root@node1 ~]# top

This is the output I got :
top – 22:35:47 up 2 days, 22:49, 6 users, load average: 4.24, 3.16, 3.34
Tasks: 236 total, 4 running, 232 sleeping, 0 stopped, 0 zombie
Cpu(s): 34.4%us, 46.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 17.5%hi, 2.1%si, 0.0%st
Mem: 1667320k total, 1650820k used, 16500k free, 132964k buffers
Swap: 8193140k total, 604000k used, 7589140k free, 1128896k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16789 oracle 20 0 626m 19m 17m R 32.6 1.2 0:39.00 oracle
21119 oracle 20 0 194m 3152 1500 R 23.4 0.2 172:30.19 mgr
142 root 20 0 0 0 0 R 21.3 0.0 140:19.24 kjournald
3685 root 20 0 5924 544 488 D 8.6 0.0 55:11.53 syslogd
19272 oracle 20 0 166m 2292 1700 S 8.3 0.1 55:32.88 extract
4704 root 20 0 109m 10m 3720 S 1.5 0.6 3:54.93 Xorg
7503 oracle 20 0 284m 11m 4432 S 1.2 0.7 1:09.44 gnome-terminal
4909 oracle 20 0 268m 2628 2328 S 0.6 0.2 15:52.64 ocssd.bin
4535 root 19 -1 112m 608 520 S 0.3 0.0 0:23.08 modclusterd

[root@node1 ~]# ps -p 16789 -f

UID PID PPID C STIME TTY TIME CMD
oracle 16789 1 33 22:33 ? 00:00:54 oracleeuro (LOCAL=NO)

Here again, It can be seen that process is running against euro database. Since remote connection was made, LOCAL=NO can be seen in the connection string

I hope this post was useful. Your comments and sugegstions are always welcome!

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

Home

Tuning Index

 

——————

ASM DISK MAPPING TO LINUX PARTITION

How to find which asm disk maps to which linux partition

- Issue following command to list all asm disks

# ls -ls /dev/oracleasm/disks/ASMDISK*

0 brw-rw—- 1 grid asmadmin 8, 17 Apr 12 18:43 /dev/oracleasm/disks/ASMDISK01
0 brw-rw—- 1 grid asmadmin 8, 18 Apr 12 18:43 /dev/oracleasm/disks/ASMDISK02

2 columns after group (asmadmin) refer to major/minor
e.g. for ASMDISK01 major = 8, minor = 17

– see the contents of /proc/partitions to find which partition is mapped to which major/minor

# cat /proc/partitions

major minor #blocks name

8 0 83886080 sda
8 1 104391 sda1
8 2 5116702 sda2
8 3 5116702 sda3
8 4 1 sda4
8 5 73545538 sda5
8 16 93487104 sdb
8 17 4008186 sdb1
8 18 4008217 sdb2
8 19 4008217 sdb3
8 20 1 sdb4

8 29 4008186 sdb13
8 30 4008186 sdb14
8 31 4008186 sdb15

Here sdb1 is mapped to major 8 and minor 17

Hence, partition /dev/sdb1 is mapped to ASMDISK01.

 

I hope this post was useful!

References:

http://mdinh.wordpress.com/2013/11/24/playing-with-asm-online-migration/
http://wadhahdaouehi.tn/2016/02/list-of-linux-physical-devices-mapped-by-asm-disks-using-major-minor-number/
————————————————–

Related links:

Home

11G R2 RAC Index

                                            ———————-

LOCAL ARCHIVE WITH NFS

 

In this post, I will demonstrate the use of NFS to perform recovery when archivelogs are stored locally by each instance .

In a cluster database we can store archivelogs on
– shared location e.g. ASM OR
– local archive log destination for each instance

If archivelogs are locally stored and an instance is trying to perform recovery, it will need logs from all the instances. Hence, it is recommended that local archive log destinations be created for each instance with NFS-read mount points to all other instances. This is known as the local archive with network file system (NFS) scheme. During recovery, one instance may access the logs from any host without having to first copy them to the local destination.

– OVERVIEW –

– Enable local archiving on each of 3 nodes in the cluster
– Take example tablespace offline in immediate mode so that it will need recovery when it is brought online.
– Trigger a manual checkpoint.
– Switch logs on all the 3 nodes to generate archivelogs
– Try to bring the example tablespace online – will fail as it needs media recovery
– Try to recover example tablespace – fails as archivelogs from other instance are inaccessible.
– Mount the archivelogs from other nodes using NFS
– Try to recover example tablespace – succeeds as archivelogs from other instance are accessible.
– Try to bring the example tablespace online – will succeed as it has been recovered.

– IMPLEMENTATION –

– create folders to store archived logs on 3 nodes –

host01$mkdir /home/oracle/archive1
host02$mkdir /home/oracle/archive2
host03$mkdir /home/oracle/archive3

— Login to the database from any of the existing nodes (host01, host02). Say host01
– set the archive log destinations of three instances  to the folders created above.

SQL>set sqlprompt ORCL1>

ORCL1>sho parmeter db_recovery

      alter system set log_archive_dest_1 = ‘location=/home/oracle/archive1′ scope=both    sid=’orcl1′;
      alter system set log_archive_dest_1 = ‘location=/home/oracle/archive2′ scope=both      sid=’orcl2′;
      alter system set log_archive_dest_1 = ‘location=/home/oracle/archive3′ scope=both     sid=’orcl3′;

ORCL1>archive log list;

– Put the database in archivelog mode if not already

host01$srvctl stop database -d orcl
                srvctl start instance -d orcl -i orcl1 -o mount

ORCL1>alter database archivelog;
      archive log list;
      shu immediate;

host01$srvctl start database -d orcl
                  srvctl status database -d orcl

— On node1 , switch logs and verify that archive logs are generated in specified location –

ORCL1>archive log list;
      alter system switch logfile;
      /
      /
      select name from v$archived_log;


      ho ls /home/oracle/archive1/

— On node2 , switch logs and verify that archive logs are generated in specified location –

SQL>set sqlprompt ORCL2>


ORCL2>archive log list;
      alter system switch logfile;
      /
      /
      select name from v$archived_log;


      ho ls /home/oracle/archive2/

— On node3, switch logs and verify that archive logs are generated in specified location –

SQL>set sqlprompt ORCL3>

ORCL3>archive log list;
      alter system switch logfile;
      /
      /
      select name from v$archived_log;

      ho ls /home/oracle/archive3/

– Take example tablespace offline immediate
–  Trigger a checkpoint
—   Switch logs on all the 3 instances

ORCL1>alter tablespace example offline immediate;
      alter system checkpoint;
      alter system switch logfile;
      /
      /
      select name from v$archived_log;

      ho ls /home/oracle/archive1/

ORCL2>alter system switch logfile;
      /
      /

      select name from v$archived_log;

      ho ls /home/oracle/archive2/

ORCL2>alter system switch logfile;
      /
      /
      select name from v$archived_log;

      ho ls /home/oracle/archive3/

– Try to bring example tablespace online – Needs media recovery

ORCL1>alter tablespace example online;

– Try to recover example tablespace from node1 – fails as local archived logs from node2 and node3 are not accessible;

host01RMAN>recover tablespace example;

– Make the folders containing archived logs on node2 and node3 sharable and start portmap and nfs service

— node2 —
– Add following line to /etc/exports

/home/oracle/archive2 *(rw,sync)

host02#service portmap restart
       service nfs restart

— node3 —
– Add following line to /etc/exports

/home/oracle/archive3 *(rw,sync)

– start portmap and nfs service on node2

host02#service portmap restart
                 service nfs restart

– on node1, create folders where archivelog folders from node2 and node3 will be mounted

host01$mkdir /home/oracle/archive2
                 mkdir /home/oracle/archive3

– As root user on node1,
   – start portmap and nfs service
   – Mount the archive folders on node2 and node3

host01#service portmap restart
                  service nfs restart

host01#mount host02:/home/oracle/archive2 /home/oracle/archive2
                 mount host03:/home/oracle/archive2 /home/oracle/archive3

– check that archivelogs on node2 and node3 are accessible on node1 –

host01$ls /home/oracle/archive2
host01$ls /home/oracle/archive3

– Try to recover example tablespace from node1 – succeeds as local archived logs from node2 and node3 are accessible;

host01RMAN>recover tablespace example;

ORCL1>alter tablespace example online;

——————————————————————————————-
Related links:
HOME

11g R2 RAC : Add Instance Manually
11g R2 RAC : Autolocate Backups
11g  R2 RAC : Clone Database Home
11g  R2 RAC : NIC Bonding

 

————

CONSISTENT READS IN ORACLE : PART- II

In my earlier post consistent reads in oracle part-I I had demonstrated that no. of CR clones of a block are dictated by the undocumented parameter _db_block_max_cr_dba. I had also mentioned that this is a soft limit in the sense that CR clones more than _db_block_max_cr_dba may also be created.

Let us see a practical demonstration of the above concept.

OVERVIEW:

  •  Create a table whose one block has 10 rows.
  •   Update one row in each of 10 sessions
  •  Check that no. of CR clones created > _db_block_max_cr_dba

IMPLEMENTATION:

– Create simple table whose one block has 10 rows

 

HR1> drop table t1 purge;
    create table t1
    (c1 int, c2 char(700));

HR1> var j number;
     begin
       :j := 1;
       for i in 1..10 loop
         insert into t1 values(:j, ‘x’);
         :j := :j+1;
       end loop;
     commit;
     end;
     /

   col c1 for 99
   select c1 from t1;

C1

1
2
3
4
5
6
7
8
9
10

– Get the block address and browse X$BH view for that block.

 

HR1> select dbms_rowid.rowid_relative_fno(rowid) as file#,
           dbms_rowid.rowid_block_number(rowid) as block#
    from t1;

FILE#     BLOCK#
———- ———-
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551

– Check that value of the parameter _db_block_max_cr_dba = 8

 

SYS>SELECT 
  a.ksppinm  “Parameter”,
  decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
  c.ksppstvl “Instance”,
  decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
  decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
  decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
  a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;

Enter value for 1: _db_block_max_cr_dba
old  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%_db_block_max_cr_dba%’)

Parameter                                          Session                      Instance                                    S I D Description
————————————————– —————————- ——————————————————- – – – ——————————————————————————————
_db_block_max_cr_dba                                                            8                                           F F F Maximum Allowed Number of CR buffers per dba

– Check in x$bh that We have one CURRENT block(state=xcur).
Enter file#/block# found above when prompted

SYS>select b.dbarfil, b.dbablk, b.class,
  decode(state,0,’free’,1,’xcur’,2,’scur’,3,’cr’, 4,’read’,5,’mrec’,6,’irec’,7,’write’,8,’pi’, 

9,’memory’,10,’mwrite’,11,’donated’) as state,
  cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
  (select object_name from dba_objects where object_id = b.obj) as object_name
from sys.x$bh b
where dbarfil = &file_no and
      dbablk = &block_no;

Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 551
old   7:       dbablk = &block_no
new   7:       dbablk = 551

DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4        551          1 xcur                0          0          0          0          0 T1
4        551          1 free                0          0          0          0          0

Now, let’s see how consecutive DML statements generate CR blocks.

— update and monitor cr block

— Start 10 HR sessions
— Update one row in each of 10 sessions

HR1> update t1 set c2 = ‘y’ where c1 = 1;
     /
HR2> update t1 set c2 = ‘y’ where c1 = 2;
     /
HR3> update t1 set c2 = ‘y’ where c1 = 3;
     /
HR4> update t1 set c2 = ‘y’ where c1 = 4;
     /
HR5> update t1 set c2 = ‘y’ where c1 = 5;
     /
HR6> update t1 set c2 = ‘y’ where c1 = 6;
     /
HR7> update t1 set c2 = ‘y’ where c1 = 7;
     /
HR8> update t1 set c2 = ‘y’ where c1 = 8;
     /
HR9> update t1 set c2 = ‘y’ where c1 = 9;
     /
HR10> update t1 set c2 = ‘y’ where c1 = 10;
     /

– Check in x$bh that We have one CURRENT block(state=xcur) and > 8 (_db_block_max_cr_dba) cr clones (status=cr)

SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 551

DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4        551          1 cr           24257659          0          3        596       1025 T1
4        551          1 cr           24257658          0          3        181       1023 T1
4        551          1 cr           24257657          0          3        181       1023 T1
4        551          1 cr           24257651          0          3        181       1023 T1
4        551          1 cr           24257649          0          3     172517        954 T1
4        551          1 cr           24257648          0          3        181       1023 T1
4        551          1 cr           24257647          0          3        181       1023 T1
4        551          1 cr           24257645          0          3     176654       1063 T1
4        551          1 cr           24257639          0          3        181       1023 T1
4        551          1 cr           24257638          0          3        181       1023 T1
4        551          1 cr           24257635          0          3     279569       1005 T1

4        551          1 cr           24257634          0          3        181       1023 T1
4        551          1 cr           24257633          0          3        181       1023 T1
4        551          1 cr           24257622          0          3     175596        916 T1
4        551          1 cr           24257621          0          3        181       1023 T1
4        551          1 cr           24257620          0          3        181       1023 T1
4        551          1 xcur                0          0          0          0          0 T1

17 rows selected.

– CLEANUP –
HR1> rollback;
HR2>rollback;
HR3> rollback;
HR4> rollback;
HR5> rollback;
HR6> rollback;
HR7> rollback;
HR8> rollback;
HR9> rollback;
HR10> rollback;
     drop table t1 purge;

SYS>alter system set “_db_block_max_cr_dba”= 6 scope=spfile;
    startup force;

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

Related links:

 

 

 

—————–

 

 

CONSISTENT READS IN ORACLE : PART – I

 
  In this post I will discuss and demonstrate how oracle creates CR (consistent read) blocks.
 

Read consistency implementation in Oracle

 
    Whenever a user queries data, he is to be displayed the data as of the time/SCN# when he queried it. 
If the desired block is not in the buffer,
    The block is read from the disk (status=xcur) 
If  clone(s) of the block are already there in buffer
      The clone just later than the desired time is selected.
      Undo is applied to the clone so as to make it consistent with the time/SCN# of the query (status=cr).
If the block is in the buffer and is dirty and no clones are there
    A copy(clone) of the current  block is made.
    Undo is applied to the clone so as to make it consistent with the time/SCN# of the query (status=cr)
 
DML is always performed on the current copy of the block (Status=xcur). 
Whenever an update is made to the block, 
  If the current block is in the buffer (status = xcur)
    A clone of the current block is made (status=cr)
    Update is performed on the current block (status=xcur)
Else (the desired block is not in the buffer)
    The block is read from the disk (status=xcur) 
     A clone of the block is also made (status=cr)
    Update is performed on the current block (status=xcur)
 
As multiple updates/queries are issued for the same block, multiple CR blocks of the same block may be created. Since all the CR clones of the same block are on the same chain, the processes searching for the block will take long time to scan the chain and there will be contention for CBC latch. Hence Oracle has limited the maximum no. of CR clones of a block  to 6 (one current (xcur) and 5 CR clones). But it is a soft limit in the sense that if more than 5 processes are simultaneously updating different rows in the same block, more than 6 CR blocks may be created. The maximum no. of CR clones of a block can be modified by an undocumented parameter _db_block_max_cr_dba.
 
Let us see a practical demonstration of the above concept:
 
– Create simple table whose one row occupies one block
HR1> drop table t1 purge;
    create table t1
    (c1 int, c2 char(2000), c3 char(2000), c4 char(2000));
– Insert one row
HR1> insert into t1 values(1, ‘x’, ‘x’, ‘x’);
     commit;
 
– Get the block address and browse X$BH view for that block.
HR> select dbms_rowid.rowid_relative_fno(rowid) as file#,
           dbms_rowid.rowid_block_number(rowid) as block#
    from t1;
File#         Block#
——        ——
    4        550
 
– Check in x$bh that We have one CURRENT block(state=xcur). CURRENT block is always one and only one.
– Enter file#/block# found above when prompted
SYS>select b.dbarfil, b.dbablk, b.class,
  decode(state,0,’free’,1,’xcur’,2,’scur’,3,’cr’, 4,’read’,5,’mrec’,6,’irec’,7,’write’,8,’pi’, 9,’memory’,10,’mwrite’,11,’donated’) as state,
  cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
  (select object_name from dba_objects where object_id = b.obj) as object_name
from sys.x$bh b
where dbarfil = &file_no and
      dbablk = &block_no;
 
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
 
Now, let’s see how consecutive DML statements generate CR blocks.
– update and monitor cr block
– 1st update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
– Check in x$bh that We have one CURRENT block(state=xcur) and on cr clone (status=cr)
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T1
 
– 2nd update
HR> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
 DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T
 
– 3rd update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
  DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24245051          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T1
 
– 4th update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24245082          0          0          0          0 T1
         4        550          1 cr           24245051          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T1
 
– 5th update
HR> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24245122          0          0          0          0 T1
         4        550          1 cr           24245082          0          0          0          0 T1
         4        550          1 cr           24245051          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T1
 
6 rows selected.
 
– 6th update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that new cr clone has not been created because the limit of the 6 (1 xcur and 5 cr) has been reached.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24245135          0          0          0          0 T1
         4        550          1 cr           24245122          0          0          0          0 T1
         4        550          1 cr           24245082          0          0          0          0 T1
         4        550          1 cr           24245051          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
 
6 rows selected.
 
– Check the value of the parameter _db_block_max_cr_dba
 
SELECT 
  a.ksppinm  “Parameter”,
  decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
  c.ksppstvl “Instance”,
  decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
  decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
  decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
  a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
 
Enter value for 1: _db_block_max_cr_dba
old  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%_db_block_max_cr_dba%’)
 
Parameter                                          Session                      Instance                                    S I D Description
————————————————– —————————- ——————————————————- – – – ——————————————————————————————
_db_block_max_cr_dba                                                            6                                           F F F Maximum Allowed Number of CR buffers per dba
 
– Increase the value of the parameter to 8
 
SYS>alter system set “_db_block_max_cr_dba”= 8 scope=spfile;
    shu immediate;
    startup;
 
– Issue update seven times and check that 2 more clones have been created
 
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
        update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
  update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
  update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
      update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
    update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24246329          0          0          0          0 T1
         4        550          1 cr           24246328          0          0          0          0 T1
         4        550          1 cr           24246327          0          0          0          0 T1
         4        550          1 cr           24246325          0          0          0          0 T1
         4        550          1 cr           24246324          0          0          0          0 T1
         4        550          1 cr           24246323          0          0          0          0 T1
         4        550          1 cr           24246322          0          0          0          0 T1
 
8 rows selected.
 
– Issue a query from another session of HR
 
HR2>select * from t1;
 
– Check that a new clone has been created and an earlier one with 
   scn#=24246322 has been overwritten by scn#=24246435 and undo tablespace has been read to create this one. (uba_file=3,uba_blk=174754)
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
 
  DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 cr           24246435          0          3     174754       1022 T1
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24246329          0          0          0          0 T1
         4        550          1 cr           24246328          0          0          0          0 T1
         4        550          1 cr           24246327          0          0          0          0 T1
         4        550          1 cr           24246325          0          0          0          0 T1
         4        550          1 cr           24246324          0          0          0          0 T1
         4        550          1 cr           24246323          0          0          0          0 T1
 
—————————————————————————–

In my next post , I will demonstrate that _db_block_max_cr_dba is a soft limit and CR clones more than its value can be created.
—————————————————————————————————-
Related links:

 

———————–