Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

DB links

A recent thread on the ODC SQL & PL/SQL forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It’s a good question as it’s fairly common knowledge that distributed queries can be very much slower than the same query when executed on an equivalent set of local tables, so it’s worth having a little poke around to see if there’s anything in the way the data moves across the link that could be tweaked to improve performance.

It’s fairly easy to get some ideas by running a couple of queries and checking session activity stats and wait events – so here’s a little test conducted between a database running 12.2.0.1 and a database running 11.2.0.4. For this type of testing it’s probably sensible to use two database rather than faking things with a loopback database link in case the loopback introduces some special features (a.k.a. dirty tricks).


rem
rem     Script:         db_link_usage.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2019
rem
rem     Last tested 
rem             12.2.0.1 -> 11.2.0.4
rem

prompt  ==================================
prompt  SQL*Plus 20,000 rows, arraysize 20
prompt  ==================================

set feedback only
set arraysize 20

execute snap_my_stats.start_snap
execute snap_events.start_snap
select * from all_objects@d11g where rownum <= 20000;

set feedback on
execute snap_events.end_snap
execute snap_my_stats.end_snap

prompt  ====================================
prompt  SQL*Plus 20,000 rows, arraysize 2000
prompt  ====================================

set feedback only
set arraysize 2000

execute snap_my_stats.start_snap
execute snap_events.start_snap
select * from all_objects@d11g where rownum <= 20000;

set feedback on
execute snap_events.end_snap
execute snap_my_stats.end_snap

All I’ve done is select 20,000 rows from view all_objects in a remote database with two different settings for the array fetch size. The “feedback only” option is a 12c option that means the session doesn’t output the data, it shows only the final “N rows selected” report. The two “snap” packages are some code I wrote a couple of decades ago to report changes in my session’s activity stats and wait events.

It turned out that there was very little of interest in the session activity stats although the impact of SQL*Net compression is always worth a quick reminder (here and here) on the other hand the wait events (specifically the wait counts) are most revealing:

================================== 
SQL*Plus 20,000 rows, arraysize 20
================================== 
Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
SQL*Net message from client                       1,004           0          15.69        .016     414,828
SQL*Net message to dblink                         1,004           0           0.04        .000           0
SQL*Net message from dblink                       1,004           0          61.02        .061          11
SQL*Net message to client                         1,004           0           0.11        .000           0

====================================
SQL*Plus 20,000 rows, arraysize 2000
====================================
Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
SQL*Net message from client                          14           0           4.88        .349     414,828
SQL*Net message to dblink                            13           0           0.00        .000           0
SQL*Net message from dblink                          13           0           7.72        .594          11
SQL*Net message to client                            14           0           0.00        .000           0

SQL*Net more data from dblink                        96           0           6.26        .065           6
SQL*Net more data to client                          96           0           0.16        .002           4

Doing a little arithmetic (and allowing for a few “metadata” messages that introduce small variations in the numbers), we can see that when we fetched the 20,000 rows with an arraysize of 20 this turned into 1,000 (fetch) calls from SQL*Plus to the server, with a matching 1,000 calls from the local server to the remote server. When the arraysize goes up to 2,000 rows, though, the SDU (session data unit) setting for SQL*Net is too small to hold the whole of a single fetch and we see a single fetch from SQL*Plus turning into one “SQL*Net message to client” accompanied by 7 or 8 “SQ:(Net more data to client” with exactly the same pattern of conversation between the local server and the remote server. You could imagine the conversation as something like:

  • Client to local server: “give me 2,000 rows”
  • local server to remote server: “give me 2,000 rows”
  • remote server to local server: “Here, I can manage 120 rows as the first installment”
  • local server to client: “Here’s a first installment”
  • Client to local server: “Got it, where’s the rest?”     *** but no message recorded in the session activity stats
  • Local server to remote server: “Where’s the rest?”
  • Remote server to local server: “You want more – here’s another 120 rows”
  • Local server to client: “Here’s a second installment”
  • Client to local server: “Got it, where’s the rest?”     *** but no message recorded in the session activity stats
  • etc.

In this example the volume of data sent back to the client in each message was limited by the SDU size negotiated between the local server and the remote server as the link opens. In my case I happened to have the default (apparently 8KB) as the SDU_SIZE in the 12c and a non-default 32KB setting in the 11g sqlnet.ora, so the negotiated SDU between servers was 8KB (the lower of the two).

Here’s what the figures looked like after I had restarted with the SDU set to 16KB in the 12c tnsnames.ora, and 16KB in the 11g listener.ora:


==================================
SQL*Plus 20,000 rows, arraysize 20
==================================
Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
SQL*Net message from client                       1,004           0          24.23        .024         588
SQL*Net message to dblink                         1,009           0           0.06        .000           0
SQL*Net message from dblink                       1,010           0          77.76        .077           4
SQL*Net message to client                         1,004           0           0.15        .000           0

====================================
SQL*Plus 20,000 rows, arraysize 2000
====================================
Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
SQL*Net message from client                          14           0           1.61        .115         588
SQL*Net message to dblink                            13           0           0.00        .000           0
SQL*Net message from dblink                          13           0           4.21        .324           4
SQL*Net message to client                            14           0           0.00        .000           0

SQL*Net more data from dblink                        45           0          13.53        .301           6
SQL*Net more data to client                          45           0           0.13        .003           0

The first set of figures (arraysize 20) don’t change. If an 8KB SDU is large enough to hold an array of 20 rows then it’s not going to make a difference when the SDU is increased. In the second set of figures we see that for each “SQL*Net message from dblink” we now have roughly 3 “SQL*Net more data from dblink” (with matching counts for “SQL*Net more data to client”). With an SDU of double the size it now takes a total of roughly 4 packets to transmit the array fetch rather than the 8 or 9 we saw with the smaller SDU size.

As a final test with SQL*Plus, what happens if we set the SDU size at 32K for the 12c database (and that means for the client calling in and the server calling out) and 8KB for the 11g database? The client negotiates a 32KB SDU with the 12c server, but the 12c server negotiates an 8KB SDU with the 11g server. Here are the stats for just the test with the large fetch size in this case:


====================================
SQL*Plus 20,000 rows, arraysize 2000
====================================
Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
SQL*Net message from client                          14           0           5.30        .379     214,570
SQL*Net message to dblink                            13           0           0.00        .000           0
SQL*Net message from dblink                          13           0           4.09        .314          13
SQL*Net message to client                            14           0           0.00        .000           0

SQL*Net more data from dblink                        96           0          14.46        .151           6
SQL*Net more data to client                          20           0           0.08        .004           0

We get the same 10 (plus a few) “message to/from client/dblink”, but now the “more data” waits are dramatically different. When the client calls for the “next fetch” the local server has to call the remote server 4 or 5 times to get enough 8KB data packets to fill a single 32KB packet to return to the client. You can confirm this (and some of my descriptions of the earlier behaviour) by enabling extended tracing for wait states. Here’s an extract of 5 consecutive lines from a suitable trace file (you have to ignore the “#bytes” parameter on the “SQL*Net more data from dblink” waits, they’ve always been garbage:


WAIT #140028098478296: nam='SQL*Net more data from dblink' ela= 2793 driver id=1413697536 #bytes=7 p3=0 obj#=-1 tim=703316387674
WAIT #140028098478296: nam='SQL*Net more data from dblink' ela= 34 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=703316388447
WAIT #140028098478296: nam='SQL*Net more data from dblink' ela= 8 driver id=1413697536 #bytes=2 p3=0 obj#=-1 tim=703316389134
WAIT #140028098478296: nam='SQL*Net more data from dblink' ela= 16 driver id=1413697536 #bytes=12 p3=0 obj#=-1 tim=703316389818
WAIT #140028098478296: nam='SQL*Net more data to client' ela= 73 driver id=1413697536 #bytes=32671 p3=0 obj#=-1 tim=703316389960

Summary Note

Oracle will use the negotiated SDU to do array fetches across a database link. Since a larger arraysize can (for large volumes of data) reduce the total work load on the remote database and on the network you may want the local server to have a specifically crafted tnsnames.ora entry and the remote server to expose a specific service with matching SDU size to help improve the efficiency of transferring data between two databases.

Lagniappe

If you want to look at other cases of how array fetching and SDU sizes interact, here are a couple of examples of using PL/SQL to execute SQL that fetches data across database links. The first is a simple, embedded “cursor for loop” that (apparently) does “row by row” procssing – although an enhancement appeared many versions ago to make this type of code use array fetching of 100 rows under the covers. The second demonstrates the effects of an explicit cursor with “fetch, bulk collect, limit”:


execute snap_events.start_snap

declare
        m_total number := 0;
        m_ct    number := 0;
begin
        for c1 in (select * from all_objects@d11g where rownum < 20000) loop
                m_total := m_total + c1.object_id;
                m_ct    := m_ct + 1;
        end loop;
end;
/

execute snap_events.end_snap

execute snap_events.start_snap

declare

        cursor c1 is  select * from all_objects@d11g where rownum <= 20000;

        type c1_array is table of c1%rowtype index by binary_integer;
        m_tab c1_array;

        m_ct number := 0;

begin
        open c1;
        loop
                fetch   c1
                bulk collect
                into    m_tab
                limit   1000
                ;

                exit when c1%notfound;
                m_ct := m_ct + 1;

        end loop;
        close c1;
end;
/

execute snap_events.end_snap

Checking the session events for these two test we see the following when the SDU_SIZE has been set at 16KB at both ends of the database link (which means the negotiated SDU will be 16KB):


Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
SQL*Net message from client                           4           0           0.66        .165         588 
SQL*Net message to dblink                           203           0           0.05        .000           0
SQL*Net message from dblink                         203           0          38.51        .190           5
SQL*Net message to client                             4           0           0.00        .000           0

Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
SQL*Net message from client                           4           0           0.08        .021         588
SQL*Net message to dblink                            24           0           0.01        .000           0
SQL*Net message from dblink                          24           0           9.19        .383           5
SQL*Net more data from dblink                        40           0           8.47        .212           6
SQL*Net message to client                             4           0           0.00        .000           0

The critical information we can see in the first example is that getting 20,000 rows from the remote database “row by row” takes 200 (plus noise) round-trips – showing that Oracle really is converting our single-row processing loop in array fetches of 100 rows.

The second example shows us that (allowing for a little noise, again) it takes 20 messages to the remote database to fetch 20,000 rows – so 1,000 rows per message – but the SDU size is too small and we have to send each array in three consecutive packets, the “message from dblink” wait and two “more data from dblink” waits.

Footnote

I did point out that the session activity stats for theses tests weren’t interesting. There is, however, one detail worth mentioning since you could otherwise be fooled into thinking the number of packet travelling between the databases was smaller than it really was. There are two statistics about network roundtrips:


Name                                         Value
----                                         -----
SQL*Net roundtrips to/from client               18         
SQL*Net roundtrips to/from dblink               13

A “roundtrip” is a “message to / message from” pair. It doesn’t account for traffic due to “more data” being requested or sent.

 

Same dog, different leash – functions in SQL

Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number is 10. Seems simple enough….but why do I get results for which the value of the second column is most certainly not 10?


SQL> with
  2    function
  3      getrand(pval in number) return number is
  4    begin
  5      return round(dbms_random.value(0,20));
  6    end;
  7  my_rnd as
  8    ( select object_id, getrand(object_id) getrnd from all_objects  )
  9  select *
 10  from my_rnd
 11  where getrnd = 10;
 12  /

 OBJECT_ID     GETRND
---------- ----------
        29          1
        53         10
        42          8
        30          3
        78         16
        87         18
        89          1
       145         12
       155         13
       175         15
       183         12
       198         15
       ...
       ...

This is just a new variation of the same old theme – determinism.

There is no guarantee when and how many times the database will execute a function within a single invocation of a SQL statement, or even how many times it will be executed for a single candidate row the SQL statement is processing. The optimizer is totally entitled to shift it to anywhere in the plan. What does this mean ? It means only deterministic functions make sense in SQL. Let’s model the above with a simpler example:


SQL> create table t as select * from dba_objects where rownum <= 20;

Table created.

SQL> create or replace
  2  function xgetrand(pval in number) return number is
  3    begin
  4      return round(dbms_random.value(0,20));
  5    end;
  6  /

Function created.

SQL> select *
  2  from (
  3    select object_id, xgetrand(object_id) gg from t
  4  )
  5  where gg = 10;

 OBJECT_ID         GG
---------- ----------
        38          2
         6          8

Immediately that looks odd, because the SQL reads like “I only want GG=10” yet I got back “2” and “8”. But if we drill into the execution plan, we’ll see this:


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     3 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("XGETRAND"("OBJECT_ID")=10)

Notice the FILTER information. The query has been transformed to be a simple:


SQL> select object_id, xgetrand(object_id) gg from t
  2  where xgetrand(object_id) = 10;

If you’re doubtful of that claim, we can validate it by dumping a 10053 trace. In that you’ll find the following:


Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."OBJECT_ID" "OBJECT_ID","XGETRAND"("T"."OBJECT_ID") "GG" FROM "T" "T" WHERE "XGETRAND"("T"."OBJECT_ID")=10

Already you can see the scope for the function being called twice per row – once for the WHERE clause, and once for the SELECT part. In fact, for all we know it could be called three times, or four times. You (the coder) does not have control over that decision. For the sake of discussion, let’s assume it is called only twice. The first execution of the function (on line 2 above) returned 10 twice across the 20 rows (because we got 2 rows back in the result set), but then we ran the function again (on line1) as we gave back each row, hence the counter-intuitive output.

In the original example, ALL_OBJECTS is being used as a source for rows which is a complex view. The function call could be pushed anywhere deep into this view, which means it might be executed tens of thousands of times, and many of those executions might return a result of “10”.

The bottom line remains the same: non-deterministic means trouble for functions in SQL.

Power BI Reporting with Oracle Essbase

This last week, I presented a few sessions at ODTUG’s KSCOPE 2019 conference in Seattle.  One of these sessions was with my wonderful co-presenter and Oracle Application Cloud expert, Opal Alapat.  I connected with her when the call for papers opened and asked her if there would be interest in doing a session with Power BI connect to the Essbase in the Oracle Applications cloud, (OAC).  There was no desire to do a bake-off or competitive session, just a morbid curiosity about what options there were to connect the two and the outcome of building reports out of valuable data with such a powerful BI tool.

https://dbakevlar.com/wp-content/uploads/2019/07/kscope19-300x128.jpg 300w" sizes="(max-width: 421px) 100vw, 421px" />

Opal agreed it was a good idea and as the conference chair, took it on to present the opportunity for us to present this to the conference committee.  It is understandable that it could be received poorly, considering the idea vendor history and a fear of blatant marketing sessions.  Lucky for us, we both have a long history of presenting strong technical sessions, a great love of Oracle technology, so this wonderful and brave conference gave us a chance to show our stuff.

The Technical World is Shrinking

I’ve taken on this type of new frontier before, realizing that few IT shops are truly single platform, so I thank them publicly for the opportunity and the attendees demonstrated their appreciation with a ton of questions during and even more so after the session.

We started the session making it clear that we would demonstrate both the Oracle Application Cloud, with sample data from Essbase connected to Oracle Analytics Cloud as well as Power BI.  We also stated we had no interest in a competitive bake- off but hoped to simply show the opportunities to provide more value with the data.

We both went into clear explanations around the architecture, requirements and licensing that would be required as part of our solution,  and since I was working in a cross-platform cloud solution, the hurdles I faced, options I attempted to work around them, what was successful, those that failed, etc.

The Use-Case

The OAC environment that Opal gave me access possessed an example schema/data based on an Audio-Video store revenue for multiple years.  I’d never worked with the OAC before, but I was quickly able to find five methods to connect Power BI to it, either to import or to direct connect to the data:

  1.  Data Direct makes a Rest API to connect to the Oracle Cloud.
  2.  Use the web interface with Power BI and “scrape the website”, allowing PBI to build the table from example.
  3.  Connect with an ODBC driver
  4.  Via the OAC interface, export to Excel and then import into Power BI
  5.  Via the OAC interface, export to a table and then import into Power BI as a CSV file.

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

Rest API

Although #1 may sound like a great option, the Rest API ran via a docker container or dedicated server to run the service.  I have the container running on my laptop, but along with a more complicated setup, the connection dropped intermittently and the container crashed from time to time.

Web “Scraping”

Connecting to the website to scrape the data isn’t difficult, as Power BI has the ability to bypass the HTML and simply build out the table by example.  You enter in a few examples of what data you want it to build a table out of and it’s quite intuitive and will take it from there.  I had made a goal to have a repeatable, automated refresh option and with the authentication to OAC, this make web scraping less attractive.

ODBC Driver

This was the option that I thought, and in my research, appeared to be the most viable and failed terribly.  The authentication method to OAC and steps that Oracle has taken to use preferred tools has made ODBC the least simple method of connecting to the data and I chose to give up vs. working towards a functioning test.

Export to Excel

The export to Excel offers a cube format for those striving for:

  1.  the cube “feel” when modeling
  2.  want to use power pivot or more familiar with Excel than Power BI

As I’m definitely a RDBMS DBA/dev over an Excel person, this wasn’t my preference.

Export to table

This turned out to be my favorite.  The export was a simple table, in a CSV format that was easy to import into Power BI and easy to build visuals and do predictive analysis on.  The table was straight forward and required no formatting or massaging once brought into Power BI.  To build out a model that could be updated on a regular basis, I was able to schedule a job to export the table on a regular interval to a shared folder and then use a simple shell script to refresh it, renaming the file first to a generic name vs. the unique name that is be default.  I then updated the data source settings to point to the shared folder, which means no manual processing once the reports were built.

https://dbakevlar.com/wp-content/uploads/2019/07/kscope4-300x181.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/07/kscope4-768x465.jpg 768w, https://dbakevlar.com/wp-content/uploads/2019/07/kscope4.jpg 1091w" sizes="(max-width: 407px) 100vw, 407px" />

Huge Win-Win

To complete my demonstration, I went through my Power BI reports, both the design and the row level security by region and user, as well as how easy it was, due to the data structure, for me to build out visualizations with predictive analysis.  This was really crucial since I don’t have a background or any experience with the data that was provided to me.  I was simply the report designer and let Power BI do the heavy lifting.

Take-Aways from the Session

  1.  Attendees are often using Power BI with these Oracle data solutions, but have very little reference material on how to be successful with it.
  2. The ability to direct connect via the rest API was a bit shaky and complicated to set up, where the ability to automate the export to flat file option, (either cube or table) was the most stable.
  3. ”Web Scraping” using and using the build by example and then creating templates to  automate the data massage is possible with Essbase, but does require manual refreshes due to credential authentication.
  4. The reports in Power BI were robust, multi-tab reports and dashboards was available with even simple data.  The predictive analysis and quantity of visualizations allowed the user to build out expanded reports, both interactive, as well as paginated.
  5. For those that have on-premises Essbase, the Essbase connector is available for download, (brand new!) and can be used for those environments with Power BI.

Sample Reports

One of the great things about the use case from Opal and my session, is that the data is all sample data and can be made available to everyone, demonstrating how easy it is to get value by anyone, even someone like me who’s never worked with it before!

If you’re interested in seeing a high level example of the reports created, I’ve added a screenshots of the report here:

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

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

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

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

Now each of the attendees for KSCOPE have access to the PBIX files with sample data to work with, including the cube example.  All the reports, unlike the screenshots, are interactive, allowing the user to dig into the data and assign role level security to those using the report depending on region and login name, (this role would have to be updated if using something than Azure Active Directory to log in, of course.)

Have a happy 4th of July everyone!!

 



Tags:  , , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBAKevlar [Power BI Reporting with Oracle Essbase], All Right Reserved. 2019.

Oracle Refresh Group consistency with nested materialized views.

A Refresh Group can be used to refresh a list of materialized views and ensures that all data is read with transaction consistency. This post is about the meaning of consistency when one materialized view depends on the other one. That’s just a quick test to understand the behavior. Any comment is welcome (preferably on twitter — @FranckPachot)

Documentation

The documentation mentions:

To preserve referential integrity and transactional (read) consistency among multiple materialized views, Oracle Database can refresh individual materialized views as part of a refresh group. After refreshing all of the materialized views in a refresh group, the data of all materialized views in the group correspond to the same transactionally consistent point in time.

Materialized View Concepts and Architecture

But what does that mean when one materialized view depends on the other? This can happen when we do a detailed aggregate, and then build finer aggregates from them. What I would like in this case is all materialized views showing data as it were at the same point-in-time in the original tables. But is that possible? Here is a little test.

Demo

I create a DEMO table where I’ll store a sequence number and a timestamp:

SQL> connect demo/demo@//localhost/PDB1
Connected.
SQL> set echo on
SQL> create table DEMO as select 0 seq,current_timestamp now
from xmltable('1 to 1000');
Table created.

I run a background job that will constantly increase the sequence number and update the timestamp:

SQL> set escape on
SQL> host { echo "set echo on" ; echo "whenever sqlerror exit failure" ; echo "exec loop update demo set seq=seq+1,now=current_timestamp where rownum=1; commit; end loop;" ; } | sqlplus -s demo/demo@//localhost/PDB1 \& :
SQL> host sleep 1;

This is a loop that updates at high rate, so do it in a noarchivelog database.

Now that I have this constantly changing table, I create a first materialized view DEMOMV1 which is just a select on DEMO:

12:17:07 SQL> create materialized view DEMOMV1 
as select * from DEMO;
Materialized view created.
12:17:07 SQL> select max(seq),max(now) 
from DEMOMV1;
   MAX(SEQ)                               MAX(NOW)
___________ ______________________________________
21393 01-JUL-19 12.17.07.221922000 PM GMT

This mview contains the original data as-of its creation time: SEQ=21393

And then I create DEMOMV2 that queries both the DEMO table and the DEMOMV1 materialized view, to show the consistency easily:

12:17:07 SQL> create materialized view DEMOMV2 as
select 'MVIEW' source,seq,now from DEMOMV1
union all
select 'TABLE' source,seq,now from DEMO;
Materialized view created.
12:17:07 SQL> select source,max(seq),max(now) 
from DEMOMV2 group by source;
SOURCE MAX(SEQ) MAX(NOW)
_________ ___________ ______________________________________
MVIEW 21393 01-JUL-19 12.17.07.221922000 PM GMT
TABLE 22099 01-JUL-19 12.17.07.336869000 PM GMT

I see the original data as-of two point in times:

  • DEMO table was read at the time of the creation of this DEMOMV2 mview, which contains data at this point-in-time where SEQ was 22099
  • DEMOMV1 was read at the time of the creation of this DEMOMV2 mview, which contain data from the last refresh of DEMOMV1, which means the original data when SEQ was 21393

So far so good, this is exactly what I expected. The refreshes were done one after the other. I cannot expect to see the original data as of the same point-in-time.

Refresh Group

Here is the creation of the refresh group:

12:17:07 SQL> exec dbms_refresh.make('DEMO.DEMORGROUP', list=>'DEMOMV1,DEMOMV2', next_date=>sysdate+100, interval=>'null');
PL/SQL procedure successfully completed.

and the current state of my tables:

12:17:07 SQL> select max(seq),max(now) from DEMO;
   MAX(SEQ)                               MAX(NOW)
___________ ______________________________________
22214 01-JUL-19 12.17.07.377349000 PM GMT
12:17:07 SQL> select max(seq),max(now) from DEMOMV1;
   MAX(SEQ)                               MAX(NOW)
___________ ______________________________________
21393 01-JUL-19 12.17.07.221922000 PM GMT
12:17:07 SQL> select source,max(seq),max(now) from DEMOMV2 group by source;
   SOURCE    MAX(SEQ)                               MAX(NOW)
_________ ___________ ______________________________________
MVIEW 21393 01-JUL-19 12.17.07.221922000 PM GMT
TABLE 22099 01-JUL-19 12.17.07.336869000 PM GMT
12:17:07 SQL> select max(seq),max(now) from DEMO;
   MAX(SEQ)                               MAX(NOW)
___________ ______________________________________
22241 01-JUL-19 12.17.07.386421000 PM GMT

DEMO had some changes, but the materialized views are still at the same state as they were not refreshed.

Now refreshing the group (I enable some traces before):

12:17:07 SQL> alter session set tracefile_identifier=rgroup
events='10979 trace name context forever, level 1
: 10046 trace name context forever, level 3';
Session altered.
12:17:07 SQL> exec dbms_refresh.refresh('DEMO.DEMORGROUP');
PL/SQL procedure successfully completed.

DEMO has newer sequence:

12:17:07 SQL> select max(seq),max(now) from DEMO;
   MAX(SEQ)                               MAX(NOW)
___________ ______________________________________
22713 01-JUL-19 12.17.07.532700000 PM GMT

Then I expect the materialize views to have been refreshed when DEMO was at a point where SEQ was between 22214 and 22713.

12:17:07 SQL> select max(seq),max(now) from DEMOMV1;
   MAX(SEQ)                               MAX(NOW)
___________ ______________________________________
22507 01-JUL-19 12.17.07.464718000 PM GMT

That’s right, this refresh of DEMOMV1 occurred when SEQ was 22507.

Now the big question is about DEMOMV2:

12:17:07 SQL> select source,max(seq),max(now) 
from DEMOMV2 group by source;
   SOURCE    MAX(SEQ)                               MAX(NOW)
_________ ___________ ______________________________________
MVIEW 22507 01-JUL-19 12.17.07.464718000 PM GMT
TABLE 22610 01-JUL-19 12.17.07.499890000 PM GMT

Same as when I did the refreshes manually, one by one: DEMOMV2 was refreshed after DEMOMV1 and sees DEMOMV1 as-of this point in time where SEQ was 22507. And data from the original table is fresher than that: SEQ was 22610 when DEMOMV2 was refreshed.

This means that if you build aggregates on top of aggregates, while the base table is changing, then the summaries will not show the aggregate data as-of the same point-in-time. It is perfectly normal that they are stale, but they don’t have the same staleness because they were nested.

Flashback query?

For a real consistency, I would have expected that DEMOMV2 had read DEMO as-of the same point-in-time as the other mviews in the refresh group. But here it behaved like a read-commited isolation level transaction: consistency is at statement level.

We can think of Flashback Query in this case. This would be fine to read DEMO. But reading DEMOMV1 with flashback query would ignore the changes done in the transaction, and I want to read the refreshed DEMOMV1 or I’ll get values from the previous refresh.

Anyway, flashback mode is disallowed here:

ORA-08182: operation not supported while in Flashback mode

Refresh order

We do not define any order when we add a materialized view to a refresh group. And, as we have seen, the order matters when one mview reads another one. The refresh group takes care of the dependencies as we can see in the 10979 trace:

The ‘Sched. In’ and ‘Out’ link the dependencies. Here DEMOMV2 (which is #2 in the group) takes input from DEMOMV1 (which is #1) and then is scheduled after it.

Actually, in this example, the refresh works exactly as if I had refreshed DEMOMV2 with the nested option, triggering the refresh of the other mviews it depends on, like:

SQL> exec dbms_mview.refresh('DEMO.DEMOMV2',nested=>true);

Serializable

Finally, what I would have expected here is that the refresh was done in a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE so that the refresh of DEMOMV2 sees DEMO at the same query SCN as when DEMOMV1 was refreshed, and sees DEMOMV1 as refreshed (uncommitted changes visible from the current transaction). Here is how it should work in my opinion, showing it with normal tables:

12:07:14 SQL> set transaction isolation level serializable;
Transaction succeeded.
12:07:14 SQL> delete from DEMOT1;
1 row deleted.
12:07:14 SQL> insert into DEMOT1  select * from DEMO;
1 row created.
12:07:14 SQL> delete from DEMOT2;
2 rows deleted.
12:07:14 SQL> insert into DEMOT2 
select 'MVIEW' source,seq,now from DEMOT1
union all
select 'TABLE' source,seq,now from DEMO;
2 rows created.
12:07:14 SQL> select max(seq),max(now) from DEMO;
   MAX(SEQ)                               MAX(NOW)
___________ ______________________________________
2023485 02-JUL-19 12.07.14.394374000 PM GMT
12:07:14 SQL> select max(seq),max(now) from DEMOT1;
   MAX(SEQ)                               MAX(NOW)
___________ ______________________________________
2023485 02-JUL-19 12.07.14.394374000 PM GMT
12:07:14 SQL> select source,max(seq),max(now) from DEMOT2 group by source;
   SOURCE    MAX(SEQ)                               MAX(NOW)
_________ ___________ ______________________________________
MVIEW 2023485 02-JUL-19 12.07.14.394374000 PM GMT
TABLE 2023485 02-JUL-19 12.07.14.394374000 PM GMT

This is what I would like to do with a materialized view refresh, but unfortunately:

ORA-08177: can’t serialize access for this transaction

This failed in the refresh of DEMOMV1:

/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "DEMO"."DEMOMV1" select * from DEMO

About the reason, it doesn’t work as I would expect it, my guess is that it has something to do with: the fact that a refresh is somehow considered DDL:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=refresh-on-materialized-view#152095200346634471

So… be careful if you have materialized views on top on the other, and want a consistent refresh, and the base tables are changing. And test it. The consistency issues are always discovered too late in production. Do you have UAT checking that?

Using DbVisualizer to work with #Oracle, #PostgreSQL and #Exasol

As a Database Developer or Database Administrator, it becomes increasingly unlikely that you will work with only one platform.

It’s quite useful to have one single tool to handle multiple different database platforms. And that’s exactly the ambition of DbVisualizer.

As a hypothetical scenario, let’s assume you are a database admin who works on a project to migrate from Oracle to EDB Postgres and Exasol.

The goal might be to replace the corporate Oracle database landscape, moving the OLTP part to EDB Postgres and the DWH / Analytics part to Exasol.

Instead of having to switch constantly between say SQL Developer, psql and EXAplus, a more efficient approach would be using DbVisualizer for all three.

I created one connection for each of the three databases here for my demo:https://uhesse.files.wordpress.com/2019/07/dbviz1.png?w=150&h=71 150w, https://uhesse.files.wordpress.com/2019/07/dbviz1.png?w=300&h=143 300w, https://uhesse.files.wordpress.com/2019/07/dbviz1.png?w=768&h=365 768w, https://uhesse.files.wordpress.com/2019/07/dbviz1.png 809w" sizes="(max-width: 620px) 100vw, 620px" />Now let’s see if statements I do in Oracle also work in EDB Postgres and in Exasol:

https://uhesse.files.wordpress.com/2019/07/dbviz2.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2019/07/dbviz2.png?w=300&h=118 300w, https://uhesse.files.wordpress.com/2019/07/dbviz2.png?w=768&h=302 768w, https://uhesse.files.wordpress.com/2019/07/dbviz2.png 956w" sizes="(max-width: 620px) 100vw, 620px" />

Oracle

https://uhesse.files.wordpress.com/2019/07/dbviz3.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2019/07/dbviz3.png?w=300&h=116 300w, https://uhesse.files.wordpress.com/2019/07/dbviz3.png?w=768&h=298 768w, https://uhesse.files.wordpress.com/2019/07/dbviz3.png 951w" sizes="(max-width: 620px) 100vw, 620px" />

EDB

https://uhesse.files.wordpress.com/2019/07/dbviz4.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2019/07/dbviz4.png?w=300&h=116 300w, https://uhesse.files.wordpress.com/2019/07/dbviz4.png?w=768&h=296 768w, https://uhesse.files.wordpress.com/2019/07/dbviz4.png 954w" sizes="(max-width: 620px) 100vw, 620px" />

Exasol

Works the same for all three! The convenient thing here is that I just had to select the Database Connection from the pull down menu while leaving the statement as it is. No need to copy & paste even.

What about schemas and tables?

https://uhesse.files.wordpress.com/2019/07/dbviz5.png?w=150&h=57 150w, https://uhesse.files.wordpress.com/2019/07/dbviz5.png?w=300&h=114 300w, https://uhesse.files.wordpress.com/2019/07/dbviz5.png?w=768&h=292 768w, https://uhesse.files.wordpress.com/2019/07/dbviz5.png 818w" sizes="(max-width: 620px) 100vw, 620px" />

Oracle

In EDB, I need to create a schema accordingly:

https://uhesse.files.wordpress.com/2019/07/dbviz6.png?w=150&h=67 150w, https://uhesse.files.wordpress.com/2019/07/dbviz6.png?w=300&h=134 300w, https://uhesse.files.wordpress.com/2019/07/dbviz6.png 696w" sizes="(max-width: 620px) 100vw, 620px" />

EDB

 

In Exasol, schema and table can be created in the same way:

https://uhesse.files.wordpress.com/2019/07/dbviz7.png?w=150&h=72 150w, https://uhesse.files.wordpress.com/2019/07/dbviz7.png?w=300&h=143 300w, https://uhesse.files.wordpress.com/2019/07/dbviz7.png 740w" sizes="(max-width: 620px) 100vw, 620px" />

Exasol

Notice that the data types got silently translated into the proper Exasol data types:

https://uhesse.files.wordpress.com/2019/07/dbviz8.png?w=150 150w, https://uhesse.files.wordpress.com/2019/07/dbviz8.png?w=300 300w" sizes="(max-width: 608px) 100vw, 608px" />

Exasol

There is no DBA_TABLES in Exasol, though:

https://uhesse.files.wordpress.com/2019/07/dbviz9.png?w=150&h=44 150w, https://uhesse.files.wordpress.com/2019/07/dbviz9.png?w=300&h=88 300w, https://uhesse.files.wordpress.com/2019/07/dbviz9.png?w=768&h=225 768w, https://uhesse.files.wordpress.com/2019/07/dbviz9.png 801w" sizes="(max-width: 620px) 100vw, 620px" />

Exasol

Of course, there’s much more to check and test upon migration, but I think you got an idea how a universal SQL Client like DbVisualizer might help for such purposes.

 

opt_estimate 4

In the previous article in this series on the opt_estimate() hint I mentioned the “query_block” option for the hint. If you can identify a specify query block that becomes an “outline_leaf” in an execution plan (perhaps because you’ve deliberately given an query block name to an inline subquery and applied the no_merge() hint to it) then you can use the opt_estimate() hint to tell the optimizer how many rows will be produced by that query block (each time it starts). The syntax of the hint is very simple:


opt_estimate(@{query block name}  query_block  rows={number of rows})

As with other options for the hint, you can use scale_rows=, min=, max= as alternatives (the last seems to be used in the code generated by Oracle for materialized view refreshes) but the simple “rows=N” is likely to be the most popular. In effect it does the same as the “non-specific” version of the cardinality() hint – which I’ve suggested from time to time as a way of telling the optimizer the size of a data set in a materialized CTE (“with” subquery), e.g.


set serveroutput off

with demo as (
        select  /*+
                        qb_name(mat_cte)
                        materialize
                        cardinality(@mat_cte 11)
--                      opt_estimate(@mat_cte query_block rows=11)
                */
                distinct trunc(created)    date_list
        from    all_objects
)
select  * from demo
;

select * from table(dbms_xplan.display_cursor);
    

Regardless of whether you use the opt_estimate() or cardinality() hint above, the materialized temporary table will be reported with 11 rows. (Note that in this case where the hint is inside the query block it applies to the “@mat_cte” isn’t necessary).

In the previous article I generated some data with a script called opt_est_gby.sql to show you the effects of the group_by and having options of the opt_estimate() hint and pointed out that there were case where you might also want to include the query_block option as well. Here’s a final example query showing the effect, with the scale_rows feature after creating a table t2 as a copy of t1 but setting pctfree 75 (to make a tablescan more expensive) and creating an index on t2(id):


create table t2 pctfree 75 as select * from t1;
create index t2_i1 on t2(id);

select
        t2.n1, t1ct
from
        t2,
        (
        select  /*+
                        qb_name(main)
                        opt_estimate(@main group_by scale_rows=4)
                        opt_estimate(@main having scale_rows=0.4)
                        opt_estimate(@main query_block scale_rows=0.5)
                */
                mod(n1,10), count(*) t1ct
        from    t1
        group by
                mod(n1,10)
        having
                count(*) > 100
        ) v1
where
        t2.id = v1.t1ct
;

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     8 |   168 |    27   (8)| 00:00:01 |
|   1 |  NESTED LOOPS                |       |     8 |   168 |    27   (8)| 00:00:01 |
|   2 |   NESTED LOOPS               |       |     8 |   168 |    27   (8)| 00:00:01 |
|   3 |    VIEW                      |       |     8 |   104 |    10  (10)| 00:00:01 |
|*  4 |     FILTER                   |       |       |       |            |          |
|   5 |      HASH GROUP BY           |       |     8 |    32 |    10  (10)| 00:00:01 |
|   6 |       TABLE ACCESS FULL      | T1    |  3000 | 12000 |     9   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | T2_I1 |     1 |       |     1   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |     8 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(COUNT(*)>100)
   7 - access("T2"."ID"="V1"."T1CT")


I’ve inlined the last query (with the two opt_estimate() hints) that I used in the previous article, and added a third opt_estimate() hint to that inline view. In this case I didn’t have to add a no_merge() hint because the numbers worked in my favour but to be safe in a production environment that’s a hint that I should have included.

You may recall that the hash group by on its own resulted in a prediction of 200 rows, and with the having clause the prediction dropped to 10 rows (standard 5%). With my three opt_estimate() hints in place I should see the effects of the following arithmetic:


group by      200       * 4   = 800
having        5% of 800 * 0.4 =  16
query block   16        * 0.5 =   8

As you can see, the cardinality prediction for the VIEW operation is, indeed, 8 – so the combination of hints has worked. It’s just a shame that we can’t see the three individual steps in the arithmetic as we walk the plan.

A Warning

As always I can only repeat – hinting is not easy; and “not easy” usually translates to “not stable / not safe” (and thanks to a Freudian slip while typing: “not sage”. You probably don’t know how do it properly, except in the very simplest cases, and we don’t really know how Oracle is interpreting the hints (particularly the undocumented ones). Here’s an example of how puzzling even the opt_estimate(query_block) hint can be – as usual starting with some data:

rem
rem     Script:         opt_estimate_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Aug 2017
rem

create table t1
as
select * from all_objects;

create table t2
as
select * from all_objects;

As you can see, I’ve been a bit lazy with this example (which I wrote a couple of years ago) and it uses all_objects as a convenient source of data. Unfortunately this means you won’t necessarily be able to reproduce exactly the results I’m about to show you, which I did on a small instance of 12.2.0.1. I’m going to examine four versions of a simple query which

  • restricts the rows from t1,
  • finds the unique set of object_types in that subset of t1
  • then joins to t2 by object_type

select
        /*+ 
                qb_name(main)
        */
        t2.object_id, t2.object_name, created
from    (
        select  /*+ qb_name(inline) */
                distinct object_type
        from    t1 
        where 
                created >= date'2017-03-01' 
        )       v1,
        t2
where
        t2.object_type = v1.object_type
;


select
        /*+ 
                qb_name(main)
                merge(@inline)
        */
        t2.object_id, t2.object_name, created
from    (
        select  /*+ qb_name(inline) */
                distinct object_type
        from    t1 
        where 
                created >= date'2017-03-01' 
        )       v1,
        t2
where
        t2.object_type = v1.object_type
;


select
        /*+ 
                qb_name(main)
                opt_estimate(@inline query_block rows=14)
        */
        t2.object_id, t2.object_name, created
from    (
        select  /*+ qb_name(inline) */
                distinct object_type
        from    t1 
        where 
                created >= date'2017-03-01' 
        )       v1,
        t2
where
        t2.object_type = v1.object_type
;


select
        /*+ 
                qb_name(main)
                merge(@inline)
                opt_estimate(@inline query_block rows=14)
        */
        t2.object_id, t2.object_name, created
from    (
        select  /*+ qb_name(inline) */
                distinct object_type
        from    t1 
        where 
                created >= date'2017-03-01' 
        )       v1,
        t2
where
        t2.object_type = v1.object_type
;

The first version is my unhinted baseline (where, in my case, Oracle doesn’t use complex view merging), the second forces complex view merging of the inline aggregate view, then queries 3 and 4 repeat queries 1 and 2 but tell the optimizer that the number of distinct object_type values  is 14 (roughly half the actual in may case). But there is an oddity in the last query – I’ve told the optimizer how many rows it should estimate for the inline view but I’ve also told it to get rid of the inline view and merge it into the outer query block; so what effect is that going to have? My hope would be that the hint would have to be ignored because it’s going to apply to a query block that doesn’t exist in the final plan and that makes it irrelevant and unusable. Here are the four execution plans:


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 61776 |  4464K|   338   (7)| 00:00:01 |
|*  1 |  HASH JOIN           |      | 61776 |  4464K|   338   (7)| 00:00:01 |
|   2 |   VIEW               |      |    27 |   351 |   173   (9)| 00:00:01 |
|   3 |    HASH UNIQUE       |      |    27 |   486 |   173   (9)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| T1   | 59458 |  1045K|   164   (4)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | T2   | 61776 |  3680K|   163   (4)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."OBJECT_TYPE"="V1"."OBJECT_TYPE")
   4 - filter("CREATED">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


--------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           | 61776 |  5308K|       |  1492   (2)| 00:00:01 |
|   1 |  VIEW                  | VM_NWVW_1 | 61776 |  5308K|       |  1492   (2)| 00:00:01 |
|   2 |   HASH UNIQUE          |           | 61776 |  5489K|  6112K|  1492   (2)| 00:00:01 |
|*  3 |    HASH JOIN RIGHT SEMI|           | 61776 |  5489K|       |   330   (5)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL  | T1        | 59458 |  1045K|       |   164   (4)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | T2        | 61776 |  4403K|       |   163   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T2"."OBJECT_TYPE"="OBJECT_TYPE")
   4 - filter("CREATED">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 32032 |  2314K|   338   (7)| 00:00:01 |
|*  1 |  HASH JOIN           |      | 32032 |  2314K|   338   (7)| 00:00:01 |
|   2 |   VIEW               |      |    14 |   182 |   173   (9)| 00:00:01 |
|   3 |    HASH UNIQUE       |      |    14 |   252 |   173   (9)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| T1   | 59458 |  1045K|   164   (4)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | T2   | 61776 |  3680K|   163   (4)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."OBJECT_TYPE"="V1"."OBJECT_TYPE")
   4 - filter("CREATED">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


--------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |    14 |  1232 |       |  1492   (2)| 00:00:01 |
|   1 |  VIEW                  | VM_NWVW_1 |    14 |  1232 |       |  1492   (2)| 00:00:01 |
|   2 |   HASH UNIQUE          |           |    14 |  1274 |  6112K|  1492   (2)| 00:00:01 |
|*  3 |    HASH JOIN RIGHT SEMI|           | 61776 |  5489K|       |   330   (5)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL  | T1        | 59458 |  1045K|       |   164   (4)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | T2        | 61776 |  4403K|       |   163   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T2"."OBJECT_TYPE"="OBJECT_TYPE")
   4 - filter("CREATED">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

The first plan tells us that most of the rows in t1 have created > 1st March 2017 and there are (estimated) 27 distinct values for object_type; and there are 61,776 rows in t2 (which is basically the same as t1), and none of them are eliminated by the join on object_type from the inline view.

The second plan (with the forced complext view merging) shows Oracle changing the view with “distinct” into a (right) semi-join between t2 and t1 with the internal view name of VM_NWVW_1 – and the cardinality is correct.

The third plan shows that my hint telling the optimizer to assume the original inline view produces 14 rows has been accepted and, not surprisingly, when we claim that we have roughly half the number of object_type values the final estimate of rows in the join is roughly halved.

So what happens in the fourth plan when our hint applies to a view that no longer exists? I think the optimizer should have discarded the hint as irrelevant the moment it merged the view. Unfortunately it seems to have carried the hint up into the merged view and used it to produce a wildly inaccurate estimate for the final cardinality. If this had been a three-table join this is the sort of error that could make a sensible hash join into a third table become an unbelievably stupid nested loop join. If you had thought you were doing something incredibly clever with (just) the one opt_estimate() hint, the day might come when a small change in the statistics resulted in the optimizer using a view merge strategy you’d never seen before and producing a catastrophic execution plan in (say) an overnight batch that then ran “forever”.

Hinting is hard, you really have to be extremely thorough in your hints and make sure you cover all the options that might appear. And then you might still run into something that looks (as this does) like a bug.

Footnote

Here’s a closing thought: even if you manage to tell the optimizer exactly how many rows will come out of a query block to be joined to the next table in the query, you may still get a very bad plan unless you can also tell the optimizer how many distinct values of the join column(s) there are in that data set. Which means you may also have to learn all about the (even more undocumented) column_stats() hint.

 

MERGE and ORA-8006

I’m sure there will be a slew of post-Kscope wrap up posts coming out into the blogosphere, so in lieu of that, and the the fact that I’m just stuck in an airport waiting for a flight, I’ll offer something slightly more technical. I did a post a while back about a curious error “unable to get a stable set of rows” when using MERGE. Here is another variant which can occur when you allow rows to physically move during a MERGE.

How is that possible?” I hear you ask. Easy. All we need is partitioned table with ENABLE ROW MOVEMENT.



SQL> create table t (pk number primary key, x number)
  2      partition by list (pk)
  3      (partition p1 values(1),
  4       partition p2 values(2)
  5      )
  6  enable row movement;

Table created.

SQL>
SQL> insert into t values (1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> merge into t
  2  using (select 1 idx, 2 new_val from dual
  3         connect by level <= 2
  4        ) u
  5  on (t.x = u.idx)
  6  when matched then
  7    update set pk=new_val;
merge into t
           *
ERROR at line 1:
ORA-08006: specified row no longer exists

You can see that the merge is going to first change the PK column values from 1 to 2, which will move the row from one partition to another. The second row from the source (CONNECT BY LEVEL <= 2) will go hunting for that original value of 1 in its original partition and not find it there. Hence it “no longer exists”.

This is just another example of why you might want to consider cleansing input data for sensibility before aimlessly throwing it at a MERGE command.

Hi Vivek, Right, but in a mixed workload, reporting is done at the same time as OLTP.

Hi Vivek,
Right, but in a mixed workload, reporting is done at the same time as OLTP. Parallel Query is ok (when resource manager manages to keep resources for the OLTP) but PDML blocks all modification on their target tables and all tables linked to them by referential integrity. It is more than a “reporting” service. It is more like an “I am alone on these tables” service.

Hi Gg,

Hi Gg,

Thanks for the feedback.
The reason is documented in https://support.oracle.com/epmos/faces/DocContentDisplay?id=2433906.1
parallel processes have their own transaction and coordinator uses 2PC to commit. Concurrent transactions could mess-up everything.

opt_estimate 3

This is just a quick note to throw out a couple of of the lesser-known options for the opt_estimate() hint – and they may be variants that are likely to be most useful since they address a problem where the optimizer can produce consistently bad cardinality estimates. The first is the “group by” option – a hint that I once would have called a “strategic” hint but which more properly ought to be called a “query block” hint. Here’s the simplest possible example (tested under 12.2, 18.3 and 19.2):


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

create table t1
as
select
        rownum                  id,
        mod(rownum,200)         n1,
        lpad(rownum,10,'0')     v1,
        rpad('x',100)           padding
)
from
        dual
connect by
        level <= 3000
;

set autotrace on explain

prompt  =============================
prompt  Baseline cardinality estimate
prompt  (correct cardinality is 10)
prompt  Estimate will be 200
prompt  =============================

select  /*+
                qb_name(main)
        */
        mod(n1,10), count(*) 
from    t2 
group by 
        mod(n1,10)
;

I’ve generated a table of 3,000 rows with a column n1 holding 15 rows each of 200 distinct values. The query then aggregates on mod(n1,10) so it has to return 10 rows, but the optimizer doesn’t have a mechanism for inferring this and produces the following plan – the Rows value from the HASH GROUP BY at operation 1 is the only thing we’re really interested in here:


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   200 |   800 |    10  (10)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |   200 |   800 |    10  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  3000 | 12000 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

It looks as if the optimizer’s default position is to use num_distinct from the underlying column as the estimate for the aggregate. We can work around this in the usual two ways with an opt_estimate() hint. First, let’s tell the optimizer that it’s going to over-estimate the cardinality by a factor of 10:


select  /*+
                qb_name(main)
                opt_estimate(@main group_by, scale_rows = 0.1)
        */
        mod(n1,10), count(*) 
from    t1 
group by 
        mod(n1,10)
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    20 |    80 |    10  (10)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |    20 |    80 |    10  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  3000 | 12000 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

The hint uses group_by as the critical option parameter, and then I’ve used the standard scale_rows=nnn to set a scaling factor that should be used to adjust the result of the default calculation. At 10% (0.1) this gives us an estimate of 20 rows.

Alternatively, we could simply tell the optimizer how many rows we want it to believe will be generated for the aggregate – let’s just tell it that the result will be 10 rows.

select  /*+
                qb_name(main)
                opt_estimate(@main group_by, rows = 10)
        */
        mod(n1,10), count(*) 
from    t1 
group by 
        mod(n1,10)
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |    40 |    10  (10)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |    10 |    40 |    10  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  3000 | 12000 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

We use the same group_by as the critical parameter, with rows=nnn.

Next steps

After an aggregation there’s often a “having” clause so you might consider using the group_by option to fix up the cardinality of the having clause if you know what the normal effect of the having clause should be. For example: “having count(*) > NNN” will use the optimizer’s standard 5% “guess” and “having count(*) = NNN” will use the standard 1% guess. However, having seen the group_by options I took a guess that there might be a having option to the opt_estimate() hint as well, so I tried it – with autotrace enabled here are three queries, first the unhinted baseline (which uses the standard 5% on my having clause) then a couple of others with hints to tweak the cardinality:

select  /*+
                qb_name(main)
        */
        mod(n1,10), count(*)
from    t1
group by
        mod(n1,10)
having
        count(*) > 100
;

select  /*+
                qb_name(main)
                opt_estimate(@main having scale_rows=0.4)
        */
        mod(n1,10), count(*)
from    t1
group by
        mod(n1,10)
having
        count(*) > 100
;

select  /*+
                qb_name(main)
                opt_estimate(@main group_by scale_rows=2)
                opt_estimate(@main having scale_rows=0.3)
        */
        mod(n1,10), count(*)
from    t1
group by
        mod(n1,10)
having
        count(*) > 100
;

The first query gives us the baseline cardinality of 10 (5% of 200). The second query scales the having cardinality down by a factor of 0.4  (with means an estimate of 4). The final query first doubles the group by cardinality (to 400), then scales the having cardinality (which would have become 20) down by a factor of 0.3 with the nett effect of producing a cardinality of 6. Here are the plans.

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    10 |    40 |    10  (10)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |   --  10
|   2 |   HASH GROUP BY     |      |    10 |    40 |    10  (10)| 00:00:01 |   -- 200
|   3 |    TABLE ACCESS FULL| T1   |  3000 | 12000 |     9   (0)| 00:00:01 |
----------------------------------------------------------------------------

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |    16 |    10  (10)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |    --   4
|   2 |   HASH GROUP BY     |      |     4 |    16 |    10  (10)| 00:00:01 |    -- 200
|   3 |    TABLE ACCESS FULL| T1   |  3000 | 12000 |     9   (0)| 00:00:01 |
----------------------------------------------------------------------------

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     6 |    24 |    10  (10)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |    --   6
|   2 |   HASH GROUP BY     |      |     6 |    24 |    10  (10)| 00:00:01 |    -- 400
|   3 |    TABLE ACCESS FULL| T1   |  3000 | 12000 |     9   (0)| 00:00:01 |
----------------------------------------------------------------------------

It’s a little sad that the FILTER operation shows no estimate while the HASH GROUP BY operation shows the estimate after the application of the having clause. It would be nice to see the plan reporting the figures which I’ve added at the end of line for operations 1 and 2.

You may wonder why one would want to increase the estimate for the group by then reduce it for the having. While I’m not going to go to the trouble of creating a worked example it shouldn’t be too hard to appreciate the idea that the optimizer might use complex view merging to postpone a group by until after a join – so increasing the estimate for a group by might be necessary to ensure that that particular transformation doesn’t happen, while following this up with a reduction to the having might then ensure that the next join is a nested loop rather than a hash join. Of course, if you don’t need to be this subtle you might simply take advantage of yet another option to the opt_estimate() hint, the query_block option – but that will (probably) appear in the next article in this series.