Explain (execution) plans and user experience - or - the OR-NULL-Bind problem
From time to time it happens that I discuss with other DBAs or clients about explain plans, execution times and why explain plans can be good and the user experience is bad - or vice versa.
One example can be found at my post about "Slow Autodesk Map" where I have shown that the execution time of a statement has a horrible execution time, while the execution plan is great. But the optimizer needs a lot of time to generate the execution plan.
Now, I was discussing about another thing where the execution plan itself may lead to a wrong assumption about plans and user experience / execution times.
create table t1_test (col1 number);
create index idx on t1_test(col1);
and fill the table with some values:
begin
for i in 1..100 loop
insert into t1_test(col1) values(i);
end loop;
end;
/
commit;
Don't forget to create the table statistics:
exec dbms_stats.gather_table_stats(user,'t1_test')
The following select is the one that should be tuned:
select * from t1_test t
where (:B1 is null or t.col1 = :B1);
As you can see, the interesting part is, what happens if the bind variable :B1 is null and what happens if the bind variable is used with a value not null.
The question was: Is there any chance that the database will not use a table access full (FTS=Full table scan)?
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 18 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1_TEST | 6 | 18 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B1 IS NULL OR "T"."COL1"=TO_NUMBER(:B1))
Someone said, if you create the following index, the database will always choose the index:
create index comm_i on emp(comm,'x');
We do the select again:
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 380 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 10 | 380 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | COMM_I | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
And that really helped - as you can see at the above execution plan. But is that right under all cirumcstances? The question is also, is this the right and wanted behaviour? Do we really want the statement to be executed ALWAYS using the index?
The answer is NO. Because if the bind variable is null and we do have a hundreds of thousands or even millions of rows in the table, the index must be read for ALL entries and then ALL rows must be read by the Table Access by Index Rowid. So the database is doing a lot of additional work.
In addition, what will really happen if the table is filled up with some millions of rows and the statistics are recreated? Is this new (function-based!!) index really taken?
You can guess it: No, it isn't. The database is intelligent enough that it switches back to the FTS with 2.5 million rows...
This is what happens if :B1 is null:
And this happens if :B1 is not null:
Both ways therefore lead to a full table scan regardles of the second index - which gives the right user experience with :B1 is null but e.g. if :B1 is a value (like 5) the database should only select 5 rows using an index. The FTS is taking a long time instead and the user experience is bad.
There is no direct solution for this problem as you can't create just an index for that. The best solution is to rewrite the statement:
if :b1 is null then
select * from t1_test t;
else
select * from t1_test t
where t.col1 = :B1;
end if:
/
With this approach the FTS is done when the :B1 bind variable is null and an index access is done if the :B1 bind variable is not null.
But there is a second solution. The following change of the select statement will also work:
SELECT *
FROM T1_TEST T
WHERE :B1 IS NULL
UNION ALL
SELECT *
FROM T1_TEST T
WHERE T.COL1 = :B1;
To see the difference, we need to do a trace (with tkprof) and do have a look at the FETCH line.
The database is doing a full table scan if you execute both statements if the bind variable is null:
ORIGINAL STATEMENT:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 25001 0.57 0.59 0 28090 0 2500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25003 0.57 0.59 0 28090 0 2500000
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2500000 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T1_TEST' (TABLE)
UNION ALL STATEMENT:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 25001 0.90 0.89 0 28090 0 2500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25003 0.90 0.89 0 28090 0 2500000
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2500000 UNION-ALL
2500000 FILTER
2500000 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T1_TEST' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'IDX' (INDEX)
Both statements are doing FTS and do have the same number of blocks read and rows selected (the difference in time is just a side effect of my environment). The index access range scan is NOT executed, even it is part of the union all select execution plan!
What happens if the bind variable is not null?
ORIGINAL SELECT:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.06 0.06 0 3848 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.06 0.06 0 3848 0 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104 (TEST_EW) (recursive depth: 1)
Number of plan statistics captured: 1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
5 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T1_TEST' (TABLE)
We can see there are only 5 rows selected, but 3848 query counts! Now to the
UNION ALL SELECT:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 5
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
5 UNION-ALL
0 FILTER
0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T1_TEST' (TABLE)
5 INDEX MODE: ANALYZED (RANGE SCAN) OF 'IDX' (INDEX)
We do see again 5 rows are selected - but with only 3 as query count. This only can happen when the full table scan, which is part of the execution plan is NOT EXECUTED.
Conclusion:
Don't rely yourself on indexes or index access only or even say a Full Table Scan in an explain plan is evil. As we can see at this post, the UNION ALL select combines a full table scan and an index access at the execution plan, but the database EXECUTES only one of both sub-selects. As the database doesn't evaluates the bind variable (due to cursor sharing) while CREATING the execution plan, it does evaluate the bind variable BEFORE EXECUTING the plan - and "optimizes" the execution to one of the both sub-selects. With this in mind we can expect and we do see at the trace output that the user experience is best either with the if - else pl/sql or with the UNION ALL select.
What is faster - the pl/sql part or the UNION ALL? Well, if you execute PL/SQL with SQL the database needs to do context switches (the old rule says: If you can do it in SQL, do it in SQL, if you can't do it in SQL, do it in PL/SQL, if you can't do it in PL/SQL, do it in ...). So if you have a loop around such kind of select, you may spend a lot of time on context switches: the UNION ALL should be the first choice here.
Lesson learned: If you need to tune statements - don't have a look only at the execution plans. The right starting point for tuning is always the user experience.
One example can be found at my post about "Slow Autodesk Map" where I have shown that the execution time of a statement has a horrible execution time, while the execution plan is great. But the optimizer needs a lot of time to generate the execution plan.
Now, I was discussing about another thing where the execution plan itself may lead to a wrong assumption about plans and user experience / execution times.
The case:
We create a table with an index:create table t1_test (col1 number);
create index idx on t1_test(col1);
and fill the table with some values:
begin
for i in 1..100 loop
insert into t1_test(col1) values(i);
end loop;
end;
/
commit;
Don't forget to create the table statistics:
exec dbms_stats.gather_table_stats(user,'t1_test')
The following select is the one that should be tuned:
select * from t1_test t
where (:B1 is null or t.col1 = :B1);
As you can see, the interesting part is, what happens if the bind variable :B1 is null and what happens if the bind variable is used with a value not null.
The question was: Is there any chance that the database will not use a table access full (FTS=Full table scan)?
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 18 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1_TEST | 6 | 18 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B1 IS NULL OR "T"."COL1"=TO_NUMBER(:B1))
Someone said, if you create the following index, the database will always choose the index:
create index comm_i on emp(comm,'x');
We do the select again:
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 380 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 10 | 380 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | COMM_I | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
And that really helped - as you can see at the above execution plan. But is that right under all cirumcstances? The question is also, is this the right and wanted behaviour? Do we really want the statement to be executed ALWAYS using the index?
The answer is NO. Because if the bind variable is null and we do have a hundreds of thousands or even millions of rows in the table, the index must be read for ALL entries and then ALL rows must be read by the Table Access by Index Rowid. So the database is doing a lot of additional work.
In addition, what will really happen if the table is filled up with some millions of rows and the statistics are recreated? Is this new (function-based!!) index really taken?
You can guess it: No, it isn't. The database is intelligent enough that it switches back to the FTS with 2.5 million rows...
This is what happens if :B1 is null:
And this happens if :B1 is not null:
Both ways therefore lead to a full table scan regardles of the second index - which gives the right user experience with :B1 is null but e.g. if :B1 is a value (like 5) the database should only select 5 rows using an index. The FTS is taking a long time instead and the user experience is bad.
There is no direct solution for this problem as you can't create just an index for that. The best solution is to rewrite the statement:
if :b1 is null then
select * from t1_test t;
else
select * from t1_test t
where t.col1 = :B1;
end if:
/
With this approach the FTS is done when the :B1 bind variable is null and an index access is done if the :B1 bind variable is not null.
But there is a second solution. The following change of the select statement will also work:
SELECT *
FROM T1_TEST T
WHERE :B1 IS NULL
UNION ALL
SELECT *
FROM T1_TEST T
WHERE T.COL1 = :B1;
To see the difference, we need to do a trace (with tkprof) and do have a look at the FETCH line.
The database is doing a full table scan if you execute both statements if the bind variable is null:
ORIGINAL STATEMENT:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 25001 0.57 0.59 0 28090 0 2500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25003 0.57 0.59 0 28090 0 2500000
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2500000 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T1_TEST' (TABLE)
UNION ALL STATEMENT:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 25001 0.90 0.89 0 28090 0 2500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 25003 0.90 0.89 0 28090 0 2500000
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2500000 UNION-ALL
2500000 FILTER
2500000 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T1_TEST' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'IDX' (INDEX)
Both statements are doing FTS and do have the same number of blocks read and rows selected (the difference in time is just a side effect of my environment). The index access range scan is NOT executed, even it is part of the union all select execution plan!
What happens if the bind variable is not null?
ORIGINAL SELECT:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.06 0.06 0 3848 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.06 0.06 0 3848 0 5
Optimizer mode: ALL_ROWS
Parsing user id: 104 (TEST_EW) (recursive depth: 1)
Number of plan statistics captured: 1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
5 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T1_TEST' (TABLE)
We can see there are only 5 rows selected, but 3848 query counts! Now to the
UNION ALL SELECT:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 5
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
5 UNION-ALL
0 FILTER
0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T1_TEST' (TABLE)
5 INDEX MODE: ANALYZED (RANGE SCAN) OF 'IDX' (INDEX)
Conclusion:
Don't rely yourself on indexes or index access only or even say a Full Table Scan in an explain plan is evil. As we can see at this post, the UNION ALL select combines a full table scan and an index access at the execution plan, but the database EXECUTES only one of both sub-selects. As the database doesn't evaluates the bind variable (due to cursor sharing) while CREATING the execution plan, it does evaluate the bind variable BEFORE EXECUTING the plan - and "optimizes" the execution to one of the both sub-selects. With this in mind we can expect and we do see at the trace output that the user experience is best either with the if - else pl/sql or with the UNION ALL select.
What is faster - the pl/sql part or the UNION ALL? Well, if you execute PL/SQL with SQL the database needs to do context switches (the old rule says: If you can do it in SQL, do it in SQL, if you can't do it in SQL, do it in PL/SQL, if you can't do it in PL/SQL, do it in ...). So if you have a loop around such kind of select, you may spend a lot of time on context switches: the UNION ALL should be the first choice here.
Lesson learned: If you need to tune statements - don't have a look only at the execution plans. The right starting point for tuning is always the user experience.
How to deploy ODA lite models with (active-active) VLAN configuration easily
This blog entry is a description of deploying an Oracle Database Appliances X6-2 S in a VLAN environment, but it is valid for all models of the X6-2 and X7-2 lite versions (X6-2 S, X6-2 M, X6-2 L, X7-2 S, X7-2 M). The release of the ODA software we used to deploy was 12.1.2.12 - the newest version at the time of the installation.
To create a VLAN, Oracle kindly provides a script called ODA_vlan_config.sh which can be found at the support.oracle.com note 2216567.1.
At this note, at the manual and even on the ODAs itself one can also find a template of a json file, which is needed to deploy the ODA. VLAN is supported on the ODA light models (since software release 12.1.2.8), but the graphical deployment user interface doesn't work in VLAN environments.
Taken the provided json template file one has to change everything that is needed to deploy the ODA silent for the own environment. We saved the json template as file /tmp/create-appliance-ODA.json before we changed the entries.
The points to change are especially the name of the instance/database, the database edition, if one want to use standard edition (actually you can't mix SE and EE deployments on the same machine), the time zone, all ip-addresses and domain names. Do NOT change the nodeNumber as it is always "0" on the ODA light models S and M. To use the right VLAN for the public interface the nicName must be changed to match the VLAN configuration.
"instance" : {
"name" : "ODA1",
"instanceBaseName" : "PROD",
"dbEdition" : "EE",
"timeZone" : "UTC",
"ntpServers" : ["10.0.3.14"],
"dnsServers" : ["10.0.4.10","10.0.4.11","10.0.4.12"],
"domainName" : "example.com",
...
"nodes" : [ {
"nodeNumber" : "0",
"nodeName" : "ODA1",
"network" : [ {
"nicName" : "sfpbond1.200",
"ipAddress" : "10.24.128.243",
"subNetMask" : "255.255.255.0",
"gateway" : "10.24.128.1",
"networkType" : [ "Public" ],
"isDefaultNetwork" : true
One needs to remove the whole ILOM tree from the json file as the ODA deployment otherwise can fail (and we did had the right names / ip addresses put in there but there may be a routing problem between the different VLANs and the management network). Also one needs to remove the Auto Service Request "asr" tree from the json file, if the ASR should be configured later on. If a non existing account is used at the ASR part or the user/password combination is not right, the whole deployment fails and one needs to start over again (undeploying the ODA first and re-start with the deployment).
If needed, the disk group redundancy can be changed (NORMAL equals mirroring, HIGH equals triple mirroring which is not available with only 2 NVMe cards) and the disk percentage. The documentation tells:
Storage Configuration Options
When Oracle Database Appliance X6-2 is deployed, you can select one of the following configuration options to divide the storage capacity between DATA diskgroup and RECO diskgroup:
Do not forget to change the entries inside the database tree at the json file. dbType can only be single instance on ODA lite models, but everything else can be set. The admin password is not only used by the database, but is set as the new "master password", e.g. also for the oracle user at linux (everything can be set after the deployment, you don't need to keep a master password for all).
Enable dbConsole if you want to use Enterprise Manager Express on 12c! Provide the right database shape you want to use (db shapes documentation). Keep ACFS as dbStorage, so you are able to use e.g. standard backup agents to backup files from the disks. The character set can't be changed later on, so it is also needed to change it before the deployment starts.
"database" : {
"dbName" : "PROD",
"dbVersion" : "12.1.0.2",
"instanceOnly" : false,
"isCdb" : false,
"pdBName" : "pdb1",
"pdbAdminuserName" : "pdbuser",
"adminPassword" : "welcome1",
"dbType" : "SI",
"dbTargetNodeNumber" : "0",
"dbClass" : "OLTP",
"dbShape" : "odb2",
"dbStorage" : "ACFS",
"dbCharacterSet" : {
"characterSet" : "AL32UTF8",
"nlsCharacterset" : "AL16UTF16",
"dbTerritory" : "AMERICA",
"dbLanguage" : "AMERICAN"
},
"dbConsoleEnable" : false
}
./ODA_vlan_config.sh add -v 3001 -i sfpbond1 -ip 10.24.30.243 -n 255.255.255.0 -g 10.24.30.1
./ODA_vlan_config.sh add -v 200 -i sfpbond1 -ip 10.24.128.243 -n 255.255.255.0 -g 10.24.128.1
In our case, we needed to change the bond to run active-active instead of active-failover (the default):
vi /etc/sysconfig/network-scripts/ifcfg-sfpbond1
BONDING_OPTS="mode=8.2.3ad miimon=100 lacp_rate=0"
If one has used configure-firstnet, it may is necessary to remove ip-address, netmask and gateway from the icfg-sfpbond1 file.
After the configuration has been changed the network service and the dcs agent must be restarted:
service network restart
initctl stop initdcsagent
initctl start initdcsagent
Best practice is to check the network configuration afterwards with:
odacli list-networkinterfaces
#/opt/oracle/dcs/bin/odacli list-networkinterfaces
#
#ID Name NIC Type
#---------------------------------------- -------------------- ------------- #----------
#b324f474-627e-4b15-af83-2ed21eda845a btbond1 btbond1 Bond
#595dfda9-67a6-4fc9-96af-afe966c287a7 em1 em1 Physical
#2033de33-99fb-4d53-8235-6ef088db54fe em2 em2 Physical
#62a09165-d9a5-4b8d-8eaa-45022988215b em3 em3 Physical
#40070762-6e87-42b4-8eb2-b8f9aa642849 em4 em4 Physical
#d70bbb83-e7de-462a-8892-42bdb687d6dc sfpbond1.3001 sfpbond1.2001 Bond
#26a0c940-5705-460a-b51d-ab8723d06f35 priv0 priv0 Dummy
#49cf2709-3c75-4c2c-bf53-718757385335 sfpbond1.200 sfpbond1.200 Bond
/opt/oracle/dcs/bin/odacli create-appliance -r /tmp/create-appliance-ODA.json
As you can see, the previously created json file is used as an input parameter for the odacli create-appliance statement. After waiting a couple of minutes, the ODA is now deployed with spfbond1.200 as standard network interface.
So one is now able to use both VLANs for different tasks. As the listener is configured by default only with the VLAN 200 (specified at the json file), one needs to configure the second ip-address as listener endpoint manually, if needed (ssh as grid user and run netmgr). So in our case the public interface for the application is the VLAN 200 and the private interface for the data guard and the backup is VLAN 3001.
Re-imaging the ODA
The easiest way to start with the deployment (if the delivered machine does not already have the latest version installed) is to just re-image the ODA following the instructions that can be found at the manual for this software version. So just configure the ILOMs and start with re-imaging. You can also use the already installed ODA software and upgrade it to the newest release. Then you do test the different steps to upgrade an ODA, but as there are more steps included it's easier to re-image and the time used for both approaches doesn't differ much.Preparation
After re-imaging, the VLAN creation can take part. We have taken the fiber bond to configure our VLAN, but one can use this article also to create VLANs using copper (then use btbond1 or btbond2 instead of sfpbond1). We didn't used the copper interface for our deployment, but on ODA X6-2 machines both network interfaces can be used in parallel (on X7-2 you have to decide either using copper OR fibre)..To create a VLAN, Oracle kindly provides a script called ODA_vlan_config.sh which can be found at the support.oracle.com note 2216567.1.
At this note, at the manual and even on the ODAs itself one can also find a template of a json file, which is needed to deploy the ODA. VLAN is supported on the ODA light models (since software release 12.1.2.8), but the graphical deployment user interface doesn't work in VLAN environments.
Taken the provided json template file one has to change everything that is needed to deploy the ODA silent for the own environment. We saved the json template as file /tmp/create-appliance-ODA.json before we changed the entries.
The points to change are especially the name of the instance/database, the database edition, if one want to use standard edition (actually you can't mix SE and EE deployments on the same machine), the time zone, all ip-addresses and domain names. Do NOT change the nodeNumber as it is always "0" on the ODA light models S and M. To use the right VLAN for the public interface the nicName must be changed to match the VLAN configuration.
"instance" : {
"name" : "ODA1",
"instanceBaseName" : "PROD",
"dbEdition" : "EE",
"timeZone" : "UTC",
"ntpServers" : ["10.0.3.14"],
"dnsServers" : ["10.0.4.10","10.0.4.11","10.0.4.12"],
"domainName" : "example.com",
...
"nodes" : [ {
"nodeNumber" : "0",
"nodeName" : "ODA1",
"network" : [ {
"nicName" : "sfpbond1.200",
"ipAddress" : "10.24.128.243",
"subNetMask" : "255.255.255.0",
"gateway" : "10.24.128.1",
"networkType" : [ "Public" ],
"isDefaultNetwork" : true
One needs to remove the whole ILOM tree from the json file as the ODA deployment otherwise can fail (and we did had the right names / ip addresses put in there but there may be a routing problem between the different VLANs and the management network). Also one needs to remove the Auto Service Request "asr" tree from the json file, if the ASR should be configured later on. If a non existing account is used at the ASR part or the user/password combination is not right, the whole deployment fails and one needs to start over again (undeploying the ODA first and re-start with the deployment).
If needed, the disk group redundancy can be changed (NORMAL equals mirroring, HIGH equals triple mirroring which is not available with only 2 NVMe cards) and the disk percentage. The documentation tells:
Storage Configuration Options
When Oracle Database Appliance X6-2 is deployed, you can select one of the following configuration options to divide the storage capacity between DATA diskgroup and RECO diskgroup:
- External: Storage capacity is split between 80% for DATA and 20% for RECO.
- Internal: Storage capacity is split between 40% for DATA and 60% for RECO.
- Custom: Storage capacity is configurable from 10% to 90% for DATA and the remainder for RECO.
Do not forget to change the entries inside the database tree at the json file. dbType can only be single instance on ODA lite models, but everything else can be set. The admin password is not only used by the database, but is set as the new "master password", e.g. also for the oracle user at linux (everything can be set after the deployment, you don't need to keep a master password for all).
Enable dbConsole if you want to use Enterprise Manager Express on 12c! Provide the right database shape you want to use (db shapes documentation). Keep ACFS as dbStorage, so you are able to use e.g. standard backup agents to backup files from the disks. The character set can't be changed later on, so it is also needed to change it before the deployment starts.
"database" : {
"dbName" : "PROD",
"dbVersion" : "12.1.0.2",
"instanceOnly" : false,
"isCdb" : false,
"pdBName" : "pdb1",
"pdbAdminuserName" : "pdbuser",
"adminPassword" : "welcome1",
"dbType" : "SI",
"dbTargetNodeNumber" : "0",
"dbClass" : "OLTP",
"dbShape" : "odb2",
"dbStorage" : "ACFS",
"dbCharacterSet" : {
"characterSet" : "AL32UTF8",
"nlsCharacterset" : "AL16UTF16",
"dbTerritory" : "AMERICA",
"dbLanguage" : "AMERICAN"
},
"dbConsoleEnable" : false
}
Creating the VLAN
After finishing the preparation, the creation of the VLAN can be done. In our deployment we have created two different VLANs on the spfbond1. So we connected as root and provided the vlan, the bondname, the ip address, network mask and the gateway as parameters to the script we have downloaded from the support.oracle.com note../ODA_vlan_config.sh add -v 3001 -i sfpbond1 -ip 10.24.30.243 -n 255.255.255.0 -g 10.24.30.1
./ODA_vlan_config.sh add -v 200 -i sfpbond1 -ip 10.24.128.243 -n 255.255.255.0 -g 10.24.128.1
In our case, we needed to change the bond to run active-active instead of active-failover (the default):
vi /etc/sysconfig/network-scripts/ifcfg-sfpbond1
BONDING_OPTS="mode=8.2.3ad miimon=100 lacp_rate=0"
If one has used configure-firstnet, it may is necessary to remove ip-address, netmask and gateway from the icfg-sfpbond1 file.
After the configuration has been changed the network service and the dcs agent must be restarted:
service network restart
initctl stop initdcsagent
initctl start initdcsagent
Best practice is to check the network configuration afterwards with:
odacli list-networkinterfaces
#/opt/oracle/dcs/bin/odacli list-networkinterfaces
#
#ID Name NIC Type
#---------------------------------------- -------------------- ------------- #----------
#b324f474-627e-4b15-af83-2ed21eda845a btbond1 btbond1 Bond
#595dfda9-67a6-4fc9-96af-afe966c287a7 em1 em1 Physical
#2033de33-99fb-4d53-8235-6ef088db54fe em2 em2 Physical
#62a09165-d9a5-4b8d-8eaa-45022988215b em3 em3 Physical
#40070762-6e87-42b4-8eb2-b8f9aa642849 em4 em4 Physical
#d70bbb83-e7de-462a-8892-42bdb687d6dc sfpbond1.3001 sfpbond1.2001 Bond
#26a0c940-5705-460a-b51d-ab8723d06f35 priv0 priv0 Dummy
#49cf2709-3c75-4c2c-bf53-718757385335 sfpbond1.200 sfpbond1.200 Bond
Deploying the ODA
As the VLANs are now fully functionable, one can start the deployment (still connected as root):/opt/oracle/dcs/bin/odacli create-appliance -r /tmp/create-appliance-ODA.json
As you can see, the previously created json file is used as an input parameter for the odacli create-appliance statement. After waiting a couple of minutes, the ODA is now deployed with spfbond1.200 as standard network interface.
So one is now able to use both VLANs for different tasks. As the listener is configured by default only with the VLAN 200 (specified at the json file), one needs to configure the second ip-address as listener endpoint manually, if needed (ssh as grid user and run netmgr). So in our case the public interface for the application is the VLAN 200 and the private interface for the data guard and the backup is VLAN 3001.
Conclusion
Even if the deployment with the GUI is not working for VLAN environments, the ODA lite models using bare metal installation without OVM (so called virtualized platform) can easily be deployed with VLAN configuration and, if you keep in mind to remove the entries from the json files you don't need, it is fast and straightforward and works on from ODA light software release 12.1.2.9.0 (until at least 12.1.2.12, but I think this is also available for later software releases). If needed, configure additional network things (like permanent routes and rule sets in /etc/sysconfig/network-scripts).DOAG conference review (Nuremberg, 21.-23.11.2017)
 |
| Robotron Booth |
Time flies by - so it's time now to reflect the 3 days at the DOAG conference this year at Nuremberg. It was my second time I have held a presentation at this big event, this time it was about "Flashback Database, Backup and Recovery for Oracle Database 12c Release 2". The presentation was held at 9am at the second day and it was astonishing, the room was filled up in total.
As the main goal of my presentation was to sensualize the administrators how to avoid traps in the new default “Single-tenant” architecture, some of the spectators may have had problems to divide between my advices for best-practice and “things not working” in database release 12.2. But they can download the presentation in the next days and I think they will see the differences then.
In addition to my own presentation, the DOAG conference was full of news and interesting other slots. I tried to follow topics I like most – tuning for example, and I have seen some really good presentations there (with new ideas for my daily work). In addition, I have met a product manager responsible for the cost-based optimizer and discussed with him the “Autodesk” tuning problem (details can be found at an older post here). I’ll send him the dump and the selects and I am wondering, which outcome I’ll get later (I'll keep you informed).
Another cool presentation was held by a Swiss employee of trivadis, regarding connecting the “Keepass” freeware to the oracle database to avoid using open passwords in database scripts. This could be something for some of my customers, too.
A lot of presentations are now without any live demos. I didone, but the lady that was presenting at my room just before me had a lot of problems with her live demo - she broke everything. I have tested my live demo two evenings before I held the presentation - and the virtualbox with my Oracle Linux crashed. My disk was full and I needed two hours in the late night to get everything running again. Thank god I did this test (because otherwise this were happen at the presentation time) and at the end it worked, but I can comprehend, why more and more speakers don't do live demos anymore.
My German colleagues had some cloud workshops - unfortunately, cloud does not seem to be a big topic for DOAG participants. There were not so many people which wanted to do some hands on. That was really a pitty.
A funny presentation was the one from Chris Saxon, where he was doing "More SQL magic!". Magic tricks with PL/SQL and SQL options - the small room was really filled up and even if I have known the technology behind each trick - it is really nice to see what is possible within the database and Chris was able to make a show out of it. Our daily work does not seem to be magic or funny all the time, so it was really a great moment to see him. One of the tricks he showed at the DOAG you can find at youtube (https://www.youtube.com/watch?v=zgXstJZETm0) and I think it's worth to follow him (and his blogs) as a developer as well as a dba (http://www.chrissaxon.co.uk/).
Most impressive are presentations which do not have anything to do with our daily work – at least at a first glimpse. One of the keynotes were held by a German Lufthansa pilot trainer and it was a very impressive presentation. He took some aircraft accidents and told in detail why they happened – and why people died or why not, and what the aircraft industry is doing to avoid crashes from a team member perspectives view. At the end, it is all about respect – trust – teaming and avoiding hierarchical levels at the cockpit (for sure they exist) for the time two pilots are working together. The aircraft industry is more or less forced since years to do a lot of the stuff, other industries are talking about now: Knowledge clusters, flat hierarchies, trust, responsibility and respect. All people on a plane are co-workers and all are doing everything to bring “my aircraft” safe home.
In total, the DOAG was again a big success, I think, for the referents as well as for the participants. You can attend dozens of presentations over 3 days – but with a high number of presentations in parallel, you sometimes decide to attend the wrong presentation. I had that only once this time. The DOAG is THE place for the DACH region to learn, to share, to see, to be seen and to meet old friends and new, interesting people!
The next DOAG conference (the 31st) will again be at Nuremberg in November 2018 - so if you understand German, you should attend. One of the main topics there, I think, will be the database release 18. The first release with the new naming convention. I’m looking forward to that…
Subscribe to:
Posts (Atom)