Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Regular Expression Functions are Considered To Be Non-Deterministic from Oracle 12.2

You cannot put non-deterministic functions into virtual columns or function-based index.  Regular expressions have always been sensitive to National Language Support (NLS) settings because what is a character is different in different languages.  However, from Oracle 12.2. regular expressions are considered to be non-deterministic because Oracle has fixed bug 20804063.

Background 

A developer came to me with the following query, complaining it was slow.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT …
FROM …
AND LOWER(REGEXP_REPLACE(ASCIISTR(my_column), '[^a-zA-Z0-9]')) ='2732018'

Obviously, functions on columns in predicates prevent the use of indexes, so I suggested creating and indexing a virtual column (slightly more elegant and almost the same as a function-based index but without needing the function in the code to match the index).
This works fine in Oracle 12.1, but they came back saying it didn't in 12.2.  Note the error messages in bold below.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">DROP TABLE t PURGE
/
CREATE TABLE t AS
SELECT TO_CHAR(sysdate-level,'YYYY/DDD') a
FROM dual
CONNECT BY LEVEL < 1000
/

CREATE INDEX t1 on t (REGEXP_REPLACE(a,'[^a-zA-Z0-9]'))
/
ORA-01743: only pure functions can be indexed

DROP INDEX t1
/
ALTER TABLE t
ADD b AS (REGEXP_REPLACE(a,'[^a-zA-Z0-9]')) VIRTUAL
/
ORA-54002: only pure functions can be specified in a virtual column expression

I eventually came across this question and answer by Connor McDonald on AskTom: Adding constraint with REGEXP_REPLACE fails that explains that Oracle has fixed bug 20804063 in 12.2.  This fix affects virtual columns, function-based indexes (because they also create virtual columns), and constraints.
There are a number of workarounds, but none are completely satisfactory.

Workaround 1: Deterministic Function 

As Connor suggests, you can work around this unwanted behaviour by creating your own PL/SQL function and telling the database it is deterministic. This is not simply lying to the database.  Not all regular expressions are actually NLS sensitive, it depends what you are doing.  However, it does appear that the validation is applied regardless. If the function is NLS sensitive that might have unwanted consequences, including incorrect query results. You would have to decide whether you can live with the risk depending on what is actually in the column and function in question.

#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 FUNCTION my_regexp_replace(p1 VARCHAR2, p2 VARCHAR2) 
RETURN varchar2
DETERMINISTIC IS
BEGIN
RETURN REGEXP_REPLACE(p1, p2);
END;
/
show error

CREATE INDEX t1 ON t (my_regexp_replace(a,'[^a-zA-Z0-9]'))
/
DROP INDEX t1
/
ALTER TABLE t
ADD b AS (my_REGEXP_REPLACE(a,'[^a-zA-Z0-9]')) VIRTUAL
/
CREATE INDEX t1 ON t (b)
/

Workaround 2: Refresh on Commit Materialized View 

Another possible workaround would be a materialized view that refreshes on commit, with the expression creating an additional column that is then indexed.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE TABLE t AS 
SELECT rownum n
, TO_CHAR(sysdate-level,'YYYY/DDD') a
FROM dual
CONNECT BY LEVEL < 1000
/
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (n)
/
CREATE MATERIALIZED VIEW LOG ON t
/

CREATE MATERIALIZED VIEW T1_MV
REFRESH ON COMMIT
FAST
WITH PRIMARY KEY
ENABLE QUERY REWRITE AS
SELECT t.*
,REGEXP_REPLACE(a,'[^a-zA-Z0-9]') b
FROM t
/

CREATE INDEX t1_mv_b ON t1_mv(b);

INSERT INTO t
SELECT ROWNUM+1000 n
, TO_CHAR(sysdate-level-1000,'YYYY/DDD') a
FROM dual
CONNECT BY LEVEL < 1000
/
commit
/

set autotrace on
SELECT * FROM t
WHERE REGEXP_REPLACE(a,'[^a-zA-Z0-9]') like '201720%';

And Oracle does indeed rewrite the query to use the materialised view and then uses the index on the materialized view.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 80%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">Plan hash value: 3543552962

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID BATCHED| T1_MV | 1 | 21 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_MV_B | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

However, this solution has the same weakness of being dependent on NLS settings – it is just that there is no validation to stop you! There is the further threat that the refresh on commit could become a performance problem if there is intense DDL on the underlying table.

Workaround 3: Use a Non-NLS Dependent Function 

Ideally, it would be better to use a non-NLS dependent function instead of a REGEXP% function. However, this may be easier said than done.  Regular expressions solve problems that are hard to do with basic string handling functions and may require a PL/SQL function anyway to hold procedural code.  Although in this relatively simple example it is possible to remove the unwanted characters with the translate and replace functions. There is no NLS restriction here.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT a
, REGEXP_REPLACE(a,'[^0-9]')
, REPLACE(TRANSLATE(a,'/',' '),' ','')
FROM t
WHERE rownum <= 10
/

A REGEXP_REPLACE(A,'[^0-9]') REPLACE(TRANSLATE(A,'/',''),'','
-------- -------------------------------- --------------------------------
2019/031 2019031 2019031
2019/030 2019030 2019030
2019/029 2019029 2019029
2019/028 2019028 2019028
2019/027 2019027 2019027
2019/026 2019026 2019026
2019/025 2019025 2019025
2019/024 2019024 2019024
2019/023 2019023 2019023
2019/022 2019022 2019022

And you can put this into a function-based index or virtual column thus

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">DROP INDEX t1 
/
ALTER TABLE t ADD b AS (REPLACE(TRANSLATE(a,'/',' '),' ','')) VIRTUAL
/
CREATE INDEX t1 ON t (b)
/

Recover dropped tables with Virtual Access Restore in #Exasol

The technique to recover only certain objects from an ordinary backup is called Virtual Access Restore. Means you create a database from backup that contains only the minimum elements needed to access the objects you request. This database is then removed afterwards.

Let’s see an example. This is my initial setup:

EXAoperation Database pagehttps://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=1240&h=270 1240w, https://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=150&h=33 150w, https://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=300&h=65 300w, https://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=768&h=167 768w, https://uhesse.files.wordpress.com/2019/02/virt_access1.png?w=1024&h=223 1024w" sizes="(max-width: 620px) 100vw, 620px" />

One database in a 2+1 cluster. Yes it’s tiny because it lives on my notebook in VirtualBox. See here how you can get that too.

It uses the data volume v0000 and I took a backup into the archive volume v0002 already.

EXAoperation volumeshttps://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=1238&h=480 1238w, https://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=300&h=116 300w, https://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=768&h=298 768w, https://uhesse.files.wordpress.com/2019/02/virt_access2.png?w=1024&h=397 1024w" sizes="(max-width: 620px) 100vw, 620px" />

I have a schema named RETAIL there with the table SALES:

RETAIL.SALEShttps://uhesse.files.wordpress.com/2019/02/virt_access3.png?w=150&h=61 150w, https://uhesse.files.wordpress.com/2019/02/virt_access3.png?w=300&h=121 300w, https://uhesse.files.wordpress.com/2019/02/virt_access3.png 649w" sizes="(max-width: 620px) 100vw, 620px" />

By mistake, that table gets dropped:

drop tablehttps://uhesse.files.wordpress.com/2019/02/virt_access4.png?w=150 150w, https://uhesse.files.wordpress.com/2019/02/virt_access4.png?w=300 300w" sizes="(max-width: 430px) 100vw, 430px" />

And I’m on AUTOCOMMIT, otherwise this could be rolled back in Exasol. Virtual Access Restore to the rescue!

First I need another data volume:

second data volumehttps://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=1240&h=240 1240w, https://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=150&h=29 150w, https://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=300&h=58 300w, https://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=768&h=149 768w, https://uhesse.files.wordpress.com/2019/02/virt_access5.png?w=1024&h=198 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Notice the size of the new volume: It is smaller than the overall size of the backup respectively the size of the “production database”! I did that to prove that space is not much of a concern here.

Then I add a second database to the cluster that uses that volume. The connection port (8564) must be different from the port used by the first database and the DB RAM in total must not exceed the licensed size, which is limited to 4 GB RAM in my case:

second databasehttps://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=1238&h=296 1238w, https://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=150&h=36 150w, https://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=300&h=72 300w, https://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=768&h=184 768w, https://uhesse.files.wordpress.com/2019/02/virt_access6.png?w=1024&h=245 1024w" sizes="(max-width: 620px) 100vw, 620px" />

I did not start that database because for the restore procedure it has to be down anyway. Clicking on the DB Name and then on the Backups button gets me here:

Foreign database backupshttps://uhesse.files.wordpress.com/2019/02/virt_access7.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2019/02/virt_access7.png?w=300&h=117 300w, https://uhesse.files.wordpress.com/2019/02/virt_access7.png?w=768&h=300 768w, https://uhesse.files.wordpress.com/2019/02/virt_access7.png 979w" sizes="(max-width: 620px) 100vw, 620px" />

No backup shown yet because I didn’t take any backups with exa_db2. Clicking on Show foreign database backups:

Backup choicehttps://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=1238&h=424 1238w, https://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=150&h=51 150w, https://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=300&h=103 300w, https://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=768&h=263 768w, https://uhesse.files.wordpress.com/2019/02/virt_access8.png?w=1024&h=351 1024w" sizes="(max-width: 620px) 100vw, 620px" />

The Expiration date must be empty for a Virtual Access Restore, so I just remove it and click Apply. Then I select the Restore Type as Virtual Access and click Restore:

Virtual Access Restorehttps://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=1240&h=414 1240w, https://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=150&h=50 150w, https://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=300&h=100 300w, https://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=768&h=257 768w, https://uhesse.files.wordpress.com/2019/02/virt_access9.png?w=1024&h=342 1024w" sizes="(max-width: 620px) 100vw, 620px" />

This will automatically start the second database:

Two databases in one clusterhttps://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=1240&h=308 1240w, https://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=150&h=37 150w, https://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=300&h=74 300w, https://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=768&h=190 768w, https://uhesse.files.wordpress.com/2019/02/virt_access10.png?w=1024&h=254 1024w" sizes="(max-width: 620px) 100vw, 620px" />

I connect to exa_db2 with EXAplus, where the Schema Browser gives me the DDL for the table SALES:

ExaPlus Schema Browser get DDLhttps://uhesse.files.wordpress.com/2019/02/virt_access11.png?w=150&h=79 150w, https://uhesse.files.wordpress.com/2019/02/virt_access11.png?w=300&h=157 300w, https://uhesse.files.wordpress.com/2019/02/virt_access11.png?w=768&h=402 768w, https://uhesse.files.wordpress.com/2019/02/virt_access11.png?w=1024&h=536 1024w, https://uhesse.files.wordpress.com/2019/02/virt_access11.png 1041w" sizes="(max-width: 620px) 100vw, 620px" />

I take that to exa_db1 and run it there, which gives me the table back but empty. Next I create a connection from exa_db1 to exa_db2 and import the table

create connection exa_db2 
to '192.168.43.11..13:8564' 
user 'sys' identified by 'exasol';

import into retail.sales 
from exa at exa_db2 
table retail.sales;

This took about 2 Minutes:

Importhttps://uhesse.files.wordpress.com/2019/02/virt_access12.png?w=150 150w, https://uhesse.files.wordpress.com/2019/02/virt_access12.png?w=300 300w" sizes="(max-width: 362px) 100vw, 362px" />

The second database and then the second data volume can now be dropped. Problem solved!

 

Descending Problem

I’ve written in the past about oddities with descending indexes ( here, here, and here, for example) but I’ve just come across a case where I may have to introduce a descending index that really shouldn’t need to exist. As so often happens it’s at the boundary where two Oracle features collide. I have a table that handles data for a large number of customers, who record a reasonable number of transactions per year, and I have a query that displays the most recent transactions for a customer. Conveniently the table is partitioned by hash on the customer ID, and I have an index that starts with the customer_id and transaction_date columns. So here’s my query or, to be a little more accurate, the client’s query – simplified and camouflaged:


select  /*+ gather_plan_statistics */
        *
from    (
             select
                    v1.*,
                    rownum rn
             from   (
                             select   /*
                                         no_eliminate_oby
                                         index_rs_desc(t1 (customer_id, transaction_date))
                                      */
                                      t1.*
                             from     t1
                             where    customer_id = 50
                             and      transaction_date >= to_date('1900-01-01','yyyy-mm-dd')
                             order by transaction_date DESC
                ) v1
                where  rownum <= 10 -- > comment to avoid WordPress format issue
         )
where    rn >= 1
;

You’ll notice some hinting – the /*+ gather_plan_statistics */ will allow me to report the rowsource execution stats when I pull the plan from memory, and the hints in the inline view (which I’ve commented out in the above) will force a particular execution plan – walking through the index on (company_id, transaction_date) in descending order.

If I create t1 as a simple (non-partitioned) heap table I get the following plan unhinted (I’ve had to edit a “less than or equal to” symbol to avoid a WordPress format issue):

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |      1 |        |    14 (100)|     10 |00:00:00.01 |      14 |
|*  1 |  VIEW                           |       |      1 |     10 |    14   (0)|     10 |00:00:00.01 |      14 |
|*  2 |   COUNT STOPKEY                 |       |      1 |        |            |     10 |00:00:00.01 |      14 |
|   3 |    VIEW                         |       |      1 |     10 |    14   (0)|     10 |00:00:00.01 |      14 |
|   4 |     TABLE ACCESS BY INDEX ROWID | T1    |      1 |    340 |    14   (0)|     10 |00:00:00.01 |      14 |
|*  5 |      INDEX RANGE SCAN DESCENDING| T1_I1 |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=1)
   2 - filter(ROWNUM .LE. 10)
   5 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE" IS NOT NULL AND "TRANSACTION_DATE">=TO_DATE('
              1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Notice the descending range scan of the index – just as I wanted it – the minimal number of buffer visits, and only 10 rows (and rowids) examined from the table. But what happens if I recreate t1 as a hash-partitioned table with local index – here’s the new plan, again without hinting the SQL:


----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |       |      1 |        |   207 (100)|     10 |00:00:00.01 |     138 |       |       |          |
|*  1 |  VIEW                                          |       |      1 |     10 |   207   (1)|     10 |00:00:00.01 |     138 |       |       |          |
|*  2 |   COUNT STOPKEY                                |       |      1 |        |            |     10 |00:00:00.01 |     138 |       |       |          |
|   3 |    VIEW                                        |       |      1 |    340 |   207   (1)|     10 |00:00:00.01 |     138 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY                      |       |      1 |    340 |   207   (1)|     10 |00:00:00.01 |     138 |  2048 |  2048 | 2048  (0)|
|   5 |      PARTITION HASH SINGLE                     |       |      1 |    340 |   206   (0)|    340 |00:00:00.01 |     138 |       |       |          |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1    |      1 |    340 |   206   (0)|    340 |00:00:00.01 |     138 |       |       |          |
|*  7 |        INDEX RANGE SCAN                        | T1_I1 |      1 |    340 |     4   (0)|    340 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=1)
   2 - filter(ROWNUM. LE. 10)
   4 - filter(ROWNUM .LE. 10)
   7 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE">=TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRANSACTION_DATE" IS NOT NULL)

Even though the optimizer has recognised that is will be visiting a single partition through a local index it has not chosen a descending index range scan, though it has used the appropriate index; so it’s fetched all the relevant rows from the table in the wrong order then sorted them discarding all but the top 10. We’ve done 138 buffer visits (which would turn into disk I/Os, and far more of them, in the production system).

Does this mean that the optimizer can’t use the descending index when the table is partitioned – or that somehow the costing has gone wrong. Here’s plan with the hints in place to see what happens when we demand a descending range scan:


----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |   207 (100)|     10 |00:00:00.01 |       8 |
|*  1 |  VIEW                                 |       |      1 |     10 |   207   (1)|     10 |00:00:00.01 |       8 |
|*  2 |   COUNT STOPKEY                       |       |      1 |        |            |     10 |00:00:00.01 |       8 |
|   3 |    VIEW                               |       |      1 |    340 |   207   (1)|     10 |00:00:00.01 |       8 |
|   4 |     PARTITION HASH SINGLE             |       |      1 |    340 |   206   (0)|     10 |00:00:00.01 |       8 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| T1    |      1 |    340 |   206   (0)|     10 |00:00:00.01 |       8 |
|*  6 |       INDEX RANGE SCAN DESCENDING     | T1_I1 |      1 |    340 |     4   (0)|     16 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=1)
   2 - filter(ROWNUM .LE. 10)
   6 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE" IS NOT NULL AND "TRANSACTION_DATE">=TO_DATE('
              1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

The optimizer is happy to oblige with the descending range scan – we can see that we’ve visited only 8 buffers, and fetched only 10 rows from the table. The cost, however, hasn’t made any allowance for the limited range scan. Check back to the plan for the simple (non-partitioned) table and you’ll see that the optimizer did allow for the reduced range scan. So the problem here is a costing one – we have to hint the index range scan if we want Oracle limit the work it does.

You might notice, by the way that the number of rowids returned in the index range scan descending operation is 16 rather than 10 – a little variation that didn’t show up when the table wasn’t partitioned. I don’t know why this happened, but when I changed the requirement to 20 rows the range scan returned 31 rowids, when I changed it to 34 rows the range scan returned 46 rows, and a request for 47 rows returned 61 index rowids – you can see the pattern, the number of rowids returned by the index range scan seems to be 1 + 15*N.

Footnote:

If you want to avoid hinting the code (or adding an SQL patch) you need only re-create the index with the transaction_date column declared as descending (“desc”), at which point the optimizer automatically chooses the correct strategy and the run-time engine returns exactly 10 rowids and doesn’t need to do any sorting. But who wants to create a descending index when they don’t really need it !

If you want to reproduce the experiments, here’s the script to create my test data.


rem
rem     Script:         pt_ind_desc_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2018
rem     Purpose:        
rem
rem     Last tested 
rem             18.3.0.0
rem             12.2.0.1
rem             12.1.0.2
rem

create table t1 (
        customer_id,
        transaction_date,
        small_vc,
        padding 
)
partition by hash(customer_id) partitions 4
nologging
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        mod(rownum,128)                         customer_id,
        (trunc(sysdate) - 1e6) + rownum         transaction_date,
        lpad(rownum,10,'0')                     v1,
        lpad('x',100,'x')                       padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
;

create index t1_i1 on t1(customer_id, transaction_date) 
local 
nologging
;

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 1'
        );
end;
/

I’ve run this test on 12.1.0.2, 12.2.0.1, and 18.3.0.0 – the behaviour is the same in all three versions.

Update (1st Feb 2019)

As the client reminded me after reading the post, it’s worth pointing out that for more complex SQL you still have to worry about the errors in the cardinality and cost calculations that could easily push the optimizer into the wrong join order and/or join method – whether you choose to hint the ascending index or create a descending index.  Getting the plan you want for this type of “pagination” query can be a messy process.

Announcement: “Oracle Indexing Internals and Best Practices Seminar” – Berlin 8-9 May 2019 !!

I’m very excited to announce I’ll be running my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar in conjunction with the German Oracle User Group (DOAG) in Berlin, Germany on 8-9 May 2019. The venue will be the DOAG Offices in Berlin (DOAG Office, 6th Floor, Tempelhofer Weg 64, 12347 Berlin). Because of venue […]

Announement: “Oracle Indexing Internals and Best Practices Seminar” – Berlin 8-9 May 2019 !!

I’m very excited to announce I’ll be running my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar in conjunction with the German Oracle User Group (DOAG) in Berlin, Germany on 8-9 May 2019. The venue will be the DOAG Offices in Berlin (DOAG Office, 6th Floor, Tempelhofer Weg 64, 12347 Berlin). Because of venue […]

Where to check Data Guard gap?

At work, we had a discussion with well-known colleagues, Luca Canali and Ludovico Caldara, about where we check that Data Guard recovery works as expected without gap. Several views can be queried, depending on the context. Here are a few comments about them.

v$database

This is my preferred because it relies on the actual state of the database, whatever the recovery process is:

SQL> select scn_to_timestamp(current_scn) 
from v$database;
SCN_TO_TIMESTAMP(CURRENT_SCN)
----------------------------------------------------------
22-JAN-19 03.08.32.000000000 PM

This reads the current System Change number (DICUR_SCN from X$KCCDI) and maps it to a timestamp (using the mapping SMON_SCN_TIME table).

However, relying on a function which is available only when the database is opened, this is easy only with Active Data Guard. When the database is in mount state, you will get ORA-00904: “SCN_TO_TIMESTAMP”: invalid identifier.

Note that I’ve also seen cases where, in case of gap, the SMON_SCN_TIME was not up-to-date and I got ORA-08181: specified number is not a valid system change number. Then this is not for automatic monitoring.

Without Active Data Guard, you need to do the SCN to timestamp conversion on the primary. Or read the SCN from the datafiles, but this is not the latest apply but the lastest checkpointed:

SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
Session altered.
SQL> select max(checkpoint_time) from v$datafile_header;
MAX(CHECKPOINT_TI
-----------------
26-01-19 17:45:04

Reading from V$DATABASE does not rely on Data Guard and then is also available when the MRP is not started and also in Standard Edition non-managed standby.

gv$recovery_progress

This is Luca’s favored one (see is adg.sql script among many other interesting ones in https://github.com/LucaCanali/Oracle_DBA_scripts):

SQL> select inst_id, max(timestamp) 
from gv$recovery_progress group by inst_id;
INST_ID MAX(TIMESTAMP)
---------- --------------------
1 22-JAN-2019 15:08:51

Where does this information come from? If you look at the execution plan you will see that it reads X$KSULOP which is the X$ that is behind V$SESSION_LONGOPS. You can get the same timestamp from it:

SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
Session altered.
SQL> select inst_id,opname,timestamp from gv$session_longops 
where opname='Media Recovery' and target_desc='Last Applied Redo';
INST_ID OPNAME               TIMESTAMP
---------- -------------------- -----------------
1 Media Recovery 26-01-19 18:56:39
1 Media Recovery 26-01-19 19:40:35

As this information comes from what the MRP (Managed Recovery Process) logs, this view is available only when the recovery is running (APPLY-ON).

v$managed_standby;

Talking about what is logged by MRP, Ludo goes to the MRP status in v$managed_standby to see the sequence and block# increase. This is very interesting as we can compare the remaining work to do, from what is received by RFS:

19:59:46 SQL> select inst_id,process,status,client_process,thread#,sequence#,block#,blocks from gv$managed_standby;
I PROCESS   STATUS       CLIENT_P THREAD# SEQUENCE# BLOCK# BLOCKS
- --------- ------------ -------- ------- --------- ------ ------
1 ARCH CLOSING ARCH 1 34 32768 481
1 DGRD ALLOCATED N/A 0 0 0 0
1 DGRD ALLOCATED N/A 0 0 0 0
1 ARCH CLOSING ARCH 1 41 28672 2046
1 ARCH CLOSING ARCH 1 37 30720 1804
1 ARCH CLOSING ARCH 1 40 26624 1608
1 RFS IDLE Archival 1 0 0 0
1 RFS IDLE UNKNOWN 0 0 0 0
1 RFS IDLE LGWR 1 42 121 1
1 RFS IDLE UNKNOWN 0 0 0 0
1 MRP0 APPLYING_LOG N/A 1 42 121 40960
11 rows selected.
19:59:50 SQL> /
I PROCESS   STATUS       CLIENT_P THREAD# SEQUENCE# BLOCK# BLOCKS
- --------- ------------ -------- ------- --------- ------ ------
1 ARCH CLOSING ARCH 1 34 32768 481
1 DGRD ALLOCATED N/A 0 0 0 0
1 DGRD ALLOCATED N/A 0 0 0 0
1 ARCH CLOSING ARCH 1 41 28672 2046
1 ARCH CLOSING ARCH 1 37 30720 1804
1 ARCH CLOSING ARCH 1 40 26624 1608
1 RFS IDLE Archival 1 0 0 0
1 RFS IDLE UNKNOWN 0 0 0 0
1 RFS IDLE LGWR 1 42 124 1
1 RFS IDLE UNKNOWN 0 0 0 0
1 MRP0 APPLYING_LOG N/A 1 42 124 40960
11 rows selected.

v$archived_log

V$ARCHIVED_LOG has an ‘APPLIED’ flag, but it is not really helpful here as it does not consider the real-time apply. In the following screenshot the changes up to 20:35:46 in sequence# 55 have been applied but V$ARCHIVED_LOG shows sequence 52 as not applied:

This view is about archived logs. But before being archived, the redo stream is received to the standby logs.

v$standby_log

With real-time apply (RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE) the redo is applied as soon as it is received in the standby redo logs. The gap should be small and is visible in v$managed_standby (number of blocks between RFS from LGWR and MRP apply). I’ve seen some monitoring queries on V$STANDBY_LOG. The idea is to read the actual state of the transport, in the same idea that when I read V$DATABASE for the actual state of apply, without relying on what is logged by the processes:

SQL> select max(last_time) 
from gv$standby_log;
MAX(LAST_TIME)
--------------------
22-JAN-2019 15:08:55

However, this query reads X$KCCSL which is not very efficient as it reads the standby redo log files. This can be long when they are large and full:

So… be careful with this one.

v$dataguard_stats

The DG Broker ‘show database’ displays the gap information. This comes from V$DATAGUARD_STATS as Data Guard checks the state at regular interval and stores the latest here:

SQL> select name||' '||value ||' '|| unit
||' computed at '||time_computed
from v$dataguard_stats;
NAME||''||VALUE||''||UNIT||'COMPUTEDAT'||TIME_COMPUTED
------------------------------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval computed at 01/27/2019 22:08:33
apply lag +00 00:00:01 day(2) to second(0) interval computed at 01/27/2019 22:08:33
apply finish time +00 00:00:06.493 day(2) to second(3) interval computed at 01/27/2019 22:08:33

You must always check when the value was calculated (TIME_COMPUTED) and may add this to gap to estimate the gap from the current time, as with DGMGRL:

Role:               PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 1 second (computed 1 second ago)
Average Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
CDB2

Oracle numbers in K/M/G/T/P/E

Oracle is very well instrumented, for decades, from a time where measuring the memory in bytes was ok. But today, we spend a lot of time converting bytes in KB, GB, TB to read it easily. I would love to see a Human-Readable format for TO_CHAR, but there’s not. Here is a workaround without having to create a new function.

DBMS_XPLAN does that when displaying execution plans and we can access the functions it uses internally. The metrics can be numbers, and then the Kilo, Mega, Giga applies to powers of 1000. Or they can be a size in bytes, and we prefer the powers of 1024. Or they can be a time in seconds, and then we use a base 60. And then we have 3 sets of functions:

  • FORMAT_SIZE for base 2 numbers where we use powers of 1024 

Oracle Linux 7 and a new YUM configuration since January 2019

For quite some time I used to have a configuration file /etc/yum.repos.d/public-yum-ol7.repo managing my package sources in lab VMs. Today I noticed that this configuration file is deprecated, and has been replaced by a new model. This is explained in the Oracle Linux 7 administrator guide and a few other sources I totally missed. I thought I’d show you the full story in this post before I go and change all my Ansible scripts :)

State of play before

To repeat the scenario I just went through, I created a new machine, server3, based on the stock Oracle Linux 7.6 image. After its initial boot I log in to the console to perform a yum upgrade.

This is of course only acceptable in a lab environment, anything deployed in an enterprise environment would use a local, change-controlled mirror based on Spacewalk or comparable software.

Prior to starting the actual upgrade, let’s have a look at how things were before the new model was rolled out. There is only a single repository configuration file present after the initial reboot and without any calls to yum whatsoever:

[ansible@server3 ~]$ ls -l /etc/yum.repos.d/
total 16
-rw-r--r--. 1 root root 13093 Nov 2 07:02 public-yum-ol7.repo
[ansible@server3 ~]$

Upgrading

Next I run the upgrade command, I have removed a lot of output for clarity

[ansible@server3 ~]$ sudo yum upgrade
Loaded plugins: ulninfo
ol7_UEKR5 | 1.2 kB 00:00
ol7_latest | 1.4 kB 00:00
(1/5): ol7_UEKR5/x86_64/updateinfo | 27 kB 00:00
(2/5): ol7_UEKR5/x86_64/primary | 2.4 MB 00:00
(3/5): ol7_latest/x86_64/group | 659 kB 00:00
(4/5): ol7_latest/x86_64/updateinfo | 767 kB 00:00
(5/5): ol7_latest/x86_64/primary | 11 MB 00:00
ol7_UEKR5 120/120
ol7_latest 11799/11799
Resolving Dependencies
--> Running transaction check
---> Package NetworkManager.x86_64 1:1.12.0-6.el7 will be updated
---> Package NetworkManager.x86_64 1:1.12.0-8.el7_6 will be an update

[ ... more output ... ]

Updating : 1:grub2-pc-2.02-0.76.0.3.el7.x86_64 15/85
Updating : cronie-1.4.11-20.el7_6.x86_64 16/85
Updating : cronie-anacron-1.4.11-20.el7_6.x86_64 17/85
Installing : python-chardet-2.2.1-1.el7_1.noarch 18/85
Installing : python-kitchen-1.1.1-5.el7.noarch 19/85
Installing : yum-utils-1.1.31-50.0.1.el7.noarch 20/85

IMPORTANT: A legacy Oracle Linux yum server repo file was found.
Oracle Linux yum server repository configurations have changed which
means public-yum-ol7.repo will no longer be updated. New repository
configuration files have been installed but are disabled. To complete
the transition, run this script as the root user:

/usr/bin/ol_yum_configure.sh

See https://yum.oracle.com/faq.html for more information.

Installing : oraclelinux-release-el7-1.0-3.el7.x86_64 21/85
Updating : rhn-client-tools-2.0.2-24.0.5.el7.x86_64 22/85
Updating : ipset-libs-6.38-3.el7_6.x86_64 23/85
Updating : selinux-policy-3.13.1-229.0.1.el7_6.6.noarch 24/85

[ ... more output ...]

The message that “A legacy Oracle Linux yum server repo file was found” started this blog post. So what is there to be done? The upgrade created a few more files in /etc/yum.repos.d/:

[ansible@server3 ~]$ ls -l /etc/yum.repos.d/
total 28
-rw-r--r--. 1 root root 3354 Jan 22 16:14 oracle-linux-ol7.repo.disabled
-rw-r--r--. 1 root root 13093 Nov 2 07:02 public-yum-ol7.repo
-rw-r--r--. 1 root root 2116 Jan 22 16:14 uek-ol7.repo.disabled
-rw-r--r--. 1 root root 226 Jan 15 21:31 virt-ol7.repo.disabled
[ansible@server3 ~]$

The files ending in *disabled are not considered eligible during any execution of “yum”.

Transitioning to the new model

According to yum.oracle.com/getting-started.html the following steps are only needed if a) you have public-yum-ol7.repo in use and the new configuration files – provided by oraclelinux-release-el7 in my case – are present as well. This applies to this VM, so I decided to go ahead and call the script /usr/bin/ol_yum_configure.sh to see what happens.

[root@server3 ~]# /usr/bin/ol_yum_configure.sh
Repository ol7_UEKR5 Fine
Repository ol7_latest Fine
[root@server3 ~]# ls -l /etc/yum.repos.d/
total 28
-rw-r--r--. 1 root root 3354 Jan 22 16:14 oracle-linux-ol7.repo
-rw-r--r--. 1 root root 13093 Nov 2 07:02 public-yum-ol7.repo.sav
-rw-r--r--. 1 root root 2116 Jan 22 16:14 uek-ol7.repo
-rw-r--r--. 1 root root 226 Jan 15 21:31 virt-ol7.repo

It appears as if ol_yum_configure.sh switched the all-in-one configuration for the new, modular one.

Implications

In other posts of mine I described how I’m upgrading public-yum-ol7.repo from Oracle’s yum server in my lab (and only in the lab!). Based on the monolithic file I can call yum-config-manager to enable and disable any repository I need. With the new modular approach I might have to rethink this approach. The Administration Guide I referred to earlier has more details about the details of the change.

Building your own local Oracle Linux 7 Vagrant base box

I have been talking about Vagrant for a long time and use it extensively on my Ubuntu-powered laptop. I am using Oracle Linux 7.6 for most of my lab builds, and I like to have specific tools such as collectl, perf, and many others available when the VM boots. I als like to stay in control of things, especially when it comes to downloading otherwise unknown things from the Internet I decided to learn how to create a Vagrant box myself.

Using Vagrant with my custom images, all I need to do is run a single command and it will spin up a clean VM using the VirtualBox provider with the exact software configuration I want. I can also supply so-called provisioners to further configure my environment. I found this particularly useful when writing and testing Ansible scripts. Sometimes I just wanted to go back to my starting point but that can be tricky at times: imagine you just partitioned your block devices for use with the database and discovered you wanted to change the flow. Getting back to unpartitioned, unformatted block devices is possible, but I don’t think it’s terribly elegant. Plus I have to manually do it, and I prefer the Ansible approach.

Building a base box

The Vagrant documentation is pretty good, so this is mostly pulling together information from 2 sources: The starting point I used was Creating a Base Box with specifics for the VirtualBox driver I’m using. I don’t claim I’m an expert in this field.

Running Vagrant VMs can be inherently insecure as you will see in a bit. It’s fine for me because I’m creating/trashing short-lived VMs on a regular basis and all I do is play around with them whilst they remain perfectly isolated from the rest of the world. If you are ok with this limitation feel free to read on, otherwise please refrain from following the steps in this blog post.

The overall process isn’t too hard to follow:

  • Create your gold image
    • Install the Operating System in VirtualBox
    • Install/upgrade any software you want to have available
    • Configure the system for Vagrant specifics
  • Create a base box off your gold image
  • Add the box to your environment
  • Start the VM and enjoy

Creating the VM and installing the Operating System

The first step obviously is to create the VM and install the operating system. For quite some time now I’m creating a VM with sufficient RAM and a couple of block devices: the first one is used as the root volume group, the second block device will be used for Oracle. Plenty of articles have been written about installing Oracle Linux on VirtualBox, I won’t write the 42nd variation here ;)

There are only a few things to pay attention to. These can all be found in the documentation I referenced earlier. First of all, please ensure that your network adaptor uses NAT. You can use port forwarding to access a NAT device in VirtualBox (configured later). The documentation furthermore recommends removing any necessary components such as USB and audio from the VM. I have used a strong password for “root” as I have no intention at all of sharing my VM. Apply security hardening at this stage.

A common error is not to enable the network device to start up automatically when the system boots. Vagrant uses port-forwarding to the NAT device and SSH keys to authenticate, there doesn’t appear to be a mechanism circumventing the network stack. With the network interface down it’s quite hard to connect via SSH.

Install/upgrade software

Once the operating system is installed and the VM rebooted, it’s time to configure it for your needs. I usually end up completing the pre-requisites for an Oracle database installation. This, too, has been covered so many times that I don’t feel like adding value by telling you how to complete the steps.

Configure the system for Vagrant

At this stage your VM should be properly configured for whichever purpose you have in mind. All that remains now is the addition of the specific configuration for Vagrant. There are a few steps to this, all to be completed on the guest.

Install VirtualBox Guest Additions

Vagrant offers the option of mounting a file system from your host on the guest VM. I very much like this feature, which is enabled by default. Please refer to the Vagrant documentation for security implications of sharing file systems between guest and host.

As with every VirtualBox VM, shared folders won’t work without installing the guest additions though so that’s what I do next. This is pretty straight forward and for Oracle Linux 7 generally speaking requires tar, bzip2, gcc and kernel-uek-devel matching your current kernel-uek. If you just completed a “yum upgrade” and your kernel was upgraded you need to reboot first. After VBoxLinuxAdditions.run has completed successfully (I am using VirtualBox 5.2.x) it’s time to move on to the next step.

Add a Vagrant user

Vagrant expects a user named vagrant to be present on the VM. It uses SSH-keys when connecting to the VM. The documentation mentions a so-called insecure key-pair I decided not to use. Instead, I created my own key pair for use with the machine and added it to ~/.ssh/authorized_keys in the vagrant user’s home directory. It is a new keypair I created on the host specifically for use with Vagrant. If you are on MacOS or Linux it’s convenient to add it to the SSH agent (ssh-add …). There are similar tools for Windows users.

Creating the user is easy and should be completed now unless you already created the user during the initial installation:

# useradd -c 'vagrant user' -m -s $(which bash) vagrant 

The user should have passwordless sudo enabled as well as per the documentation. It is also recommended by the Vagrant documentation to assign a weak password to the vagrant account, which I didn’t. I never ran the passwd command to set a password for the vagrant user and so far seem to be doing ok.

Create a base box

This concludes the preparations on the VM side. Next up you need to create the base box, which you can then refer to in your own Vagrantfile. The command to do so is just one line. Be careful though: it will create a compressed file named package.box in your current working directory. This file can be rather large, so make sure you have enough space to store it.

$ vagrant package --base 

Depending on how powerful your laptop is this can take a little while.

Add the box to your environment

The previous command will complete eventually. This is the moment where you add the box to Vagrant’s local inventory as shown here:

$ vagrant box add --name blogpost /home/martin/package.box 

This command shouldn’t take too long to complete. If you see a line “box: successfully added box ‘blogpost’ (v0) for ‘virtualbox’ you are good. You can assign any name to the box you add, it will alter on show up under that designation when you run “vagrant box list”

Start the VM and enjoy

The remaining tasks are identical to using Vagrant boxes off their repository. Start off by vagrant init and make all the changes you normally do to the Vagrantfile. As I’m using my own SSH key I have to make sure that I’m telling Vagrant where to find it using a configuration option:

config.ssh.private_key_path = "/path/to/ssh/keyfile" 

Once you start the VM using “vagrant up” you are good to go!

Patch conflicts

My last post was about patching my home databases from 18.3 to 18.5 on Windows, and how I encountered a patch conflict when I tried to patch the JVM. I thought I’d give a little bit of info for anyone who runs into patch conflicts from time to time. It can be stressful especially if unforeseen, or you are in the middle of limited time outage window etc.

So before you jump into applying a patch, a nice little tool you might like to explore is the patch conflict checker on My Oracle Support. You can get it via:

https://support.oracle.com/epmos/faces/PatchConflictCheck

It is straightforward to use, you simply fill in the platform and your current patch inventory details, and then list out the patches you intend to apply.

image

 

One caveat – the online tool does not work with Windows environments Sad smile but you can get around that by downloading the patches you intend to apply to your local machine. Then you can use opatch itself to perform that check:


opatch prereq CheckConflictAmongPatchesWithDetail -phBaseDir path

where “path” is where you have unzipped the patch to.

Like all patching activities, the more preparation work you can do before actually commencing the work, the more likely your chances of not encountering a sticky moment during the work.