Why your Parallel DML is slower than you thought
In the Data Warehouse Administration course that I delivered this week, one topic was Parallel Operations. Queries, DDL and DML can be done in parallel, but DML is special: You need to enable it for your session! This is reflected in v$session with the three columns PQ_STATUS, PDDL_STATUS and PDML_STATUS. Unlike the other two, PDML_STATUS defaults to disabled. It requires not only a parallel degree on the table respectively a parallel hint inside the statement, but additionally a command like ALTER SESSION ENABLE PARALLEL DML; Look what happens when I run an UPDATE with or without that command:
The table sales has a parallel degree of 4. The two marked statements seem to be identical – and they are. But the second has a much longer runtime. Why is that?
It’s because Parallel DML is disabled in that session. The fetching of the rows can be done in parallel, but the Query Coordinator (QC) needs to do the update! That is of course not efficient. The mean thing is that you see actually Parallel Processes (PXs) running and appearing in the execution plan, so this may look like it does what it is supposed to – but is does NOT. Here is how it should be, with the correct ALTER SESSION ENABLE PARALLEL DML before the update:
The QC does only the job of coordinating the PXs here that do both, fetching and updating the rows now. Result is a way faster execution time. I’m sure you knew that already, but just in case 😉
The Data Guard Broker: Why it is recommended
When it comes to Data Guard on a recent version, I will always use the Data Guard Broker. Not the Enterprise Manager; don’t get me wrong: Strictly Command Line with DGMGRL. It is for Standby Databases what RMAN is for Backup & Recovery: The recommended way to go. Why? Four reasons at least:
1. The Broker helps during the setup
This demo uses two Linux machines: uhesse1 has the Primary Database prima running. uhesse2 is for the Standby Database physt. The Oracle Net Configuration on uhesse1:
[oracle@uhesse1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = uhesse1)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prima_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = prima)
)
)
[oracle@uhesse1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
PRIMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = uhesse1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = prima)
)
)
PHYST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = uhesse2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = physt)
)
)
_DGMGRL is the only special part here: The Broker needs that to be able to restart the instance during Role Changes. Standby Configuration is the same, except that there is physt instead of prima in the listener.ora. The initialization parameters for prima are
[oracle@uhesse1 ~]$ sqlplus sys/oracle@prima as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 11:44:05 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> create pfile='/home/oracle/initprima.ora' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@uhesse1 ~]$ cat initprima.ora
*.compatible='11.2.0.3'
*.control_files='/home/oracle/prima/control01.ctl'
*.db_block_size=8192
*.db_name='prima'
*.db_recovery_file_dest='/home/oracle/flashback'
*.db_recovery_file_dest_size=5g
*.diagnostic_dest='/home/oracle/prima'
*.remote_login_passwordfile='exclusive'
*.undo_management='auto'
*.undo_tablespace='undotbs1'
I kept that as minimalistic as possible in order to give you an easy overview about what is relevant for Data Guard here – defaults almost everywhere. Only a few customizations for the Standby gives me
[oracle@uhesse1 ~]$ cat initphyst.ora *.compatible='11.2.0.3' *.control_files='/home/oracle/physt/control01.ctl' *.db_block_size=8192 *.db_name='prima' *.db_unique_name=physt *.db_file_name_convert='prima','physt' *.log_file_name_convert='prima','physt' *.db_recovery_file_dest='/home/oracle/flashback' *.db_recovery_file_dest_size=5g *.diagnostic_dest='/home/oracle/physt' *.remote_login_passwordfile='exclusive' *.undo_management='auto' *.undo_tablespace='undotbs1'
I copy that pfile and the password file to the Standby host and go there into NOMOUNT before the duplicate command
[oracle@uhesse1 ~]$ scp initphyst.ora uhesse2:/home/oracle initphyst.ora 100% 431 0.4KB/s 00:00 [oracle@uhesse1 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwprima uhesse2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwphyst orapwprima 100% 1536 1.5KB/s 00:00 [oracle@uhesse1 ~]$ ssh uhesse2 mkdir /home/oracle/physt [oracle@uhesse1 ~]$ ssh uhesse2 mkdir /home/oracle/flashback [oracle@uhesse1 ~]$ sqlplus sys/oracle@physt as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 12:07:30 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='/home/oracle/initphyst.ora'; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 238034944 bytes Fixed Size 2227136 bytes Variable Size 180356160 bytes Database Buffers 50331648 bytes Redo Buffers 5120000 bytes SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
It is most efficient to create the Standby Redo Logs (SRLs) at this point on the Primary, because (from 11g on) RMAN will duplicate them onto the Standby then. SRLs are recommended on either side and are required on the Standby for LGWR Transport
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 9 104857600 512 1 NO
CURRENT 238195 19-JAN-12 2.8147E+14
2 1 8 104857600 512 1 YES
INACTIVE 234561 18-JAN-12 238195 19-JAN-12
SQL> alter database add standby logfile '/home/oracle/prima/srl_g3.rdo' size 100m;
Database altered.
SQL> alter database add standby logfile '/home/oracle/prima/srl_g4.rdo' size 100m;
Database altered.
SQL> alter database add standby logfile '/home/oracle/prima/srl_g5.rdo' size 100m;
Database altered.
[oracle@uhesse1 ~]$ rman target sys/oracle@prima auxiliary sys/oracle@physt
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 8 12:08:56 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIMA (DBID=2003897072)
connected to auxiliary database: PRIMA (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 08-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwprima' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwphyst' ;
}
executing Memory Script
Starting backup at 08-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
Finished backup at 08-JUL-13
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/oracle/physt/control01.ctl';
}
executing Memory Script
Starting backup at 08-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prima.f tag=TAG20130708T121004 RECID=3 STAMP=820239005
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 08-JUL-13
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/home/oracle/physt/temp01.dbt";
switch clone tempfile all;
set newname for datafile 1 to
"/home/oracle/physt/system01.dbf";
set newname for datafile 2 to
"/home/oracle/physt/sysaux01.dbf";
set newname for datafile 3 to
"/home/oracle/physt/undotbs01.dbf";
set newname for datafile 4 to
"/home/oracle/physt/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/home/oracle/physt/system01.dbf" datafile
2 auxiliary format
"/home/oracle/physt/sysaux01.dbf" datafile
3 auxiliary format
"/home/oracle/physt/undotbs01.dbf" datafile
4 auxiliary format
"/home/oracle/physt/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/physt/temp01.dbt in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 08-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/prima/system01.dbf
output file name=/home/oracle/physt/system01.dbf tag=TAG20130708T121013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/prima/sysaux01.dbf
output file name=/home/oracle/physt/sysaux01.dbf tag=TAG20130708T121013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/prima/undotbs01.dbf
output file name=/home/oracle/physt/undotbs01.dbf tag=TAG20130708T121013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/prima/users01.dbf
output file name=/home/oracle/physt/users01.dbf tag=TAG20130708T121013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-JUL-13
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=820239080 file name=/home/oracle/physt/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=820239080 file name=/home/oracle/physt/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=820239080 file name=/home/oracle/physt/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=820239080 file name=/home/oracle/physt/users01.dbf
Finished Duplicate Db at 08-JUL-13
So far no DGMGRL involved. RMAN gave me a Physical Standby but did not configure Redo Transport from Primary to Standby nor did it start Redo Apply on the Standby. DGMGRL will now do that:
[oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> help create
Creates a broker configuration
Syntax:
CREATE CONFIGURATION AS
PRIMARY DATABASE IS
CONNECT IDENTIFIER IS ;
DGMGRL> CREATE CONFIGURATION myconf AS PRIMARY DATABASE IS prima CONNECT IDENTIFIER IS prima;
Error:
ORA-16525: the Data Guard broker is not yet available
ORA-06512: at "SYS.DBMS_DRS", line 157
ORA-06512: at line 1
DGMGRL> exit
[oracle@uhesse1 ~]$ oerr ora 16525
16525, 00000, "the Data Guard broker is not yet available"
// *Cause: The Data Guard broker process was either not yet started, was
// initializing, or failed to start.
// *Action: If the broker has not been started, set the DG_BROKER_START
// initialization parameter to true and allow the broker to finish
// initializing before making the request. If the broker failed to
// start, check the Data Guard log for possible errors. Otherwise,
// retry the operation.
Oops, I forgot to set that parameter – that was of course intentionally for didactical reasons 😉
SQL> alter system set dg_broker_start=true; System altered. SQL> connect sys/oracle@physt as sysdba Connected. SQL> alter system set dg_broker_start=true; System altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options [oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima "CREATE CONFIGURATION myconf AS PRIMARY DATABASE IS prima CONNECT IDENTIFIER IS prima" DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. Configuration "myconf" created with primary database "prima"
The built in help function is so good (wished that RMAN had it also!) that I don’t need the documentation here:
[oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> help add
Adds a standby database to the broker configuration
Syntax:
ADD DATABASE
[AS CONNECT IDENTIFIER IS ]
[MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> ADD DATABASE physt AS CONNECT IDENTIFIER IS physt MAINTAINED AS PHYSICAL;
Database "physt" added
DGMGRL> enable configuration;
Enabled.
You should monitor the alert.log of the two databases while that enabling is in progress – the Broker does a lot here, especially it configures Redo Transport and Redo Apply.
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxPerformance
Databases:
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database physt;
Database - physt
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
physt
Database Status:
SUCCESS
That was already it. The hardest part was the Oracle Net Configuration, right? Our heroes RMAN & DGMGRL did the rest.
2. Role Changes are much easier with the Broker
Without the Broker, Data Guard Role Changes require a complex sequence of steps (versions before 12c) on both sides that differ between Logical and Physical Standby. Not so with DGMGRL:
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxPerformance
Databases:
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to physt;
Performing switchover NOW, please wait...
New primary database "physt" is opening...
Operation requires shutdown of instance "prima" on database "prima"
Shutting down instance "prima"...
ORACLE instance shut down.
Operation requires startup of instance "prima" on database "prima"
Starting instance "prima"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "physt"
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxPerformance
Databases:
physt - Primary database
prima - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Try that with SQL commands and you spend a significant amount of time reading the documentation in order to get these steps right. Furthermore, I don’t need to bother about LOG_ARCHIVE_DEST_2 because the Broker sets it correctly – without manual intervention and without VALID_FOR. The other Role Changes are also one-liners with the Broker:
DGMGRL> failover to physt;
That’s it for the manual failover. And there is also only one command needed for the Snapshot Standby:
DGMGRL> convert database physt to snapshot standby;
Easy, isn’t it?
3. The Data Guard Broker delivers basic monitoring of the Configuration
The Broker is quite sensitive and spots problems with the Data Guard Configuration fast. It is a good indicator that everything is actually okay when you see this:
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxPerformance
Databases:
prima - Primary database
physt - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
4. Fast-Start Failover requires the Data Guard Broker
Conclusion: In many ways, the Data Guard Broker respectively DGMGRL is comparable to RMAN: The tool is recommended because it makes critical tasks easier to do with less risk of errors than a manual approach has. Some features even require it, like RMAN for incremental backups and the Broker for Fast-Start Failover. It is the most efficient and reliable way to go. Yes, there have been bugs (some in version 9 for the Broker, and RMAN wasn’t reliable in version 8) – but we don’t live in the past. I’ll go with the Broker for Data Guard anytime. The shown demo should be easy to reproduce for you, so as always: „Don’t believe it, test it!“ 🙂
Addendum: I didn’t want to give the impression that you shouldn’t use Enterprise Manager for Data Guard – it is of course perfectly valid to do so. It is just my personal preference to maintain Data Guard on the Command Line. EM also triggers the Broker under the covers, pretty much like it triggers RMAN when you manage Backup & Recovery over EM. Check out this great article when you are interested in Data Guard administration with EM 12c Cloud Control: http://www.oracle.com/technetwork/articles/oem/havewala-odg-oem12c-1999410.html
Fine Grained Access Control with DBMS_RLS using UPDATE_CHECK=>true
Fine Grained Access Control aka Virtual Private Database (VPD) has been there since Oracle 8 and got enhanced in each subsequent version. One minor New Feature of 11.2 was the addition of the parameter UPDATE_CHECK to the DBMS_RLS.ADD_POLICY procedure. During the OCM Preparation Workshop that I deliver presently, one of the attendees asked me what this parameter is actually doing – the doc is not so clear about it – which is why I came up with this simplified example. Hope you find it useful also 🙂
SQL> grant dba to adam identified by adam;
Grant succeeded.
SQL> connect adam/adam
Connected.
SQL> create table emp (ename varchar2(5),salary number);
Table created.
SQL> insert into emp values ('SCOTT',3000);
1 row created.
SQL> insert into emp values ('KING',9000);
1 row created.
SQL> commit;
Commit complete.
SQL> grant create session to scott identified by tiger;
Grant succeeded.
SQL> grant select,update on adam.emp to scott;
Grant succeeded.
The user SCOTT is not supposed to see the salary of the other employees and VPD is an elegant way to achieve that. The following technique will silently attach a WHERE-condition to statements hitting the table emp:
SQL> connect adam/adam
Connected.
SQL> create or replace function whoisit(schema varchar2, tab varchar2) return varchar2
as
begin
return '''' || sys_context('userenv','session_user') || ''' = ename ';
end;
/
Function created.
SQL> begin
dbms_rls.add_policy
(object_schema=>'ADAM',
object_name=>'EMP',
policy_name=>'EMP_POLICY',
function_schema=>'ADAM',
policy_function=>'WHOISIT',
update_check=>true);
end;
/
PL/SQL procedure successfully completed.
SQL> connect scott/tiger
Connected.
SQL> select * from adam.emp;
ENAME SALARY
----- ----------
SCOTT 3000
Although there are two rows in the table, SCOTT sees only his own salary! So far, this has been the same in earlier versions already. Now to the effect of update_check:
SQL> update adam.emp set ename='KING';
update adam.emp set ename='KING'
*
ERROR at line 1:
ORA-28115: policy with check option violation
Without that parameter setting, the update would succeed – and the row would vanish for the user SCOTT as if the update would have deleted the row. Imagine the confusion of the user about that weird behavior 🙂
Talking about weird, by the way:
SQL> connect adam/adam Connected. SQL> select * from adam.emp; no rows selected SQL> connect / as sysdba Connected. SQL> select * from adam.emp; ENAME SALARY ----- ---------- SCOTT 3000 KING 9000 SQL> grant exempt access policy to adam; Grant succeeded. SQL> connect adam/adam Connected. SQL> select * from adam.emp; ENAME SALARY ----- ---------- SCOTT 3000 KING 9000
That was funny, wasn’t it?
Conclusion: The new parameter UPDATE_CHECK in the DBMS_RLS.ADD_POLICY procedure restricts updates that would else lead to the updated rows to fall out of the allowed visibility for that user. Check out the old behavior by just omitting that parameter. Because: Don’t believe it, test it 🙂
Pickleball spielen auf YouTube
Pickleball spielen 