I was successfully able to complete rolling upgrade using Dataguard for one of our environment and application downtime was reduced to just 2 minutes from 4 hours. Below are the high level steps performed and case study for this rolling database upgrade.
Current Dataguard configuration looks like as below:
Primary database: rec2p455 running on server dgtest02.
Physical standby database: rec3p455 running on server dgtest03.
1. Review prerequisites and best practices from Dataguard Rolling upgrade white paper.
a. Enable flashback and created a guaranteed restore point on each databases for flashaback purpose
b. Install upgraded ORACLE_HOME 11.2.0.4 on each servers dgtest02 and dgtest03 servers.
c. Make sure that your physical standby database is configured correctly to support switchover operations. Below parameters should be configured correctly on these databases -
On primary rec2p455 -
alter system set db_unique_name='rec2p455' scope=spfile sid='*';
alter system set log_archive_config = 'DG_CONFIG=(rec2p455,rec3p455)' scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set db_file_name_convert='/oracle/data/ora01/rec2p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec2p455/','/oracle/data/ora02/rec2p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec2p455/','/oracle/data/ora03/rec2p455/','/oracle/data/ora03/rec3p455/','/oracle/data/ora03/rec3p455/','/oracle/data/ora03/rec2p455/','/oracle/data/ora04/rec2p455/','/oracle/data/ora04/rec3p455/','/oracle/data/ora04/rec3p455/','/oracle/data/ora04/rec2p455/' scope=spfile sid='*';
alter system set log_file_name_convert='/oracle/data/redo01/rec2p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec3p455/','/oracle/data/redo01/rec2p455/','/oracle/data/redo02/rec2p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec3p455/','/oracle/data/redo02/rec2p455/' scope=spfile sid='*';
alter system set log_archive_dest_2='SERVICE=rec3p455 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=rec3p455' scope=spfile sid='*';
alter system set log_archive_dest_state_2=ENABLE scope=spfile;
alter system set fal_server='rec3p455' scope=spfile sid='*';
alter system set fal_client='rec2p455' scope=spfile sid='*';
On standby rec3p455:
alter system set db_unique_name='rec3p455' scope=spfile sid='*';
alter system set log_archive_config = 'DG_CONFIG=(rec2p455,rec3p455)' scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set db_file_name_convert='/oracle/data/ora01/rec2p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec2p455/','/oracle/data/ora02/rec2p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec2p455/','/oracle/data/ora03/rec2p455/','/oracle/data/ora03/rec3p455/','/oracle/data/ora03/rec3p455/','/oracle/data/ora03/rec2p455/','/oracle/data/ora04/rec2p455/','/oracle/data/ora04/rec3p455/','/oracle/data/ora04/rec3p455/','/oracle/data/ora04/rec2p455/' scope=spfile sid='*';
alter system set log_file_name_convert='/oracle/data/redo01/rec2p455/','/oracle/data/ora01/rec3p455/','/oracle/data/ora01/rec3p455/','/oracle/data/redo01/rec2p455/','/oracle/data/redo02/rec2p455/','/oracle/data/ora02/rec3p455/','/oracle/data/ora02/rec3p455/','/oracle/data/redo02/rec2p455/' scope=spfile sid='*';
alter system set log_archive_dest_2='SERVICE=rec2p455 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=re2p455' scope=spfile sid='*';
alter system set log_archive_dest_state_2=ENABLE scope=spfile;
alter system set fal_server='rec2p455' scope=spfile sid='*';
alter system set fal_client='rec3p455' scope=spfile sid='*';
2. Convert the existing Physical Standby rec3p455 to a Logical Standby using the keep identity feature which doesn't require database name to be changed. While logical standby build is going on, Applications are still conected to primary database rec2p455 on
icmfg74pharma server and performing usual transactions as below -
SQL> conn userid@rec2p455_dg
Enter password:
Connected.
SQL>
create table tmp as select 1 dummy from dual;
table created.
insert into tmp as select * from tmp;
1 row inserted;
/
2 rows inserted;
/
4 rows inserted;
/
8 rows inserted;
/
16 rows inserted;
/
32 rows inserted;
/
128 rows inserted;
commit;
256 rows inserted.
commit;
SQL> select count(1) from tmp;
COUNT(1)
----------
256
On Primary rec2p455 -
---------------------------------------------------------------
CREATE RESTORE POINT PRE_UPGRADE_1 GUARANTEE FLASHBACK DATABASE;
EXECUTE DBMS_LOGSTDBY.BUILD;
On standby database rec3p455 for converting it to logical from physical -
CREATE RESTORE POINT PRE_UPGRADE_2 GUARANTEE FLASHBACK DATABASE; -
ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');
CREATE RESTORE POINT PRE_UPGRADE_3 GUARANTEE FLASHBACK DATABASE;
3. Perform the upgrade on the logical standby rec3p455 following the upgrade process as per oracle documentation upgrade guide as you do in any normal database and start the apply process so that logical standby is completely synchronized with the primary database rec2p455. While upgrade is going on, primary database rec2p455 is still available and applications can query and perform transactions.
Database mounted.
Database opened.
> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 08-29-2014 17:17:34
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:09:44
Final Actions
. 00:00:10
Total Upgrade Time: 00:09:55
PL/SQL procedure successfully completed.
SQL> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
--------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ -----------------------------
22-SEP-10 03.42.04.503870 PM APPLY SERVER 11.1.0.7 3 CPU CPUOct2009
22-SEP-10 03.42.45.067417 PM CPU 6452863 view recompilation
20-MAR-13 08.50.32.513178 PM APPLY SERVER 11.1.0.7 13 PSU PSU 11.1.0.7.13
19-JUL-13 05.20.52.017167 PM VIEW INVALIDATE 8289601 view invalidation
19-JUL-13 05.21.00.848183 PM UPGRADE SERVER 11.2.0.3.0 Upgraded from 11.1.0.7.0
19-JUL-13 05.26.58.688228 PM APPLY SERVER 11.2.0.3 4 PSU PSU 11.2.0.3.4
20-NOV-13 05.46.43.353854 PM APPLY SERVER 11.2.0.3 7 PSU PSU 11.2.0.3.7
29-AUG-14 05.16.13.596405 PM VIEW INVALIDATE 8289601 view invalidation
29-AUG-14 05.16.23.809173 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 11.2.0.3.0
29-AUG-14 05.18.10.822624 PM APPLY
4. Switchover the database role so that existing logical standby database rec3p455 becomes primary while rec2p455 becomes logical standby database. At this point downtime for application is required as switchover to logical standby database doesn't require database to be started so application connections will not failover even if you are using service to support transparent application failover. In my case switchover was done in less than a minute and sometimes it can be more than a minute as it takes additional time in applying the redo gap if any to database being converted to primary. Downtime can be arranged with application team for rolling upgrade accordingly. I would say 5-10 minutes would be sufficient.
On Primary rec3p455 -
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
On Standby rec2p455
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL PRIMARY;
5. Flashback the original primary rec2p455 (now a logical standby) to the guaranteed restore
point from #1
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE_1;
6. Remount the original primary (now a logical standby) under the new Oracle Home.
shut down database;
a.) change oracle home in oratab and listener.ora.
b.) reload listener.ora
c.) create symbolic links to spfile and pwfile to $ORACLE_HOME/dbs as applicable if you are storing these files in another location otherwise simply copy the files from older home to this location
d.) startup mount;
7. Convert the logical standby rec2p455 back to a physical standby and resynchronize with the
new primary, automatically completing the upgrade process.
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
shut immediate;
startup mount;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; ( Before starting the managed recovery, make sure that standby destination is enabled on new primary database rec3p455).
8. Wait for physical standby to be upgraded via the redo stream. In alert log you can see that new incarnation for the the original primary(now standby) will be registered -
RFS[6]: Selected log 7 for thread 1 sequence 21 dbid 829525374 branch 856888795
Archived Log entry 4334 added for thread 1 sequence 20 rlc 856888795 ID 0x32798a25 dest 2:
Archived Log entry 4335 added for thread 1 sequence 19 rlc 856888795 ID 0x32798a25 dest 2:
Fri Aug 29 17:57:49 2014
started logmerger process
Fri Aug 29 17:57:49 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 12111287417698
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /oracle/data/redo01/rec2p455/ora_log_01_01.rdo
Clearing online log 1 of thread 1 sequence number 3924
RFS[5]: Opened log for thread 1 sequence 3913 dbid 829525374 branch 838060608
Fri Aug 29 17:57:49 2014
Archived Log entry 4336 added for thread 1 sequence 21 ID 0x32798a25 dest 1:
Archived Log entry 4337 added for thread 1 sequence 3913 rlc 838060608 ID 0x32784362 dest 2:
Fri Aug 29 17:57:50 2014
Completed: alter database recover managed standby database disconnect from session
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oracle/data/redo01/rec2p455/ora_log_02_01.rdo
Clearing online log 2 of thread 1 sequence number 3925
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oracle/data/redo01/rec2p455/ora_log_03_01.rdo
Clearing online log 3 of thread 1 sequence number 3923
Clearing online redo logfile 3 complete
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_3911_838060608.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_3912_838060608.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_3913_838060608.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_1_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_2_856888795.arc
Fri Aug 29 17:58:00 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_3_856888795.arc
Fri Aug 29 17:58:12 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_4_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_5_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_6_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_7_856888795.arc
Fri Aug 29 17:58:26 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_8_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_9_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_10_856888795.arc
Fri Aug 29 17:58:38 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_11_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_12_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_13_856888795.arc
Fri Aug 29 17:58:48 2014
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_14_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_15_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_16_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_17_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_18_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_19_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_20_856888795.arc
Media Recovery Log /oracle/data/arch02/rec2p455/rec2p455_1_21_856888795.arc
Media Recovery Waiting for thread 1 sequence 22 (in transit)
Fri Aug 29 18:00:53 2014
9. Switch back to the original configuration (optional). This can be done later whenever applications are ready for another downtime of 2 minutes. Since this time switchover is required between primary and physical standby databases which required database remount, so application connections will also failover automatically to new primary database if they are connecting to database service supporting transparent application failover. All select queries will continue to execute but any transactions executed during the time when switchover is happening, needs to be re-executed.
On current primary rec3p455 -
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
On standby rec2p455 -
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
alter database open;
On new standby rec3p455 after switchover -
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
10. Raise the COMPATIBLE settings
You can see that select queries (reporting sessions) are still alive and returning the correct results and connecting to primary database -
SQL> /
COUNT(1)
----------
256
SQL> select database_role, name from v$database;
DATABASE_ROLE NAME
---------------- ---------
PRIMARY REC2P455
SQL> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
--------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ -------------------------------
22-SEP-10 03.42.04.503870 PM APPLY SERVER 11.1.0.7 3 CPU CPUOct2009
22-SEP-10 03.42.45.067417 PM CPU 6452863 view recompilation
20-MAR-13 08.50.32.513178 PM APPLY SERVER 11.1.0.7 13 PSU PSU 11.1.0.7.13
19-JUL-13 05.20.52.017167 PM VIEW INVALIDATE 8289601 view invalidation
19-JUL-13 05.21.00.848183 PM UPGRADE SERVER 11.2.0.3.0 Upgraded from 11.1.0.7.0
19-JUL-13 05.26.58.688228 PM APPLY SERVER 11.2.0.3 4 PSU PSU 11.2.0.3.4
20-NOV-13 05.46.43.353854 PM APPLY SERVER 11.2.0.3 7 PSU PSU 11.2.0.3.7
29-AUG-14 05.16.13.596405 PM VIEW INVALIDATE 8289601 view invalidation
29-AUG-14 05.16.23.809173 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 11.2.0.3.0
29-AUG-14 05.18.10.822624 PM APPLY SERVER 11.2.0.4 2 PSU PSU 11.2.0.4.2
10 rows selected.
SQL> sho parameter db_unique
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_unique_name string rec2p455
SQL>
For reporting services, below service was created along with triggers to support transparent application connections failover -
exec DBMS_SERVICE.CREATE_SERVICE -
(service_name => 'rec2p455_dg', -
network_name => 'rec2p455_dg', -
aq_ha_notifications => true, -
failover_type => 'SELECT', -
failover_retries => 180, -
failover_delay => 1);
create or replace trigger start_database_service after startup on database
declare
role varchar2(50);
omode varchar2(50);
begin
select database_role into role from v$database;
if role='PRIMARY' then
dbms_service.start_service('rec2p455_dg');
end if;
end;
/
CREATE OR REPLACE TRIGGER start_service_test_role_change AFTER
DB_ROLE_CHANGE ON DATABASE
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE(''rec2p455_dg')');
END IF;
END;
I hope you find this post useful. Pls let me know for any suggestions and questions you have.
Friday, August 29, 2014
Tuesday, August 19, 2014
Dynamic Remastering versus Dynamic Reconfiguration in Oracle RAC
Often DBAs get themselves confused with two most distinct and significant concepts of RAC -Dynamic Remastering and Dynamic Reconfiguration. In this article I would try to explain these two concepts and will also share useful scripts that can be used in day today administration for finding master of a block or to forcefully enable the desired master for a particular block.
Dynamic Remastering: Before starting discussion on dynamic remastering, you need to be familiar with Global Resource Directory. GRD basically contains useful information such as data block address, mode (null, shared or exclusive), role(local or global), SCN, past image and current image. Each instance maintains the portion of GRD in its own SGA. In other words, any node which is master of particular object or a block maintains the GRD portion for those blocks in its SGA. GCS in conjunction with GES is actually responsible for updating GRD for a resource in instance's SGA. The objective of dynamic remastering is to master buffer cache resource on the instance where it's mostly accessed.
Mastering of block is decided based on the demand for a block, so basically when demand for particular object or block is high on one node, that instance will become master for that object or block. And this is where dynamic mastering comes into play. For Example, instance A is currently master of object (Table EMP) because there were huge number of read/write requests made by instance A on table EMP. After sometime, other instance B started making such requests very frequently for EMP such that demand for EMP table increased on instance B. In such situations, instance A has to either ship the blocks to instance B or forward the request to other instance which is holding most current copy of the requested blocks. To avoid unnecessary interactions and network traffic, GCS remasters the blocks to instance B.
Dynamic Reconfiguration: Dynamic Reconfiguration is the process of reconfiguring resources of failed nodes among surviving nodes. Dynamic Reconfiguration comes into play when an instance crashes unexpectedly. Below are some of the high level steps due to instance crash when node failure is detected by CSSD daemon.
Dynamic remastering comes into play here as well as cache resources have to be mastered among surviving nodes the only difference here that remastering was forced due to instance crash but not due to demand for resources.
Bases on the above discussion remastering of an object or block can be triggered mainly due to below three reasons:
First of all, retrieve data_object_id of table EMP using below script:
References:
http://oracleinaction.com/dynamic-remastering/
https://support.oracle.com
Dynamic Remastering: Before starting discussion on dynamic remastering, you need to be familiar with Global Resource Directory. GRD basically contains useful information such as data block address, mode (null, shared or exclusive), role(local or global), SCN, past image and current image. Each instance maintains the portion of GRD in its own SGA. In other words, any node which is master of particular object or a block maintains the GRD portion for those blocks in its SGA. GCS in conjunction with GES is actually responsible for updating GRD for a resource in instance's SGA. The objective of dynamic remastering is to master buffer cache resource on the instance where it's mostly accessed.
Mastering of block is decided based on the demand for a block, so basically when demand for particular object or block is high on one node, that instance will become master for that object or block. And this is where dynamic mastering comes into play. For Example, instance A is currently master of object (Table EMP) because there were huge number of read/write requests made by instance A on table EMP. After sometime, other instance B started making such requests very frequently for EMP such that demand for EMP table increased on instance B. In such situations, instance A has to either ship the blocks to instance B or forward the request to other instance which is holding most current copy of the requested blocks. To avoid unnecessary interactions and network traffic, GCS remasters the blocks to instance B.
Dynamic Reconfiguration: Dynamic Reconfiguration is the process of reconfiguring resources of failed nodes among surviving nodes. Dynamic Reconfiguration comes into play when an instance crashes unexpectedly. Below are some of the high level steps due to instance crash when node failure is detected by CSSD daemon.
- The enqueue part of GRD is reconfigured among surviving nodes by global enqueue service. This operation occurs relatively faster.
- The cache part of GRD is reconfigured and SMON reads the redo log of the failed instance to identify the data blocks that it needs to recover. Since blocks are shipped over the interconnect, SMON may have to merge the redo logs of the failed instance to overcome holes in the redo.
- SMON issues GRD requests to obtain all the data blocks that need recovery. Redo threads of failed instances are merged and applied to the most current versions or past image of the blocks from other instance cache. If past images are not available, then redo logs will be applied after reading the blocks from disk. All blocks become accessible now.
- Undo blocks are applied to the database in back ground. You can tune this rollback recovery by setting parameter fast_start_rollback_recovery parameter appropriately.
Dynamic remastering comes into play here as well as cache resources have to be mastered among surviving nodes the only difference here that remastering was forced due to instance crash but not due to demand for resources.
Bases on the above discussion remastering of an object or block can be triggered mainly due to below three reasons:
- Manual remastering
- Resource affinity
- Instance crash
Lets discuss these criteria for dynamic remastering one by one in detail.
The scripts and demo used in this article are referred from oracle metalink support and very nice article from Anju Garg in blog (oracleinaction.com).
I have used similar demo in one of my in-house environment with some additional tweaks with the help of oracle metalink support and sharing same in this article. The environment is RAC cluster of 3 nodes (RAC1, RAC2, RAC3) and the experiments were performed on oracle provided SCOTT schema.
First of all, retrieve data_object_id of table EMP using below script:
select owner, data_object_id, object_name
from dba_objects
where owner = 'SCOTT'
and object_name = 'EMP';
OWNER DATA_OBJECT_ID OBJECT_NAME
———- ————– —————————————
SCOTT 32614 EMP
Get File_id and block_id of emp table:
SQL>select empno, dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
from scott.emp
where empno in (1, 2);
EMPNO DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
———- ———————————— ————————————
1 6 120
2 6 120
MANUAL REMASTERING
You can manually remaster an object with oradebug command :
oradebug lkdebug -m pkey <data_object_id>
Step I. shutdown the database and restart it from RAC1
[oracle@rac1 ~]$ srvctl stop database -d rac
srvctl start database -d rac
srvctl status database -d rac
Step II. Issue a select on the object from NODE2
SCOTT@NODE2> select * from emp;
Step III. Find the GCS resource name to be used in the query
x$kjbl.kjblname = resource name in hexadecimal format([id1],[id2],[type]
x$kjbl.kjblname2 = resource name in decimal format
Hexname will be used to query resource in v$gc_element and v$dlm_rss views.
SYS@rac2>col hexname for a25
col resource_name for a15
select b.kjblname hexname, b.kjblname2 resource_name,
b.kjblgrant, b.kjblrole, b.kjblrequest
from x$le a, x$kjbl b
where a.le_kjbl=b.kjbllockp
and a.le_addr = (select le_addr
from x$bh
where dbablk = 120
and obj = 32614
and class = 1
and state <> 3);
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLREQUE
————————- ————— ——— ———- —————— ———- ———
[0x97][0x4],[BL] 120,6,BL KJUSERPR 0 KJUSERNL
Step IV. Check the current master of the block –
SYS>select o.object_name, m.CURRENT_MASTER,
m.PREVIOUS_MASTER, m.REMASTER_CNT
from dba_objects o, v$gcspfmaster_info m
where o.data_object_id=32614
and m.data_object_id = 32614 ;
OBJECT CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
—— ————– ——————— ————— ————— —
EMP 0 32767 1
Note that current master of scott.emp is node RAC1 (numbering starts from 0). Value of previous master (32767) indicates that the previous master was not known and current the master is 0 which is instance RAC1. REMASTER_CNT = 1 indicates the object has been remastered only once.
Step V. Use following SQL to show master and owner of the block.
This SQL joins x$kjbl with x$le to retrieve resource name.
– Note that current master is node1(KJBLMASTER=0) and current owner of the block is
node2(KJBLOWNER = 1)
SYS@NODE2> select kj.kjblname, kj.kjblname2, kj.kjblowner,
kj.kjblmaster
from (select kjblname, kjblname2, kjblowner,
kjblmaster, kjbllockp
from x$kjbl
where kjblname = '[0x97][0x4],[BL]'
) kj, x$le le
where le.le_kjbl = kj.kjbllockp
order by le.le_addr;
KJBLNAME KJBLNAME2 KJBLOWNER KJBLMASTER
—————————— —————————————-
[0x97][0x4],[BL] 120,6,BL 1 0
Step VI. Manually master the EMP table to node RAC2 using oradebug.
SYS@rac1>oradebug lkdebug -m pkey 32614
SYS>select o.object_name, m.CURRENT_MASTER,
m.PREVIOUS_MASTER, m.REMASTER_CNT
from dba_objects o, v$gcspfmaster_info m
where o.data_object_id=32614
and m.data_object_id = 32614 ;
OBJECT CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
—— ————– ————— —————— ————– ————— ————
EMP 1 0 2
As you can clearly see that current_master has changed from node 1 to node 2 and remaster count has also increased to 2. It also show that previously master for the object was node 1 (PREVIOUS_MASTER = 0).
You can also determine the master at block level using below query.
SYS> select kj.kjblname, kj.kjblname2, kj.kjblowner,
kj.kjblmaster
from (select kjblname, kjblname2, kjblowner,
kjblmaster, kjbllockp
from x$kjbl
where kjblname = '[0x97][0x4],[BL]') kj, x$le le
where le.le_kjbl = kj.kjbllockp
order by le.le_addr;
KJBLNAME KJBLNAME2 KJBLOWNER KJBLMASTER
—————————— —————————— ———- ———-
[0x97][0x4],[BL] 120,6,BL 1 1
Note that current owner of the block is Node2 (KJBLOWNER=1)from where query was issued)
Current master of the block has been changed to node2 (KJBLMASTER=1).
REMASTERING DUE TO RESOURCE AFFINITY
Resource affinity allows frequently used resource to be mastered on its local node; it used dynamic resource mastering to move location of the resource masters. There are number of parameters that can be used to dynamically remaster an object.
There may be situations when you would like to tweak or disable dynamic resource mastering for an object or for the whole RAC database. Frequent remastering of any block or object can cause significant traffic in RAC interconnect and cause delays in serving resource requests as GCS has to quiesce the resource for performing DRM. Frequent DRM can result in database spending time mostly on waits such as GCS buffer busy (acquire/release) and other DRM freeze/releases as well as remaster events. These waits can be easily visible in AWR/ASH/AMM or cluster coherency section under OEM . There are ways you can tune this by setting resource affinity attributes. DRM attributes are intentionally undocumented and normally these attributes should not be changed without discussing with Oracle Support.
There may be situations when you would like to tweak or disable dynamic resource mastering for an object or for the whole RAC database. Frequent remastering of any block or object can cause significant traffic in RAC interconnect and cause delays in serving resource requests as GCS has to quiesce the resource for performing DRM. Frequent DRM can result in database spending time mostly on waits such as GCS buffer busy (acquire/release) and other DRM freeze/releases as well as remaster events. These waits can be easily visible in AWR/ASH/AMM or cluster coherency section under OEM . There are ways you can tune this by setting resource affinity attributes. DRM attributes are intentionally undocumented and normally these attributes should not be changed without discussing with Oracle Support.
Following parameters affect dynamic remastering due to resource affinity :
Pls note that these parameters are renamed from 11G onwards as earlier they used to be _gc_affinity_ but 11g onwards they are changed to _gc_policy_
_gc_policy_limit : If an instance opens 50 more open BL requests on an object than the other instance (controlled by _gc_policy_limit parameter), then that object is a candidate for remastering. That object is queued and LMD0 reads the queue and initiates GRD freeze. LMON in conjunction with LMS is responsible for remastering of resources. All these are visible in LMD0/LMON trace files. These statistics are maintained in X$object_affinity_statistics.
_gc_policy_time : Intervals in minutes in which object statistics are evaluated and queue is checked to determine if dynamic remastering must be triggered.
_gc_policy_minimum: The minimum number of global cache operations (OPEN/CONVERT/CLOSE) per minute to qualify an object for affinity (object remastering).
_gc_read_mostly_locking: Setting this parameter to false disable read mostly related DRM. Default is TRUE.
_gc_undo_affinity: This parameter enables dynamic undo remastering. Mastering of undo segments differ from that of non-undo segments. Non-undo segments remastering uses hash techniques and remastering occurs on the basis of number of open BL lock requests while undo segment remastering enabled for a instance that actually activates undo segment which makes sense.
_gc_read_mostly_locking: Setting this parameter to false disable read mostly related DRM. Default is TRUE.
_gc_undo_affinity: This parameter enables dynamic undo remastering. Mastering of undo segments differ from that of non-undo segments. Non-undo segments remastering uses hash techniques and remastering occurs on the basis of number of open BL lock requests while undo segment remastering enabled for a instance that actually activates undo segment which makes sense.
_lm_drm_max_banned_objs: The parameter plays an important role if you want to skip dynamic remastering at object level. Set this parameter to number of objects you want to disable remastering. Maximume value is 235
_lm_drm_banned_objs: Set this parameter to list of object ids separated by commas to skip dynamic remastering for the objects.
Lets consider a scenario where you would like to minimize dynamic remastering for an object. Lets say for an object name EMP, there have been frequent requests coming from nodes RAC1 and RAC2. If number of opens gets beyond default value of 50 for an instance, you would not just want that object to be remastered among nodes just because of that.
So if its normal for multiple instances to issue these many requests, you would prefer to avoid dynamic remastering happening frequently. You can increase the value of _gc_policy_limit to higher than average requests of object per instance as well as you can consider setting higher value of _gc_policy_minimum to control dynamic remastering.
Pls also note that setting higher values or setting above parameters to zero completely disable dynamic remastering. Setting the above parameters also require database restart.
You can use queries described earlier under section "MANUAL REMASTERING" as well as check for number of opens in X$object_policy_statistics to verify if you should consider tuning the parameters discussed above. Sum of sopens, xopens and xfers decides whether the object will be considered for DRM .
There are other useful views that DBA should always keep checking for tuning the dynamic remastering latency. gv$policy_history which displays DRM history at object level (included from 11g onwards) as well as gv$dynamic_remaster_stats which displays statistical information about dynamic remastering process.
When DRM triggers, it can easily be visible from LMON and LMD trace files. Alternatively, you can also explicitly turn on the tracing using below:
alter system set events '10430 trace name context forever, level 8';
Diable tracing:
Alter system set events '10430 trace name context off';
So if its normal for multiple instances to issue these many requests, you would prefer to avoid dynamic remastering happening frequently. You can increase the value of _gc_policy_limit to higher than average requests of object per instance as well as you can consider setting higher value of _gc_policy_minimum to control dynamic remastering.
Pls also note that setting higher values or setting above parameters to zero completely disable dynamic remastering. Setting the above parameters also require database restart.
You can use queries described earlier under section "MANUAL REMASTERING" as well as check for number of opens in X$object_policy_statistics to verify if you should consider tuning the parameters discussed above. Sum of sopens, xopens and xfers decides whether the object will be considered for DRM .
SQL> select object, node, sopens, xopens, xfers from x$object_policy_statistics where object=
( select object_id pe from dba_objects where object_name='EMP' and object_type='TABLE' and owner='SCOTT');
OBJECT NODE SOPENS XOPENS XFERS
---------- ---------- ---------- ---------- ---------- ---------- ----------
32614 2 0 3817 1195
There are other useful views that DBA should always keep checking for tuning the dynamic remastering latency. gv$policy_history which displays DRM history at object level (included from 11g onwards) as well as gv$dynamic_remaster_stats which displays statistical information about dynamic remastering process.
select * from gv$policy_history;
INST_ID POLICY_EVENT DATA_OBJECT_ID TARGET_INSTANCE_NUMBER EVENT_DATE
---------- -------------------- -------------- ---------------------- -------------------- --------------------
1 initiate_affinity 32614 1 09/14/2014 13:11:14
1 push_affinity 32614 2 09/14/2014 13:15:54
When DRM triggers, it can easily be visible from LMON and LMD trace files. Alternatively, you can also explicitly turn on the tracing using below:
alter system set events '10430 trace name context forever, level 8';
Diable tracing:
Alter system set events '10430 trace name context off';
REMASTERING DUE TO INSTANCE CRASH
Presently node RAC2 is the master of SCOTT.EMP. Lets crash this node by shutting it down abnormally and see what's happening:
$srvct stop instance -d rac -i rac2 -o abort
$srvct stop instance -d rac -i rac2 -o abort
You can clearly see in the output of below query that current_master has changed to 1 i.e. node 2 from previous_master = 2 (node 3) and remaster count has also increased to 5.
SYS@RAC2>select o.object_name, m.CURRENT_MASTER,
m.PREVIOUS_MASTER, m.REMASTER_CNT
from dba_objects o, v$gcspfmaster_info m
where o.data_object_id=32614
and m.data_object_id = 32614;
OBJECT_NAME CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
————— ————– ————— ————————— ————– ————
EMP 1 2 5
I hope you find this article useful for you. Pls let me know for any questions or suggestions to make it more understandable for you.
References:
http://oracleinaction.com/dynamic-remastering/
https://support.oracle.com
Wednesday, August 6, 2014
What a bitmap join Index can do for you
Intoduction
You might have used bitmap indexes in data warehouse environment for the columns in which ratio of number of distinct values to number of rows in the table is very small but you might not have used extended functionality of bitmap join indexes to overcome query performance issues. In this article I would give you an overview of using bitmap join index with examples. Bitmap indexes are not explained in much detail as they are out of scope of this article.How It Works
An index provides pointers to the rows in a table that contain a given key value. A regular index stores a list of rowids for each key corresponding to the rows with that key value. In a bitmap index, a bitmap for each key value replaces a list of rowids.
Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a regular index. If the number of different key values is small, bitmap indexes save space.
Bitmap indexes are most effective for queries that contain multiple conditions in the WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.
Bitmap Join Indexes extend the functionality of regular bitmap indexes by allowing the index creation on the join condition of query. Bitmap join index is basically a bitmap index on the join of two or more tables. For each value in a column of a table, a bitmap join index stores the rowids of corresponding rows in one or more other tables. In a data warehousing environment, the join condition is an equi-inner join between the primary key column or columns of the dimension tables and the foreign key column or columns in the fact table.
Bitmap join indexes are much more efficient in storage than materialized join views, an alternative for materializing joins in advance. This is because the materialized join views do not compress the rowids of the fact tables
Too much of theory, its time to see an example now
I recently tuned below query in one of the production system. You can see that below query is written in very inefficient manner and no wonder why cost is so high in execution plan.
SELECT COUNT (*) COUNT
FROM (SELECT a.*, ROWNUM rnum
FROM ( SELECT a.parent_item_id id,
a.soonest_expiration soonest_expiration,
a.quantity,
b.subsample_parent_id subsample_parent_id,
c.symbol units,
d.name,
e.parent_item_id,
f.name location,
f.PATH,
g.current_state_id,
h.name state_name,
h.action_count,
property_header1ss.property_header1,
property_header2ss.property_header2,
property_header3ss.property_header3,
property_header4ss.property_header4,
property_header5ss.property_header5,
property_header6ss.property_header6,
property_header7ss.property_header7,
property_header8ss.property_header8,
property_header9ss.property_header9,
property_header10ss.property_header10,
property_header11ss.property_header11,
property_header12ss.property_header12,
property_header13ss.property_header13,
property_header14ss.property_header14,
property_header15ss.property_header15,
property_header16ss.property_header16,
property_header17ss.property_header17,
property_header18ss.property_header18
FROM consumables$aud a,
consumables b,
units$aud c,
templates$aud d,
consumable_types$aud e,
locations$aud f,
wf_instances$aud g,
wf_states$aud h,
(SELECT a.VALUE property_header1, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_739579')
AND a.descriptor_id = c.id) property_header1ss,
(SELECT a.VALUE property_header2, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_772637')
AND a.descriptor_id = c.id) property_header2ss,
(SELECT a.VALUE property_header3, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_739685')
AND a.descriptor_id = c.id) property_header3ss,
(SELECT a.VALUE property_header4, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property879375853')
AND a.descriptor_id = c.id) property_header4ss,
(SELECT a.VALUE property_header5, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_739806')
AND a.descriptor_id = c.id) property_header5ss,
(SELECT a.VALUE property_header6, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_739947')
AND a.descriptor_id = c.id) property_header6ss,
(SELECT a.VALUE property_header7, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_740018')
AND a.descriptor_id = c.id) property_header7ss,
(SELECT a.VALUE property_header8, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_740091')
AND a.descriptor_id = c.id) property_header8ss,
(SELECT a.VALUE property_header9, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_740251')
AND a.descriptor_id = c.id) property_header9ss,
(SELECT a.VALUE property_header10, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_740338')
AND a.descriptor_id = c.id) property_header10ss,
(SELECT a.VALUE property_header11, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_740428')
AND a.descriptor_id = c.id) property_header11ss,
(SELECT a.VALUE property_header12, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_817093')
AND a.descriptor_id = c.id) property_header12ss,
(SELECT a.VALUE property_header13, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_817204')
AND a.descriptor_id = c.id) property_header13ss,
(SELECT a.VALUE property_header14, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_817317')
AND a.descriptor_id = c.id) property_header14ss,
(SELECT a.VALUE property_header15, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_818108')
AND a.descriptor_id = c.id) property_header15ss,
(SELECT a.VALUE property_header16, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_818230')
AND a.descriptor_id = c.id) property_header16ss,
(SELECT a.VALUE property_header17, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_1868863741')
AND a.descriptor_id = c.id) property_header17ss,
(SELECT a.VALUE property_header18, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_895380')
AND a.descriptor_id = c.id) property_header18ss
WHERE b.id = a.parent_item_id
AND f.id = a.location_id
AND a.template_id = d.id
AND a.in_case = 0
AND b.site_id = 107132
AND d.consumable_type_id = e.id
AND c.id = e.units_id
AND g.id = a.workflow_instance_id
AND h.id = g.current_state_id
AND a.version = (SELECT version
FROM consumables
WHERE id = a.parent_item_id)
AND h.action_count != 0
AND property_header1ss.owner_id(+) = a.id
AND property_header2ss.owner_id(+) = a.id
AND property_header3ss.owner_id(+) = a.id
AND property_header4ss.owner_id(+) = a.id
AND property_header5ss.owner_id(+) = a.id
AND property_header6ss.owner_id(+) = a.id
AND property_header7ss.owner_id(+) = a.id
AND property_header8ss.owner_id(+) = a.id
AND property_header9ss.owner_id(+) = a.id
AND property_header10ss.owner_id(+) = a.id
AND property_header11ss.owner_id(+) = a.id
AND property_header12ss.owner_id(+) = a.id
AND property_header13ss.owner_id(+) = a.id
AND property_header14ss.owner_id(+) = a.id
AND property_header15ss.owner_id(+) = a.id
AND property_header16ss.owner_id(+) = a.id
AND property_header17ss.owner_id(+) = a.id
AND property_header18ss.owner_id(+) = a.id
ORDER BY UPPER (d.name) ASC) a
WHERE parent_item_id = 155550 AND 1 = 1) a
WHERE rnum >= 0
SQL> select * from table(dbms_xplan.display());
The contents of execution plan has been truncated as it was quite lengthy.
PLAN_TABLE_OUTPUT
Plan hash value: 166816580
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | 176M (1)|127:23:26 |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 77 | 1001 | | 176M (1)|127:23:26 |
| 3 | COUNT | | | | | | |
| 4 | VIEW | | 77 | | | 176M (1)|127:23:26 |
| 5 | SORT ORDER BY | | 77 | 2730K| 232G| 176M (1)|127:23:26 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| 6 | FILTER | | | | | | |
| 7 | HASH JOIN RIGHT OUTER | | 15M| 515G| 219M| 11M (1)| 08:18:56 |
| 8 | VIEW | | 113K| 218M| | 1348 (4)| 00:00:04 |
| 9 | HASH JOIN | | 113K| 4562K| | 1348 (4)| 00:00:04 |
| 10 | TABLE ACCESS FULL | PROPERTY_DESCRIPTORS$AUD | 17 | 391 | | 26 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | PROPERTY_VALUES$AUD | 589K| 10M| | 1308 (3)| 00:00:04 |
| 12 | HASH JOIN RIGHT OUTER | | 4811K| 153G| 219M| 3705K (1)| 02:40:13 |
| 13 | VIEW | | 113K| 218M| | 1348 (4)| 00:00:04 |
| 14 | HASH JOIN | | 113K| 4562K| | 1348 (4)| 00:00:04 |
| 15 | TABLE ACCESS FULL | PROPERTY_DESCRIPTORS$AUD | 17 | 391 | | 26 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | PROPERTY_VALUES$AUD | 589K| 10M| | 1308 (3)| 00:00:04 |
122 rows selected.
In the above query you clearly see that below subquery was used multiple times for the same tables with different values of column 'safe_name'.
(SELECT a.VALUE property_header1, a.owner_id
FROM property_values$aud a,
property_descriptors$aud c
WHERE c.safe_name IN ('Property_739579')
One thing we could do here was merge these multiple subqueries in single subquery and use 'case when' in outer select query which would have dramatically improved the query performance.
But again this query is something running inside vendor provided cots packages and application team rejected the suggestion of rewriting the query as below:
SQL> explain plan for SELECT COUNT (*) COUNT
2 FROM (SELECT a.*, ROWNUM rnum
3 FROM ( SELECT a.parent_item_id id,
4 a.soonest_expiration soonest_expiration,
5 a.quantity,
6 b.subsample_parent_id subsample_parent_id,
7 c.symbol units,
8 d.name,
9 e.parent_item_id,
10 f.name location,
11 f.PATH,
12 g.current_state_id,
13 h.name state_name,
14 h.action_count
/* use here case when statement to display various property headers */
15 FROM consumables$aud a,
16 consumables b,
17 units$aud c,
18 templates$aud d,
19 consumable_types$aud e,
20 locations$aud f,
21 wf_instances$aud g,
22 wf_states$aud h,
23 (SELECT a.VALUE property_header, a.owner_id
24 FROM property_values$aud a,
25 property_descriptors$aud c
26 WHERE c.safe_name IN ('Property_739579','Property_772637','Property_739685','Property879375853','Property_739806','Property_739947','Property_740091','Property_740251','Property_740338','Property_740251','Property_740338
27 AND a.descriptor_id = c.id) property_header
28 WHERE b.id = a.parent_item_id
29 AND f.id = a.location_id
30 AND a.template_id = d.id
31 AND a.in_case = 0
32 AND b.site_id = 107132
33 AND d.consumable_type_id = e.id
34 AND c.id = e.units_id
35 AND g.id = a.workflow_instance_id
36 AND h.id = g.current_state_id
37 AND a.version = (SELECT version
38 FROM consumables
39 WHERE id = a.parent_item_id)
40 AND h.action_count != 0
41 AND property_header.owner_id(+) = a.id
42 ORDER BY UPPER (d.name) ASC) a
43 WHERE parent_item_id = 155550 AND 1 = 1) a
44 WHERE rnum >= 0
45 /
Explained.
SQL>
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 4236004457
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | 26734 (1)| 00:01:10 |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1 | 13 | | 26734 (1)| 00:01:10 |
| 3 | COUNT | | | | | | |
| 4 | VIEW | | 1 | | | 26734 (1)| 00:01:10 |
| 5 | SORT ORDER BY | | 1 | 177 | 7080K| 26734 (1)| 00:01:10 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| 6 | FILTER | | | | | | |
| 7 | HASH JOIN OUTER | | 19433 | 3359K| | 1629 (5)| 00:00:05 |
| 8 | HASH JOIN | | 1185 | 197K| | 286 (4)| 00:00:01 |
| 9 | TABLE ACCESS FULL | WF_STATES$AUD | 791 | 15029 | | 7 (0)| 00:00:01 |
| 10 | HASH JOIN | | 1185 | 175K| | 278 (3)| 00:00:01 |
| 11 | HASH JOIN | | 1185 | 163K| | 133 (4)| 00:00:01 |
| 12 | TABLE ACCESS FULL | LOCATIONS$AUD | 32 | 928 | | 4 (0)| 00:00:01 |
| 13 | HASH JOIN | | 1185 | 129K| | 128 (3)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | CONSUMABLES$AUD | 720 | 27360 | | 83 (2)| 00:00:01 |
| 15 | NESTED LOOPS | | 1286 | 110K| | 94 (2)| 00:00:01 |
| 16 | HASH JOIN | | 2 | 100 | | 8 (13)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| 17 | NESTED LOOPS | | 1 | 23 | | 3 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID| CONSUMABLE_TYPES$AUD | 1 | 15 | | 2 (0)| 00:00:01 |
| 19 | INDEX RANGE SCAN | CON_TYPES_PARENT_VERSION | 1 | | | 1 (0)| 00:00:01 |
| 20 | INDEX UNIQUE SCAN | SYS_C003779 | 1 | | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | TEMPLATES$AUD | 50 | 1350 | | 4 (0)| 00:00:01 |
| 22 | INDEX RANGE SCAN | CONSUMABLES$AUD_TEMPLATEID | 2572 | | | 4 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | CONSUMABLES | 10443 | 244K| | 33 (4)| 00:00:01 |
| 24 | TABLE ACCESS FULL | WF_INSTANCES$AUD | 35991 | 386K| | 144 (3)| 00:00:01 |
| 25 | VIEW | | 589K| 3455K| | 1329 (4)| 00:00:04 |
| 26 | HASH JOIN | | 589K| 19M| | 1329 (4)| 00:00:04 |
| 27 | INDEX FAST FULL SCAN | PROPERTY_DESCRIPTORS$ID_SAFE | 111 | 2553 | | 7 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| 28 | TABLE ACCESS FULL | PROPERTY_VALUES$AUD | 589K| 6335K| | 1308 (3)| 00:00:04 |
| 29 | TABLE ACCESS BY INDEX ROWID | CONSUMABLES | 1 | 9 | | 2 (0)| 00:00:01 |
| 30 | INDEX UNIQUE SCAN | SYS_C003629 | 1 | | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------
The only other solution I was left with - create bitmap join index on the common join condition being used multiple times in the above query and that's what I did. I created below index and performance you can see yourself. Cost of execution plan dramatically reduced from 176M to 37K and the query which was not executing at all started giving results in less than a minute.
alter session set current_schema = dbuser;
create bitmap index propval$desc_bm
on property_values$aud (property_descriptors$aud.safe_name)
from property_values$aud, property_descriptors$aud
where property_values$aud.descriptor_id = property_descriptors$aud.id;
The index now contains the bitmap for column value 'safe_name' and since it contains bitmaps only for matching rowids returned by join condition, size of index will be comparatively smaller and hence the index is more efficient in comparison to regular bitmap index.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 2868485995
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | 37748 (1)| 00:01:38 |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
|* 2 | VIEW | | 1 | 13 | | 37748 (1)| 00:01:38 |
| 3 | COUNT | | | | | | |
| 4 | VIEW | | 1 | | | 37748 (1)| 00:01:38 |
| 5 | SORT ORDER BY | | 1 | 36315 | 18M| 37748 (1)| 00:01:38 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
|* 6 | FILTER | | | | | | |
|* 7 | HASH JOIN RIGHT OUTER | | 1194 | 41M| 3656K| 27511 (1)| 00:01:12 |
| 8 | VIEW | | 1852 | 3631K| | 369 (1)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | PROPERTY_VALUES$AUD | 1852 | 33336 | | 369 (1)| 00:00:01 |
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | |
|* 11 | BITMAP INDEX SINGLE VALUE | PROPVAL$DESC_BM | | | | | |
|* 12 | HASH JOIN RIGHT OUTER | | 1194 | 39M| 3656K| 25022 (1)| 00:01:05 |
| 13 | VIEW | | 1852 | 3631K| | 369 (1)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PROPERTY_VALUES$AUD | 1852 | 33336 | | 369 (1)| 00:00:01 |
| 15 | BITMAP CONVERSION TO ROWIDS | | | | | | |
|* 16 | BITMAP INDEX SINGLE VALUE | PROPVAL$DESC_BM | | | | | |
Limitation of bitmap join indexes
Join results must be stored, therefore, bitmap join indexes have the following restrictions:
1.) Parallel DML is currently only supported on the fact table. Parallel DML on one of the participating dimension tables will mark the index as unusable.
2.) Only one table can be updated concurrently by different transactions when using the bitmap join index.
3.) No table can appear twice in the join.
4.) You cannot create a bitmap join index on an index-organized table or a temporary table.
5.) The columns in the index must all be columns of the dimension tables.
6.) The dimension table join columns must be either primary key columns or have unique constraints.
7.) If a dimension table has composite primary key, each column in the primary key must be part of the join.
Subscribe to:
Posts (Atom)