Oakies Blog Aggregator

I keep saying I’m going to start sharing what I’m doing in the Analytics space soon, but heck, there’s too much I need to keep adding to on the Oracle in Azure arena!

So, as most people know, I’m not a big fan of Oracle RAC, (Real Application Cluster).  My opinion was that it was often sold for use cases that it doesn’t serve, (such as HA) and the resource demands between the nodes, as well as what happens when a node is evicted to those that are left are not in the best interest for most use cases.  On the other hand, I LOVE Oracle Data Guard, active or standard, don’t matter, the product is great and it’s an awesome option for those migrating their Oracle databases to Azure VMs.

What is Oracle Data Guard

Oracle Data Guard is a cross between what Always on Availability Groups and SQL Server log shipping.  It “ships” the redo logs from the primary database to a standby database, (or active database that can be used for reporting, hence the similarities to AG) and with a sync process.  They is a complex configuration, but surprisingly simple failover process for the business to switch to one of the standbys if there is a failure on the primary.

The product has received excellent support from Oracle and the community and it’s something I highly recommend for those businesses now migrating databases over to Azure, but want something similar to what they had on-premises.    Unlike RAC, I can put a standby, (secondary replica for you Microsoft folks) in a second location, then use a Far Sync Instance as a “pass through” from the primary to the standby vs. using the built in Sync direct to the standby database.

Why Use a Far Sync Instance

Oracle has done some great research on the benefit of a Far Sync Instance and the truth is, it improves the performance of the Data Guard sync on a VM by over 12% vs. using a standard Sync between a primary and a standby in Data Guard.

https://dbakevlar.com/wp-content/uploads/2019/07/azure_vm_farsync-300x12... 300w, https://dbakevlar.com/wp-content/uploads/2019/07/azure_vm_farsync-768x32... 768w, https://dbakevlar.com/wp-content/uploads/2019/07/azure_vm_farsync.png 1179w" sizes="(max-width: 489px) 100vw, 489px" />

Twelve may not seem like a high number, but when you’re looking to decrease latency any way you can and working between two different location zones in the cloud, this can be significant improvement.

A Far Sync Instance, is just a small allocation of memory for the Oracle background processes, a matching number of redo logs, (+1 for threads used by the Fast Sync) and no actual datafiles.  The primary then “ships” the redo logs to the Fast Sync Instance and then the Fast Sync can easily focus on its one task-  send the redo logs wherever they need to go, no matter where that standby is located.

All of this is build out on Azure Virtual Machines, (VMs) with the appropriate version of Oracle installed on each VM, but of course, the Fast Sync instance can be placed on a very low VM sizing tier, as it doesn’t require many resources or it can be on a shared server if the existing product sharing the VM isn’t very “chatty”.   The Oracle SGA for the Fast Sync can be as small as 300Mb and the CPU_COUNT=1.  Remember, this is pushing redo logs to and from it all day, so it’s going to be busy on that front.

The Architecture

https://dbakevlar.com/wp-content/uploads/2019/07/azure_oracle_dg-300x167... 300w, https://dbakevlar.com/wp-content/uploads/2019/07/azure_oracle_dg-768x429... 768w" sizes="(max-width: 650px) 100vw, 650px" />

The above diagram demonstrates the best practices of both Oracle, as well as Azure for an Oracle Database, using Oracle Data Guard, but having the standby in a secondary location.  By using the Far Sync instance, we’re able to ensure that the standby commit of the redo logs is timely enough for the primary, (as this is part of the configuration for the Oracle Data Guard.)

In this configuration, you’ll notice that to support Oracle Data Guard in Azure, we’ve included Express Route to ensure solid performance to the users, since we all know, the network is the last bottleneck.

The Details

There are a few best practices to consider when building out this solution and this includes:

• Use Async compression to decrease latency on the Far Sync transfers.
• Configure redundant network links on the customer side of the Express Route to tolerate outages.
• Choose IOPS for the standby VM that is faster than that of the redo on the primary VM to keep up with the sync, (something you might not consider for a standby server, but it’s necessary for this configuration)
• The Far Sync instance should have the same number of redo logs as the primary, +1 for every thread
• Instead of using RMAN, use Azure Site Recovery to take snapshots of each of the VMs, including the Far Sync VM, eliminating extra stress on the database tier.

If you’ve wondered how you would design an Oracle Data Guard environment, either standard or active in Azure, this is how I recommend my customers to do it.

Tags:  azure, Data guard, oracle

Del.icio.us

Facebook

TweetThis

Digg

StumbleUpon

An example configuring Oracle ODBC gateway to PostgreSQL

That sounds futuristic, like a 2025 post-microservices trend. Monolithic databases are definitely dismantled, we developed in microservices, spread data to many databases. Because we had agile development teams, they have chosen the database technology that fits their need: cheap, easy, free from any infrastructure constraints, and of course cool enough for their CV. The enterprise information system is now a zoo with all species represented.

Then, the business needs to query and operate on data that has been produced in many different sources, heterogeneous systems, and polyglot data structures. Of course, all was sourced, streamed and replicated, with an approximate consistency and latency.

But after all that fun, the data analysts need a consistent and integrated view of this data to meet their business objectives. They need to process it in one system only. They need to map it and query transparently, without having to learn different languages and structures. They may even want to enrich it, updating the multiple systems, in a transaction that stays consistent even in case of failure.

Actually, this is not new. The screenshot above is from 1995 when Oracle introduced their Oracle Gateway to connect to other databases like if they were Oracle ones. We can run DML, procedures, and transactions over Database Links. It has evolved with gateways to specific database systems, but the gateway to ODBC databases is still there and free. And Heterogenous Services are available even in Standard Edition.

Today, whether you want to offload some tables to a different DB, or you already have multiple DBMS that you want to query from a single service, you will have to run queries and transactions across different systems. Here is an example where I’ll connect Oracle DB Links to a PostgreSQL database.

In this example, I prefix the name with the type, like: usrdemo for the user, dsndemo for the data source name, lsndemo for the listener… That’s not something I do usually but it helps to understand clearly the link of components. There is a double network communication for each call here: from the database Heterogenous Service to the Gateway listener, and the listener calls the remote database through ODBC.

1. Install a PostgreSQL database

For this demo, I have setup an Oracle DBaaS in the Oracle Cloud Infrastructure which is a PaaS (the database is installed automatically and ready to use) but with IaaS access (I can connect as root and install whatever I need).

I chose a 19c Standard Edition (19c has just been made available this week on this service) running on Oracle Linux 7.5 which is the same as RHEL7.

There, I have an Oracle Database 19c already running on Oracle Enterprise Linux 7 and I install PostgreSQL 11 to demo the gateway.

sudo rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql11-server

I create a database and initialize it with pgbench.

sudo mkdir -p /u01/pgdata && sudo chown -R postgres /u01/pgdata
sudo su postgres
 export PGDATA=/u01/pgdata
 # create the database
 /usr/pgsql-11/bin/initdb -D $PGDATA
 # I prefer password authentication:
 sed -i '/^host *all/s/trust/md5/' /u01/pgdata/pg_hba.conf
 # start the server
 /usr/pgsql-11/bin/pg_ctl -D $PGDATA -l /u01/pgdata/logfile start
 # create user and database
 /usr/pgsql-11/bin/psql 
  create user usrdemo password 'pwddemo';
  create database demo;
  \q
 whoami | grep postgres && exit
sudo su - oracle

PGPASSWORD=pwddemo /usr/pgsql-11/bin/pgbench -i -h localhost -p 5432 -U usrdemo demo

Now I have a ‘demo’ database with pgbench tables, accessible from my oracle user through TCP/IP port 5432 with user ‘usrdemo’ password ‘pwddemo’

2. Install Linux ODBC and Postgres ODBC

The free Oracle Gateway connects to any database through ODBC. I install the ODBC driver for PostgreSQL

sudo yum install -y postgresql11-odbc
ls /usr/pgsql-11/lib/psqlodbc.so

I define my PostgreSQL database as the ODBC Data Source:

cat > /home/oracle/odbcDG4PGDEMO.ini <[DSNPGDEMO]
Database         = demo
Description      = Connection to PostgreSQL
Driver           = PostgreSQL
Servername       = localhost
Port             = 5432
Username         = usrdemo
END

You can use the default location like /etc/odbc.ini or $HOME/.odbc.ini but here I show a specific location just to mention it.

3. Install Oracle Gateway

I download the Heterogeneous Services Database Gateways to non-Oracle Databases. On the Oracle Database Software Downloads, choose the “See All” link:

Oracle Database 19c Download for Linux x86-64

From there I download the LINUX.X64_193000_gateways.zip (that must be done manually, clicking the “I accept the license terms” checkbox).

I install it in my Oracle Home (ORACLE_HOME must be set):

unzip LINUX.X64_193000_gateways.zip
cd gateways
./runInstaller -silent
sudo $ORACLE_HOME/root.sh

I have now some additional binaries and an HS (Heterogeneous Services) product directory:

4. Setup Oracle Gateway to ODBC

In this directory I define the Gateway configuration which references the ODBC driver and DSN:

cat > $ORACLE_HOME/hs/admin/initDG4PGDEMO.ora <set ODBCINI=/home/oracle/odbcDG4PGDEMO.ini
HS_FDS_CONNECT_INFO = DSNPGDEMO
HS_FDS_SHAREABLE_NAME=/usr/pgsql-11/lib/psqlodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#HS_FDS_TRACE_LEVEL=ON
END

The “set ODBCINI” must be used if we don’t use the default /etc/odbc.init or $HOME/.odbc.ini

The trace can dump more information to the log subdirectory in case of an error is encountered.

This Database Gateway has a System IDentifier, that will be referenced by the listener. The exposed database name is HO.WORLD by default, which is very ugly, but you can change that with HS_DB_NAME and HS_DB_DOMAIN.

5. Setup Oracle Gateway listener

The name is referenced with SID_NAME from a listener:

cat >> $ORACLE_HOME/network/admin/listener.ora <LSNPGDEMO=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1520))))
SID_LIST_LSNPGDEMO=(SID_LIST=(SID_DESC=(SID_NAME=DG4PGDEMO)(ORACLE_HOME=$ORACLE_HOME)(PROGRAM=dg4odbc))))
END

I start this listener:

lsnrctl < set current_listener LSNPGDEMO
 start
 set displaymode raw
 services
END

The listener is the Gateway that will be contacted by the Heterogenous Services.

6. Setup Heterogenous Services

I define a tnsnames.ora entry to connection to this listener:

cat >> $ORACLE_HOME/network/admin/tnsnames.ora <TNSPGDEMO=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1520))(CONNECT_DATA=(SID=DG4PGDEMO))(HS=OK))
END

I can tnsping but not connect because this is an Heterogenous Service agent

7. Create Heterogenous Services database link

All components are there and ready for heterogeneous queries and transactions. I have a pluggable database PDB1 and create a demo user there.

echo -e 'alter session set container=PDB1;\ngrant dba to demo identified by "Ora4c!eC!0ud" container=current;' | sqlplus / as sysdba

Note that I’ll set global_names to false here because I’ve not defined a name and domain for my gateway. Then it is called HO.WORLD and I don’t want to have my database link names starting with “HO.WORLD”. And if I don’t, with the default global_names, I’ll get something like: “ORA-02085: database link DBLPGDEMO.SUB11131414120.ACEDVCN.ORACLEVCN.COM”. To avoid this, either I set HS_DB_NAME and HS_DB_DOMAIN or I forget about global name enforcement.

7a. dictionary translation

sqlplus demo/'Ora4c!eC!0ud'@//localhost:1521/PDB1
 alter system set global_names=false;
 drop database link DBLPGDEMO;
 create database link DBLPGDEMO connect to "usrdemo" identified by  "pwddemo" using 'TNSPGDEMO';
 desc "pgbench_accounts"@DBLPGDEMO

That’s the first gem from Oracle transparent gateway: you see the metadata as if it were an Oracle database. The PostgreSQL tables, columns and datatypes were converted to Oracle ones.

7b. distributed queries

The second gem is about the optimizer. You can run joins between two remote queries and they will be processed on the right site:

explain plan for
select count(*) from "pgbench_branches"@DBLPGDEMO 
 join "pgbench_accounts"@DBLPGDEMO using ("bid")
 where "abalance"=0;
set pagesize 5000 linesize 150
select * from dbms_xplan.display();

Cardinality estimation is not as accurate as for local tables, but the query that is pushed will be optimized by the remote optimizer.

7c. native passthrough

The third gem is the possibility to run any native query with the DBMS_HS_PASSTHROUGH which is very similar to DBMS_SQL:

set serveroutput on
declare
   val number;
   c number;
   n number;
begin
  c:=dbms_hs_passthrough.open_cursor@DBLPGDEMO;
  dbms_hs_passthrough.parse@DBLPGDEMO(c,q'[
    select count(*) from "pgbench_branches"
    join "pgbench_accounts" using ("bid")
    where "abalance"=0;
  ]');
  loop
    n := dbms_hs_passthrough.fetch_row@DBLPGDEMO(c);
    exit when n = 0;
    dbms_hs_passthrough.get_value@DBLPGDEMO(c,1,val);
    dbms_output.put_line(val);
  end loop;
  dbms_hs_passthrough.close_cursor@DBLPGDEMO(c);
end;
/

You wonder how this DBMS_HS_PASSTHROUGH is called with a database link as of it was on the remote database, but you know that it is not defined there? It is actually interpreted by the Gateway, more info about this magic:

dbms_hs_passthrough-the magic package

8. Troubleshoot

There are limitation, some cryptic error messages but enabling the trace with HS_FDS_TRACE_LEVEL=ON creates a file in ?/hs/log/
.trc

Oracle Corporation --- 2019-07-09 21:39:02.167558000

Heterogeneous Agent Release
19.0.0.0.0

Oracle Corporation --- 2019-07-09 21:39:02.167304000

Version 19.0.0.0.0

HOSGIP for "HS_FDS_TRACE_LEVEL" returned "ON"
HOCXU_VC2_MAX=4000
HOCXU_RAW_MAX=2000
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/pgsql-11/lib/psqlodbc.so"
HOSGIP for "HS_OPEN_CURSORS" returned "50"
HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
HOSGIP for "HS_NLS_NUMERIC_CHARACTERS" returned ".,"
HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_TRUNC_ANSI_DATE" returned "OFF"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
 using postgres as default schema
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
 HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
SQL text from hgodtab, id=0, len=44 ...
     00: 73656C65 6374202A 2066726F 6D202270  [select * from "p]
     10: 6762656E 63685F61 63636F75 6E747322  [gbench_accounts"]
     20: 20776865 72652031 203D2030           [ where 1 = 0]
SQL text from hgopars, id=1, len=42 ...
     00: 53454C45 43542043 4F554E54 282A2920  [SELECT COUNT(*) ]
     10: 46524F4D 20227067 62656E63 685F6163  [FROM "pgbench_ac]
     20: 636F756E 74732220 4131               [counts" A1]
 Deferred open until first fetch.
 Performing delayed open.
HOA 07/09 21:39:02.235762000: (horcrces_CleanupExtprocSession) Entered!
HOA 07/09 21:39:02.235786000: (horcrpooe_PopOciEnv) Entered!
HOA 07/09 21:39:02.235797000: (horcrfoe_FreeOciEnv) Entered!
HOA 07/09 21:39:02.235807000: (horcrfoe_FreeOciEnv) Exiting...
HOA 07/09 21:39:02.235816000: (horcrfse_FreeStackElt) Entered!
HOA 07/09 21:39:02.235825000: (horcrfse_FreeStackElt) Exiting...
HOA 07/09 21:39:02.235835000: (horcrpooe_PopOciEnv) Exiting...
HOA 07/09 21:39:02.235844000: (horcrces_CleanupExtprocSession) Exiting...

At least we have the default values for the non-specified parameters.

Finally, a little note about the architecture: the Gateway with the ODBC driver and the heterogeneous service listener can be installed on the Oracle side, the remote database side, or in the middle (why not a Docker container if it makes it more appealing?) as it communicates by TCP/IP and is not subject to licensing. Like a streaming replication server but with redundancy, no latency, and transparent real-time query.

If you’ve been wondering why I resurrected my drafts on the opt_estimate() hint, a few weeks ago I received an email containing an example of a query where a couple of opt_estimate() hints were simply not working. The critical features of the example was that the basic structure of the query was of a type that I had not previously examined. That’s actually a common type of problem when trying to investigate any Oracle feature from cold – you can spend days thinking about all the possible scenarios you should model then the first time you need to do apply your knowledge to a production system the requirement falls outside every model you’ve examined.

Before you go any further reading this note, though, I should warn you that it ends in frustration because I didn’t find a solution to the problem I wanted to fix – possibly because there just isn’t a solution, possibly because I didn’t look hard enough.

So here’s a simplified version of the problem – it involves pushing a predicate into a union all view. First some data and a baseline query:

rem
rem     Script:         opt_estimate_3a.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2019
rem

create table t1
as
select
        rownum                          id,
        100 * trunc(rownum/100)-1       id2,
        mod(rownum,1e3)                 n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        dual
connect by
        rownum <= 1e4   -- > comment to avoid WordPress format issue
;

create table t2a pctfree 75 as select * from t1;
create table t2b pctfree 75 as select * from t1;

create index t2ai on t2a(id);
create index t2bi on t2b(id);

explain plan for
select
        t1.v1,
        t2.flag,
        t2.v1
from
        t1,
        (select 'a' flag, t2a.* from t2a
         union all
         select 'b', t2b.* from t2b
        )       t2
where
        t2.id = t1.n1
and     t1.id = 99
/

select * from table(dbms_xplan.display(null,null,'outline alias'))
/

There is one row with t1.id = 99, and I would like the optimizer to use an indexed access path to select the one matching row from each of the two tables in the union all view. The smart execution plan would be a nested loop using a “pushed join predicate” – and that’s exactly what we get by default with this data set:

-----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |     2 |    96 |    30   (4)| 00:00:01 |
|   1 |  NESTED LOOPS                          |      |     2 |    96 |    30   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                    | T1   |     1 |    19 |    26   (4)| 00:00:01 |
|   3 |   VIEW                                 |      |     1 |    29 |     4   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE          |      |       |       |            |          |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2A  |     1 |    15 |     2   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | T2AI |     1 |       |     1   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2B  |     1 |    15 |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN                  | T2BI |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1        / T1@SEL$1
   3 - SET$5715CE2E / T2@SEL$1
   4 - SET$5715CE2E
   5 - SEL$639F1A6F / T2A@SEL$2
   6 - SEL$639F1A6F / T2A@SEL$2
   7 - SEL$B01C6807 / T2B@SEL$3
   8 - SEL$B01C6807 / T2B@SEL$3

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$639F1A6F" "T2A"@"SEL$2")
      INDEX_RS_ASC(@"SEL$639F1A6F" "T2A"@"SEL$2" ("T2A"."ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B01C6807" "T2B"@"SEL$3")
      INDEX_RS_ASC(@"SEL$B01C6807" "T2B"@"SEL$3" ("T2B"."ID"))
      USE_NL(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      NO_ACCESS(@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      PUSH_PRED(@"SEL$1" "T2"@"SEL$1" 1)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$B01C6807")
      OUTLINE_LEAF(@"SEL$639F1A6F")
      ALL_ROWS
      OPT_PARAM('_nlj_batching_enabled' 0)
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."ID"=99)
   6 - access("T2A"."ID"="T1"."N1")
   8 - access("T2B"."ID"="T1"."N1")

So that worked well – operation 2 predicts one row for the tablescan of t1, with a nested loop join and union all pushed predicate where an index range scan of t2a_i1 and t2b_i1 gives us one row from each table. The “Predicate Information” tells us that the t1.n1 join predicate has been pushed inside the view to both subqueries so we see “t2a.id = t1.n1”, and “t2b.id = t1.n1”.

So what if I want to tell Oracle that it will actually find 5 rows in the t2a range scan and table access and 7 rows in the t2b range scan and table access (perhaps in a more complex view that would persuade Oracle to use two different indexes to get into the view and change the join order and access method for the next few tables it accessed). Since I’ve recently just written about the nlj_index_scan option for opt_estimate() you might think that this is the one we need to use – perhaps something like:

opt_estimate(@sel$639f1a6f nlj_index_scan, t2a@sel$2 (t1), t2a_i1, scale_rows=5)
opt_estimate(@sel$b01c6807 nlj_index_scan, t2b@sel$3 (t1), t2b_i1, scale_rows=7)

You’ll notice I’ve been very careful to find the fully qualified aliases for t2a and t2b by looking at the “Query Block Name / Object Alias” section of the plan (if the view appeared as a result of Oracle using Concatenation or OR-Expansion you would find that you got two query block names that looked similar but had suffixes of “_1” and “_2”). But it wasn’t worth the effort, it didn’t work. Fiddling around with all the possible variations I could think of didn’t help (maybe I should have used set$5715ce2e as the query block target for both the hints – no; what if I …)

Of course if we look at the “Outline Data” we’d notice that the use_nl() hint in the outline says: “USE_NL(@SEL$1 T2@SEL$1)”, so we don’t have a nested loop into t2a and t2b, we have a nested loop into the  view t2. So I decided to forget the nested loop idea and just go for the indexes (and the tables, when I got to them) with the following hints (you’ll notice that during the course of my experiments I added my own query block names to the initial query blocks – so the generated query block names have changed):

explain plan for
select
        /*+
                qb_name(main)
                opt_estimate(@sel$f2bf1101, index_scan, t2a@subq_a, t2ai, scale_rows=5)
                opt_estimate(@sel$f2bf1101, table,      t2a@subq_a,       scale_rows=5)
                opt_estimate(@sel$f4e7a233, index_scan, t2b@subq_b, t2bi, scale_rows=7)
                opt_estimate(@sel$f4e7a233, table,      t2b@subq_b,       scale_rows=7)
        */
        t1.v1,
        t2.flag,
        t2.v1
from
        t1,
        (select /*+ qb_name(subq_a) */ 'a' flag, t2a.* from t2a
         union all
         select /*+ qb_name(subq_b) */ 'b', t2b.* from t2b
        )       t2
where
        t2.id = t1.n1
and     t1.id = 99
;

select * from table(dbms_xplan.display(null,null,'outline alias'));


-----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |     2 |    96 |    30   (4)| 00:00:01 |
|   1 |  NESTED LOOPS                          |      |     2 |    96 |    30   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                    | T1   |     1 |    19 |    26   (4)| 00:00:01 |
|   3 |   VIEW                                 |      |     1 |    29 |     4   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE          |      |       |       |            |          |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2A  |     5 |    75 |     2   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | T2AI |     5 |       |     1   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2B  |     7 |   105 |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN                  | T2BI |     7 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Excellent – we get the cardinalities we want to see for the tables – except the view operator doesn’t hold the sum of the table cardinalities, and the join doesn’t multiply up the estimates either. I couldn’t find a way of getting the view to show 12 rows (not even with a guessed – but presumably unimplemented – opt_estimate(view …) hint!), however during the course of my experiments I tried the hint: “opt_estimate(@main, table, t2@main, scale_rows=15)”. This didn’t have any visible effect in the plan but while searching through the 10053 trace file I found the following lines:

Table Stats::
  Table: from$_subquery$_002  Alias: T2  (NOT ANALYZED)
  #Rows: 20000  SSZ: 0  LGR: 0  #Blks:  37  AvgRowLen:  15.00  NEB: 0  ChainCnt:  0.00  ScanRate:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 9

Access path analysis for from$_subquery$_002
    >> Single Tab Card adjusted from 20000.000000 to 300000.000000 due to opt_estimate hint

Access path analysis for from$_subquery$_002
    >> Single Tab Card adjusted from 12.000000 to 180.000000 due to opt_estimate hint

So at some point in the code path the optimizer is aware that 5 + 7 = 12, and that 12 * 15 = 180. But this doesn’t show up in the final execution plan. You might notice, by the way, that the scale_rows=15 has been applied NOT ONLY to the place where I was aiming – it’s also been applied to scale up the 20,000 rows that are estimated to be in the union all to 300,000 as the estimate for a tablescan of the two tables.

Possibly if I spent more time working through the 10053 trace file (which, as I’ve said before, I try to avoid doing) I might have found exactly which code path Oracle followed to get to the plan it produced and managed to tweak some hints to get the numbers I wanted to see. Possibly the optimizer was already following the code path that actually produced the numbers I wanted, then “forgot” to use them. One day, perhaps, I’ll tale another look at the problem – but since I wasn’t trying to solve a problem for a client (and given that there was an alternative workaround) I closed the 10053 trace file and put the model aside for a rainy day.

Footnote

One thought did cross my mind as a way of finding out if there was a real solution – and I offer this for anyone who wants to play: create a second data set that genuinely produces the 5 and 7 I want to see (and, check that the view reports the sum of the two components); then run the original query against the original data so that you’ve got the execution plan in memory, overwrite the original data with the new data set (without changing the statistics on the orginal). Then use the SQL Tuning Advisor to see if it produces a SQL profile for the captured SQL_ID that reproduces the correct plan for the second data set and check what opt_estimate() hints it uses.  (Warning – this may turn into a frustrating waste of time.)

A couple weeks back, Oracle and Microsoft announced their cross-cloud partnership.  This was wonderful news to me, as I’ve been working on numerous Oracle projects at Microsoft with Azure.

The Gist

To know that there is now a partnership between the two clouds and that there’s also a large amount of documentation about working between the two clouds is very helpful vs. the amount I’ve been working on based off just my knowledge.  Just as anyone appreciates a second set of eyes, I now have two company’s worth!

If you missed the announcement and curious what it’s about, Oracle has forged a partnership to do cross-cloud support to Azure for the non-database tier for many of their products.  These products include:

• Oracle E-business Suite
• Peoplesoft and
• JD Edwards
• Oracle Retail Merchandising Suite

There’s multiple support authentications for Ebiz to Azure.  The documentation linked about is for the Oracle Access Manager, but it offers you some insight to how in-depth Oracle and Microsoft have gone into making this a successful venture.

Oracle on Azure

Currently, most of my Oracle customers aren’t looking to leave Oracle-  they just want to use the Azure cloud to house the database instances.  This isn’t a difficult option, as I can build out what they need in Azure bare metal, (VMs) almost anything they require.  The list is long, (and not limited to):

• Oracle Databases 12c-18c, with hopefully 19c to soon follow
• Oracle Dataguard
• Oracle Enterprise Manager
• Oracle Golden Gate
• Oracle Essbase
• Oracle Hyperion
• Oracle Data Integrator
• APEX

I haven’t run into any databases that were too large for Azure to handle and for the one customer that was using over 1.7TB of memory, a quick AWR report granted me an opportunity to provide them with insight on how to eliminate a huge percentage of their memory, (and CPU) needs.

RAC

Yes, you can build Oracle RAC on Azure, but it requires a third party tool to support the software clustering such as FlashGrid and it’s not certified for support from Oracle.  Don’t fret though, because before ever going down this path, you should find out the business reason the customer is using RAC to begin with.  There is significant overhead to the software clustering and often the goal isn’t met by the product.

1. High Availability-  If the nodes all reside in one datacenter, does this meet HA?
2. Failover #1-  Many times neither the apps are able to reconnect when there is a failover
3. Failover #2- Due to the extra resource usage by each node/cluster, a failover can cause failure and evictions of more nodes
4. The Cloud- Azure possesses a number of HA features already built in and due to this, Oracle Dataguard will more than suffice over RAC

So this is part of my new world at Microsoft and I’ve foretold this for over a decade.  No matter what platform you choose, there is always some outlier that is mission critical that you need to manage.  With the introduction of the cloud, which creates easier access to other platforms and technology, our world just keeps getting smaller, only faster.  I’m alright with this and no, I won’t get bored this way.

Tags:  azure, Microsoft, oracle

Del.icio.us

Facebook

TweetThis

Digg

StumbleUpon