Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

sql_patch

This note is a short follow-up to a note I wrote some time ago about validating foreign key constraints where I examined the type of SQL Oracle generates internally to do the validation between parent and child tables.  In that article I suggested (before testing) that you could create an SQL patch for the generated SQL to over-ride the plan taken by Oracle – a plan dictated to some extent by hints (including a “deprecated” ordered hint) embedded in the code. I did say that the strategy might not work for SQL optimised by SYS, but it turned out that it did.

Here’s a little script I ran to test a few variations on the theme:


declare
        v1      varchar2(128);
begin
        v1 :=   dbms_sqldiag.create_sql_patch(
                        sql_id  => 'g2z10tbxyz6b0',
                        name    => 'validate_fk',
                        hint_text => 'ignore_optim_embedded_hints'
--                      hint_text => 'parallel(a@sel$1 8)'      -- worked
--                      hint_text => 'parallel(8)'              -- worked
--                      hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}'  -- worked
                );
        dbms_output.put_line(v1);
end;
/

I’ve tested this on 12.2.0.1 and 19.3.0.0, but for earlier versions of Oracle, and depending what patches you’ve applied, you will need to modify the code.

The SQL_ID represents the query for my specific tables, of course, so you will have to do a test run to find the query and SQL_ID for the validation you want to do. This is what the statement for my parent/child pair looked like (cosmetically adjusted):

select /*+ all_rows ordered dynamic_sampling(2) */ 
        A.rowid, :1, :2, :3
from
        "TEST_USER"."CHILD" A , 
        "TEST_USER"."PARENT" B 
where
        ("A"."OBJECT_ID" is not null) 
and     ("B"."OBJECT_ID"(+) = "A"."OBJECT_ID")
and     ("B"."OBJECT_ID" is null)

The patch that the script creates simply tells Oracle to ignore the embedded hints (in particular I don’t want that ordered hint), but I’ve left a few other options in the text, commenting them out.

Without the patch I got the following plan:.


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  NESTED LOOPS ANTI (cr=399 pr=279 pw=0 time=47801 us starts=1 cost=70 size=22000 card=1000)
    100000     100000     100000   INDEX FAST FULL SCAN CHI_FK_PAR (cr=250 pr=247 pw=0 time=19943 us starts=1 cost=32 size=1700000 card=100000)(object id 73191)
     10000      10000      10000   INDEX UNIQUE SCAN PAR_PK (cr=149 pr=32 pw=0 time=3968 us starts=10000 cost=0 size=49995 card=9999)(object id 73189)

Rerunning the validation test with the patch in place I got the following plan – clearly the patch had had an effect.

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  HASH JOIN RIGHT ANTI (cr=246 pr=242 pw=0 time=96212 us starts=1 cost=39 size=22000 card=1000)
     10000      10000      10000   INDEX FAST FULL SCAN PAR_PK (cr=24 pr=23 pw=0 time=1599 us starts=1 cost=4 size=50000 card=10000)(object id 73235)
    100000     100000     100000   INDEX FAST FULL SCAN CHI_FK_PAR (cr=222 pr=219 pw=0 time=27553 us starts=1 cost=32 size=1700000 card=100000)(object id 73237)
(object id 73229)

Don’t worry too much about the fact that in my tiny example, and with a very new, nicely structured, data set the original plan was a little faster. In a production environment creating a hash table from the parent keys and probing it with the child keys may reduce the CPU usage and random I/O quite dramatically.

Bear in mind that the best possible plan may depend on many factors, such as the number of child rows per parent, the degree to which the parent and child keys arrive in sorted (or random) order, and then you have to remember that Oracle gets a little clever with the original anti-join (note that there are only 10,000 probes for 100,000 child rows – there’s an effect similar to the scalar subquery caching going on there), so trying to patch the plan the same way for every parent/child pair may not be the best strategy.

If you want to drop the patch after playing around with this example a call to execute dbms_sqldiag.drop_sql_patch(name=>’validate_fk’) will suffice.

 

AUSOUG Connect 2019 Conference Series

  AUSOUG will again be running their excellent CONNECT 2019 conference series this year at the following great venues: Monday 14th October – Rendezvous Hotel In Melbourne Wednesday 16th October –  Mercure Hotel in Perth As usual, there’s a wonderful lineup of speakers from both Australia and overseas including: Connor McDonald Scott Wesley Guy Harrison […]

Join View

It’s strange how one thing leads to another when you’re trying to check some silly little detail. This morning I wanted to find a note I’d written about the merge command and “stable sets”, and got to a draft about updatable join views that I’d started in 2016 in response to a question on OTN (as it was at the time) and finally led to a model that I’d written in 2008 showing that the manuals were wrong.

Since the manual – even the 19c manual – is still wrong regarding the “Delete Rule” for updatable (modifiable) join views I thought I’d quickly finish off the draft and post the 2008 script. Here’s what the manual says about deleting from join views (my emphasis on “exactly”):

Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. The key preserved table can be repeated in the FROM clause. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.

But here’s a simple piece of code to model a delete from a join view that breaks the rule:


rem
rem     Script:         delete_join.sql 
rem     Dated:          Dec 2008
rem     Author:         J P Lewis
rem

create table source
as
select level n1
from dual
connect by level <= 10
/ 
 
create table search
as
select level n1
from dual
connect by level <= 10
/ 

alter table source modify n1 not null;
alter table search modify n1 not null;

create unique index search_idx on search(n1);
-- create unique index source_idx on source(n1)

I’ve set up a “source” and a “search” table with 10 rows each and the option for creating unique indexes on each table for a column that’s declared non-null. Initially, though, I’ve only created the index on search to see what happens when I run a couple of “join view” deletes using “ANSI” syntax.

prompt  ===============================
prompt  Source referenced first in ANSI
prompt  ===============================

delete from (select * from source s join search s1 on s.n1 = s1.n1);
select count(1) source_count from source;
select count(1) search_count from search;
rollback;
 
prompt  ===============================
prompt  Search referenced first in ANSI
prompt  ===============================

delete from (select * from search s join source s1 on s.n1 = s1.n1);
select count(1) source_count from source;
select count(1) search_count from search;
rollback;

With just one of the two unique indexes in place the order of the tables in the inline view makes no difference to the outcome. Thanks to the unique index on search any row in the inline view corresponds to exactly one row in the source table, while a single row in the search table could end up appearing in many rows in the view – so the delete implictly has to operate as “delete from source”. So both deletes will result in the source_count being zero, and the search_count remaining at 10.

If we now repeat the experiment but create BOTH unique indexes, both source and search will be key-preserved in the join. According to the manual the delete should produce some sort of error. In fact the delete works in both cases – but the order that the tables appear makes a difference. When source is the first table in the in-line view the source_count drops to zero and the search_count stays at 10; when search is the first table in the in-line view the search_count drops to zero and the source_count stays at 10.

I wouldn’t call this totally unreasonable – but it’s something you need to know if you’re going to use the method, and something you need to document very carefully in case someone editing your code at a later date (or deciding that they could add a unique index) doesn’t realise the significance of the table order.

This does lead on to another important test – is it the order that the tables appear in the from clause that matters, or the order they appear in the join order that Oracle uses to optimise the query. (We hope – and expect – that it’s the join order as written, not the join order as optimised, otherwise the effect of the delete could change from day to day as the optimizer chose different execution plans!). To confirm my expectation I switched to traditional Oracle syntax with hints (still with unique indexes on both tables), writing a query with search as the first table in the from clause, but hinting the inline view to vary the optimised join order.


prompt  ============================================
prompt  Source hinted as leading table in join order 
prompt  ============================================

delete from (
        select 
                /*+ leading(s1, s) */
                * 
        from 
                search s,
                source s1 
        where
                s.n1 = s1.n1
        )
;

select count(1) source_count from source; 
select count(1) search_count from search;
rollback;

prompt  ============================================
prompt  Search hinted as leading table in join order 
prompt  ============================================

delete from (
        select 
                /*+ leading(s, s1) */
                * 
        from 
                search s,
                source s1 
        where
                s.n1 = s1.n1
        )
;

select count(1) source_count from source; 
select count(1) search_count from search;
rollback;

In both cases the rows were deleted from search (the first table in from clause). And, to answer the question you should be asking, I did check the execution plans to make sure that the hints had been effective:


============================================
Source hinted as leading table in join order
============================================

------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | DELETE STATEMENT    |            |    10 |    60 |     1 |
|   1 |  DELETE             | SEARCH     |       |       |       |
|   2 |   NESTED LOOPS      |            |    10 |    60 |     1 |
|   3 |    INDEX FULL SCAN  | SOURCE_IDX |    10 |    30 |     1 |
|*  4 |    INDEX UNIQUE SCAN| SEARCH_IDX |     1 |     3 |       |
------------------------------------------------------------------

============================================
Search hinted as leading table in join order
============================================

------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | DELETE STATEMENT    |            |    10 |    60 |     1 |
|   1 |  DELETE             | SEARCH     |       |       |       |
|   2 |   NESTED LOOPS      |            |    10 |    60 |     1 |
|   3 |    INDEX FULL SCAN  | SEARCH_IDX |    10 |    30 |     1 |
|*  4 |    INDEX UNIQUE SCAN| SOURCE_IDX |     1 |     3 |       |
------------------------------------------------------------------

Summary

Using updatable join views to handle deletes can be very efficient but the manual’s statement of the “Delete Rule” is incorrect. It is possible to have several key-preserved tables in the view that you’re using, and if that’s the case you need to play safe and ensure that the table you want to delete from is the first table in the from clause. This means taking steps to eliminate the risk of someone editing some code at a later date without realising the importance of the table order.

 

pgbench retry for repeatable read transactions — first (re)tries

pgbench retry for repeatable read transactions — first (re)tries

Trying a no-patch solution for pgbench running on repeatable read transactions, using a custom script with PL/pgSQL

In a previous post I was running pgBench on YugaByteDB in serializable isolation level. But Serializable is optimistic and requires that the transactions are re-tried when failed. But pgBench has no retry mode. There was a patch proposed in several commit fests for that, but patch acceptance is a long journey in PostgreSQL:

WIP: Pgbench Errors and serialization/deadlock retries

Rather than patching pgbench.c I’m trying to implement a retry logic with the preferred procedural code: plpgsql. This is not easy because there are many limits with transaction management in procedures. So let’s start simple with a Repetable Read isolation level and trying to get most of the threads not failing before the end.

pgBench simple-update builtin

I create a database for my demo with Repeatable Read default isolation level, and initialize the pgBench schema:

## restart the server
pg_ctl -l $PGDATA/logfile restart
## create the database and the procedure
psql -e <<'PSQL'
-- re-create the demo database and set serializable as default
drop database if exists franck;
create database franck;
alter database franck set default_transaction_isolation='repeatable read';
PSQL
pgbench --initialize --init-steps=dtgvpf franck

alter database set default_transaction_isolation=’repeatable read’;
\! pgbench — initialize — init-steps=dtgvpf

Then I run the built-in simple update, from 10 threads:

pgbench --builtin=simple-update --time 30 --jobs=10 --client=10 franck

Here is the disappointing result:

My clients fail quickly as soon as they encounter a serialization error and all finishes with few threads only. Those errors are normal in a MVCC database. Maximum concurrency is achieved with optimistic locking. It is up to the application to re-try when encountering a serialization error.

But pgBench has no option for that. To workaround this I create a procedure that does the same job as the simple-update builtin, so that I have a procedural language (PL/pgSQL) to do those retries.

pgBench simple-update script

Here is what is actually harcoded for the simple-update builtin:

cat > /tmp/simple-update.sql <<'CAT'
-- simple-update
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
CAT

I can run it with:

pgbench --file=/tmp/simple-update.sql --time 30 --jobs=10 --client=10 franck

Of course, the result is the same as with the built-in: threads die as soon as they encounter a serialization error.

pgBench simple-update anonymous block

My first idea was to run an anonymous block in order to add some procedural code for retries:

cat > /tmp/simple-update.sql <<'CAT'
-- simple-update
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
DO
$$
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
$$
CAT

but this is not possible because bind variables are not possible in anonymous blocks:

ERROR: bind message supplies 7 parameters, but prepared statement “” requires 0

pgBench simple-update procedure

Then I create a stored procedure for that procedural code.

Here is the call:

cat > /tmp/simple-update.sql <<'CAT'
-- simple-update
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
call SIMPLE_UPDATE_RETRY(:aid, :bid, :tid, :delta);
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
CAT

I keep the SELECT in the client as I cannot return a resultset from a procedure.

The UPDATE and INSERT are run into the procedure:

psql -e <<'PSQL'
\connect franck
create or replace procedure SIMPLE_UPDATE_RETRY
(p_aid integer, p_bid integer, p_tid integer, p_delta integer) AS $$
BEGIN
UPDATE pgbench_accounts SET abalance = abalance + p_delta
WHERE aid = p_aid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (p_tid, p_bid, p_aid, p_delta, CURRENT_TIMESTAMP);
END;
$$ language plpgsql;
PSQL

Of course, I’ll get the same error as there’s no retry logic yet:

pgbench --file=/tmp/simple-update.sql --time 30 --jobs=10 --client=10 franck

ERROR: could not serialize access due to concurrent update

pgBench simple-update procedure with retry

Now that I have a procedure I can add the retry logic:

  • a loop to be able to retry
  • in the loop, a block with exception
  • when no error is encountered, exit the loop
  • when error is encountered, rollback and continue the loop
  • in case there are too many retries, finally raise the error
psql -e <<'PSQL'
\connect franck
create or replace procedure SIMPLE_UPDATE_RETRY
(p_aid integer, p_bid integer, p_tid integer, p_delta integer) AS $$
declare
retries integer:=0;
BEGIN
loop
begin
UPDATE pgbench_accounts SET abalance = abalance + p_delta
WHERE aid = p_aid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (p_tid, p_bid, p_aid, p_delta, CURRENT_TIMESTAMP);
-- exits the loop as we got no error after all DML
exit;
exception
when serialization_failure then
-- need to rollback to run in a new snapshot
rollback;
-- give up after 10 retries
if retries >10 then
raise notice 'Give Up after % retries. tid=%',retries,p_tid;
raise;
end if;
-- continue the retry loop
end;
retries=retries+1;
end loop;
commit;
if retries > 2 then
raise notice 'Required % retries (tid=%)',retries,p_tid;
end if;
END;
$$ language plpgsql;
PSQL

Note that the “rollback” is required. The exception block does a rollback to savepoint, but then it continues to run in the same snapshot. Without starting another transaction the same serialization error will be encountered.

Here is one run without any serialization error:

However, with less luck I can encounter this:

The serialization errors now occur on COMMIT. That’s another big surprise when coming from Oracle. Oracle has weakest isolation levels, but the goal is to avoid errors at commit, which is critical for OLTP and even more with distributed transactions. With PostgreSQL, the commit can raise an error. But unfortunately, my PL/pgSQL procedure cannot prevent that because I cannot commit within an exception block. That’s another limitation of transaction control in procedures.

protocol=prepared

I used the default “simple” protocol here. Obviously, I want to use “prepared” because I don’t want to parse each execute: my benchmark goal is to measure execution rate, not parsing. But running what I have here (call to procedure with a rollback in the exception handler) crashes with prepared statement:

And with PostgreSQL it seems that when a client crashes, many others fear a memory corruption and prefer to abort. I even got some case where instance recovery was required. That’s annoying and not easy to troubleshoot. The core dump says segmentation fault on pquery.c:1241 when reading pstmt->utilityStmt.

In summary… small solutions and big questions

The retry logic is mandatory when benchmarking on repeatable read or serializable isolation level. Pgbench is there to do benchmarks for PostgreSQL flavors. I want to also use it to benchmark other databases with postgres-compatible APIs, like CockroachDB or YugaByteDB. Those databases require a serializable isolation level, at least for some features. But I faced many limitations:

  • I cannot use anonymous blocks with bind variables ➛ stored procedure
  • I cannot return the select result from a stored procedure (and cannot do transaction management in functions) ➛ SELECT is done out of the transaction
  • cannot commit in exception blocks ➛ remains the possibility of non-retried error

If you have any remark about this, please tell me (Twitter: @FranckPachot). Of course I’ll investigate further on the core dump and other possibilities. In my opinion, those retries must be done in the server and not in a client loop. It makes no sense to add additional round-trips and context switches for this. Pgbench is there to do TPC-B-like benchmarks where each transaction should be at most one call to the database.

DDL invalidates your SQL right ?

I stumbled upon this post by optimizer guru Nigel Bayliss last week, so please have a read of that first before proceeding. But I wanted to show a simple demo of how management of cursors continues to improve with each version of the database.

Throughout the many years and versions of using Oracle, a common mantra has been: if you perform DDL on a table, then any SQL cursors that reference that table will become invalidated and re-parsed on next execution. That makes a good deal of sense because if you (say) drop a column from a table, then a “SELECT * FROM MY_TABLE WHERE ID = 123” is a significantly different proposition than it was before the drop of the column. The asterisk (‘*’) now resolves to something different, and the SQL might not even be valid anymore if the column that was dropped was the ID column. A re-parse is mandatory.

But not all DDL’s are equal, or perhaps a better phrasing would be “not all DDLs are as severe as others”, and since parsing is an expensive operation, any time that we can avoid having to do it is a good thing. Some DDLs are so “innocuous” that we know that a re-parse is not required. Here is an example of that in action in 18c.

I’ll create a table, issue a simple query on it, and check its details in V$SQL. I’m using the cool “set feedback on sql_id” facility in SQL*Plus 18c to immediately get the SQL_ID.


SQL> create table t as select * from all_objects;

Table created.

SQL> set feedback on sql_id
SQL> select count(*) from t;

  COUNT(*)
----------
     76921

1 row selected.

SQL_ID: cyzznbykb509s

SQL> select
  2       sql_text
  3      ,sql_fulltext
  4      ,sql_id
  5      ,executions
  6      ,parse_calls
  7      ,invalidations
  8      ,ddl_no_invalidate
  9      ,is_rolling_invalid
 10      ,is_rolling_refresh_invalid
 11   from v$sql where sql_id = 'cyzznbykb509s'
 12  @pr

==============================
SQL_TEXT                      : select count(*) from t
SQL_FULLTEXT                  : select count(*) from t
SQL_ID                        : cyzznbykb509s
EXECUTIONS                    : 1
PARSE_CALLS                   : 1
INVALIDATIONS                 : 0
DDL_NO_INVALIDATE             : N
IS_ROLLING_INVALID            : N
IS_ROLLING_REFRESH_INVALID    : N

PL/SQL procedure successfully completed.

Now I’ll perform a DDL on the table that does not change the structure, the data or the security privileges on the table


SQL> alter table t read only;

Table altered.

In versions of yester year, this would invalidate any cursors even though nothing about the table has changed. But with 18c, the DDL_NO_VALIDATE column tells us that even though a DDL was performed, we did not need to invalidate the cursor.


SQL> select
  2       sql_text
  3      ,sql_fulltext
  4      ,sql_id
  5      ,executions
  6      ,parse_calls
  7      ,invalidations
  8      ,ddl_no_invalidate
  9      ,is_rolling_invalid
 10      ,is_rolling_refresh_invalid
 11   from v$sql where sql_id = 'cyzznbykb509s'
 12  @pr
==============================
SQL_TEXT                      : select count(*) from t
SQL_FULLTEXT                  : select count(*) from t
SQL_ID                        : cyzznbykb509s
EXECUTIONS                    : 1
PARSE_CALLS                   : 1
INVALIDATIONS                 : 0
DDL_NO_INVALIDATE             : Y
IS_ROLLING_INVALID            : N
IS_ROLLING_REFRESH_INVALID    : N

PL/SQL procedure successfully completed.

SQL>

Note that this column in V$SQL is also present in most versions of 12c, but does not appear to be populated reliably until you get to 18c.

Oracle 19c Automatic Indexing: My First Auto Index (Absolute Beginners)

I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issues below: I thought it was time to show the new Oracle 19c Automatic Indexing feature in action and what better way than to go through how I created my first ever Automatic Index. To start, I create a […]

ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions and NULLs

While researching interval partitioning in Oracle 19c I came across a phenomenon I had already solved a long time ago but then forgot about. This time I decided to write about it so I don’t have to spend 5 minutes rediscovering the wheel. As always, if you are following along, make sure you are appropriately licensed for this feature

The Situation

Consider the following example:

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 16 09:32:17 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri Aug 16 2019 09:07:55 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> create table t (id, vc, d) 
  2  partition by range (id) interval (5000)
  3  ( 
  4   partition p1 values less than (5000)
  5  ) as
  6  select object_id, object_name, created from dba_objects;
create table t (id, vc, d)
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted
number of partitions

SQL> !oerr ora 14300
14300, 00000, "partitioning key maps to a partition outside maximum permitted number of partitions"
// *Cause:  The row inserted had a partitioning key that maps to a partition number greater than 1048575
// *Action  Ensure that the partitioning key falls within 1048575 partitions or subpartitions.

I knew about the limit of 1048575 partitions, but that shouldn’t be the case here:

SQL> select max(object_id), max(object_id) / 5000 from dba_objects;

MAX(OBJECT_ID) MAX(OBJECT_ID)/5000
-------------- -------------------
         75743             15.1486

There shouldn’t be more than 16 partitions …

NEARBY: not having a not-null constraint on a partition key might be an indication of a sub-optimal design choice.

A quick search revealed an article on AskTom mentioning NULLs. I hadn’t thought about NULL values in dba_objects.object_id, but apparently there are:

SQL> select object_id, object_name, object_type from dba_objects where object_id is null;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
           SYS_HUB                        DATABASE LINK

Today I learned that database links don’t have object_ids. Neither is there a data_object_id.

Summary

Don’t try inserting NULLs as a partitioning key if that partition key uses interval partitioning. The error message, although strictly speaking correct, lead me astray for a moment:

SQL> create table t (id, vc, d) 
  2  partition by range (id) interval (5000)
  3  (
  4   partition p1 values less than (5000)
  5  )
  6  as
  7  select object_id, object_name, created from dba_objects 
  8  WHERE OBJECT_ID IS NOT NULL;

Table created.

Problem solved! Another, probably better approach, would be the addition of a not null constraint in the first place:

SQL> create table t (id not null, vc, d)
  2  partition by range (id) interval (5000)
  3  (
  4   partition p1 values less than (5000)
  5  )
  6  as select object_id, object_name, created from dba_objects;

ERROR at line 6:
ORA-01400: cannot insert NULL into ("MARTIN"."T"."ID")

The intention of my post was to help users who get an ORA-14300 although they don’t exceed the partition limit. It’s not a post about good design ;)

Interlude

This actually works for non-interval partitioned tables, but ONLY if you have a catch all partition:

SQL> select dbms_rowid.rowid_object(rowid) from t1 where id is null;

DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
                         75564

SQL> select object_name, subobject_name, object_id, data_object_id 
  2  from dba_objects where data_object_id = 75564
  3  /

OBJECT_NAME          SUBOBJECT_NAME        OBJECT_ID DATA_OBJECT_ID
-------------------- -------------------- ---------- --------------
T1                   PMAX                      75564          75564

SQL> select high_value from user_tab_partitions where table_name = 'T1' and partition_name = 'PMAX'
  2  /

HIGH_VALUE
------------------------------
MAXVALUE

Hope this helps!

Oracle Connection Manager (CMAN) quick reporting script

Here is a script I use to parse Connection Manager “show service”

List services registered by instance

CMCTL can show the services in a long list, but I want something quick like this, with one line per service, and one column per endpoint that registers to CMAN:

script output, a bit obfuscated

The following script

  • get all CMAN running on the current host (with pgrep tnslsnr)
  • run CMCTL with the righ environment variables
  • run “administer” and “show services”
  • parse the output with AWK to put instances into columns
  • resolves IP addresses by colling “host” and removes the domain name
ps --no-headers -o pid,args -p$(pgrep -f "tnslsnr .* -mode proxy") |
while IFS=" " read pid tnslsnr args
do
# get environment variables
awk '
BEGIN{RS="\0"}
/^ORACLE_HOME|^TNS_ADMIN|^LD_LIBRARY_PATH/{printf "%s ",$0}
END{print cmctl,here}
' cmctl="$(dirname $tnslsnr)/cmctl" here="<<-'CMCTL'" /proc/$pid/environ
# name is probably the first arg without '-' nor '='
name=$(echo "$args"|awk 'BEGIN{RS=" "}/^[^-][^=]*$/{print;exit}')
echo "administer $name"
echo "show services"
echo "CMCTL"
done | sh | awk '
/Service ".*" has .* instance/{
gsub(qq," ")
sub(/[.].*/,"") # remove domain
service=$2
all_service[service]=1
stats="-"
}
/Instance ".*", status READY, has .* handler.* for this service/{
gsub(qq," ")
instance=$2
all_instance[instance]=0
stats="-"
}
/established:.* refused:.* state:.*/{
sub(/^ */,"")
sub(/.DEDICATED./,"D")
sub(/established:/,"")
sub(/refused:/,"/")
sub(/state:/,"")
sub(/ready/,"R")
stats=$0
}
/ADDRESS.*HOST=.*PORT=/{
port=$0;sub(/.*PORT=/,"",port);sub(/[)].*/,"",port)
host=$0;sub(/.*HOST=/,"",host);sub(/[)].*/,"",host)
if (host ~ /^[0-9.]+$/) {
"host "host| getline host_host
sub(/^.* /,"",host_host)
sub(/[.]$/,"",host_host)
host=host_host
}
sub(/[.].*/,"",host) # remove domain
all_instance_host[instance]=host
all_instance_port[instance]=port
all_instance_instance[instance]=instance
all_instance_stats[instance]=stats
all_service_instance[service,instance]=instance
if (length(host) > all_instance[instance] ) {
all_instance[instance]= length(host)
}
if (length(port) > all_instance[instance] ) {
all_instance[instance]= length(port)
}
if (length(instance) > all_instance[instance] ) {
all_instance[instance]= length(instance)
}
}
END{
# host
printf "1%39s ","host:"
for (instance in all_instance){
printf "%-"all_instance[instance]"s ", all_instance_host[instance]
}
printf "\n"
# port
printf "2%39s ","port:"
for (instance in all_instance){
printf "%-"all_instance[instance]"s ", all_instance_port[instance]
}
printf "\n"
# instance
printf "3%39s ","instance:"
for (instance in all_instance){
printf "%-"all_instance[instance]"s ", all_instance_instance[instance]
}
printf "\n"
# stats
printf "4%39s ","established/refused:"
for (instance in all_instance){
printf "%-"all_instance[instance]"s ", all_instance_stats[instance]
}
printf "\n"
# header
printf "5%39s ","---------------------------------------"
for (instance in all_instance){
printf "%-"all_instance[instance]"s ", substr("----------------------------------------",1,all_instance[instance])
}
printf "\n"
# services
for (service in all_service){
printf "%-40s ",service
for (instance in all_instance){
if (all_service_instance[service,instance]!="") {
printf "%-"all_instance[instance]"s ", all_service_instance[service,instance]
} else {
printf "%-"all_instance[instance]"s ", ""
}
}
printf "\n"
}
}' qq='"'| sort

Of course, it may be improved, and probably there are better solutions already existing. This was just faster for me rather than looking for an existing solution. Feedbacks on twitter, please:

No more stale statistics in 19c

There is an odd contradiction that we all encounter for most databases, especially if they are predominantly used during the business day. Here is how that contradiction comes to be – it is in the way that we obtain and use optimizer  statistics on those databases. The contradiction runs like this:

  • To minimize service disruption, we gather statistics at a quiet time, for example, in the middle of the night
  • We then use those statistics during the business day whilst user activity is at its highest.
  • Highest user activity will typically mean the highest frequency of data changes.
  • Hence the statistics are at their peak accuracy when no-one is using them to optimize queries, and they are at their least accurate when everyone is using them to optimize queries!

We can demonstrate this easily with the following script run in 18c.


SQL> select banner from v$version where rownum = 1 ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

1 row selected.

SQL> create table t (
  2    id number(10),
  3    num number(10),
  4    constraint t_pk primary key ( id )
  5  );

Table created.

SQL> insert into t select rownum,rownum from dual connect by rownum<=10000;

10000 rows created.

SQL> exec dbms_stats.gather_table_stats(user,'t')

PL/SQL procedure successfully completed.

SQL> select stale_stats from user_tab_statistics
  2  where  table_name = 'T';

STALE_S
-------
NO

So far I’ve mimicked a table that has been populated with (say) a days worth of data (10,000) rows, and I’ve gathered statistics at the end of the business day so that my statistics reflect the current data in the table.



SQL> select table_name, num_rows, blocks
  2  from   user_tab_statistics
  3  where  table_name = 'T';

TABLE_NAME NUM_ROWS     BLOCKS
---------- -------- ----------
T             10000         20

1 row selected.

SQL> select table_name, column_name, low_value, high_value, num_distinct
  2  from   user_tab_col_statistics
  3  where  table_name = 'T';

TABLE_NAME COLUMN_NAME          LOW_VALUE       HIGH_VALUE      NUM_DISTINCT
---------- -------------------- --------------- --------------- ------------
T          ID                   C102            C302                   10000
T          NUM                  C102            C302                   10000

2 rows selected.

Hence, if I was to optimize and run a query now, I can expect an excellent estimate from the optimizer. The query below returns a COUNT of 1000 rows, and we can see from the PLAN_TABLE output, that the estimated rows was also 1000. So a perfect estimate by the optimizer!



SQL> select max(num),count(*)
  2  from t where id > 9000;

  MAX(NUM)   COUNT(*)
---------- ----------
     10000       1000

1 row selected.

SQL> select *
  2  from table(dbms_xplan.display_cursor(format=>'typical'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |       |       |
|   1 |  SORT AGGREGATE                      |      |     1 |     8 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |  1000 |  8000 |
|*  3 |    INDEX RANGE SCAN                  | T_PK |  1000 |       |
---------------------------------------------------------------------

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

   3 - access("ID">9000)


20 rows selected.

But of course, the next business day rolls on, and by mid-morning we may have added 1000 more rows to our table, bringing the total number of rows to 11000.


SQL> insert into t select rownum+10000,rownum+10000 from dual connect by rownum<=1000;

1000 rows created.

SQL> commit;

Commit complete.

Even with an explicit flush of the database monitoring information, we not yet reached the threshold where this tables statistics would be considered stale, so even a GATHER_STALE operation would have no effect if we ran one. The statistics still reflect the data from last night.


SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select stale_stats from user_tab_statistics
  2  where  table_name = 'T';

STALE_S
-------
NO

SQL> select table_name, num_rows, blocks
  2  from   user_tab_statistics
  3  where  table_name = 'T';

TABLE_NAME NUM_ROWS     BLOCKS
---------- -------- ----------
T             10000         20

1 row selected.

SQL> select table_name, column_name, low_value, high_value
  2  from   user_tab_col_statistics
  3  where  table_name = 'T';

TABLE_NAME COLUMN_NAME          LOW_VALUE       HIGH_VALUE
---------- -------------------- --------------- ---------------
T          ID                   C102            C302
T          NUM                  C102            C302

2 rows selected.

When I run my same query, and don’t forget, this would now be during the business day, when it is critical that I get a good optimizer plan, you can see that the result is now 2000 rows, but the plan estimate is left languishing at its original 1000 rows because the statistics no long reflect the current state of the table.


SQL> select max(num),count(*)
  2  from t where id > 9000;

  MAX(NUM)   COUNT(*)
---------- ----------
     11000       2000

1 row selected.

SQL> select *
  2  from table(dbms_xplan.display_cursor(format=>'typical'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID  8juuu5warw2z6, child number 0
-------------------------------------
select max(num),count(*) from t where id > 9000

Plan hash value: 2053823973

---------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |       |       |
|   1 |  SORT AGGREGATE                      |      |     1 |     8 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |  1000 |  8000 |
|*  3 |    INDEX RANGE SCAN                  | T_PK |  1000 |       |
---------------------------------------------------------------------

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

   3 - access("ID">9000)


20 rows selected.

Having great statistics that are not used, and poor statistics that are used all the time seems a nonsensical way to do things, but there really hasn’t been any alternative unless you were prepared to consume precious server resources to collect statistics whilst your users are performing their transactions. I have known customers to do this in the past, but of course, it takes careful monitoring and management to ensure that the slicing up of the resource “pie” is done appropriately to keep the right balance between business needs and background database tasks.

19c brings a nifty solution to this contradiction by having the database automatically maintain details about how data is changing in a table via a feature called Real Time Statistics. DML activities can be tracked, and that information can then be looped back as input into future query parsing. Here’s the same example from above, this time performed in 19c.

The same table is seeded with the same initial 10000 rows as before. I have included a new column in my data dictionary queries called NOTES. You will see the significance of that shortly.



SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

1 row selected.

SQL>
SQL> create table t (
  2    id number(10),
  3    num number(10),
  4    constraint t_pk primary key ( id )
  5  );

Table created.

SQL> insert into t select rownum,rownum from dual connect by rownum<=10000;

10000 rows created.

SQL> exec dbms_stats.gather_table_stats(user,'t')

PL/SQL procedure successfully completed.

SQL> select stale_stats from user_tab_statistics
  2  where  table_name = 'T';

STALE_S
-------
NO

1 row selected.

SQL> select notes, table_name, num_rows, blocks
  2  from   user_tab_statistics
  3  where  table_name = 'T';

NOTES                            TABLE_NAME NUM_ROWS     BLOCKS
-------------------------------- ---------- -------- ----------
                                 T             10000         20

1 row selected.

SQL> select notes, table_name, column_name, low_value, high_value, num_distinct
  2  from   user_tab_col_statistics
  3  where  table_name = 'T';

NOTES                            TABLE_NAME COLUMN_NAME          LOW_VALUE       HIGH_VALUE      NUM_DISTINCT
-------------------------------- ---------- -------------------- --------------- --------------- ------------
                                 T          ID                   C102            C302                   10000
                                 T          NUM                  C102            C302                   10000

And since this is a simple query, the optimizer performs exactly as per 18c for its estimate. Once again, at this point, where the data and statistics are in alignment, the estimate is perfect.


                                 
SQL> select max(num),count(*)
  2  from t where id > 9000;

  MAX(NUM)   COUNT(*)
---------- ----------
     10000       1000

1 row selected.                                 


SQL> select *
  2  from table(dbms_xplan.display_cursor(format=>'typical'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------

----------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |       |       |     5 (100)|
|   1 |  SORT AGGREGATE                      |      |     1 |     8 |            |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |  1000 |  8000 |     5   (0)|
|*  3 |    INDEX RANGE SCAN                  | T_PK |  1000 |       |     3   (0)|
----------------------------------------------------------------------------------

The business day commences, and a fresh 1000 rows are added to the database.



SQL> insert into t select rownum+10000,rownum+10000 from dual connect by rownum<=1000;

1000 rows created.

SQL> commit;

Commit complete.

And just like before, this is not enough for the statistics on the table to be considered stale by the database



SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL>
SQL> select stale_stats from user_tab_statistics
  2  where  table_name = 'T';

STALE_S
-------
NO

But, here is where the 19c enhancements come into play. When we query the data dictionary, we now have TWO rows of statistics for this single table.



SQL> select notes, table_name, num_rows, blocks
  2  from   user_tab_statistics
  3  where  table_name = 'T';

NOTES                            TABLE_NAME NUM_ROWS     BLOCKS
-------------------------------- ---------- -------- ----------
                                 T             10000         20
STATS_ON_CONVENTIONAL_DML        T             11000         20

2 rows selected.

The first row is the original statistics we gathered. The second row is new statistics we have derived from the normal DML activities that have occurred on the database. This is not a full GATHER operation, it is simply taking advantage of simple mathematics, eg if I have 10000 rows and then insert 50 and deleted 20, then I would have 10030 rows etc. The real time statistics can also keep track of simple column level statistics, like whether the low and high water mark values have changed.



SQL> select notes, table_name, column_name, low_value, high_value
  2  from   user_tab_col_statistics
  3  where  table_name = 'T';

NOTES                            TABLE_NAME COLUMN_NAME          LOW_VALUE       HIGH_VALUE
-------------------------------- ---------- -------------------- --------------- ----------
                                 T          ID                   C102            C302
                                 T          NUM                  C102            C302
STATS_ON_CONVENTIONAL_DML        T          ID                   C102            C302094A
STATS_ON_CONVENTIONAL_DML        T          NUM                  C102            C302094A

The real test is whether these statistics can be beneficial to our sample query. I’ll run that through again.



SQL> select max(num),count(*)
  2  from t where id > 9000;

  MAX(NUM)   COUNT(*)
---------- ----------
     11000       2000

1 row selected.

SQL> select *
  2  from table(dbms_xplan.display_cursor(format=>'typical'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     7 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1895 | 15160 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("ID">9000)

Note
-----
   - dynamic statistics used: statistics for conventional DML

Notice the estimate of 1895 is much closer now to the true value of 2000. You can expect a little variation here from the true value, because as I mentioned before, real time statistics is not about performing a full gather operation on the table – that would be far too resource hungry for every single DML. So some elements of the statistics (for example, the number of distinct values, or histogram distribution) are not feasible to keep up to date every time a DML is performed. But even having the improved row counts and column extrema have yielded benefit on the execution plan. When the normal nightly gather job comes along, these DML statistics are no longer relevant and will be expunged


SQL> exec dbms_stats.gather_table_stats(user,'t')

PL/SQL procedure successfully completed.

SQL> select notes, table_name, num_rows, blocks
  2  from   user_tab_statistics
  3  where  table_name = 'T';

NOTES                            TABLE_NAME NUM_ROWS     BLOCKS
-------------------------------- ---------- -------- ----------
                                 T             11000         20

1 row selected.

SQL> select notes, table_name, column_name, low_value, high_value
  2  from   user_tab_col_statistics
  3  where  table_name = 'T';

NOTES                            TABLE_NAME COLUMN_NAME          LOW_VALUE       HIGH_VALUE
-------------------------------- ---------- -------------------- --------------- ----------
                                 T          ID                   C102            C3020B
                                 T          NUM                  C102            C3020B

2 rows selected.

There are other enhancements in 19c to keep the statistics in the dictionary closer to the true data in database tables which you can read about here.

Full disclosure: Some 19c new features are available on certain Oracle Database platforms only. Check the Oracle Database Licensing Guide for more information

2019 Public Appearances (What In The World)

I’ll be presenting at a number of Oracle events over the remainder of the year. Details as follows: Oracle Open World – San Francisco (16-19 September 2019)