Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

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.

 

Oracle Materialized View Refresh Group atomicity— How to prove transactional consistency with…

Oracle Materialized View Refresh Group atomicity— How to prove transactional consistency with LogMiner

I had recently to prove to myself, and then to the Oracle Support, that a Materialized View Group was not refreshed atomically as it should, according to the documentation:

Add materialized views to a refresh group to ensure transactional consistency between the related materialized views in the refresh group. When a refresh group is refreshed, all materialized views that are added to a particular refresh group are refreshed at the same time.

Database Administrator's Guide

AskTOM

The first idea was suggested by the user who encountered the issue. He has read a similar question on, AskTOM, which is a very good idea:

Ask TOM does DBMS_REFRESH.REFRESH a atomic refresh of mviews?

There, Connor quickly shows that there are no intermediate commits by enabling SQL_TRACE and looking at the XCTEND lines from the trace dump.

sql_trace

So I did the same in my case and here is an extract from the interesting lines, just grepping the inserts and the XCTEND:

Clearly, the first 4 tables were done in the same transaction. But we can see some commits between the 4 others. This seemed to confirm what the user has observed: a query on tables shows data from a different point in time. And then I opened a SR to fill a bug.

However, the support engineer disapproved this proof because the XCTEND can come from recursive transactions. And he is totally right. With SQL_TRACE you can prove that it is atomic, but you cannot prove that it is not.

LogMiner

When it comes to transactions, LogMiner is the right tool. It is incredibly powerful (all persistent changes on your database go to the redo stream, and LogMiner can read the most interesting out of it. And it is incredibly easy to use — at least until the latest release where Oracle removes many replication features which may overlap with GoldenGate — a product sold separately.

Here I’m refreshing the materialized view group LSA.CCDB_VIEWS, which contains 8 MVIEWs. I am tracking the SCN before (scn1) and after (scn2).

set numwidth 16 linesize 200 pagesize 1000
column scn1 new_value scn1
column scn2 new_value scn2
column sid format 999999 new_value sid
column seg_type format 99
column seg_name format a30
column seg_owner format a12
column operation format a12
set linesize 1000
alter database add supplemental log data;
select current_timestamp,current_scn scn1,sys_context('userenv','sid') sid from v$database;
exec dbms_refresh.refresh('LSA.CCDB_VIEWS');
commit;
select current_timestamp,current_scn scn2,sys_context('userenv','sid') sid from v$database;
alter database drop supplemental log data;

Note that as I don’t have supplemental logging enabled here, I enable it just for this test. It is not a bad idea to enable it always, as long as the redo size is acceptable.

Then I start Log Miner for this SCN range. I use CONTINUOUS_MINE as I am on 18c here (it has been deprecated, de-supported and even removed in 19c

Glitches

Here’s a question just in from Oracle-L that demonstrates the pain of assuming things work consistently when sometimes Oracle development hasn’t quite finished a bug fix or enhancement. Here’s the problem – which starts from the “scott.emp” table (which I’m not going to create in the code below):

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

-- create and populate EMP table from SCOTT demo schema

create index e_sort1 on emp (job, hiredate);
create index e_low_sort1 on emp (lower(job), hiredate);

set serveroutput off
alter session set statistics_level = all;
set linesize 156
set pagesize 60

select * from emp where job='CLERK'         order by hiredate fetch first 2 rows only; 
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last outline alias'));

select * from emp where lower(job)='clerk' order by hiredate fetch first 2 rows only; 
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last outline alias'));

Both queries use the 12c “fetch first” feature to select two rows from the table. We have an index on (job, hiredate) and a similar index on (lower(job), hiredate), and given the similarity of the queries and the respective indexes (get the first two rows by hiredate where job/lower(job) is ‘CLERK’/’clerk’) we might expect to see the same execution plan in both cases with the only change being the choice of index used. But here are the plans:


select * from emp where job='CLERK'         order by hiredate fetch
first 2 rows only

Plan hash value: 92281638

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     2 (100)|      2 |00:00:00.01 |       4 |
|*  1 |  VIEW                         |         |      1 |      2 |     2   (0)|      2 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY       |         |      1 |      3 |     2   (0)|      2 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      3 |     2   (0)|      3 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN          | E_SORT1 |      1 |      3 |     1   (0)|      3 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."HIREDATE")<=2)
   4 - access("JOB"='CLERK')


select * from emp where lower(job)='clerk' order by hiredate fetch
first 2 rows only

Plan hash value: 4254915479

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |     1 (100)|      2 |00:00:00.01 |       2 |       |       |          |
|*  1 |  VIEW                                 |             |      1 |      2 |     1   (0)|      2 |00:00:00.01 |       2 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK             |             |      1 |      1 |     1   (0)|      2 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP         |      1 |      1 |     1   (0)|      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | E_LOW_SORT1 |      1 |      1 |     1   (0)|      4 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."HIREDATE")<=2)
   4 - access("EMP"."SYS_NC00009$"='clerk')


As you can see, with the “normal” index Oracle is able to walk the index “knowing” that the data is appearing in order, and stopping as soon as possible (almost) – reporting the WINDOW operation as “WINDOW NOSORT STOPKEY”. On the other hand with the function-based index Oracle retrieves all the data by index, sorts it, then applies the ranking requirement – reporting the WINDOW operation as “WINDOW SORT PUSHED RANK”.

Clearly it’s not going to make a lot of difference to performance in this tiny case, but there is a threat that the whole data set for ‘clerk’ will be accessed – and that’s the first performance threat, with the additional threat that the optimizer might decide that a full tablescan would be more efficient than the index range scan.

Can we fix it ?

Yes, Bob, we can. The problem harks back to a limitation that probably got fixed some time between 10g and 11g – here are two, simpler, queries against the emp table and the two new indexes, each with the resulting execution plan when run under Oracle 10.2.0.5:


select ename from emp where       job  = 'CLERK' order by hiredate;
select ename from emp where lower(job) = 'clerk' order by hiredate;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |    66 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     3 |    66 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | E_SORT1 |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("JOB"='CLERK')


--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |    66 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |             |     3 |    66 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP         |     3 |    66 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | E_LOW_SORT1 |     3 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(LOWER("JOB")='clerk')

The redundant SORT ORDER BY is present in 10g even for a simple index range scan. By 11.2.0.4 the optimizer was able to get rid of the redundant step, but clearly there’s a little gap in the code relating to the over() clause that hasn’t acquired the correction – even in 18.3.0.0 (or 19.2 according to a test on https://livesql.oracle.com).

To fix the 10g problem you just had to include the first column of the index in the order by clause: the result doesn’t change, of course, because you’re simply prefixing the required columns with a column which holds the single value you were probing the index for but suddenly the optimizer realises that it can do a NOSORT operation – so the “obvious” guess was to do the same for this “first fetch” example:

select * from emp where lower(job)='clerk' order by lower(job), hiredate fetch first 2 rows only;

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |     3 (100)|      2 |00:00:00.01 |       4 |
|*  1 |  VIEW                         |             |      1 |      2 |     3  (34)|      2 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY       |             |      1 |      1 |     3  (34)|      2 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP         |      1 |      1 |     2   (0)|      3 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN          | E_LOW_SORT1 |      1 |      1 |     1   (0)|      3 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."SYS_NC00009$","EMP"."HIREDATE")<=2)
   4 - access("EMP"."SYS_NC00009$"='clerk')

It’s just one of those silly little details where you can waste a HUGE amount of time (in a complex case) because it never crossed your mind that something that clearly ought to work might need testing for a specific use case – and I’ve lost count of the number of times I’ve been caught out by this type of “not quite finished” anomaly.

Footnote

If you follow the URL to the Oracle-L thread you’ll see that Tanel Poder has supplied a couple of MoS Document Ids discussing the issue and warning of other bugs with virtual column / FBI translation, and has shown an alternative workaround that takes advantage of a hidden parameter.

 

opt_estimate 2

This is a note that was supposed to be a follow-up to an initial example of using the opt_estimate() hint to manipulate the optimizer’s statistical understanding of how much data it would access and (implicitly) how much difference that would make to the resource usage. Instead, two years later, here’s part two – on using opt_estimate() with nested loop joins. As usual I’ll start with a little data set:


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

create table t1
as
select 
        trunc((rownum-1)/15)    n1,
        trunc((rownum-1)/15)    n2,
        rpad(rownum,180)        v1
from    dual
connect by
        level <= 3000 --> hint to avoid wordpress format issue
;

create table t2
pctfree 75
as
select 
        mod(rownum,200)         n1,
        mod(rownum,200)         n2,
        rpad(rownum,180)        v1
from    dual
connect by
        level <= 3000 --> hint to avoid wordpress format issue
;

create index t1_i1 on t1(n1);
create index t2_i1 on t2(n1);

There are 3,000 rows in each table, with 200 distinct values for each of columns n1 and n2. There is an important difference between the tables, though, as the rows for a given value are well clustered in t1 and widely scattered in t2. I’m going to execute a join query between the two tables, ultimately forcing a very bad access path so that I can show some opt_estimate() hints making a difference to cost and cardinality calculations. Here’s my starting query, with execution plan, unhinted (apart from the query block name hint):

select
        /*+ qb_name(main) */
        t1.v1, t2.v1
from    t1, t2
where
        t1.n1 = 15
and     t2.n1 = t1.n2
;

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |   225 | 83700 |    44   (3)| 00:00:01 |
|*  1 |  HASH JOIN                           |       |   225 | 83700 |    44   (3)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    15 |  2805 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL                  | T2    |  3000 |   541K|    42   (3)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."N1"="T1"."N2")
   3 - access("T1"."N1"=15)

You’ll notice the tablescan and hash join with t2 as the probe (2nd) table and a total cost of 44, which largely due to the tablescan cost of t2 (which I had deliberately defined with pctfree 75 to make the tablescan a little expensive). Let’s hint the query to do a nested loop from t1 to t2 to see why the hash join is preferred over the nested loop:


alter session set "_nlj_batching_enabled"=0;

select
        /*+
                qb_name(main)
                leading(t1 t2)
                use_nl(t2)
                index(t2)
                no_nlj_prefetch(t2)
        */
        t1.v1, t2.v1
from    t1, t2
where
        t1.n1 = 15
and     t2.n1 = t1.n2
;

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |   225 | 83700 |   242   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |   225 | 83700 |   242   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    15 |  2805 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |    15 |  2775 |    16   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | T2_I1 |    15 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."N1"=15)
   5 - access("T2"."N1"="T1"."N2")

I’ve done two slightly odd things here – I’ve set a hidden parameter to disable nlj batching and I’ve used a hint to block nlj prefetching. This doesn’t affect the arithmetic but it does mean the appearance of the nested loop goes back to the original pre-9i form that happens to make it a little easier to see costs and cardinalities adding and multiplying their way through the plan.

As you can see, the total cost is 242 with this plan and most of the cost is due to the indexed access into t2: the optimizer has correctly estimated that each probe of t2 will acquire 15 rows and that those 15 rows will be scattered across 15 blocks, so the join cardinality comes to 15*15 = 255 and the cost comes to 15 (t1 rows) * 16 (t2 unit cost) + 2 (t1 cost) = 242.

So let’s tell the optimizer that its estimated cardinality for the index range scan is wrong.


select
        /*+
                qb_name(main)
                leading(t1 t2)
                use_nl(t2)
                index(t2)
                no_nlj_prefetch(t2)
                opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06)
        */
        t1.v1, t2.v1
from    t1, t2
where
        t1.n1 = 15
and     t2.n1 = t1.n2
;

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |   225 | 83700 |    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |   225 | 83700 |    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    15 |  2805 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |    15 |  2775 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | T2_I1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."N1"=15)
   5 - access("T2"."N1"="T1"."N2")

I’ve used the hint opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06).

The form is: (@qb_name nlj_index_scan, table_alias (list of possible driving tables), target_index, numeric_adjustment).

The numeric_adjustment could be rows=nnn or, as I have here, scale_rows=nnn; the target_index has to be specified by name rather than list of columns, and the list of possible driving tables should be a comma-separated list of fully-qualified table aliases. There’s a similar nlj_index_filter option which I can’t demonstrate in this post because it probably needs an index of at least two-columns before it can be used.

The things to note in this plan are: the index range scan at operation 5 has now has a cardinality (Rows) estimate of 1 (that’s 0.06 * the original 15). This hasn’t changed the cost of the range scan (because that cost was already one before we applied the opt_estimate() hint) but, because the cost of the table access is dependent on the index selectivity the cost of the table access is down to 2 (from 16). On the other hand the table cardinality hasn’t dropped so now it’s not consistent with the number of rowids predicted by the index range scan. The total cost of the query has dropped to 32, though, which is 15 (t1 rows) * 2 (t2 unit cost) + 2 (t1 cost).

Let’s try to adjust the predication that the optimizer makes about the number of rows we fetch from the table. Rather than going all the way to being consistent with the index range scan I’ll dictate a scaling factor that will make it easy to see the effect – let’s tell the optimizer that we will get one-fifth of the originally expected rows (i.e. 3).


select
        /*+
                qb_name(main)
                leading(t1 t2)
                use_nl(t2)
                index(t2)
                no_nlj_prefetch(t2)
                opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06)
                opt_estimate(@main table         , t2@main     ,        scale_rows=0.20)
        */
        t1.v1, t2.v1
from    t1, t2
where
        t1.n1 = 15
and     t2.n1 = t1.n2
;

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |    47 | 17484 |    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |    47 | 17484 |    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    15 |  2805 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |     3 |   555 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | T2_I1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."N1"=15)
   5 - access("T2"."N1"="T1"."N2")

By adding the hint opt_estimate(@main table, t2@main, scale_rows=0.20) we’ve told the optimizer that it should scale the estimated row count down by a factor of 5 from whatever it calculates. Bear in mind that in a more complex query the optimizer might decode to follow the path we expected and that factor of 0.2 will be applied whenever t2 is accessed. Notice in this plan that the join cardinality in operation 1 has also dropped from 225 to 47 – if the optimizer is told that it’s cardinality (or selectivity) calculation is wrong for the table the numbers involved in the selectivity will carry on through the plan, producing a different “adjusted NDV” for the join cardinality calculation.

Notice, though, that the total cost of the query has not changed. The cost was dictated by the optimizer’s estimate of the number of table blocks to be visited after the index range scan. The estimated number of table blocks hasn’t changed, it’s just the number of rows we will find there that we’re now hacking.

Just for completion, let’s make one final change (again, something that might be necessary in a more complex query), let’s fix the join cardinality:


select
        /*+
                qb_name(main)
                leading(t1 t2)
                use_nl(t2)
                index(t2)
                no_nlj_prefetch(t2)
                opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06)
                opt_estimate(@main table         , t2@main     ,        scale_rows=0.20)
                opt_estimate(@main join(t2 t1)   ,                      scale_rows=0.5)
        */
        t1.v1, t2.v1
from    t1, t2
where
        t1.n1 = 15
and     t2.n1 = t1.n2
;

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |    23 |  8556 |    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |    23 |  8556 |    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    15 |  2805 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |     2 |   370 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | T2_I1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."N1"=15)
   5 - access("T2"."N1"="T1"."N2")

I’ve used the hint opt_estimate(@main join(t2 t1), scale_rows=0.5) to tell the optimizer to halve its estimate of the join cardinality between t1 and t2 (whatever order they appear in). With the previous hints in place the estimate had dropped to 47 (which must have been 46 and a large bit), with this final hint it has now dropped to 23. Interestingly the cardinality estimate for the table access to t2 has dropped at the same time (almost as if the optimizer has “rationalised” the join cardinality by adjusting the selectivity of the second table in the join – that’s something I may play around with in the future, but it may require reading a 10053 trace, which I tend to avoid doing).

Side not: If you have access to MoS you’ll find that Doc ID: 2402821.1 “How To Use Optimizer Hints To Specify Cardinality For Join Operation”, seems to suggest that the cardinality() hint is something to use for single table cardinalities, and implies that the opt_estimate(join) option is for two-table joins. In fact both hints can be used to set the cardinality of multi-table joins.

Finally, then, let’s eliminate the hints that force the join order and join method and see what happens to our query plan if all we include is the opt_estimate() hints (and the qb_name() and no_nlj_prefetch hints).

select
        /*+
                qb_name(main)
                no_nlj_prefetch(t2)
                opt_estimate(@main nlj_index_scan, t2@main (t1), t2_i1, scale_rows=0.06)
                opt_estimate(@main table         , t2@main     ,        scale_rows=0.20)
                opt_estimate(@main join(t2 t1)   ,                      scale_rows=0.5)
        */
        t1.v1, t2.v1
from    t1, t2
where
        t1.n1 = 15
and     t2.n1 = t1.n2
;

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |    23 |  8556 |    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |    23 |  8556 |    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    15 |  2805 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |     2 |   370 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | T2_I1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."N1"=15)
   5 - access("T2"."N1"="T1"."N2")

Note
-----
   - this is an adaptive plan

WIth a little engineering on the optimizer estimates we’ve managed to con Oracle into using a different path from the default choice. Do notice, though, the closing Note section (which didn’t appear in all the other examples): I’ve left Oracle with the option of checking the actual stats as the query runs, so if I run the query twice Oracle might spot that the arithmetic is all wrong and throw in some SQL Plan Directives – which are just another load of opt_estimate() hints.

In fact, in this example, the plan we wanted because desirable as soon as we applied the nlj_ind_scan fix-up as this made the estimated cost of the index probe into t2 sufficiently low (even though it left an inconsistent cardinality figure for the table rows) that Oracle would have switched from the default hash join to the nested loop on that basis alone.

Closing Comment

As I pointed out in the previous article, this is just scratching the surface of how the opt_estimate() hint works, and even with very simple queries it can be hard to tell whether any behaviour we’ve seen is actually doing what we think it’s doing. In a third article I’ll be looking at something prompted by the most recent email I’ve had about opt_estimate() – how it might (or might not) behave in the presence of inline views and transformations like merging or pushing predicates. I’ll try not to take 2 years to publish it.

 

Importing and Working with Exported AWR/ASH data in an Oracle database in a VirtualBox VM

A lot of my performance tuning work involves analysis of ASH and AWR data.  Frequently, I do not have direct access to the databases in question.  Sometimes, I ask clients to run EDB360 on their databases and send me the results, but sometimes I also want to work directly with ASH or AWR metrics.  So, I ask for an export of their AWR repository.
Oracle distributes a pair of scripts in $ORACLE_HOME/rdbms/admin.

  • awrextr.sql exports the AWR repository (including the ASH data), optionally for a range of AWR snapshot IDs. 
  • awrload.sql the export into a staging table and then moves it into the AWR repository in the current database from where you can reference it via the usual DBA_HIST% views and work with it in the usual way.

I import this into an Oracle database in a virtual machine in VirtualBox and work on it there.  This note explains how I create and set up that machine and then work with the data.

Getting Started

1. Build Oracle VirtualBox Linux VM

I use Frits Hoogland's vagrant builder to create a standard Oracle database on a Linux VM in VirtualBox.  I always build the latest version of Oracle because I can import an AWR export into a higher version of the database, but not a lower one.

2. Make Life Easier For Yourself

I sometimes install other utilities into the virtual machine, such as git, using yum.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">yum install git

The VirtualBox is only going to run a single Oracle database, and I am very lazy.  So, I set up the environment in the shell profile by adding the following commands the ~oracle/.bash_profile.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">export ORACLE_SID=oracle
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

3. Share a Folder on the Host Machine

I usually leave the AWR export file on my host machine rather than copy it into the virtual machine.  I create a VirtualBox shared folder so that the VirtualBox VM can reference a folder on the host machine.  In this case, I have shared c:\temp on my host Windows laptop and called the filesystem temp.  Note that it is auto-mounted and permanent.

In the VirtualBox, the shared folder has been mounted as /media/sf_temp.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">[oracle@oracle-database sql]$ df /media/sf_temp
Filesystem 1K-blocks Used Available Use% Mounted on
temp 974661916 340278844 634383072 35% /media/sf_temp

The sf_temp directory is owned by root and accessible only to the vboxsf group.  Therefore, you must add any user who needs to reference this directory to that group.  In this case, Oracle must be made a member of the vboxsf group.  For safety, this should be done with the usermod command rather than by editing /etc/group directly.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">usermod -G vboxsf -a oracle

You can see the result of this command in the /etc/group file

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">grep oracle /etc/group
vboxsf:x:994:oracle

When logged in as Oracle, the id command will also report the groups of which it is a member.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">[oracle@oracle-database ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),100(users),994(vboxsf),54322(dba)

Create a directory in the database to reference this directory on the filesystem.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE OR REPLACE DIRECTORY TEMP as '/media/sf_temp';

4. Oracle FILESYSTEMIO_OPTIONS Parameter

Initially, I got the following error when trying to run a data pump import from the shared folder:

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/media/sf_temp/awrdat_xxxx_xxxx.dmp" for read
ORA-27041: unable to open file
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2

On VirtualBox, the Oracle parameter FILESYSTEMIO_OPTIONS will be set to setall by default.  To work around the import error set it to none and restart the database.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=none SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

5. Data Pump Import

Now you can import an AWR export (previously exported with awrextr.sql) with the script awrload.sql.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@awrload

Working with Imported Data

The AWR data exported from another database will be visible through all the usual database views.  It can be distinguished by having the database ID of the original database.  If, like me, you are used to mining ASH data with SQL scripts, then they will work if you specify the DBID.  However, I have a couple of tips for working with imported AWR data.

SQLDB360 (EDB360 and SQLD360)

Sometimes I run an EDB360 report on the imported data.  The reports and graphs of ASH data and the system time model are particularly useful.
I can generate AWR reports for any pair of snapshots in the imported repository, not just the ones generated by the client and provided to me.  EDB360 generates AWR reports for peak periods by various criteria.
Having installed git, I can use it to download the latest version of SQLDB360.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">cd ~/
git clone https://github.com/sqldb360/sqldb360.git
cd ~/sqldb360

Although, I could have used curl instead.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">cd ~/
curl -LO https://github.com/sqldb360/sqldb360/archive/unstable.zip
unzip unstable.zip

Obtain the DBID of the database that you have just imported by looking at one of the AWR views

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">col max(startup_time) format a26
col min(begin_interval_time) format a26
col max(end_interval_time) format a26
SELECT dbid, db_name, count(*), max(startup_time)
FROM dba_hist_database_instance
GROUP BY dbid, db_name
/
SELECT dbid, min(begin_interval_time), max(end_interval_time)
FROM dba_hist_snapshot
GROUP BY dbid
/

In this case, my database is ORACLE, and XXXXXXX is the database that I have just imported.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">      DBID DB_NAME     COUNT(*) MAX(STARTUP_TIME)
---------- --------- ---------- --------------------------
1884370030 ORACLE 9 20-JUN-19 10.51.50.000 AM
3743596248 XXXXXXX 4 05-MAY-19 01.03.57.000 AM

DBID MIN(BEGIN_INTERVAL_TIME) MAX(END_INTERVAL_TIME)
---------- -------------------------- --------------------------
1884370030 18-JUN-19 07.03.44.000 PM 20-JUN-19 11.02.49.684 AM
3743596248 18-JUN-19 05.00.13.342 PM 19-JUN-19 11.00.20.020 AM

Create a custom configuration file in the sqldb360/sql directory

  • Set the database ID with the DBID from the export.
  • Specify edb360_conf_date_from/to parameters to match the range of dates in the import.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">- this is a sample custom configuration requesting to produce just one section
-- for all possible DEF values please review edb360_00_config.sql

-- section to report. null means all (default)
-- report column, or section, or range of columns or range of sections i.e. 3, 3-4, 3a, 3a-4c, 3-4c, 3c-4
DEF edb360_sections = '';

-- range of dates below superceed history days when values are other than YYYY-MM-DD
-- default values YYYY-MM-DD mean: use edb360_conf_days
-- actual values sample: 2016-04-26
DEF edb360_conf_date_from = '2019-06-18';
DEF edb360_conf_date_to = '2019-06-19';

-- use if you need tool to act on a dbid stored on AWR, but that is not the current v$database.dbid
DEF edb360_config_dbid = '3743596248';

Run EDB360 referencing the custom config file

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">@edb360 T custom_config_01.sql
@sqld360 5dcbnw1d9yfna T custom_config_01.sql

NB: EDB360 and SQLD360 reports generated in this way will contain a mixture of data local database and the database from which the AWR export was taken.

  • Some reports are based on v$ and x$ views or data dictionary views (DBA_TABLES etc.).  These will report on the database in the virtual machine.
  • Only the reports based on DBA_HIST% views will report on the database from which the AWR data was exported.

It is important to keep this distinction in mind.
Nearly every section of the EDB360 report contains reports based on the AWR data, so it is not worth trying to exclude any sections when running EDB360 imported data.

Extracting Execution Plans from AWR

We would normally extract the execution plan of a particular statement from the AWR for the local database using the DISPLAY_AWR function in DBMS_XPLAN package as follows.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT * FROM table(dbms_xplan.display_awr('burz0q5qjtgmv'));

You can specify the database ID to DISPLAY_AWR(), but if it is not the DBID of the current database, then it returns nothing.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT * FROM table(dbms_xplan.display_awr('5dcbnw1d9yfna',db_id=>3743596248));

The various DISPLAY% functions in DBMS_XPLAN are formatted report generators for data held in a particular data structure.  DISPLAY_CURSOR() references V$SQL_PLAN, DISPLAY_AWR() references DBA_HIST_SQL_PLAN, and you can pass a table name to DISPLAY so that it can reference any table or view.  That is useful if you have your own private plan table.
In this case, instead of DISPLAY_AWR(), we can use the DISPLAY() function directly on the DBA_HIST_SQLPLAN view, thus:

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">set long 5000 lines 150 pages 999
column sql_text format a150
SELECT sql_text from dba_hist_sqltext
where dbid=3743596248 AND sql_id = '5dcbnw1d9yfna'
/
SELECT * FROM table(dbms_xplan.display(
table_name=>'DBA_HIST_SQL_PLAN'
, format=>'ADVANCED +ADAPTIVE'
, filter_preds=>'dbid=3743596248 AND sql_id = ''5dcbnw1d9yfna'''))
/

Note that:

  • The DBID and SQL_ID are passed as quoted literals to the function.
  • DBMS_XPLAN.DISPLAY() does not report the SQL statement, so that must be queried separately.

Oracle ATP: MEDIUM and HIGH services are not for OLTP

The Autonomous Transaction Processing services HIGH and MEDIUM are forcing Parallel DML, which can lock the tables in eXclusive mode.

This may seem obvious that the TP and TPURGENT are for OLTP. But when you know that the service names are associated with Resource Manager consumer groups, you may think that high priority use cases should run on the HIGH service. However those LOW, MEDIUM, HIGH services were probably named when ADW was the only Autonomous Database and it is not directly obvious that they are there for reporting only, or maybe for some batch operations.

Application “enq: TM — contention” and Scheduler “resmgr:pq queued” waits

What is less obvious is that when you do some modifications through these services, they run with Parallel DML (PDML). And PDML can acquire an exclusive lock on tables. And then it blocks your concurrent transactions. You will see some waits on ‘enq: TM contention’ and probably some ‘resmgr:pq queued

What does the documentation say about parallelism?

The documentation or the Autonomous Database FAQ explains that MEDIUM and HIGH use parallel query, but it is not immediately visible that they also run in parallel DML:

https://www.oracle.com/technetwork/database/bi-datawarehousing/adw-technical-faq-public-5069016.pdf

You may also refer to Maria Colgan description of those services:

Getting started with Oracle Autonomous Transaction Processing

How to enable Parallel DML?

As far as I know, the documentation mentions only two ways to enable Parallel DML. At the session level:

alter session enable parallel DML;

to enable it (when the table degree is >1) or even to force it even for no parallel tables:

alter session force parallel DML;

Or, since 12c, at statement level with the ENABLE_PARALLEL_DML hint:

Parallel DML in 12c - Blog dbi services

It is important that Oracle requires us to explicitly enable PDML because PDML queries can acquire exclusive locks on the tables, and DML is not expected to do that — exclusive lock is usually for DDL to prevent concurrent DML.

Let’s also precise here that there’s a big difference between Parallel Query and Parallel DML. Parallel Query is for SELECT. it can consume a lot of resources, but will never lock tables. Parallel DML concerns only modifications (like INSERT, DELETE, UPDATE). This is a bit misleading because, in SQL, the Data Manipulation Language also includes the SELECT. But it is common that people use the DML term for non-SELECT DML.

What does the dictionary say about parallelism?

V$SESSION has information about the sessions that have Parallel DML enabled:

select distinct regexp_replace(service_name,'.*_')
,pdml_enabled, pdml_status, pddl_status, pq_status
FROM V$session order by 2,3,4;

That tells us that Parallel DML is enabled, for all services. But not forced. When you ALTER SESSION FORCE PARALLEL DML, you see it explicitly:

SQL> connect demo/demo@//localhost/PDB1
Connected.

SQL> select distinct regexp_replace(service_name,'.*_') "service"
,pdml_enabled, pdml_status, pddl_status
FROM V$session where sid=sys_context('userenv','sid');
   service    PDML_ENABLED    PDML_STATUS    PDDL_STATUS
__________ _______________ ______________ ______________
pdb1 NO DISABLED ENABLED
SQL> alter session enable parallel dml;
Session altered.
SQL> select distinct regexp_replace(service_name,'.*_') "service"
,pdml_enabled, pdml_status, pddl_status
FROM V$session where sid=sys_context('userenv','sid');
   service    PDML_ENABLED    PDML_STATUS    PDDL_STATUS
__________ _______________ ______________ ______________
pdb1 YES ENABLED ENABLED
SQL> alter session force parallel dml;
Session altered.
SQL> select distinct regexp_replace(service_name,'.*_') "service"
,pdml_enabled, pdml_status, pddl_status
FROM V$session where sid=sys_context('userenv','sid');
   service    PDML_ENABLED    PDML_STATUS    PDDL_STATUS
__________ _______________ ______________ ______________
pdb1 YES FORCED ENABLED
SQL> alter session disable parallel dml;
Session altered.
SQL> select distinct regexp_replace(service_name,'.*_') "service"
,pdml_enabled, pdml_status, pddl_status
FROM V$session where sid=sys_context('userenv','sid');
   service    PDML_ENABLED    PDML_STATUS    PDDL_STATUS
__________ _______________ ______________ ______________
pdb1 NO DISABLED ENABLED

So, in my ATP service, all services have PDML enabled, and none are forced.

What does the execution plan say about parallelism?

However, it seems that when connected with the MEDIUM and HIGH services, PDML can be used:

And you can see that I even created the table explicitly in NO PARALLEL (which is the default anyway). The PDML is enabled but not forced. Then what I often do in this case is look at the ‘+OUTLINE’ dbms_xplan format as there may be some clues about specific optimizer settings:

SQL> select * from dbms_xplan.display(format=>'+outline');
...
Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
FULL(@"DEL$1" "DEMO"@"DEL$1")
OUTLINE_LEAF(@"DEL$1")
SHARED(8)
ALL_ROWS
OPT_PARAM('_fix_control' '20648883:0')
DB_VERSION('18.1.0')
OPTIMIZER_FEATURES_ENABLE('18.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Note
-----
- automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

ATP service disables the fix about “Lift restrictions on view merging for the CURSOR expression” but this has nothing to do with PDML. However, there’s something interesting here: a SHARED hint.

What does V$SQL_HINT say about parallelism?

Actually, the true hint for running in parallel is not PARALLEL but SHARED. This is visible in V$SQL_HINT as it is the inverse of NOPARALLEL (and its synonym NO_PARALLEL added later to follow the NO_% hint syntax convention):

SQL> select name,inverse,version from v$sql_hint 
where name like '%SHARED';
     NAME        INVERSE    VERSION
_________ ______________ __________
SHARED NO_PARALLEL 8.1.0
SQL> select name,inverse,version from v$sql_hint
where name like '%PARALLEL';
          NAME    INVERSE     VERSION
______________ __________ ___________
NOPARALLEL SHARED 8.1.0
NO_PARALLEL SHARED 10.1.0.3

PARALLEL is just a synonym for SHARED.

So, it seems that even with no parallel degree and no forced parallel DML, parallelism has been forced to a degree of 8 as we can see with the SHARED(8) hint. Probably related to the number of CPU:

ATP number of OCPU

SQL> show parameter cpu_count
NAME TYPE VALUE
--------- ------- -----
cpu_count integer 20
SQL> show parameter degree_limit
NAME VALUE
--------------------- ----
parallel_degree_limit CPU

I have 10 OCPUs allocated to this ATP service, which means 20 threads (CPU_COUNT) but DBMS_XPLAN also mentions that Auto DOP has computed the degree because of degree limit. The Resource Manager plan has some limits for the maximum degree of parallelism, like:

SQL> select plan,group_or_subplan,parallel_degree_limit_p1
from DBA_RSRC_PLAN_DIRECTIVES where plan='OLTP_PLAN';
PLAN         GROUP_OR_SUBPLAN       PARALLEL_DEGREE_LIMIT_P1
--------- ---------------- ------------------------OLTP_PLAN HIGH 20
OLTP_PLAN MEDIUM 4
OLTP_PLAN LOW 1
OLTP_PLAN TPURGENT
OLTP_PLAN OTHER_GROUPS 1
OLTP_PLAN TP 1

What does the LOGON Trigger say about parallelism?

Usually, when we want to enable Parallel DML automatically for a user we add a Logon Trigger, like:

CREATE OR REPLACE TRIGGER app_user.after_logon_trg
AFTER LOGON ON app_user.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
END;
/

Note that this AFTER LOGON ON SCHEMA comes from Tim Hall example:

Database Triggers Overview

So, let’s have a look at the LOGON triggers defined in the Autonomous Transaction Processing PDB (Don’t forget that for whatever reason the TRIGGERING_EVENT in DBA_TRIGGERS ends with a space):

set echo on long 10000
select dbms_metadata.get_ddl('TRIGGER',trigger_name,owner)
from dba_triggers where triggering_event like 'LOGON ';

No mention of ENABLE or FORCE parallel DML here, but there is a minimum parallel degree set to the number of CPU, with the DOP policy set to AUTO, for the MEDIUM and HIGH services only.

I have all information here, let’s reproduce the same in a non-autonomous database. And show the reason of the sessions blocked on “enq: TM contention”

What does ksqgtl trace say about parallelism?

I’m connected to a 19.3 database here where I enable PDML (not forced) and I also trace the Kernel Service enQueue (like what we did with 10704 before 12cR2) in order to show the locks acquired.

The Auto DOP is set with a minimum degree equal to CPU, as in the ATP logon trigger.

I have created a simple DEMO table with DEGREE 1 (which means no parallel, and is the default anyway). I run a DELETE and explain plan:

This is what I observed in ATP but here, with no resource manager, the parallel degree is set to 48 which is my CPU_COUNT.

Now looking at the ksq trace for ksqgtl (GeT Lock):

My delete, because it is running as PDML, has acquired a mode=6 (eXclusive) lock on the DEMO table.

If you want to reproduce, here are the commands I’ve run:

set pagesize 1000 echo on
drop table DEMO;
connect demo/demo@//localhost/PDB1
create table DEMO (n) parallel 1 as select rownum from xmltable('1 to 1000');
alter session enable parallel dml;
alter session set parallel_degree_policy=auto parallel_min_degree=cpu;
select distinct regexp_replace(service_name,'.*_') "service"
,pdml_enabled, pdml_status, pddl_status, pq_status
from v$session where sid=sys_context('userenv','sid')
/
alter session set events 'trace[ksq] disk medium';
alter session set tracefile_identifier=KSQ;
delete DEMO;
select * from dbms_xplan.display_cursor(format=>'+outline');
alter session set events 'trace[ksq]off';
select object_id , to_char(object_id,'0XXXXXXX') , object_name,object_type from user_objects where object_name='DEMO';
column value new_value tracefile
select value from v$diag_info where name='Default Trace File';
column value clear
host grep TM- &tracefile
rollback;

One question remains…

In my example, I’ve enabled PDML:

alter session enable parallel dml;

because I’ve seen it enabled for my MEDIUM and HIGH services. But I don’t know (yet — please tweet @FranckPachot if you have ideas) where this comes from. There’s nothing about it in the LOGON trigger.

If I do not enable PDML in my non-autonomous database, the SELECT part is done in the parallel query but the DELETE is not in PDML (there is no DELETE below the PX COORDINATOR)

So, the question that remains: how is PDML enabled in the Autonomous Transaction Processing service?

But the most important to remember is that running some INSERT/UPDATE/DELETE with the MEDIUM of HIGH service can block all your Transaction Processing because of the exclusive lock.

Autonomous does not mean that we can run without understanding.

ANSI bug

The following note is about a script that I found on my laptop while I was searching for some details about a bug that appears when you write SQL using the ANSI style format rather than traditional Oracle style. The script is clearly one that I must have cut and pasted from somewhere (possibly the OTN/ODC database forum) many years ago without making any notes about its source or resolution. All I can say about it is that the file has a creation date of July 2012 and I can’t find any reference to a problem through Google searches – though the tables and even a set of specific insert statements appears in a number of pages that look like coursework for computer studies and MoS has a similar looking bug “fixed in 11.2”.

Here’s the entire script:

rem
rem     Script:         ansi_bug.sql
rem     Author:         ???
rem     Dated:          July 2012
rem

CREATE TABLE Student (
  sid INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  address VARCHAR(20) NOT NULL,
  major CHAR(2)
);

CREATE TABLE Professor (
  pid INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  department VARCHAR(10) NOT NULL
);

CREATE TABLE Course (
  cid INT PRIMARY KEY,
  title VARCHAR(20) NOT NULL UNIQUE,
  credits INT NOT NULL,
  area VARCHAR(5) NOT NULL
);

CREATE TABLE Transcript (
  sid INT,
  cid INT,
  pid INT,
  semester VARCHAR(9),
  year CHAR(4),
  grade CHAR(1) NOT NULL,
  PRIMARY KEY (sid, cid, pid, semester, year),
  FOREIGN KEY (sid) REFERENCES Student (sid),
  FOREIGN KEY (cid) REFERENCES Course (cid),
  FOREIGN KEY (pid) REFERENCES Professor (pid)
);

INSERT INTO Student (sid, name, address, major) VALUES (101, 'Nathan', 'Edinburg', 'CS');
INSERT INTO Student (sid, name, address, major) VALUES (105, 'Hussein', 'Edinburg', 'IT');
INSERT INTO Student (sid, name, address, major) VALUES (103, 'Jose', 'McAllen', 'CE');
INSERT INTO Student (sid, name, address, major) VALUES (102, 'Wendy', 'Mission', 'CS');
INSERT INTO Student (sid, name, address, major) VALUES (104, 'Maria', 'Pharr', 'CS');
INSERT INTO Student (sid, name, address, major) VALUES (106, 'Mike', 'Edinburg', 'CE');
INSERT INTO Student (sid, name, address, major) VALUES (107, 'Lily', 'McAllen', NULL);

INSERT INTO Professor (pid, name, department) VALUES (201, 'Artem', 'CS');
INSERT INTO Professor (pid, name, department) VALUES (203, 'John', 'CS');
INSERT INTO Professor (pid, name, department) VALUES (202, 'Virgil', 'MATH');
INSERT INTO Professor (pid, name, department) VALUES (204, 'Pearl', 'CS');
INSERT INTO Professor (pid, name, department) VALUES (205, 'Christine', 'CS');

INSERT INTO Course (cid, title, credits, area) VALUES (4333, 'Database', 3, 'DB');
INSERT INTO Course (cid, title, credits, area) VALUES (1201, 'Comp literacy', 2, 'INTRO');
INSERT INTO Course (cid, title, credits, area) VALUES (6333, 'Advanced Database', 3, 'DB');
INSERT INTO Course (cid, title, credits, area) VALUES (6315, 'Applied Database', 3, 'DB');
INSERT INTO Course (cid, title, credits, area) VALUES (3326, 'Java', 3, 'PL');
INSERT INTO Course (cid, title, credits, area) VALUES (1370, 'CS I', 4, 'INTRO');

INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (101, 4333, 201, 'Spring', '2009', 'A');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (101, 6333, 201, 'Fall', '2009', 'A');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (101, 6315, 201, 'Fall', '2009', 'A');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (103, 4333, 203, 'Summer I', '2010', 'B');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (102, 4333, 201, 'Fall', '2009', 'A');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (103, 3326, 204, 'Spring', '2008', 'A');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (104, 1201, 205, 'Fall', '2009', 'B');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (104, 1370, 203, 'Summer II', '2010', 'A');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (106, 1201, 205, 'Fall', '2009', 'C');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (106, 1370, 203, 'Summer II', '2010', 'C');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (105, 3326, 204, 'Spring', '2001', 'A');
INSERT INTO Transcript (sid, cid, pid, semester, year, grade) VALUES (105, 6315, 203, 'Fall', '2008', 'A');

SELECT 
        pid, 
        name, title
FROM 
        Professor 
NATURAL LEFT OUTER JOIN 
        (
                Transcript 
        NATURAL JOIN 
                Course
        )
;

SELECT 
        name, title
FROM 
        Professor 
NATURAL LEFT OUTER JOIN 
        (
                Transcript 
        NATURAL JOIN 
                Course
        )
;

SELECT 
        name, title
FROM 
        Professor 
NATURAL LEFT OUTER JOIN 
        (
                Transcript 
        NATURAL JOIN 
                Course
        )
order by pid
;

I’ve run three minor variations of the same query – the one in the middle selects two columns from a three table join using natural joins. The first query does the same but includes an extra column in the select list while the third query selects only the original columns but orders the result set by the extra column.

The middle query returns 60 rows – the first and third, with the “extra” column projected somewhere in the execution plan, return 13 rows.

I didn’t even have a note of the then-current version of Oracle when I copied this script, but I’ve just run it on 12.2.0.1, 18.3.0.0, and 19.2.0.0 (using LiveSQL), and the error reproduces on all three versions.

Oracle/Hibernate de-queuing

or how to use SELECT … FOR UPDATE SKIP LOCKED without any ROWNUM or FETCH FIRST ROWS, but rather scroll() and setFetchSize() in order to process a job queue in multi-thread

This is a common problem: you have a queue of events that you want to process, like for example, application storing the e-mails to send, and a background job reading them, send the e-mail, and update the status from ‘to-do’ to ‘done’, when successful. There are some message queue specialized software, but a SQL table can be preferred, especially when the queuing is done by the database application: the same database means same Atomicity, Consistency, and Durability. And you want this robustness because you don’t want to miss an e-mail to send, and you don’t want to send duplicate e-mails.

But you can see that I’ve mentioned only the ACD of the ACID properties. You want a special thing about Isolation here. Because normal isolation would mean that you lock the rows that you process. But then, your de-queuing cannot be multi-threaded because all the others will wait on it. Oracle has the FOR UPDATE SKIP LOCKED exactly for that purpose: you don’t wait, you don’t stop, you just skip the row. This was documented lately (11g if I remember well) but it is there for a long time for internal use by Advanced Queuing.

In my opinion, even if it is documented we should take it with care, especially:

  • use it for what it is designed for — an AQ-like case
  • keep it simple and don’t tweak the limitations

If you want to get an idea about the limitations, your google search should find those two links:

Jonathan Lewis:

Skip Locked

AskTOM:

Ask TOM SELECT FOR UPDATE SKIP LOCKED

In Jonathan Levis blog, you can see the problem when you want to use ROWNUM so that multiple threads can dequeue a small subset of rows to process. ROWNUM is evaluated before the FOR UPDATE. And then, two concurrent jobs will read the same rows. The first thread will take all of them and the second one will discard them. What you want is to limit the rows fetched after verifying that they are not locked by another session.

In the second link, Connor Mc Donalds shows how to do it properly: not limiting the query result, but only fetching the first rows. Connor’s example is in PL/SQL. My post here is to show the correct way from Hibernate.

Then you will add ‘Vlad’ to your google search an find:

How to implement a database job queue using SKIP LOCKED - Vlad Mihalcea

Vlad Mihalcea shows how to do it in general, but the implementation of SKIP LOCKED ROWS is very different from one database to the other. And, as explained above, with Oracle we should not mention a ‘ROWNUM<’ or ‘FETCH FIRST ROWS’ or ‘row_number()’ to limit the result of a SKIP LOCKED. Actually, it can be tempting to mixing FOR UPDATE SKIP LOCKED with ROWNUM for row limitation, ORDER BY, and maybe even DBMS_RANDOM. But that’s too many non-deterministic operations that are very dangerous, especially for something as critical as job de-dequeuing.

The good example

I’ve built a small example with a simple MESSAGE table with an id and a flag. The rows are inserted with flag=0 and each thread will update the flag with its thread number.

Here is my Message class:

class Message {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
private Integer flag;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Integer getFlag() {
return flag;
}
public void setFlag(Integer flag) {
this.flag = flag;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Message)) return false;
Message message = (Message) o;
return Objects.equals(getId(), message.getId());
}
@Override
public int hashCode() {
return Objects.hash(getId());
}
}

And here is my de-queuing code:

s=sf.openSession();
Message message;
Transaction x;
x=s.beginTransaction();
ScrollableResults i = s.createQuery("from HibernateSkipLocked$Message where flag=0").setLockMode(LockModeType.PESSIMISTIC_WRITE).setHint("javax.persistence.lock.timeout",LockOptions.SKIP_LOCKED).setFetchSize(5).scroll(ScrollMode.FORWARD_ONLY);

int limit=5;
while( i.next() && limit-->0) {
message=(Message)i.get(0);
try { Thread.sleep(1000); } catch (Exception e) {}
message.setFlag(thread);
System.out.println(message.getId()+" -> "+message.getFlag()+ " "+new Timestamp(System.currentTimeMillis()));
}
x.commit();
s.close();

And here are the what/why…

This will generate the FOR UPDATE:

.setLockMode(LockModeType.PESSIMISTIC_WRITE

This will generate the SKIP LOCKED:

.setHint("javax.persistence.lock.timeout",LockOptions.SKIP_LOCKED)

This will fetch 5 rows maximum for each fetch call (as my goal is to process 5 rows I don’t want the cursor to go further — as all fetched rows will be locked):

.setFetchSize(5)

This will get an iterator on the cursor. Do not use .list() because then all rows will be read and locked by the first thread and the next thread will have nothing to process:

.iterate()

This reads only 5 rows from the result:

int limit=5;
while( i.hasNext() && limit-->0)

That, combined with the fetch size, will ensure that we do one fetch call that finds 5 unlocked rows, lock them, return them. And we read and process those rows and stop (close the cursor) without another fetch call.

If you are not familiar with the LockModeType and LockOptions here is the query generated by Hibernate:

    select
hibernates0_.id as col_0_0_
from
message hibernates0_
where
hibernates0_.flag=0 for update
skip locked

The simpler the better here: FOR UPDATE SKIP LOCKED.

Here is a screenshot of the result, with my System.out.println in yellow showing the ids updated by thread 1 ( -> 1 ) and thread 2 ( ->2 ) at the same time.

Now let’s show what happens if we don’t do that correctly

The bad example without SKIP LOCKED

Without SKIP LOCKED, the first thread locks the rows and the second one to read them waits on it — the threads are finally serialized:

java.util.Iterator i = s.createQuery("from HibernateSkipLocked$Message where flag=0").setLockMode(LockModeType.PESSIMISTIC_WRITE).setFetchSize(5).iterator();


The ugly example with list() instead of scroll()

There’s a common misconception that the result is fully read when the query is executed. That’s not the normal behavior of the database. Where there’s no blocking operation (like GROUP BY or ORDER BY) the rows are read-only when needed. Yes, SQL RDBMS does lazy reads! Except when you explicitly read all rows to put them in a list, with list(). But in this case, the first thread locks all rows, even if it processes only 5 of them. And the second thread then skips all of them:

java.util.Iterator i = s.createQuery("from HibernateSkipLocked$Message where flag=0").setLockMode(LockModeType.PESSIMISTIC_WRITE).setHint("javax.persistence.lock.timeout",LockOptions.SKIP_LOCKED).setFetchSize(5).list().iterator();


Order By

You may want to process rows in order. That should be rare because the goal of multi-threaded de-queuing is to process quickly so the order does not really matter. If you need a first-in-first-out then maybe you don’t want to multi-thread. And then add an ORDER BY but remove the SKIP LOCKED.

Be very careful if you add an ORDER BY to the HQL query above. You will see a warning and the SELECT separated from the FOR UPDATE SKIP LOCKED:

WARN: HHH000444: Encountered request for locking however dialect reports that database prefers locking be done in a separate select (follow-on locking); results will be locked after initial query executes
Hibernate:
select
hibernates0_.id as id1_0_,
hibernates0_.flag as flag2_0_
from
message hibernates0_
where
hibernates0_.flag=0
order by
hibernates0_.id
Hibernate:
select
id
from
message
where
id =? for update
skip locked

But then, that defeats the goal of multi-threading because we want to skip in the initial SELECT.

Then the idea is to fall back to a native query, here doing a last-in-first-out

ScrollableResults i = s.createNativeQuery("select * from message where flag=0 order by id desc for update skip locked",Message.class).setFetchSize(5).scroll(ScrollMode.FORWARD_ONLY);

Here is the result where the thread 2 has taken id 14 to 10 and thread 1 then got id 9 to 5:


A few additional thoughts

While talking about the order, maybe you want an index on the flag that you use in the predicate. That’s useful if you don’t delete the rows processed but just update them. Because after a while a full table scan will have to read a big history of rows before finding those to process. Except if you can partition so that the processed ones are moved to another partition. Deleted rows leave the space for the new inserts and that’s really what we want here.

You can also plan the shuffling at the time of insertion. For example, adding a round-robin number (from a modulo or a cycle sequence) and each thread will be assigned a batch number.

Another consideration to keep in mind with queuing tables: the statistics. The queue is volatile by nature. You will probably prefer to lock the statistics. And if an unexpected de-queuing outage had made the queue grow larger than normal, then think about a SHRINK operation to lower the high water mark.

Updated 21-JUN-2019

You can see when looking at my Java code style, that I’m not a developer. So please read the feedback from Vlad Michalcea:

I’ve updated this post to use scroll() instead if iterate() and the nice thing is that it works with native queries as well.

Seattle PostgreSQL Meetup This Thursday: New Location

I’m looking forward to the Seattle PostgreSQL User Group meetup this Thursday (June 20, 2019) at 5:30pm! We’re going to get an early sneak peek at what’s coming later this year in PostgreSQL’s next major release. The current velocity of development in this open source community is staggering and this is an exciting and valuable opportunity to keep up with where PostgreSQL is going next.

One thing that’s a bit unusual about this meetup is the new location and late timing of the announcement. I think it’s worth a quick blog post to mention the location: for some people this new location might be a little more accessible than the normal spot (over at the Fred Hutch).

The meetup this week will be closer to downtown at 2201 6th Avenue (the building says “Blanchard Plaza” above the entrance): right next to the Spheres, easily accessible from public transportation and free parking across the street.

https://ardentperf.files.wordpress.com/2019/06/seapug-parking.png?w=150 150w, https://ardentperf.files.wordpress.com/2019/06/seapug-parking.png?w=300 300w, https://ardentperf.files.wordpress.com/2019/06/seapug-parking.png?w=768 768w, https://ardentperf.files.wordpress.com/2019/06/seapug-parking.png 777w" sizes="(max-width: 750px) 100vw, 750px" />

If you live or work in Seattle and you’re interested in databases but you don’t normally attend the Seattle PostgreSQL User Group, it’s worth checking if this location might be more convenient and make the visit worthwhile.

Bring any database question you have – there are people here who know PostgreSQL well enough to answer anything you can throw at them! Also, as always, some pizza and drinks will be provided. Hope to see you there!