Oracle Scratchpad

July 22, 2021

SQL Macro

Filed under: 19c,Oracle — Jonathan Lewis @ 10:18 am BST Jul 22,2021

A question came up recently on the Oracle Developer forum that tempted me into writing a short note about SQL Macro functions – a feature that was touted for 20c but which has been back-ported [ed: in part, just the “table macro” feature – see comment 1] to the more recent releases of 19c. Specifically I set up this demo using 19.11.0.0.

The OP supplied a script to prepare some data. I’ll postpone that to the end of this note and start with variations of the query that could be used against that data set. I’ll be looking at the original query, a variant of the query that uses a pipelined function, then a variant that uses an SQL Macro function.

The requirement starts with a query to turn a pair of dates into a date range – which can be done in many ways but the OP had used a recursive “with subquery” (CTE/common table expression).

with calendar ( start_date, end_date ) as (
        select date '2021-07-01', date '2021-07-30' from dual
        union all
        select start_date + 1, end_date
        from   calendar
        where  start_date + 1 <= end_date
)
select start_date as day
from   calendar
;

Getting on to the full requirement we can use this subquery as if it were a table (or inline view) and join it to any other tables where we want data from a date range, for example:

select
        e.employee_id, c.day
from
        employees e
inner join
        (
                with calendar ( start_date, end_date ) as (
                        select date '2021-07-01', date '2021-07-30' from dual
                        union all
                        select start_date + 1, end_date
                        from   calendar
                        where  start_date + 1 <= end_date
                )
                select start_date as day
                from   calendar
        ) c
partition by
        (e.employee_id)
on      (substr(e.work_days, trunc(c.day) - trunc(c.day, 'IW') + 1, 1) = 'Y')
where
        not exists (
                select  1
                from    holidays h
                where   c.day = h.holiday_date
        )
and     not exists(
                select  1
                from    timeoff t
                where   e.employee_id = t.employee_id
                and     t.timeoff_date = c.day
        )
order by
        e.employee_id,
        c.day

If we want a report for a different month we just have to supply a different pair of dates, and we can probably work out a way of making it easy for the end-users to supply those dates as parameters to a report.

The pipelined function

However, we may want to use the same little “recursive CTE” (or similar) pattern in many different reports, and ad hoc queries that users might want to write for themselves. To avoid wasting time on logic, or basic typing errors, is it possible to hide some of the complexity of the subquery structure. The answer is yes, and for a long time we could have used a “pipelined function” to do this – though we have to create a simple object type and an object table type to do so. For example:

create or replace type obj_date is object (day date);
/

create or replace type nt_date is table of obj_date;
/

create or replace function generate_dates_pipelined(
        p_from  in date,
        p_to    in date
)
return nt_date 
pipelined
is
begin
        for c1 in (
                with calendar (start_date, end_date ) as (
                        select trunc(p_from), trunc(p_to) from dual
                        union all
                        select start_date + 1, end_date
                        from   calendar
                        where  start_date + 1 <= end_date
                )
                select start_date as day
                from   calendar
        ) loop
                pipe row (obj_date(c1.day));
        end loop;

        return;

end generate_dates_pipelined;
/

I’ve started by creating an object type with a single attribute called day of type date, and an object table type of that object type. This means I can use the object type and the object table type to pass data between SQL and PL/SQL. Then I’ve created a pl/sql function that returns the object table type, but in a pipelined fashion using the pipe row() mechanism to supply the data one object at a time.

In my final SQL I can now use the table() operator to cast the result of the function call from an object table to a relational table, implicitly mapping the object attributes to their basic Oracle data types.

select
        e.employee_id, c.day
from
        employees e
inner join
        table(generate_dates_pipelined(date '2021-07-01', date '2021-07-30')) c
partition by
        (e.employee_id)
on      (substr(e.work_days, trunc(c.day) - trunc(c.day, 'IW') + 1, 1) = 'Y')
where
        not exists (
                select  1
                from    holidays h
                where   c.day = h.holiday_date
        )
and     not exists(
                select  1
                from    timeoff t
                where   e.employee_id = t.employee_id
                and     t.timeoff_date = c.day
        )
order by
        e.employee_id,
        c.day
;

I’ve replaced the 9 lines of the inline “with subquery” by a single line call:

        table(generate_dates_pipelined(date '2021-07-01', date '2021-07-30')) c

In fact the table() operator hasn’t been needed since some time in the 12c timeline, but it might be useful as a little reminder of what’s going on behind the scenes. It’s also a reminder that the data really will behave as if it’s coming from a relational table rather then a pl/sql loop.

Although this pipelined function approach can be very effective another member of the forum pointed out that behind the scenes it is based on a pl/sql loop walking through a cursor which, in this example, was doing row by row processing (though it could be changed to bulk collect with a limit to improve performance a little). We might want to look at options for doing things differently.

The SQL Macro function

In many programming languages a “macro” is a symbol that is used as a short-hand for a longer piece of code. Even in environments like your favourite shell environment you can usually set up shorthand for longer texts that you use frequently, for example:

alias otr="cd /u01/app/oracle/diag/rdbms/or19/or19/trace"

The Oracle equivalent is a PL/SQL function (declared as a “SQL_Macro” function) that you include in your SQL statement, and at run-time Oracle will execute the function and use the text it returns to modify your statement [ed: see comment 1: this expansion is done at hard parse time, not at run-time] . Here’s the macro strategy applied to the date range generation:

create or replace function generate_dates_macro(
        p_from  in date,
        p_to    in date
)
return varchar2
sql_macro
is
        v_sql varchar2(4000) := q'{
                with calendar (start_date, end_date ) as (
                        select
                                to_date('xxxx-xx-xx','yyyy-mm-dd'),
                                to_date('yyyy-yy-yy','yyyy-mm-dd')
                        from    dual
                        union all
                        select start_date + 1, end_date
                        from   calendar
                        where  start_date + 1 <= end_date
                )
                select start_date as day
                from   calendar
                }'
        ;

begin
        v_sql := replace(v_sql,'xxxx-xx-xx',to_char(p_from,'yyyy-mm-dd'));
        v_sql := replace(v_sql,'yyyy-yy-yy',to_char(p_to  ,'yyyy-mm-dd'));

--      dbms_output.put_line(v_sql);
        return v_sql;

end generate_dates_macro;
/

I’ve created a function, flagged as a sql_macro, that returns a varchar2. It has two input parameters which are declared as dates. The initial value of the variable v_sql looks very similar to the CTE I used in the original query except the two “dates” it uses are “xxxx-xx-xx” and “yyyy-yy-yy”, but in the body of the function I’ve replaced those with the text forms of the two incoming date parameters. There’s a call to dbms_output.put_line() that I’ve commented out that will show you that the final text returned by the function is:

                with calendar (start_date, end_date ) as (
                        select
                                to_date('2021-07-01','yyyy-mm-dd'),
                                to_date('2021-07-30','yyyy-mm-dd')
                        from    dual
                        union all
                        select start_date + 1, end_date
                        from   calendar

                 where  start_date + 1 <= end_date
                )
                select start_date as day
                from   calendar

So now we can rewrite the original statement as follows (with just a minor change from the pipelined version):

select
        e.employee_id, c.day
from
        employees e
inner join
        generate_dates_macro(date '2021-07-01', date '2021-07-30') c
partition by
        (e.employee_id)
on      (substr(e.work_days, trunc(c.day) - trunc(c.day, 'IW') + 1, 1) = 'Y')
where
        not exists (
                select  1
                from    holidays h
                where   c.day = h.holiday_date
        )
and     not exists(
                select  1
                from    timeoff t
                where   e.employee_id = t.employee_id
                and     t.timeoff_date = c.day
        )
order by
        e.employee_id,
        c.day
;

When we first call this statement (i.e. assuming a suitable child cursor does not yet exist) Oracle evaluates the function, slots the generated text in place, then optimises and executes the resulting text instead. Interestingly the text reported by a call to dbms_xplan.display_cursor() shows the original text even though the plan clearly includes references to the table(s) in the SQL macro – a search of the library cache also shows the original text, but reveals an anonymous pl/sql block calling the SQL Macro function (in a style reminiscent of the way that row-level security (RLS, FGAC, VPD) calls a security predicate function) that is invisibly folded into a query.

declare
begin 
        :macro_ text := "GENERATE_DATES_MACRO"(
                TO_DATE(' 2021-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'),
                TO_DATE(' 2021-07-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
        );
end;

Here’s the execution plan for the query using the SQL Macro:

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |            |      1 |        |    41 (100)|     83 |00:00:00.01 |     130 |       |       |          |
|   1 |  SORT ORDER BY                                |            |      1 |      1 |    41   (5)|     83 |00:00:00.01 |     130 |  9216 |  9216 | 8192  (0)|
|*  2 |   FILTER                                      |            |      1 |        |            |     83 |00:00:00.01 |     130 |       |       |          |
|*  3 |    HASH JOIN ANTI                             |            |      1 |      1 |    39   (3)|     84 |00:00:00.01 |      46 |  1744K|  1744K| 1542K (0)|
|   4 |     NESTED LOOPS                              |            |      1 |      1 |    21   (0)|     88 |00:00:00.01 |      23 |       |       |          |
|   5 |      TABLE ACCESS FULL                        | EMPLOYEES  |      1 |      1 |    17   (0)|      4 |00:00:00.01 |      23 |       |       |          |
|*  6 |      VIEW                                     |            |      4 |      1 |     4   (0)|     88 |00:00:00.01 |       0 |       |       |          |
|   7 |       UNION ALL (RECURSIVE WITH) BREADTH FIRST|            |      4 |        |            |    120 |00:00:00.01 |       0 |  2048 |  2048 | 2048  (0)|
|   8 |        FAST DUAL                              |            |      4 |      1 |     2   (0)|      4 |00:00:00.01 |       0 |       |       |          |
|   9 |        RECURSIVE WITH PUMP                    |            |    120 |        |            |    116 |00:00:00.01 |       0 |       |       |          |
|  10 |     TABLE ACCESS FULL                         | HOLIDAYS   |      1 |      2 |    17   (0)|      1 |00:00:00.01 |      23 |       |       |          |
|* 11 |    INDEX UNIQUE SCAN                          | TIMEOFF_PK |     84 |      1 |     1   (0)|      1 |00:00:00.01 |      84 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter( IS NULL)
   3 - access("START_DATE"="H"."HOLIDAY_DATE")
   6 - filter(SUBSTR("E"."WORK_DAYS",TRUNC(INTERNAL_FUNCTION("START_DATE"))-TRUNC(INTERNAL_FUNCTION("START_DATE"),'fmiw')+1,1)='Y')
  11 - access("T"."EMPLOYEE_ID"=:B1 AND "T"."TIMEOFF_DATE"=:B2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

As you can see, even though the query as written didn’t include the recursive CTE, the recursive query against DUAL appears in the plan. In fact the plan is exactly the same as the plan for the original query with the embedded CTE, though there is one interesting little difference – the generated query block names differ between plans.

Pros and Cons

Given that this is a lightweight example of a simple use of the SQL macro there’s not really a lot that can be said when comparing pipelined functions with macro functions. Both hide complexity and give you the opportunity to optimise an awkward piece of the code that might be (in effect) a common sub-routine.

The pipelined function does have to deal with the PL/SQL to SQL interchange – but that’s not a significant feature in this example. The main benefits, perhaps, of the macro are that the plan shows you the table(s) that would be hidden by the pipelined function, and may allow the optimizer to get better estimates of data sizes because it will be examining real tables with real statistics rather than taking a guess at a “pickler fetch” from a collection with a block box function.

Update (pre-publication)

There is some pleasure to be had by making mistakes in public, because that’s when you can learn something new. In my example to the OP on the Developer forum I used a much messier piece of code to embed the date values into the macro string, with lots of doubled and trebled quotes, to_char() functions, and concatenation all over the place.

Alex Nuijten replied to my suggestion pointing out that this degree of complexity was not necessary, and you could reference the functions parameters to construct the string. The only problem with that was that it hadn’t worked when I had tried it. Alex’s comment, however, also mentioned the problem and supplied the explanation: Bug 32212976: USING SCALAR ARGUMENTS IN WITH CLAUSE IN SQL TABLE MACRO RAISES ORA-06553 PLS-306 ). This was exactly the problem that I had been getting (the error message was – wrong number or types of arguments in call to ‘GENERATE_DATES_MACRO’ and I hadn’t thought about searching for known bugs or patches, I just hacked my way around the problem.

Here’s an alternative macro function supplied by Alex (edited slightly to be consistent with the function and column names in my example):

create or replace function generate_dates_macro(
    p_from in date,
    p_to  in date
)
return varchar2
sql_macro
is
    v_sql varchar2(4000);
begin
  v_sql := 'select trunc (generate_dates_macro.p_from) - 1 + level as day
       from dual
       connect by level <= (generate_dates_macro.p_to - generate_dates_macro.p_from) + 1';

--  dbms_output.put_line(v_sql);
    return v_sql;

end generate_dates_macro;
/

Test Code

If you want to experiment further, here’s the code to create the tables used in this demo:

rem
rem     Script:         19c_macro_2.sql
rem     Author:         Jonathan Lewis / "BeefStu"
rem     Dated:          July 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem
rem     Notes:
rem     A Macro solution to a problem that might
rem     otherwise be solved with a pipelined function
rem


drop table holidays;
drop table employees;
drop table timeoff;
drop table  emp_attendance;    
drop table absences;

drop function generate_dates_pipelined;
drop type nt_date;
drop type obj_date;

drop function generate_dates_macro;

-- @@setup

create table holidays(
        holiday_date    date,
        holiday_name    varchar2(20)
)
;

insert into holidays (holiday_date, holiday_name)
values ( to_date('2021/07/21 00:00:00', 'yyyy/mm/dd hh24:mi:ss'), 'July 21 2021') ;

create table employees(
        employee_id     number(6), 
        first_name      varchar2(20),
        last_name       varchar2(20),
        card_num        varchar2(10),
        work_days       varchar2(7)
)
;

alter table employees
        add constraint employees_pk primary key (employee_id)
;

insert into employees(employee_id, first_name, last_name, card_num, work_days)
with names as ( 
select 1, 'Jane', 'Doe', 'f123456', 'NYYYYYN' from dual 
union all 
select 2, 'Madison', 'Smith', 'r33432','NYYYYYN' from dual 
union all 
select 3, 'Justin', 'Case', 'c765341','NYYYYYN' from dual 
union all 
select 4, 'Mike', 'Jones', 'd564311','NYYYYYN' from dual 
) 
select * from names
;

create table timeoff(
        seq_num         integer generated by default as identity (start with 1) not null,
        employee_id     number(6),
        timeoff_date    date,
        timeoff_type    varchar2(1),
        constraint timeoff_chk check (timeoff_date = trunc(timeoff_date, 'dd')),
        constraint timeoff_pk primary key (employee_id, timeoff_date)
)
;

insert into timeoff (employee_id,timeoff_date,timeoff_type) 
with dts as ( 
select 1, to_date('20210726 00:00:00','yyyymmdd hh24:mi:ss'),'V'    from dual union all 
select 2, to_date('20210726 00:00:00','yyyymmdd hh24:mi:ss'),'V'    from dual union all 
select 2, to_date('20210727 00:00:00','yyyymmdd hh24:mi:ss'),'V'    from dual  
) 
select * from dts
;

create table  emp_attendance(    
        seq_num         integer  generated by default as identity (start with 1) not null,
        employee_id     number(6),
        start_date      date,
        end_date        date,
        week_number     number(2),
        create_date     date default sysdate
)
;

create table absences(
        seq_num         integer  generated by default as identity (start with 1) not null,
        employee_id     number(6),
        absent_date     date,
        constraint absence_chk check (absent_date=trunc(absent_date, 'dd')),
        constraint absence_pk primary key (employee_id, absent_date)
)
;

insert into emp_attendance (employee_id, start_date,end_date,week_number)
with dts as ( 
select 1, to_date('20210728 13:10:00','yyyymmdd hh24:mi:ss'), to_date('20210728 23:15:00','yyyymmdd hh24:mi:ss'), 30  from dual 
union all 
select 2, to_date('20210728 12:10:10','yyyymmdd hh24:mi:ss'), to_date('20210728 20:15:01','yyyymmdd hh24:mi:ss'), 30  from dual
)
select * from dts
;


July 20, 2021

Hex tip

Filed under: 19c,Bugs,Oracle,Troubleshooting — Jonathan Lewis @ 5:40 pm BST Jul 20,2021

A surprising amount of the work I do (or used to do) revolves around numbers; and once I’m outside the realm of the optimizer (i.e. getting away from simple arithmetic), one of the bits of playing with numbers that I do most often is conversion – usually decimal to hexadecimal, sometimes decimal to binary.

Here’s an example of how this helped me debug an Oracle error a few days ago. We start with someone trying to purge data from aud$ using the official dbms_audit_mgmt package, first setting the package’s db_delete_batch_size parameter to the value 100,000 then calling dbms_audit_mgmt.clean_audit_trail.

In theory this should have deleted (up to) 100,000 rows from aud$ starting from the oldest data. In practice it tried to delete far more rows, generating vast amounts of undo and redo, and locking up resources in the undo tablespace for ages. The SQL statement doing all the work looked like the following (after a little cosmetic work):

DELETE FROM SYS.AUD$ 
WHERE  DBID = 382813123 
AND    NTIMESTAMP# < to_timestamp('2020-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')
AND    ROWNUM <= 140724603553440

That’s a rather large number in the rownum predicate, much larger than the expected 100,000. Whenever I am puzzled by very large numbers in places I’m not expecting to see them one of the first things I do to poke it around is to convert it to hexadecimal. (Although it seems a fairly random thing to do it doesn’t take very long and it produces an interesting result fairly frequently.)

140724603553440 (dec) = 0x7FFD000186A0

You may not think that the resulting hex number is very interesting – but there’s a string of zeros in the middle that is asking for a little extra poking. So let’s convert the last 8 digit (starting with those 3 zeros) back to decimal.

0x000186A0 = 100,000 (dec)

There’s an interesting coincidence – we’ve got back to the 100,000 that the OP had set as the db_delete_batch_size. Is this really a coincidence or does it tell us something about a bug? That’s easy enough to test, just try setting a couple of different values for the parameter and see if this affects the rownum predicate in a consistent fashion. Here are the results from two more test values:

1,000,000 ==> 140733194388032 (dec) = 0x7FFF000F4240 .... 0x000F4240 = 1,000,000 (dec)
   50,000 ==> 140728898470736 (dee) = 0x7FFE0000C350 .... 0x0000C350 =    50,000 (dec)

The top 4 digits (2 bytes) have changed, but the bottom 8 digits (4 bytes) do seem to hold the db_delete_batch_size requested. At this point I felt that we were probably seeing some sort of pointer error in a C library routine. If you examine the file $ORACLE_HOME/rdbms/admin/prvtamgt.plb) you’ll find that one of the few readable lines says:

CREATE OR REPLACE LIBRARY audsys.dbms_audit_mgmt_lib wrapped

My guess was that there were probably a couple of external C routines involved, with PL/SQL wrappers in the public package; and that there was a mismatch between the declarations in C and the declarations in the PL/SQL.

It turns out that I wasn’t quite right, but I was in the right olympic stadium. This is now (unpublished) bug 33136016, and if you’ve been seeing unexpected work patterns when purging the audit trail after upgrading to 19c or later then there may be a patch for you in the not too distant future.

July 11, 2021

Quiz Night

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 11:41 pm BST Jul 11,2021

How do you explain the apparent inconsistency between the two outputs from this tiny fragment of an SQL*plus script (last tested 19.11.0.0):

describe t1
create table t2 as select * from t1;
describe t2

The results of the two describe commands are as follows (cut-n-paste, with no editing, including the feedback from the CTAS):

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                            NOT NULL NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(10)
 PADDING                                VARCHAR2(100)


Table created.

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                                     NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(10)
 PADDING                                VARCHAR2(100)

Answer, and comments on why it’s worth knowing, some time tomorrow (Monday)

Update (Monday morning)

.Well that didn’t take very long at all (31 minutes according to the timestamps). The effect is due to Oracle’s implementation of declared primary key constraints. A primary key means “unique and not null”, and when you add it Oracle flags the column as NOT NULL in the data dictionary (col$.null$ > 1) but doesn’t create a “not null” check constraint.

When you drop the primary key constraint the implicit “not null” is dropped; when you copy the table with a CTAS the implicit “not null” is not copied.

Sample code:

rem
rem     Script:         pk_overhead_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem             11.2.0.4
rem

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4    -- > comment to avoid WordPress format issue
)
select
        rownum                          n1,
        rownum                          n2,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1
;

alter table t1 add constraint t1_pk primary key(n1);
alter table t1 modify n2 not null;

set echo on

describe t1

create table t2 as select * from t1;
describe t2

alter table t1 drop primary key;
describe t1

set echo off

And the output, cut-n-paste:

SQL> describe t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                            NOT NULL NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

SQL> 
SQL> create table t2 as select * from t1;

Table created.

SQL> describe t2
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                                     NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

SQL> 
SQL> alter table t1 drop primary key;

Table altered.

SQL> describe t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                                     NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

SQL> 
SQL> set echo off

Why does it matter?

The reason why I wrote and posted this note is because of a recent request for comments on the Oracle-l mailing list about deleting half the data from a 222GB table with 900M rows. Since this is running on 11.2.0.4 none of the really nice options that appeared in 12.2 is available (and even then there might be time, space, or performance costs that would make the newer options unnacceptable).

One of the suggestions (probably the best option in the circumstances) was to use CTAS to create a partitioned table with one partition, selecting only the data that was needed from the original table, add the primary key and any indexes needed, then exchange the original table with the one partition.

The beneftis of this approach are:

  • no massively expensive delete
  • minimal undo and redo on the CTAS
  • option for maximum parallel processing on the CTAS and any index creation
  • no worries about making mistakes with access privileges on the table (thanks to the exchange “trick”)

And this is where the blog note comes in. The OP started asking questions about the cost of creating primary key. When you’ve got a table with 450M rows (half the original) of 111GB (half the original) you probably care about Oracle doing a full tablescan to add a NOT NULL constraint as step 1 of adding the primary key constraint. So you need to know how the primary key was created on the original table – was the column explicitly declared NOT NULL, or did its NOT NULL appear as a side effect of adding its primary key.

To be on the safe size, of course, you can write the CTAS to list the columns and explicitly include a NOT NULL on the primary key columns. Here’s one last fragment of my demo script:

create table t3(n1 not null, n2, v1, padding) 
as 
select * from t1
;

describe t3

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 N1                            NOT NULL NUMBER
 N2                            NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 PADDING                                VARCHAR2(100)

There are several other details that would need to be addressed (and tested) before applying this mechanism e.g (but probably incomplete):

  • any indexes you create on the partitioned table have to be local, and if that’s not possible you have to create them on the standalone table after the exchange;
  • since it’s 11.2.0.4 there may be problems if the original table has had columns marked as unused but not dropped (and you don’t want to drop a couple of columns from a 900M row table), of if there were virtual columns, or function-based indexes with their hidden columns – you might need to create the partitioned table empty using the exact steps that got the original table to its current state otherwise you could run into the error “ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION”
  • At this type of scale you probably want to be certain that all the operations take place at the maximum degree of parallelism that the machine (or your ration of the machine) can handle.
  • Other … give me a little time and I’ll probably think of something.
  • And here’s another – which appeared at comment 3 while I was typing in the update: CTAS loses any default declarations you’ve made for columns.

ORA-14097: Oracle addressed the problem of getting the right table definition in 12c with the “create table for exchange” option – but we’re taking advantage of an exchange “the wrong way round” so that wouldn’t help in this particular case.

Footnote

There are a couple of other posts on my blog that might also be relevant to the OP, one is about the effect of dropping a primary key constraint the other about the cost of adding a primary key to a table.

July 9, 2021

19c tweak 2

Filed under: CBO,Oracle,Performance — Jonathan Lewis @ 4:40 pm BST Jul 9,2021

Trying to find out why a plan had changed in the upgrade from 11g to 19c I came across this cunning little tweak that must have appeared in the 19c timeline. I’ll start with a simple query, then the execution plans (autotrace traceonly) from 19.11.0.0 – first with the parameter optimizer_features_enable set to 18.1.0, then with the it set to 19.1.0. The table t1 is a copy of the first 10,000 rows of view all_objects:

SQL> alter session set optimizer_features_enable = '18.1.0';
SQL> select count(data_object_id) from t1 where f1(object_id) = 'Y';

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |    38  (37)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |   700 |    38  (37)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("F1"("OBJECT_ID")='Y')



SQL> alter session set optimizer_features_enable = '19.1.0';
SQL> select count(data_object_id) from t1 where f1(object_id) = 'Y';

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |    26   (8)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     5 |    35 |    26   (8)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DATA_OBJECT_ID" IS NOT NULL AND "F1"("OBJECT_ID")='Y')

Optimising with the optimizer features set back to 18.1 the cardinality estimate is 100 (that’s 1% of the rows in the table, the standard guess for “function() = constant”) with a cost of 38, or which 37% is CPU cost.

Running with the optimizer features of 19c enabled the cardinality estimate drops to 5 and the cost drops to 26 with CPU making up 5% of the cost. Where does the difference come from?

As ever you have to look at the Predicate Information. Running as 18c Oracle has decided to call my function for every row in the table; running as 19c Oracle has decided that since I’m counting non-null entries of column data_object_id it need only call the function when data_object_id is not null, so it’s introduced an extra predicate to make that happen, and that extra predicate has reduced the cardinality and cost estimates. (In my sample data set there are 9,456 nulls and 544 distinct values for data_object_id – so the difference in workload is significant. And 1% of 544 is 5, which explains the cardinality estimate.)

This looks like fix control 24761824 “add is not null for high null column in set function” introduced in 19.1.0. The description suggests that the feature will only be used in cases where the column is “often” null, but we have no clue, yet, about what “often” means. [Update 12th July: thanks to comment #1 below from Andi Schloegl we now have a pretty good idea that the break point is at 5%.]

This means that there may be cases where an execution plan changes on an upgrade to 19c because a tablescan has become cheaper or a cardinality estimate has been reduced.

Just as a confirmation of how the change in plan is echoing reality, here are the execution plans pulled from memory after executing them with the statistics_level set to all to enable collection of the rowsource execution statistics. First the 18c plan, then the 19c plan:

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:11.14 |    1780K|
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:11.14 |    1780K|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    100 |  10000 |00:00:11.14 |    1780K|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("F1"("OBJECT_ID")='Y')



-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.46 |   97010 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.46 |   97010 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      5 |    544 |00:00:00.46 |   97010 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("DATA_OBJECT_ID" IS NOT NULL AND "F1"("OBJECT_ID")='Y'))

As you can see, the buffer gets has dropped from 1,780K in 18c to 97K in 19c (mainly because the function results in a tablescan of a table of 178 blocks and the number of calls has dropped from 10,000 to 544), and the run time has dropped from 11.14 seconds to 0.46 seconds.

Code

If you want to run and refine this test, here’s the code I used to generate the data.

rem
rem     Script:         19c_not_null_tweak.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem

create table t1 as select * from all_objects where rownum <= 10000;
create table t2 as select * from t1;

create or replace function f1(i_obj in number) return varchar2
is
        n1 number;
begin
        select count(*) into n1 from t2 where object_id = i_obj;

        if n1 = 0 then
                return 'N';
        else
                return 'Y';
        end if;
end;
/

set autotrace traceonly explain

alter session set optimizer_features_enable = '18.1.0';
select count(data_object_id) from t1 where f1(object_id) = 'Y';

alter session set optimizer_features_enable = '19.1.0';
select count(data_object_id) from t1 where f1(object_id) = 'Y';

set autotrace off

set serveroutput off
alter session set statistics_level = all;

alter session set optimizer_features_enable = '18.1.0';
select count(data_object_id) from t1 where f1(object_id) = 'Y';
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

alter session set optimizer_features_enable = '19.1.0';
select count(data_object_id) from t1 where f1(object_id) = 'Y';
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

alter session set statistics_level = typical;
set serveroutput on

July 5, 2021

Fussy FBIs

Filed under: CBO,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 11:19 am BST Jul 5,2021

In a recent thread on the Oracle Developer Forum a user was seeing a significant increase in time spent waiting for row locks after the number of executions of a particular “select for update” had increased from a couple of hundred per hour to a thousand per hour.

It turned out that the locking was a deliberate queueing mechanism following the basic pattern:

lock a row in the "locks" table

do some work in "another table" to flag some rows (perhaps to "own" them).

commit;

The intent was to ensure that processes did not collide (and possibly deadlock) while working on “another table”. It turned out that the increased wait time was due to an increase in the time spent between the lock and the commit; and the reason for that increase was simply a change in the execution path of a key statement executed between the two steps. The core of the work was simply the execution of one or both of two statements:

UPDATE TRAN_TAB 
SET 
        PID     = :B3,
        LOCK_ID = :B2,
        STATUS  = 'I' 
WHERE
        PID    IS NULL 
AND     STATUS = 'W' 
AND     ROWNUM <= :B1
;

UPDATE TRAN_TAB 
SET 
        PID     = :B3,
        LOCK_ID = :B2,
        STATUS  = 'I' 
WHERE
        PID    IS NULL 
AND     STATUS = 'T' 
AND     ROWNUM <= :B1
;

Originally the query had been using an index range scan on an index defined as (status, id, lock_id) but it had switched to using a tablescan because the estimated cardinality had changed from 18 rows to 3.5 million rows.

When you notice that the leading column of the index is called status you might guess (correctly) that there are just a few distinct values for the status, and just a few rows each for values ‘T’ and ‘W’ and that something unexpected had happened during statistics collection that had made Oracle “lose” sight of the special cases and treat ‘T’ (or ‘W’) as an “average” case either using “total rows / num_distinct” or “half the least popular” to estimate the cardinality. [At the time of writing it looks as if the problem appears as a side effect of “real-time statistics”.]

One fix, of course, would be to ensure that the statistics for this column never ever went wrong – and there are various ways of doing that, some more complicated and fragile than others (it’s a partitioned table and needs a suitable frequency histogram in place to get good estimates – the combination isn’t nice). Another strategy would simply be to hint the code (or add an sql_plan_baseline or sql_patch) to use the relevant index.

The nicest strategy (especially given the update to two columns out of the three in the index) might be to take advantage of function-based indexes – creating an index that would be impossible for the optimizer to avoid for these queries, that is as small and efficient as possible, and is highly unlikely to be used in the wrong circumstances. For example, a “two-index” solution:

create index tt_ft on tran_tab(
        case when status = 'T' and pid is null then 0 end
);

create index tt_fw on tran_tab(
        case when status = 'W' and pid is null then 0 end
);

or “single-index” solution:

create index tt_ftw on tran_tab(
        case when status in ('W','T') and pid is null then status end
);

The indexes hold entries only for the very small number of interesting rows, and when the status is updated the entries disappear from the index (rather than being deleted from, and re-inserted to, a very large index). Given the number of partitions in the table (ca. 100) and the very small number of rows involved, and the time-critical nature of the requirement, there’s a good case for making this a global index to avoid the need for doing lots of index probes that will find no data.

The next critical issue is that the code has to be modified to use the index – and the code has to be very precisly written. Here, from a simple model (see footnote), are a couple of examples followed by their (actual) execution plans:

select  lock_id 
from    tran_tab 
where   case when status = 'T' and pid is null then 0 end = 0 
and     rownum <= 5;

select * from table(dbms_xplan.display_cursor);


-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |       |       |     6 (100)|          |
|*  1 |  COUNT STOPKEY                       |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TRAN_TAB |     5 |    30 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | TT_FT    |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5)
   3 - access("TRAN_TAB"."SYS_NC00006$"=0)


select  lock_id 
from    tran_tab 
where   case when status in ('W','T') and pid is null then status end = 'W'
;

select * from table(dbms_xplan.display_cursor);


------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |       |       |    11 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TRAN_TAB |    10 |    60 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TT_FTW   |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TRAN_TAB"."SYS_NC00008$"='W')

Be Careful

The title of this piece is “Fussy FBI” – and the reason for writing it is a reminder that it’s nicer to create and index virtual columns rather than creating function-based indexes. And, if you’re on any recent version of Oracle (12c onwards) it’s a good idea to make the virtual columns invisible so that lazy code (select *, or insert without a specified list of columns, or pl/sql “insert row”) doesn’t result in an error due to the virtual column.

Take the two where clauses I’ve used above and change them slightly – in one case swapping the order of predicates, in the other swapping the order of the IN lists – and the execution paths change from index ranges scans to tablescans.

where   case when status = 'T' and pid is null then 0 end = 0 -- index range scan
where   case when pid is null and status = 'T' then 0 end = 0 -- tablescan


where   case when status in ('W','T') and pid is null then status end = 'W' -- index range scan
where   case when status in ('T','W') and pid is null then status end = 'W' -- tablescan

When you create the function-based index Oracle may rewrite the definition into a “normalised” form – for example when I query user_ind_expressions for my tt_ftw index it turns out that the stored definition is:

CASE  WHEN (("STATUS"='W' OR "STATUS"='T') AND "PID" IS NULL) THEN "STATUS" END

But when you write a query that looks as if it should match the predicate that’s visible in user_ind_expressions the optimizer won’t necessarily notice the match.

Summary

When you create a function-based index the expression you use in your queries must be a very good match for the expression that you used when creating the index. This is just one reason why it may be better to create a virtual column using the expression – then no-one has to remember exactly what the expression was in their queries.

Defining the virtual column as invisible is then a sensible strategy to avoid problems due to code that doesn’t specify explicit column names in all the cases where they should appear.

Footnote

The following script will create the table and indexes used in this note:

rem
rem     Script:         fussy_fbi.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2021
rem
rem     Last tested 
rem             19.3.0.0
rem
rem     Notes:
rem     You have to be careful with FBI definitions and usage.
rem     the match has to be very good.
rem

create table tran_tab (
        pid             number,
        id              number,
        lock_id         number,
        status          varchar2(1),
        padding         varchar2(100)
);

insert into tran_tab
select
        case when mod(rownum,10) = 0 then to_number(null) else rownum end,
        rownum,
        rownum,
        chr(65 + 8 * mod(rownum,4)),
        rpad('x',100)
from
        all_objects
where
        rownum <= 1e4
;

update tran_tab set status = 'T' where mod(lock_id,1000) = 0;
update tran_tab set status = 'W' where mod(lock_id, 990) = 0;

create index tt_ft on tran_tab(
        case when status = 'T' and pid is null then 0 end
);

create index tt_fw on tran_tab(
        case when status = 'W' and pid is null then 0 end
);

create index tt_ftw on tran_tab(
        case when status in ('W','T') and pid is null then status end
);

commit;

execute dbms_stats.gather_table_stats(user,'tran_tab')

set serveroutput off

prompt  ===========
prompt  Correct use
prompt  ===========

select  lock_id 
from    tran_tab 
where   case when status = 'T' and pid is null then 0 end = 0 
and     rownum <= 5;

select * from table(dbms_xplan.display_cursor);

select  lock_id 
from    tran_tab 
where   case when status in ('W','T') and pid is null then status end = 'W'
;

select * from table(dbms_xplan.display_cursor);

prompt  ==========
prompt  Failed use
prompt  ==========

select  lock_id 
from    tran_tab 
where   case when pid is null and status = 'T' then 0 end = 0 
and     rownum <= 5;

select * from table(dbms_xplan.display_cursor);

select  lock_id 
from    tran_tab 
where   case when status in ('T','W') and pid is null then status end = 'W'
;

select * from table(dbms_xplan.display_cursor);

June 1, 2021

Distributed Sequences

Filed under: distributed,Infrastructure,Oracle — Jonathan Lewis @ 5:55 pm BST Jun 1,2021

This is an other detail to add to the note I wrote recently about the effects of adding a sequence number to a remote select when moving data from one database to another. It probably shouldn’t have much impact on performance unless it runs into code that does a lot of single row processing to handle a large batch of data.

We start with the database link, tables and sequence I created in the previous article (link will open in a separate tab/window for easy viewing), but this time I’m going to do nothing but a simple select from a single remote table – with, or without, a call to my local sequence.

alter system flush shared_pool;

select  /*+ id_only */
        id 
from    test_user.t1@&m_target
where   rownum < 4;

select * from table(dbms_xplan.display_cursor(format=>'basic +predicate +remote -plan_hash'));

select  /*+ seq_only */
        s1.nextval 
from    test_user.t1@&m_target
where   rownum < 4;

select * from table(dbms_xplan.display_cursor(format=>'basic +predicate +remote -plan_hash'));

select  /*+ both */
        id, v1, s1.nextval 
from    test_user.t1@&m_target
where   rownum < 4;

select * from table(dbms_xplan.display_cursor(format=>'basic +predicate +remote -plan_hash'));


I’ve flushed the shared pool before running my three queries because I’m going to want to do a trivial search of the library cache to pick up some run-time information as easily as possible.

A key feature of this set of queries is the “rownum” predicate – and the surprisie you get when sequences and remote queries don’t treat it nicely. (It’s also demonstrating a point about checking which bits of Oraclec code run at what stage of a query.)

Here’s the execution plan output from the three queries:

select /*+ id_only */  id from test_user.t1@orclpdb@loopback where rownum < 4

NOTE: cannot fetch plan for SQL_ID: a3jkr0hqbyccp, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)



select  /*+ seq_only */  s1.nextval from test_user.t1@orclpdb@loopback where rownum < 4

-------------------------------------------------
| Id  | Operation        | Name | Inst   |IN-OUT|
-------------------------------------------------
|   0 | SELECT STATEMENT |      |        |      |
|   1 |  SEQUENCE        | S1   |        |      |
|*  2 |   COUNT STOPKEY  |      |        |      |
|   3 |    REMOTE        | T1   | ORCLP~ | R->S |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<4)

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT 0 FROM "TEST_USER"."T1" "T1" (accessing
       'ORCLPDB.LOCALDOMAIN@LOOPBACK' )



select /*+ both */  id, s1.nextval from test_user.t1@orclpdb@loopback where rownum < 4

-------------------------------------------------
| Id  | Operation        | Name | Inst   |IN-OUT|
-------------------------------------------------
|   0 | SELECT STATEMENT |      |        |      |
|   1 |  SEQUENCE        | S1   |        |      |
|*  2 |   COUNT STOPKEY  |      |        |      |
|   3 |    REMOTE        | T1   | ORCLP~ | R->S |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<4)

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "ID" FROM "TEST_USER"."T1" "T1" (accessing
       'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

The first query (id only) is full remote, so the call to dbms_xplan.display_cursor() couldn’t find a plan in the local library cache for it, hence the Note.

The second query (sequence number only) has the sort of plan you’d expect – a fetch with stop key. Looking at the “Remote SQL information”, though, you can see that the “rownum” predicate has not reached the remote site – you might wonder what effect this wil have.

The final query (id and sequence number) seems to have an identical plan, again without passing a rownum predicate to the remote.

But let’s search for the remote SQL in the library cache – we know from experience that it will be sent in all capitals (with identifiers double quoted), Here’s a suitable query with its results:

column sql_text format a60


select
        sql_id, parse_calls, fetches, executions, rows_processed, sql_text
from    v$sql
where   sql_text like 'SELECT%T1%'
;


SQL_ID        PARSE_CALLS    FETCHES EXECUTIONS ROWS_PROCESSED SQL_TEXT
------------- ----------- ---------- ---------- -------------- ------------------------------------------------------------
71bq0j03wtsmz           2          0          0              0 SELECT /*+ FULL(P) +*/ * FROM "TEST_USER"."T1" P
1kzgwm8cj1t1x           1          2          1              3 SELECT 0 FROM "TEST_USER"."T1" "T1"
dkxd06sct6y76           1          2          1              3 SELECT "A1"."ID" FROM "TEST_USER"."T1" "A1" WHERE ROWNUM<4
c6cavnnps7kn8           1          2          1          10000 SELECT "ID" FROM "TEST_USER"."T1" "T1"


There are 4 statements that have been parsed at the remote site. I think the first statement above is probably sent from the local to the remote to allow semantic checking at the local site – there’s a little oddity in the timing of when the parse_calls column is incremented, the statement is actually sent for all three of my queries, but always seems to report one less parse than the number of statement I actually test with.

The other three statements are the different effects we get from the three statements I executed. You’ll notice that they’ve all done 2 Fetch calls – this is the standard “get the first row, get more rows” effect that you usually see from SQL*Plus. The interesting points lie in the differences between the texts sent and in the numbers of rows fetched.

  • Selecting the id only results in the (3rd) text: SELECT “A1″.”ID” FROM “TEST_USER”.”T1″ “A1” WHERE ROWNUM<4 which fetches exactly the 3 rows demanded by its rownum predicate.
  • Selecting the sequence only results in the (2nd) text: SELECT 0 FROM “TEST_USER”.”T1″ “T1” which has no rownum predicate, but manages to fetch only the 3 rows required.
  • Selecting both id and sequence results in the (4th) text: SELECT “ID” FROM “TEST_USER”.”T1″ “T1” which again has no rownum predicate, but in this case fetches all 10,000 rows from the table. This isn’t quite as bad as it appears at first glance, the data fetched is one SDU (session data unit), it’s not deliberately the entire table and if we select id and v1 the rows_processed drops, in my case, to 3,879. It’s worth remembering, of course, that this could still be a lot of work at the remote server, and the SDU in 12.2 onwards has a maxmimum value of 2MB.

tl;dr

The bottom line on this little note, though is simply this: even in the very simplest cases, sequences make a difference to the way that Oracle handles remote/disrtibuted queries – so look very closely at how you’re using them in these circumstances

May 29, 2021

Partitioning existing data

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 10:35 am BST May 29,2021

I started drafting this note 3 years ago and never got round to finishing it, but I rediscovered it today while looking for something I had written about the “alter table … modify” command options that you can use to convert a non-partitioned table into a partitioned table, or change the partitioning of an already partitioned table (which may need extra preparatory work). I’ve left the opening comments and list of thoughts in place, but there are two particular details I want to talk about in a little more detail because it’s possible that they may turn into relatively commonly occurring issues that will stopyou using this mechanism.

So let’s go back three years.


When someone asks the question, as happened on the ODC (OTN) database forum a little while ago [ed: March 2018]: “How do I partition a 90GB table?” there’s really only one correct (though somewhat cruel) answer: “If you have to ask the question, you probably shouldn’t.”

There are so many possible side effects to partitioning that if you’ve managed to work out what column(s) to partition on, what partitioning strategy is appropriate, what partitioning maintenance operations you will have to handle, what problems you might have with referential integrity, and how you’re going to re-engineer the indexes then the question of how to do the physical re-arrangement is trivial by comparison. Let’s assume we have done all the other homework, though, and consider possibilities.

  • Would partitioning “from now on” be acceptable, or do you have to partition the past ?
  • Is there down-time for the operation, or does it have to be done online.
  • Whether online or “offline” you need a lot of space somewhere to duplicate the data (and, possibly, the indexes)
  • Could you benefit from different storage attibutes for different (e.g. aged) partitions: pctfree 0, basic compresion
  • Could you take advantage of new features as you partition (partial indexes, attribute clustering)
  • Have you allowed time for re-creating indexes on the copy
  • Will you need, or could you benefit from, changes to indexes – local / global / removing the partiitoning column
  • Do you need to allow time for re-validating referential integrity (both ways, perhaps)
  • Do you have a library of scripts that could handle all the referential integrity, privileges, etc.
  • Do you have a standby, and what are the implications for refreshing it or keeping it in synch
  • Do you have any columns marked unused that you would like to see dropped.
  • Do you have any columns that were “fast add default” columns that could be cleaned up

Today (May 2021) I had a reason to consider the last two points in that list and how well the options in “alter table XXX modify” for partitioning might handle them. I’ll take the unused columns first because their implementation is the more commonly known.

Unused Columns

When you set a column unused, it gets flagged and renamed in the data dictionary but any data stored in that column stays in place (invisibly) in the segment until you subsequently drop the column – and dropping a column is a very expensive thing to do, dropping multiple columns is even worse as Oracle drops each column for each row as a separate pair of undo and redo vectors. I wrote an article about this for Simpletalk a few years ago – the figures in the article came from an instance running 12.1.0.2 but the behaviour is the same in 19.11.0.0.

So marking a column unused is quick and cheap, dropping it is hideously slow and expensive. However it would be good to have a mechanism for getting rid of the column without the awful overheads because if you don’t do so the day will come when someone, someday, will accidentally “drop column” when they really meant “set unused” and then your production system will effecetively stop until the whole operation is complete.

This is where I thought the 12.2 option for online turning a non-partitioned table into a partitioned table or even the simple “alter table XXX  move online” might help. These commands will copy every single row in the table from a source copy to a destination copy (dealing with all the indexes etc.) so they could (in principle) rewrite the rows without the unused columns and then fix up the data dictionary at the end of the move/modify.

They don’t.

They get a little way towards cleaning the table up, all the unused columns are set to null while the rows are copied so you get the space back – and it’s more likely to be usable and safe to use than it would be with the “drop column” command, but the column is still there (with an obscure name in the data dictionary telling you when it was dropped (e.g. SYS_C00002_21052719:24:01$ was once the second column in the table, marked unused at 19:24:01 on 27th May 2021). So the table is still exposed to the risk of someone coming along and issuing a system-threatening “drop column”. And, of course, it’s still sitting there contributing to the 255 “real-column” limit where the row has to start splitting into multiple row-pieces.

Fast Add Nullable (FAN) Default

What about tables where you’ve added a column with a default value.  In 11g Oracle introduced a mechanism that stored the value in the data dictionary if the column were declared as not null and didn’t have to update every row in the table. In 12c the mechanism was extended to include nullable columns by adding an extra column on the table to flag rows that were inserted or had the default value updated since the column was added. (Each bit in this extra column corresponds to one FAN column in the table.)

Again you a might think that “alter table … move” and “alter table … modify” would be able to tidy things up by filling in the default value as they rewrite the table. And, just as we’ve seen with unused columns, they nearly do the job  – but they still leave the spare (and now apparently redundant) mapping column in place with all the infrastructure that would allow Oracle to work out whether or not a null meant null.

But there’s more to this than just the a little bit of infrastructure in this case because the optimizer and run-time engine carry on doing all the work that they did when the mapping column was needed. Here’s a little code to demonstrate the point:


rem
rem     Script:         fast_default_add_col.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem             18.3.0.0
rem             12.2.0.1
rem

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4    -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1
where
        rownum <= 1e3   -- > comment to avoid WordPress format issue
;

alter table t1 add (def_val varchar2(10) default 'XXX' /* not null */);

column data_default format a20

select
        column_name, column_id, segment_column_id, internal_column_id, avg_col_len, data_default
from
        user_tab_cols
where
        table_name = 'T1'
order by
        column_id
;

insert into t1(id, v1, padding, def_val) values(-1, 'x', 'x', 'xz');
insert into t1(id, v1, padding, def_val) values(-2, 'x', 'x', null);
update t1 set def_val = 'yy' where id = 1;
commit;

column sys_nc00004$ format a32

select distinct sys_nc00004$, count(*) 
from t1 
group by sys_nc00004$
;

I’ve created table with 1,000 rows, added a (nullable) column with default value, then inserted two rows and update the new column of a pre-existing row. I’ve run a query to show that there’s a “spare” system generated column which is a real (stored in the segment) column. I’ve then reported the aggregate information about this sytem generated column. Here are the results of the two queries:

OLUMN_NAME           COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID AVG_COL_LEN DATA_DEFAULT
-------------------- ---------- ----------------- ------------------ ----------- --------------------
ID                            1                 1                  1           4
V1                            2                 2                  2          11
PADDING                       3                 3                  3         101
DEF_VAL                       4                 5                  5           4 'XXX' /* not null */
SYS_NC00004$                                    4                  4           0


SYS_NC00004$                       COUNT(*)
-------------------------------- ----------
01                                        3
                                        999

If you cared to query t1 where sys_nc00004$ is not null you’d see that the three rows from the aggregate report were the two I’d inserted plus the one I’d updated.

So let’s run a couple of queries against the data and check their execution plans – first a simple aggregate query on the def_val column:


set serveroutput off

set feedback only
select def_val, count(*) from t1 group by def_val;
set feedback on
select * from table(dbms_xplan.display_cursor(format=>'+projection'));

Plan hash value: 136660032

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     5 (100)|          |
|   1 |  HASH GROUP BY     |      |     1 |     4 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  1000 |  4000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00004$",0)),NULL,NVL("DEF_VAL
       ",'XXX'),'0',NVL("DEF_VAL",'XXX'),'1',"DEF_VAL")[10], COUNT(*)[22]
   2 - DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00004$",0)),NULL,NVL("DEF_VAL
       ",'XXX'),'0',NVL("DEF_VAL",'XXX'),'1',"DEF_VAL")[10]

The Column Projection Information tells use that Oracle is not just looking at the def_val column to produce a result for this query. The optimizer has used the data dictionary information to construct a decode (not case !) expression that will be evaluated for every row. The expression checks the system generated column to check the bit relevant to the def_val column and uses it to decide whether to report the def_val column itself, or to report the default value from the data dictionary.  (It is possible to get the same result using a decode with just two components, but I think the Oracle developer may have decided that the longer expression would be decoding “most likely first”.)

The same complicated expression appears as the projection for operation 1, but I don’t think this means Oracle is actually re-evaluating the expression – after all def_val isn’t being projected upwards from operation 2 so isn’t available for slotting into the expression.

Here’s another example that might be used – find all the rows where null was explicitly supplied:

set feedback only
select * from t1 where def_val is null;
set feedback on
select * from table(dbms_xplan.display_cursor(format=>'+projection'));

Plan hash value: 3617692013

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00004$",0)),NULL,NVL("
              DEF_VAL",'XXX'),'0',NVL("DEF_VAL",'XXX'),'1',"DEF_VAL") IS NULL)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T1"."ID"[NUMBER,22], "T1"."V1"[VARCHAR2,40],
       "T1"."PADDING"[VARCHAR2,100], DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00004$
       ",0)),NULL,NVL("DEF_VAL",'XXX'),'0',NVL("DEF_VAL",'XXX'),'1',"DEF_VAL")[
       10]

As you can see, the same decode() expression appears in both the Predicate Information and as a column projection. Again, this probably doesn’t mean it has to be evaluated twice per row but there’s clearly a CPU overhead for taking advantage of the fast add default mechanism – though it might be possible to find cases where this is offset by the improved data packing due to shorter rows.

Eventually you might decide to tidy things up a bit with a simple ‘alter table move’ (or, as for the rest of this note, ‘alter table … modify” so what changes if you do this?

The actual default value will get written into the row, but apart from that nothing changes – the mapping column stays exactly where it is, none of its bits are altered, and (possibly most importantly) the complex decode() expression doesn’t go away. It’s another case where there seems to be an opportunity for the code to do a more thorough job.

tl;dr

From 12c onwards when “alter table move” and “alter table … modify “ are going to rewrite the entire table you might expect them to tidy up all the mess introduced by setting columns unused or introducing nullable columns with default values . On the plus side they do clean the data, but they don’t clean the metadata as thoroughly as they could do and this does leave processing overheads continuing into the future.

Footnote

You will often hear the warning “The execution plans from Explain Plan may be telling lies”. The new mechanisms for adding columns with default values gives it a lovely opportunity to do so. Here’s what explain plan does with the last query above:


explain plan for
select * from t1 where def_val is null;

select * from table(dbms_xplan.display(format=>'projection'));

lan hash value: 3617692013

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEF_VAL" IS NULL)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T1"."ID"[NUMBER,22], "T1"."V1"[VARCHAR2,40],
       "T1"."PADDING"[VARCHAR2,100], "DEF_VAL"[VARCHAR2,10]

 

May 22, 2021

255 columns

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 1:19 pm BST May 22,2021

Here’s a little note about column counts that I’ve not mentioned before now in the various articles I’ve written about the side effects of declaring tables with more than 255 columns. The count is about “real” columns and you should ignore virtual columns – but there are some real columns you might not notice unless you look carefully. Here’s a little quiz, starting with a table definition:

rem
rem     Script:         column_count.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem 

create type jpl_row as object (n1 number, v1 varchar2(10));
/

create table t1 (
        n1      number, 
        ot      jpl_row, 
        n2      number
)
;

How many “real” columns are there in table t1?

Let’s ask the data dictionary:

select  column_name, column_id, data_type
from    user_tab_columns
where   table_name = 'T1'
order by 
        column_id
/

COLUMN_NAME           COLUMN_ID DATA_TYPE
-------------------- ---------- -------------------------
N1                            1 NUMBER
OT                            2 JPL_ROW
N2                            3 NUMBER

3 rows selected.

It looks like three, just as we declared, except we’re looking at the wrong bit of the data dictionary:

select
        column_name, 
        column_id,
        segment_column_id,
        data_type,
        hidden_column,
        virtual_column
from
        user_tab_cols
where   table_name = 'T1'
order by 
        column_id
/

COLUMN_NAME           COLUMN_ID SEGMENT_COLUMN_ID DATA_TYPE                 HID VIR
-------------------- ---------- ----------------- ------------------------- --- ---
N1                            1                 1 NUMBER                    NO  NO
OT                            2                 2 JPL_ROW                   NO  NO
SYS_NC00003$                  2                 3 NUMBER                    YES NO
SYS_NC00004$                  2                 4 VARCHAR2                  YES NO
N2                            3                 5 NUMBER                    NO  NO

5 rows selected.

The correct answer is five, and they’re all “real” – check the segment_column_id. I was slightly surprised at this because I had been assuming that the two attributes of the jpl_row type would be real but hidden and the jpl_row type itself to be virtual.

Conclusion

When checking the number of columns in a table, make sure you look at the xxx_tab_cols view – not the xxx_tab_columns to get complete definitions of all the columns, real, virtual, and hidden.

Lagniappe

How many “real” columns does the following statement add to the table definition:

alter table t1 add n3 number default 999;

Here’s the result of repeating my query against user_tab_cols:

COLUMN_NAME           COLUMN_ID SEGMENT_COLUMN_ID DATA_TYPE                 HID VIR
-------------------- ---------- ----------------- ------------------------- --- ---
N1                            1                 1 NUMBER                    NO  NO
SYS_NC00003$                  2                 3 NUMBER                    YES NO
OT                            2                 2 JPL_ROW                   NO  NO
SYS_NC00004$                  2                 4 VARCHAR2                  YES NO
N2                            3                 5 NUMBER                    NO  NO
N3                            4                 7 NUMBER                    NO  NO
SYS_NC00006$                                    6 RAW                       YES NO

7 rows selected.

The mechanics of Oracle’s “fast add” of a default column actually adds two column, one hidden, if the column being added is a nullable column. [See comment #1 below from Adam Leszczyński you get just the one “extra” column no matter how many nullable columns with defaults you subsequently add to the table.]

May 9, 2021

Driving site patch

Filed under: distributed,Hints,Oracle — Jonathan Lewis @ 12:23 pm BST May 9,2021

A recent question on the Oracle-L list server asked “Is there a way I can use an SQL_PATCH to add a driving_site() hint to a query?” to which the reply was: “Just do it, it should work.” Unfortunately this produced the response: “I’m on 11.2.0.4, I’ve tried it, it doesn’t seem to be working.” I do approve of the “doesn’t seem to be” – it’s much more encouraging than a flat assertion that “it doesn’t work”, and helps encourage further dialogue.

I’ve come across this problem before (though I couldn’t find any notes I’d written about it – so possibly they’re only on a client site, or maybe it was a different hint displaying the same symptom) and it’s possible that the solution is very easy. Here’s a little data set to test with – created on 11.2.0.4 and then tested on 19.3.0.0:

rem
rem     Script:         patch_driving_site.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem             11.2.0.4
rem

define m_target=orclpdb@loopback

execute sys.dbms_sqldiag.drop_sql_patch('driving_site');

create table t1
as
select
        *
from
        all_objects
where
        rownum <= 10000
;

alter table t1 add constraint t1_pk primary key (object_id);

create table t2
as
select
        *
from
        all_objects
where
        rownum <= 10000
;


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

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

I’ve created two tables which I’m going to join, but I’ve created a loopback database link that I’ll use to make one of them appear to be at a remote database. The data comes from view all_objects, and includes just 7 rows out of 10,000 for owner=’OUTLN’, which is why I’ve created a histogram on the owner column – but only for one of the tables.

You’ll notice I’ve issued a call to the dbms_sqldiag package to drop an SQL patch called “driving_site” just to make sure I don’t confuse the issue (i.e. myself) if I re-run the test in a couple of days time (I’ve also got a similar call at the end of the script). To run this test you’ll have to grant execute privileges on this package to your test schema, and if you’re on 11g you’ll also have to grant execute privileges on the package dbms_sqldiag_internal. so that you can create the SQL patch.

Here’s my query, with the default execution plan I got on the run against 19.3.0.0:

select
        t1.object_name,
        t1.object_type,
        t2.object_name,
        t2.object_type
from
        t1,
        t2@&m_target    t2
where
        t2.object_id = t1.object_id
and     t2.owner     = 'OUTLN'
/

select * from table(dbms_xplan.display_cursor(format=>'outline alias'));


SQL_ID  4dssxbjvzzrc0, child number 0
-------------------------------------
select  t1.object_name,  t1.object_type,  t2.object_name,
t2.object_type from  t1,  t2@orclpdb@loopback t2 where  t2.object_id =
t1.object_id and t2.owner     = 'OUTLN'

Plan hash value: 3828311863

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |    54 (100)|          |        |      |
|   1 |  NESTED LOOPS                |       |  1111 |   165K|    54   (8)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS               |       |       |       |            |          |        |      |
|   3 |    REMOTE                    | T2    |  1111 |   130K|    26   (4)| 00:00:01 | ORCLP~ | R->S |
|*  4 |    INDEX UNIQUE SCAN         | T1_PK |       |       |            |          |        |      |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    33 |    27   (8)| 00:00:01 |        |      |
------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T2@SEL$1
   4 - SEL$1 / T1@SEL$1
   5 - SEL$1 / T1@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      NLJ_BATCHING(@"SEL$1" "T1"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "OWNER","OBJECT_NAME","OBJECT_ID","OBJECT_TYPE" FROM "T2" "T2" WHERE
       "OWNER"='OUTLN' (accessing 'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

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

The optimizer has decided to do a nested loop join, fetching an estimated 1,111 rows (there are 10,000 rows in total and 9 distinct values for owner – and the optimizer doesn’t examine remote histograms!) The costing may seem a little surprising – an incremental cost of 27 for 1,111 probes of the index and table does seem a little low, but I’m not going to comment on that in this note.

We are actually going to get only 7 rows in the tablescan, so the path is a good one; but the Note tells us it is an adaptive plan and if at run-time the number of rows had been too large (as it would probably be for some other value of owner) Oracle would have switched to a hash join as the query is running.

Let us pretend, however, that we know that we could get better performance if the remote database optimised and executed the query. If we add the hint /*+ driving_site(t2) */ to the query we get the following from the call to dbms_xplan.display_cursor():

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID  86n3j9s9q9k47, child number 0

select  /*+ driving_site(t2) */  t1.object_name,  t1.object_type,
t2.object_name,  t2.object_type from  t1,  t2@orclpdb@loopback t2 where
 t2.object_id = t1.object_id and t2.owner     = 'OUTLN'

NOTE: cannot fetch plan for SQL_ID: 86n3j9s9q9k47, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

The call can’t find the plan because it’s the remote database that generated it from a piece of text that the local database sent to it, and the plan “belongs to” that text, not to the original query. So that almost tells us that the driving_site() hint has done its job properly – but we can double-check by searching the remote database’s library cache for the SQL that it actually ran.

set linesize 132
column sql_text wrap word format a75

select  sql_id, sql_text 
from    V$sql 
where   sql_text like '%OUTLN%'
;


SQL_ID        SQL_TEXT
------------- ---------------------------------------------------------------------------
5hmjcxgt0jc8t SELECT
              "A2"."OBJECT_NAME","A2"."OBJECT_TYPE","A1"."OBJECT_NAME","A1"."OBJECT_TYPE"
              FROM "T1"@! "A2","T2" "A1" WHERE "A1"."OBJECT_ID"="A2"."OBJECT_ID" AND
              "A1"."OWNER"='OUTLN'


select * from table(dbms_xplan.display_cursor('5hmjcxgt0jc8t'));


SQL_ID  5hmjcxgt0jc8t, child number 0
-------------------------------------
SELECT "A2"."OBJECT_NAME","A2"."OBJECT_TYPE","A1"."OBJECT_NAME","A1"."OB
JECT_TYPE" FROM "T1"@! "A2","T2" "A1" WHERE
"A1"."OBJECT_ID"="A2"."OBJECT_ID" AND "A1"."OWNER"='OUTLN'

Plan hash value: 3485226535

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    33 (100)|          |        |      |
|   1 |  NESTED LOOPS      |      |     7 |   924 |    33   (4)| 00:00:01 |        |      |
|*  2 |   TABLE ACCESS FULL| T2   |     7 |   280 |    26   (4)| 00:00:01 |        |      |
|   3 |   REMOTE           | T1   |     1 |    92 |     1   (0)| 00:00:01 |      ! | R->S |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A1"."OWNER"='OUTLN')

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "OBJECT_NAME","OBJECT_ID","OBJECT_TYPE" FROM "T1" "A2" WHERE
       :1="OBJECT_ID" (accessing '!' )


As we can see from the plan – which executed from the remote instance – the (now-local) histogram comes into play with a correct cardinality estimate for t2 and we do a nested loop requesting one row at a time from the (now remote) t1 by object_id. So the driving_site() hint works when written inline, which means we can work on creating a patch to apply it from “outside”.

begin
        dbms_output.put_line('19.3 patch name: ' || sys.dbms_sqldiag.create_sql_patch(
--      sys.dbms_sqldiag_internal.i_create_patch(
                sql_text        =>  
q'{
select
        t1.object_name,
        t1.object_type,
        t2.object_name,
        t2.object_type
from
        t1,
        t2@&m_target    t2
where
        t2.object_id = t1.object_id
and     t2.owner     = 'OUTLN'
}',
                hint_text       => 'driving_site(t2)',
--              hint_text       => 'driving_site(t2@sel$1)',
                name            => 'driving_site'
        ))       -- extra bracket for dbms_output.put_line
        ;
end;
/

There are a couple of funny format details in this code fragment. First, dbms_sqldiag in 19c uses a function to create an SQL patch while 11g uses a procedure in dbms_sqldiag_internal, so there’s a messy optional bit near the top of the code and the reminder to count closing brackets near the bottom.

Second – when I used an SQL patch to add the hint that had worked it didn’t do what it was suppoed to do (even though the Note for the execution plan reported: “SQL patch “driving_site” used for this statement”. That’s why you see two versions (one commented) of the hint_text parameter. To get the SQL patch working I had to use the fully qualified alias of the remote table which, as you can see in the Query Block / Object Alias information from the first plan I reported, is t2@sel$1.

With the corrected SQL patch in place the text sent to the remote database was exactly the same as it was when I used the inline (but slightly lucky) /*+ driving_site(t2) */ hint. Quite possibly I should have gone one step further and made the hint_text include the query block as well, vizl: driving_site(@sel$1 t2@sel$1) I suspect, though, that that might not always be necessary (or even correct) – at some point I’ll have to check what happens if the reported query block has appeared as a consequence of a transformation and no longer matches the original query block from the object alias.

Summary

I’ve often made the point that you have to be very thorough with hints and this note demonstrates a variation of that theme. There are cases where you can get away with being a little sloppy when specifying an object alias in an “inline” hint but have to be more precise when imposing it from outside the original source SQL.

Adding the “originating” query block to construct the “fully qualified” object alias is the first refinement that you should think of; adding in a “target” query block name may also be necessary for some hints. Both these pieces of information can be reported in the various calls to dbms_xplan by including the “alias” option in the format parameter as you build your test cases towards a complete solution.

Footnote

Another example of the need for “refinement” to the simple in-line hint – a tweet thread from a couple of years ago where the hint use_nl_with_index() needing a fully qualified alias and/or the query block name when moved into an SQL patch.

April 13, 2021

CSV

Filed under: Infrastructure,Oracle,Problem Solving — Jonathan Lewis @ 3:00 pm BST Apr 13,2021

An old question with a little extra twist came up on the Oracle Developer forum recently – someone wanted to export a table into a CSV file but wanted to do a character set conversion at the same time because various “non-printable” characters were getting corrupted.

Looking at the code they had supplied they were running from SQL*Plus and were trying to use a select statement to generate a script that, for each row in a table, produced a statement of the form:

insert into tableX({list of columns}) values({list of values});

This worked reasonably well when they didn’t attempt to get a UTF8 output, but when they modified their client O/S environment to make SQL*Plus produce the desired UTF8 output the operation was much slower, increasing from about 1 minute to more than half an hour. (This may simply be the overhead of SQL*Net translating each line of output from the server character set to the client character set.)

Since the system in question was running on 12.2 I suggested a change of strategy so that they could at least take advantage of a built-in CSV generator to see how this behaved. Oracle extended the “set markup” command in 12c to include a CSV option:

set markup csv on delimiter {character} quote on

If this behaves nicely with the user’s attempt to get the output in their chosen characterset then a change in strategy to generate a “proper” CSV file and reload it through an external table, rather than generating and executing a list of “insert ….” statements might do a lot less work. (Unfortunately they had a requirement to generate the insert statements rather than adopting this bulk strategy – but I wasn’t going to let that stop me finishing this note.)

One detail to note with this markup option is that, unlike the “delimiter” character, the “quote” character cannot be specified and will always be the double-quote (ASCII character 34). If this character appears in any of the reported strings Oracle will “escape” it by duplicating it. Here’s a little example to demonstrate the effects – first a little data with a basic output (running 12.2.0.1):

rem
rem     Script:         markup.csv.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2020
rem

create table t1 (v1 varchar2(10), n1 number, n2 number, v2 varchar2(10));

insert into t1 values('abcdef',1,1,'qwer');
insert into t1 values('abc"def',2,null,'asdf');
insert into t1 values (null,3,3,null);

commit;

prompt  ==================
prompt  markup not yet set
prompt  ==================

select * from t1;


V1                 N1         N2 V2
---------- ---------- ---------- ----------
abcdef              1          1 qwer
abc"def             2            asdf
                    3          3

3 rows selected.

I’ve included one row with a double-quote in one of the columns, and a couple of rows with NULLs in various columns. Now I just switch markup to CSV using its default options:

prompt  ==================
prompt  Default CSV markup
prompt  ==================

set markup csv on
select * from t1;


"V1","N1","N2","V2"
"abcdef",1,1,"qwer"
"abc""def",2,,"asdf"
,3,3,

3 rows selected.


As you can see this has behaved nicely – every column is present (even when empty – judging by the number of delimiters) and character strings have been quoted with the double-quote character, and the stored double-quote in the second row has been duplicated. The default option for CSV should be nearly perfect for use as an extrernal table – though we probably want to get rid of column headings, page breaks, and feedback etc.

Before creating a suitable external table, let’s play around with a couple of variations for the options:

prompt  ===========
prompt  delimiter =
prompt  ===========

set heading off
set pagesize 0
set feedback off

set markup csv on delimiter =
select * from t1;

"abcdef"=1=1="qwer"
"abc""def"=2=="asdf"
=3=3=

prompt  ================================
prompt  quote off (delimiter remembered)
prompt  ================================

set markup csv on quote off
select * from t1;

abcdef=1=1=qwer
abc"def=2==asdf
=3=3=

Now to read back one of the outputs – I’ll pick the default output for this (but with headers etc. off to leave just three lines of data). Here’s how I can define my external table to read back the values from the file:

create or replace directory ext_tab as '/mnt/working/';
create or replace directory ext_log as '/mnt/working/';

create table ext(
        v1 varchar2(10), 
        n1 number, 
        n2 number, 
        v2 varchar2(10)
)
organization external(
        type oracle_loader
        default directory ext_tab
        access parameters (
                records delimited by newline 
                discardfile     ext_log:'exttab_dis.txt'
                badfile         ext_log:'exttab_bad.txt'
                logfile         ext_log:'exttab_log.txt'
        fields 
                csv 
                with embedded
        missing field values are null
        )
        location ('markup_csv.lst')
)
reject limit 10
;

select * from ext;

V1                 N1         N2 V2
---------- ---------- ---------- ----------
abcdef              1          1 qwer
abc"def             2            asdf
                    3          3

3 rows selected.

I’ve created a couple of directories to get started – linking a physical location to a logical name that I can use in the external table definition.

The key details that allow Oracle to read the file correctly appear in the “fields” specification: it’s a pity that the output from the default CSV markup doesn’t become the default expectation for the external table CSV option, but I needed to use the “with embedded” to adjust the duplicated “double-quote” characters and then add “missing field values are null” to cope with a null column at the end of the line.

Summary

With a simple call to “set markup csv on” (and a handful of other set {formatting} commands) it’s very easy to turn a table into a flat CSV file that is comma separated, (double)quote deliminted.

It’s then very easy – but requires two non-default settings – to read this flat file back into an Oracle database using “fields CSV” as the basic formatting directive.

Footnote

I’ve kept the example very simple, but as soon as you start dealing with dates and timestamps – and even very large numbers – you’ll have to worry about formatting when generate the flat file (set nls_date_format, column n1 format 99999, etc.) to make sure you don’t lose precision on the output; and for dates and timestamps you’ll have to include the equivalent date_format options in the external table definition.

I’ve only demonstrated how to reload the file back into Oracle as an external table. If you wanted to use this markup option to prepare the data for an Excel spreadsheet all you have to do is spool to a file with the “.csv” extension and Excel will be able to open the output file and parse the data correctly into a multi-column display. (But see comment #1 below for a warning from Jason Bucata.)

April 5, 2021

Case Study

Filed under: Oracle,Tuning — Jonathan Lewis @ 3:36 pm BST Apr 5,2021

A recent question on the Oracle Developer forum posed an interesting question on “finding the closest match” to a numeric value. The OP supplied SQL to create two tables, first a set of “valid” values each with an Id, then a set of measures. The requirement was to find, for each measure, the closest valid value and report its id.

In this note I’m going to make a few comments on three topics:

  • how the question was posed,
  • general thoughts on modelling,
  • some ideas on what to look for when testing possible solutions

We’ll start with the data (almost) as supplied:

rem
rem     Script:         closest_match.sql
rem     Author:         Jonathan Lewis / user626688
rem     Dated:          Apr 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem             11.2.0.4         (with event 22829)
rem
 
create table nom_val_lkp(
        lkp_id  number       not null,
        nom_val number(3,2)  primary key
)
-- organization index
/

insert into nom_val_lkp values(1, 0.1);
insert into nom_val_lkp values(2, 0.2);
insert into nom_val_lkp values(3, 0.3);
insert into nom_val_lkp values(4, 0.4);
insert into nom_val_lkp values(5, 0.5);
insert into nom_val_lkp values(6, 0.6);
insert into nom_val_lkp values(7, 0.7);
insert into nom_val_lkp values(8, 0.8);
insert into nom_val_lkp values(9, 0.9);
commit;

create table measure_tbl(
        id              number      not null, 
        measure_val     number(3,2) not null
)
/

insert into measure_tbl values(1, 0.24);
insert into measure_tbl values(2, 0.5);
insert into measure_tbl values(3, 0.14);
insert into measure_tbl values(4, 0.68);
commit;

insert into measure_tbl values(5, 1.38);
insert into measure_tbl values(6, 0.05);
commit;


execute dbms_stats.gather_table_stats(null,'measure_tbl')
execute dbms_stats.gather_table_stats(null,'nom_val_lkp')

There are a couple of differences between the original and the SQL I’ve listed above. Given the nature of the requirement I’ve added not null constraints to both the lkp_id and nom_val columns of the “valid values” table. I think it’s also reasonable to assume that both columns outght to be (individually) unique and could both be candidate keys for the table although I’ve not bothered to add a uniqueness constraint to the lkp_id. I have made the nom_val (the interesting bit) the primary key because that’s potentially an important feature of a good solution. Obviously this is guesswork on my part, but I think they’re reasonable guesses of what the “real application” will look like and they’re details that ought to be been included in the original specification.

You’ll see that I’ve also included the option for making the table an index organized table – but that’s a generic implementation choice for small look-up tables not something that you could call an omission in the specification of requirements.

One thing to note about the nom_val_lkp table is that the nom_val is strictly constrained to be 3 digits with 2 decimal places, which means values between -9.99 to +9.99. It’s going to be a pretty small table – no more than 1,999 rows. (In “real life” it’s possible that the measure all have to be postive – and if so that’s another detail that could have gone into the specification – so the column could also have a check constraint to that effect.)

Looking at the measure_tbl (which is the “big data” table) I’ve added not null constraints to both columns; I’ve also added a couple of extra rows to the table to make sure that we can test boundary conditions when we write the final SQL statement. We’re looking for “the closest match” so we’ll be looking in the nom_val_lkp table for values above and below the measure value – so we ought to have a measure row where there is no “below” value and one with no “above” value. A common oversight in modelling is to forget about checking special cases, and simple boundary conditions are often overlooked (or inadequately covered).

Thinking about the “above / below / closest” requirement, an immediate follow-up questions springs to mind. What if there is no exact match and the valid values either side are the same distance from the measure? If there’s a tie should the query return the lower value or the higher value, or does it not matter? The specification is not complete, and the most efficient solution may depend on this detail.

Interestingly the measure_val column is constrained in exactly the same way as the nom_val column -3 digits with 2 d.p. Apparently the requirement isn’t something like “take a measurement to 6 decimal places then give me a value to 2 d.p.”; no matter how large the measure_val table gets the number of distinct values it records is pretty small – which means caching considerations could become important. With this thought in mind I added a few more lines (before gathering stats) to make multiple copies of the supplied measures data to model (approximately, and with a very large bias) a large table with a small number of distinct measures.

insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
commit;

update measure_tbl set id = rownum;
commit;

execute dbms_stats.gather_table_stats(null,'measure_tbl')

This doubling-up code resulted in a total of 6 * 1,024 = 6,144 rows in the table. I only increased the data volume after I’d checked that I had a query that produced the correct results, of course.

A possible solution

By the time I saw the thread on the Oracle forum there were already three solutions on offer, but they all took the route of using analytic functions, including one that used keep(dense_rank …), and these all involved sorting the entire measures dataset; so I thought I’d try an approach that demonstrated a completely different method that was visibly following the strategy: “for each row do two high-precision lookups”. I implemented this by joining two lateral views of the lookup table to the measures table. Since I was sitting in front of a copy of 11.2.0.4 at the time I had to set the event 22829 to enable the feature – here’s the basic code with the plan produced by 11g:

select  /*+ qb_name(main) */
        mt.id,
        mt.measure_val,
        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,
        nt_low.nom_val  low_val,
        nt_low.lkp_id   low_lkp,
        nt_high.nom_val high_val,
        nt_high.lkp_id  high_lkp 
from
        measure_tbl     mt,
        lateral(
                select
                        /*+ qb_name(low) index_rs_desc(nt (nom_val)) */
                        nt.lkp_id, nt.nom_val
                from    nom_val_lkp nt
                where   nt.nom_val <= mt.measure_val
                and     rownum = 1
        )(+) nt_low,
        lateral(
                select
                        /*+ qb_name(high) index_rs_asc(nt (nom_val)) */
                        nt.lkp_id, nt.nom_val
                from    nom_val_lkp nt
                where   nt.nom_val >= mt.measure_val
                and     rownum = 1
        ) (+) nt_high
/

        ID MEASURE_VAL     LKP_ID    LOW_VAL    LOW_LKP   HIGH_VAL   HIGH_LKP
---------- ----------- ---------- ---------- ---------- ---------- ----------
         1         .24          2         .2          2         .3          3
         2          .5          5         .5          5         .5          5
         3         .14          1         .1          1         .2          2
         4         .68          7         .6          6         .7          7
         5        1.38          9         .9          9
         6         .05          1                               .1          1


6 rows selected.

You’ll notice that (for debugging purposes) I’ve included columns in my output for the lkp_id and nom_val just lower than (or matching) and just higher than (or matching) the measure_val. The blanks this produces in two of the rows conveniently highlights the cases where the measure is “out of bounds”.

With my tiny data set I had to include the index_rs_desc() hint. Of course I should really have included an “order by” clause in the two subqueries and used an extra layer of inline views to introduce the rownum = 1 predicate, viz:

        lateral(
                select  * 
                from    (
                        select  /*+ qb_name(low) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        order by
                                nom_val desc
                )
                where   rownum = 1
        )(+) nt_low,

There were two reasons I didn’t do this: first I wanted to keep the code short, secondly it wouldn’t have worked with 11g because it was only in 12c that a correlated subquery could correlate more than one level up – the predicate referencing mt.measure_val would have raised error “ORA-00904: invalid identifier”.

If you’re not familiar with lateral views, the idea is quite simple: as with any inline view in the from clause it’s just a query that returns a result set that looks like a table, but it has the special condition that the predicafes in the query can reference columns from tables (or views) that have appeared further to the left in (or, equivalently, further up) the from clause. In this case both of my inline views query nom_val_lkp and both of them reference a column in measure_tbl which was the first table in the from clause.

There are two distinguishing details that are a consequence of the lateral view. First, the view effectively has a join to the driving table built into it so my main query doesn’t have any where clause predicates joining the views to the rest of the query. Se,condly I want to do outer joins (to deal with the cases where there isn’t a nom_val higher/ lower than the measure_val) so in the absence of a join predicate in the main query the necessary syntax simply adds Oracle’s traditional “(+)” to the lateral() operator itself. (If you want to go “full-ANSI” you would use outer apply() instead of lateral()(+) at this point – but 11g doesn’t support outer apply().

Here’s the execution plan from 11g for this query – I’ve enabled rowsource execution stats and pulled the plan from memory using the ‘allstats last’ format option:

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |      6 |00:00:00.01 |      29 |
|   1 |  NESTED LOOPS OUTER              |              |      1 |      6 |      6 |00:00:00.01 |      29 |
|   2 |   NESTED LOOPS OUTER             |              |      1 |      6 |      6 |00:00:00.01 |      18 |
|   3 |    TABLE ACCESS FULL             | MEASURE_TBL  |      1 |      6 |      6 |00:00:00.01 |       7 |
|   4 |    VIEW                          |              |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  5 |     COUNT STOPKEY                |              |      6 |        |      5 |00:00:00.01 |      11 |
|   6 |      TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP  |      6 |      2 |      5 |00:00:00.01 |      11 |
|*  7 |       INDEX RANGE SCAN DESCENDING| SYS_C0072287 |      6 |      6 |      5 |00:00:00.01 |       6 |
|   8 |   VIEW                           |              |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  9 |    COUNT STOPKEY                 |              |      6 |        |      5 |00:00:00.01 |      11 |
|  10 |     TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|* 11 |      INDEX RANGE SCAN            | SYS_C0072287 |      6 |      4 |      5 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(ROWNUM=1)
   7 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
       filter("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
   9 - filter(ROWNUM=1)
  11 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")


As you can see we’ve done a full tablescan of measure_tbl, then performed an outer join to each of two (unnamed) views for each row, and each time we’ve accessed a view we’ve done an index range scan (descending in one case) into nom_val_lkp. passing in (according to the Predicate Information) the measure_val from measure_tbl.

It’s a little oddity I hadn’t noticed before that the ascending and descending range scans behave slightly differently – the descending range scan says we’ve used the predicate as both an access and a filter predicate. I’ll have to check whether this is always the case or whether it’s version-dependent or whether it’s only true under some conditions.

The only other detail to mention is the expression I’ve used to report the closest match – which is a little messy to allow for “out of range” measures::

        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,

This case expression says that if the higher nom_val is closer to (or, to be precise, not further from) the meause_val than the lower nom_val then report the higher lkp_id. otherwise report the lower lkp_id. The ordering of the comparison means that when the differences are the same the higher value will always be reported; and the “cross-over” use of the nvl() function ensures that when the measure_val is out of range (which means one of the nom_val subqueries will have returned null) we see the nom_val that’s at the end of the range rather than a null.

Some bad news

At first sight the lateral() view looks as if it might be a candidate for scalar subquery caching – so when I create multiple copies of the 6 rows in the measure_tbl and run my query against the expanded data set I might hope to get excellent performance because Oracle might only have to call each lateral view once and and cache the subquery inputs and results from that point onwards. But here are the stats I get from the 11g plan after exanding the data to 6,144 rows:

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |   6144 |00:00:00.82 |   22953 |
|   1 |  NESTED LOOPS OUTER              |              |      1 |   6144 |   6144 |00:00:00.82 |   22953 |
|   2 |   NESTED LOOPS OUTER             |              |      1 |   6144 |   6144 |00:00:00.47 |   11689 |
|   3 |    TABLE ACCESS FULL             | MEASURE_TBL  |      1 |   6144 |   6144 |00:00:00.03 |     425 |
|   4 |    VIEW                          |              |   6144 |      1 |   5120 |00:00:00.28 |   11264 |
|*  5 |     COUNT STOPKEY                |              |   6144 |        |   5120 |00:00:00.20 |   11264 |
|   6 |      TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP  |   6144 |      2 |   5120 |00:00:00.12 |   11264 |
|*  7 |       INDEX RANGE SCAN DESCENDING| SYS_C0072291 |   6144 |      5 |   5120 |00:00:00.04 |    6144 |
|   8 |   VIEW                           |              |   6144 |      1 |   5120 |00:00:00.32 |   11264 |
|*  9 |    COUNT STOPKEY                 |              |   6144 |        |   5120 |00:00:00.19 |   11264 |
|  10 |     TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP  |   6144 |      2 |   5120 |00:00:00.11 |   11264 |
|* 11 |      INDEX RANGE SCAN            | SYS_C0072291 |   6144 |      3 |   5120 |00:00:00.04 |    6144 |
-----------------------------------------------------------------------------------------------------------


Look at the Starts column: the two views were called once each for every single row in the expanded measure_tbl, there’s no scalar subquery caching going on.

Bug time (1)

Of course, this is 11g and I’ve enabled lateral views by setting an event; it’s not an officially supported feature so maybe if I upgrade to 12c (or 19c), where the feature is official, Oracle will do better.

Here are the results of the original query against the original data set in 12c and 19c:

        ID MEASURE_VAL     LKP_ID    LOW_VAL    LOW_LKP   HIGH_VAL   HIGH_LKP
---------- ----------- ---------- ---------- ---------- ---------- ----------
         6         .05          1                               .1          1
         3         .14          1         .1          1
         1         .24          1         .1          1
         2          .5          1         .1          1
         4         .68          1         .1          1
         5        1.38          1         .1          1

On the upgrade I’ve got the wrong results! So what does the execution plan look like:

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |      1 |        |      6 |00:00:00.01 |      17 |       |       |          |
|   1 |  MERGE JOIN OUTER       |                 |      1 |      6 |      6 |00:00:00.01 |      17 |       |       |          |
|   2 |   SORT JOIN             |                 |      1 |      6 |      6 |00:00:00.01 |      12 |  2048 |  2048 | 2048  (0)|
|   3 |    MERGE JOIN OUTER     |                 |      1 |      6 |      6 |00:00:00.01 |      12 |       |       |          |
|   4 |     SORT JOIN           |                 |      1 |      6 |      6 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   5 |      TABLE ACCESS FULL  | MEASURE_TBL     |      1 |      6 |      6 |00:00:00.01 |       7 |       |       |          |
|*  6 |     SORT JOIN           |                 |      6 |      1 |      5 |00:00:00.01 |       5 |  2048 |  2048 | 2048  (0)|
|   7 |      VIEW               | VW_DCL_A18161FF |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|*  8 |       COUNT STOPKEY     |                 |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|   9 |        TABLE ACCESS FULL| NOM_VAL_LKP     |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|* 10 |   SORT JOIN             |                 |      6 |      1 |      1 |00:00:00.01 |       5 |  2048 |  2048 | 2048  (0)|
|  11 |    VIEW                 | VW_DCL_A18161FF |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|* 12 |     COUNT STOPKEY       |                 |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|  13 |      TABLE ACCESS FULL  | NOM_VAL_LKP     |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(INTERNAL_FUNCTION("NOM_VAL")<=INTERNAL_FUNCTION("MT"."MEASURE_VAL"))
       filter(INTERNAL_FUNCTION("NOM_VAL")<=INTERNAL_FUNCTION("MT"."MEASURE_VAL"))
   8 - filter(ROWNUM=1)
  10 - access("NOM_VAL">="MT"."MEASURE_VAL")
       filter("NOM_VAL">="MT"."MEASURE_VAL")
  12 - filter(ROWNUM=1)


Check what’s appeared in the Name for the view operations 7 and 11: VW_DCL_ A18161FF (DCL = “decorrelate”), I was expecting to see names starting with VW_LAT (LAT = “lateral”). And then I remembered reading this article by Sayan Malakshinov – Oracle (12c+) can decorrelate lateral views but gets the wrong results with rownum. So let’s add in a few hints to avoid decorrelation and check the results and execution plan.

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |      1 |        |      6 |00:00:00.01 |      30 |       |       |          |
|   1 |  MERGE JOIN OUTER                        |                 |      1 |      6 |      6 |00:00:00.01 |      30 |       |       |          |
|   2 |   MERGE JOIN OUTER                       |                 |      1 |      6 |      6 |00:00:00.01 |      19 |       |       |          |
|   3 |    TABLE ACCESS FULL                     | MEASURE_TBL     |      1 |      6 |      6 |00:00:00.01 |       8 |       |       |          |
|   4 |    BUFFER SORT                           |                 |      6 |      1 |      5 |00:00:00.01 |      11 |  2048 |  2048 | 2048  (0)|
|   5 |     VIEW                                 | VW_LAT_D77DA787 |      6 |      1 |      5 |00:00:00.01 |      11 |       |       |          |
|*  6 |      COUNT STOPKEY                       |                 |      6 |        |      5 |00:00:00.01 |      11 |       |       |          |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| NOM_VAL_LKP     |      6 |      2 |      5 |00:00:00.01 |      11 |       |       |          |
|*  8 |        INDEX RANGE SCAN                  | SYS_C0055681    |      6 |      3 |      5 |00:00:00.01 |       6 |       |       |          |
|   9 |   BUFFER SORT                            |                 |      6 |      1 |      5 |00:00:00.01 |      11 |  2048 |  2048 | 2048  (0)|
|  10 |    VIEW                                  | VW_LAT_D77DA787 |      6 |      1 |      5 |00:00:00.01 |      11 |       |       |          |
|* 11 |     COUNT STOPKEY                        |                 |      6 |        |      5 |00:00:00.01 |      11 |       |       |          |
|  12 |      TABLE ACCESS BY INDEX ROWID BATCHED | NOM_VAL_LKP     |      6 |      2 |      5 |00:00:00.01 |      11 |       |       |          |
|* 13 |       INDEX RANGE SCAN DESCENDING        | SYS_C0055681    |      6 |      5 |      5 |00:00:00.01 |       6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(ROWNUM=1)
   8 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")
  11 - filter(ROWNUM=1)
  13 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
       filter("NT"."NOM_VAL"<="MT"."MEASURE_VAL")

Blocking decorrelation was sufficient to get the correct result but there’s still a funny little glitch in the execution plan: why do we have merge join (outer) for operations 1 and 2?

It’s not quite the threat you might think; we’re not multiplying up rows catastrophically. For each row in measures_tbl Oracle does a Cartesian merge join to (at most) one row in each view – so there’s no accidental explosion in data volume, and there’s no real sorting. Nevertheless there may be unnecessary CPU usage so let’s add a few more hints to try and get a nested loop by adding the following hints to the start of the query:

        /*+
                qb_name(main)
                leading(@main mt@main nt_high@main nt_low@main)
                use_nl(@main nt_high@main)
                use_nl(@main nt_low@main)
        */

I was a little surprised at the benefit – roughly a 30% saving on CPU for the same data set.

But there’s more to investigate – I didn’t like the index hints that I’d had to use in 11g, but 12c allows for the more complex “two layer” lateral subquery with its deeply correlated predicate – so what happens if I use the following corrected query (with minimal hinting) in 12c or 19c:

select
        /*+
                qb_name(main)
--              leading(@main mt@main nt_high@main nt_low@main)
--              use_nl(@main nt_high@main)
--              use_nl(@main nt_low@main)
        */
        mt.id,
        mt.measure_val,
        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,
        nt_low.nom_val  low_val,
        nt_low.lkp_id   low_lkp,
        nt_high.nom_val high_val,
        nt_high.lkp_id  high_lkp 
from
        measure_tbl     mt,
        lateral(
                select  *
                from    (
                        select  /*+ qb_name(low) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        order by
                                nom_val desc
                        )
                where   rownum = 1
        )(+) nt_low,
        lateral(
                select  *
                from    (
                        select  /*+ qb_name(high) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        order by
                                nom_val
                )
                where   rownum = 1
        )(+) nt_high
/

First – Oracle doesn’t use decorrelation so I get the right results; secondly Oracle uses the correct index descending without hinting, which is an important part of getting the right results. Unfortunately I still see merge joins unless I include the use_nl() hints (with the leading() hint as an extra safety barrier) to get that 30% reduction in CPU usage.

The sad news is that I still don’t see scalar subquery caching. If I have 6,144 rows in measure_tbl I still see 6,144 executions of both the lateral subqueries.

Since 12c onwards supports “outer apply” it’s worth testing to see what happens if I replace my lateral()(+) operator with the outer apply() mechanism. It works, but behaves very much like the lateral approach (including the unexpected merge joins unless hinted), except it introduces another layer of lateral joins. Here’s the plan (12c and 19c) with 6,144 rows:

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |   6144 |00:00:00.14 |   22954 |       |       |          |
|   1 |  MERGE JOIN OUTER                   |                 |      1 |   6144 |   6144 |00:00:00.14 |   22954 |       |       |          |
|   2 |   MERGE JOIN OUTER                  |                 |      1 |   6144 |   6144 |00:00:00.08 |   11690 |       |       |          |
|   3 |    TABLE ACCESS FULL                | MEASURE_TBL     |      1 |   6144 |   6144 |00:00:00.01 |     426 |       |       |          |
|   4 |    BUFFER SORT                      |                 |   6144 |      1 |   5120 |00:00:00.06 |   11264 |  2048 |  2048 | 2048  (0)|
|   5 |     VIEW                            | VW_LAT_F8C248CF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|   6 |      VIEW                           | VW_LAT_A18161FF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|*  7 |       COUNT STOPKEY                 |                 |   6144 |        |   5120 |00:00:00.03 |   11264 |       |       |          |
|   8 |        VIEW                         |                 |   6144 |      2 |   5120 |00:00:00.03 |   11264 |       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP     |   6144 |      6 |   5120 |00:00:00.02 |   11264 |       |       |          |
|* 10 |          INDEX RANGE SCAN DESCENDING| SYS_C0023500    |   6144 |      2 |   5120 |00:00:00.01 |    6144 |       |       |          |
|  11 |   BUFFER SORT                       |                 |   6144 |      1 |   5120 |00:00:00.06 |   11264 |  2048 |  2048 | 2048  (0)|
|  12 |    VIEW                             | VW_LAT_F8C248CF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|  13 |     VIEW                            | VW_LAT_E88661A9 |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|* 14 |      COUNT STOPKEY                  |                 |   6144 |        |   5120 |00:00:00.03 |   11264 |       |       |          |
|  15 |       VIEW                          |                 |   6144 |      1 |   5120 |00:00:00.02 |   11264 |       |       |          |
|  16 |        TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP     |   6144 |      1 |   5120 |00:00:00.02 |   11264 |       |       |          |
|* 17 |         INDEX RANGE SCAN            | SYS_C0023500    |   6144 |      4 |   5120 |00:00:00.01 |    6144 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter(ROWNUM=1)
  10 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
  14 - filter(ROWNUM=1)
  17 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")

Note operations 5 and 6, then 12 and 13: the “ANSI” syntax outer apply seems to be another case of Oracle doing more work because it has to transform the query before optimising.

A Traditional Solution

Having worked through a few of the newer mechanisms in Oracle, why not think back to how the same pattern of implementation could have been achieved in older versions of Oracle. What’s wrong, for example, with using scalar subqueries in the select list? If we can expect plenty of scalar subquery caching this might be a very effective way of writing the query.

The immediate problem, though, is that scalar subqueries in the select list only allow one column to be returned (unless you want to fake things through by playing nasty games with user-defined types). So our two lateral views will have to change to four scalar subqueres to get all the data we need.

Here’s a possible solution (I’ve stuck with the hinted shorter, but bad practice, “first row” mechanism for compactness) – with execution stats:

select
        id,
        measure_val,
        case
                when
                        nt_high_nom_val - measure_val <=
                        measure_val - nt_low_nom_val
                then    nvl(nt_high_lkp_id,nt_low_lkp_id)
                else    nvl(nt_low_lkp_id,nt_high_lkp_id)
        end     lkp_id,
        nt_low_nom_val,
        nt_low_lkp_id,
        nt_high_nom_val,
        nt_high_lkp_id
from    (
        select
                mt.id,
                mt.measure_val,
                (
                        select
                                /*+ index_rs_asc(nt (nom_val)) */
                                nt.lkp_id
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        and     rownum = 1
                ) nt_high_lkp_id,
                (
                        select
                                /*+ index_rs_asc(nt (nom_val)) */
                                nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        and     rownum = 1
                ) nt_high_nom_val,
                (
                        select
                                /*+ index_rs_desc(nt (nom_val)) */
                                nt.lkp_id
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        and     rownum = 1
                ) nt_low_lkp_id,
                (
                        select
                                /*+ index_rs_desc(nt (nom_val)) */
                                nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        and     rownum = 1
                ) nt_low_nom_val
        from
                measure_tbl     mt
        )
/

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        |   6144 |00:00:00.01 |     426 |
|*  1 |  COUNT STOPKEY                          |              |      6 |        |      5 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN                      | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  3 |   COUNT STOPKEY                         |              |      6 |        |      5 |00:00:00.01 |       6 |
|*  4 |    INDEX RANGE SCAN DESCENDING          | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  5 |    COUNT STOPKEY                        |              |      6 |        |      5 |00:00:00.01 |      11 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED | NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  7 |      INDEX RANGE SCAN                   | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  8 |     COUNT STOPKEY                       |              |      6 |        |      5 |00:00:00.01 |      11 |
|   9 |      TABLE ACCESS BY INDEX ROWID BATCHED| NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|* 10 |       INDEX RANGE SCAN DESCENDING       | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|  11 |  TABLE ACCESS FULL                      | MEASURE_TBL  |      1 |   6144 |   6144 |00:00:00.01 |     426 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - access("NT"."NOM_VAL">=:B1)
   3 - filter(ROWNUM=1)
   4 - access("NT"."NOM_VAL"<=:B1)
       filter("NT"."NOM_VAL"<=:B1)
   5 - filter(ROWNUM=1)
   7 - access("NT"."NOM_VAL">=:B1)
   8 - filter(ROWNUM=1)
  10 - access("NT"."NOM_VAL"<=:B1)
       filter("NT"."NOM_VAL"<=:B1)

I’ve left the index hints in place in this example so that the code can run on 11g and earlier (without setting any special events, of course); but in 12c and 19c if you replace the subqueries with the double-layer subqueries (inline order by, then rownum = 1) as shown further up the page the hints (specifically the descending hints) are no longer necessary.

The key performance benefit of this approach is visible in the Starts column – although I now have 4 subqueries to run (which should mean doing more work) each one runs only once thanks to an extremely “lucky” level of scalar subquery caching.

This, really, is where this note takes us back to the beginning. Will this be a fantastic solution for the end-user, or does the pattern of the data mean that it’s going to be a total disaster. It’s nice to see the SQL that defines the tables and supplies a bit of test data – but there’s not point in trying to provide a solution without a better idea of what the data really looks like and what the critical usage is in production.

Bug time (2)

Nothing’s perfect, of course – and even though this last SQL statement is pretty simple and its execution plan is (for the right data pattern) very efficient, the shape of the plan is wrong – and in more complex plans you could be fooled into thinking that Oracle isn’t doing what you want it do.

Operations 1,3,5,8 and 11 should all be at the same depth (you’ll find that they all have parent_id = 0 if you look at the underlying data in v$sql_plan): there’s a defect in Oracle’s calculation of the depth column of v$sql_plan (et. al.) that introduces a pattern of indentation that shouldn’t be there.

Summary

This has been a fairly informal ramble through the playing around that I did after I read the original post. It holds some comments about the way the question was asked, the test data as supplied and corrected, and the observations and tweaks as the testing progressed.

On the plus size, the OP has supplied code to create and populate a model, and described what they wanted to see as a result. However the requirement didn’t mention (and the model therefore didn’t cater for) a couple of special cases. There were also a few cases where unique and mandatory columns were likely to be appropriate but were not mentioned, even though they could affect the correctness or performance of any suggested solutions.

More importantly, although the model implied some fairly narrow restrictions on what the production data might look like this information wasn’t presented explcitily, and there were no comments about the ultimate scale and distribution patterns of the data that might give some clues about the most appropriate features of SQL to use.

March 25, 2021

v$resource_limit

Filed under: Oracle,Performance,Troubleshooting — Jonathan Lewis @ 2:59 pm GMT Mar 25,2021

From time to time I see people on the public Oracle forums asking whether they should adjust one or other of the resource-related parameters – and it’s often the hidden or derived parameters that get targetted for this type of request. For example I came across a request fairly recently that said:

I’ve got a problem with the CF enqueue, I see lots of time spent on waits for this enqueue, should I increase the parameter _enqueue_locks?

Quick tip: if you have to ask a question like this the answer is almost certainly “no”. On the other hand if you can present a rational argument why an observation might point you to a parameter and an explanation of why you think the change might help there’s a simple check that you could do (for some parameters) before you ask the question. There’s a dynamic performance view that lists the utilitisation of a number of the special “resource” parameters and lets you see very easily whether you’re reaching the limit – it’s the view named in the title of this piece: v$resource_limit.

Here’s a simple script to report the contents of the view. It’s a script that has to be run by SYS (unless you care to grant suitable privileges to a non-SYS user), and in a container database it has to be executed in CDB$ROOT.

rem
rem     Script:         resource_limit.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Feb 2007
rem

set linesize 180
set pagesize 60
set trimspool on

column resource_name            format a32
column max_utilization          format 999,999
column current_utilization      format 999,999
column initial_allocation       format a18
column limit_value              format a11

spool resource_limit.lst

select
        resource_name,
        max_utilization,
        current_utilization,
        lpad(initial_allocation,18)     initial_allocation,
        lpad(limit_value,11)            limit_value
from
        v$resource_limit
;

spool off

The following is a sample output from a small 19.3 instances shortly after startup. In this case you can see that the RAC (ges) resources all show zero utilisation, and most of the others are fairly low.

RESOURCE_NAME                    MAX_UTILIZATION CURRENT_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------------------- --------------- ------------------- ------------------ -----------
processes                                     92                  85                360         360
sessions                                     109                  83                564         564
enqueue_locks                                 94                  39               6644        6644
enqueue_resources                             68                  31               2700   UNLIMITED
ges_procs                                      0                   0                  0           0
ges_ress                                       0                   0                  0   UNLIMITED
ges_locks                                      0                   0                  0   UNLIMITED
ges_cache_ress                                 0                   0                  0   UNLIMITED
ges_reg_msgs                                   0                   0                  0   UNLIMITED
ges_big_msgs                                   0                   0                  0   UNLIMITED
ges_rsv_msgs                                   0                   0                  0           0
gcs_resources                                  0                   0                  0   UNLIMITED
gcs_shadows                                    0                   0                  0   UNLIMITED
smartio_overhead_memory                  171,032                   0                  0   UNLIMITED
smartio_buffer_memory                          0                   0                  0   UNLIMITED
smartio_metadata_memory                        0                   0                  0   UNLIMITED
smartio_sessions                               1                   0                  0   UNLIMITED
dml_locks                                     29                   0               2480   UNLIMITED
temporary_table_locks                          5                   0          UNLIMITED   UNLIMITED
transactions                                   6                   0                620   UNLIMITED
branches                                       0                   0                620   UNLIMITED
cmtcallbk                                      3                   0                620   UNLIMITED
max_rollback_segments                         22                  22                620       65535
sort_segment_locks                             9                   3          UNLIMITED   UNLIMITED
k2q_locks                                      0                   0               1128   UNLIMITED
max_shared_servers                             2                   1          UNLIMITED   UNLIMITED
parallel_max_servers                          26                  26                 32       32767


I’ve listed the columns in an order that doesn’t match the view definition because the thing we’re likely to be interested in is the maximum utilisation we’ve seen since instance startup – so that’s the one I’ve reported first. After that I’ve reported the current utilisation, and then the initial allocation (the value that, in many cases, is set by one of the startup parameters), followed by the limiting value for the resource.

In some cases the initial allocation is the same as the limiting value – processes and sessions (mapping to v$process/x$ksupr and v$session/x$ksuse) are good examples of this – these are fixed arrays defined when the instance starts – but in some cases the initial allocation is only a “reasonable” starting guess which allows Oracle to extend on demand, often through segmented arrays of 16 entries at a time, and in some cases there is no final limit to what resources you’re allowed (until the instance crashes with an ORA-04030 error, of course).

So, for example, to answer the question posed at the start of this note – should you increase the hidden parameter _enqueue_locks ? Certainly not for this instance because we can see the initial allocation is 6,644 and we’ve only reached a maximum of 94 enqueue locks allocated simultaneously.

AWR / Statspack

As with most of the dynamic performance views, there’s a summary report of v$resource_limit in the AWR (or Statspack) reports. The type of output you get is as follows;

Resource Limit Stats                            DB/Inst: OR19/or19  Snap: 4576
-> Only rows with Current or Maximum Utilization > 80% of Limit are shown
-> For "UNLIMITED" resources, rows whose Current or Maximum Utilization
   exceeds 2*Initial Allocation are shown
-> Ordered by Resource Name

                                  Current      Maximum     Initial
Resource Name                   Utilization  Utilization Allocation   Limit
------------------------------ ------------ ------------ ---------- ----------
smartio_overhead_memory                   0      171,032          0  UNLIMITED
smartio_sessions                          0            1          0  UNLIMITED
                          ------------------------------------------------------

As you can see the output is strictly limited by an 80% “stress” condition – but there’s more data available if you query the dba_hist_resource_limit view (or wrh$_resource_limit table, or Statspack equivalents) directly. For example:

select
        resource_name,
        max_utilization,
        current_utilization,
        lpad(initial_allocation,18)     initial_allocation,
        lpad(limit_value,11)            limit_value
from
        wrh$_resource_limit
where
        snap_id         =  4849
and     instance_number =  1
and     dbid            =  3091945231
and     max_utilization != 0
;


RESOURCE_NAME                    MAX_UTILIZATION CURRENT_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------------------- --------------- ------------------- ------------------ -----------
cmtcallbk                                      2                   0                620   UNLIMITED
dml_locks                                     28                  19               2480   UNLIMITED
enqueue_locks                                 68                  41               6644        6644
enqueue_resources                             62                  55               2700   UNLIMITED
max_rollback_segments                         22                  22                620       65535
parallel_max_servers                          12                   8                 32       32767
processes                                     99                  69                360         360
sessions                                     123                  87                564         564
transactions                                   4                   3                620   UNLIMITED

I’ve limited the history query to rows with non-zero utilization, which is why it’s showing so few rows, but It’s an interesting oddity that the dba_hist_resource_limit view (and the underlying table) does actually hold fewer statistics than the initial dynamic performance view. In a 12.1.0.2 (RAC) instance I found that 3 of the dynamic statistics had not been captured in the history, in 19.3 this had gone up to 5 missing statistics, namely:

Not captured in 12.1.0.2 (RAC) history 

max_shared_servers
sort_segment_locks
temporary_table_locks

Further items not captured in 19.3.0.0 
ges_procs
ges_rsv_msgs

It’s possible, of course, that the two extra stats from 19.3 would be captured in a RAC system.

Finally you might want to run a query through history if you needed to see if there was a pattern to some unexpected change in resource utilisation, so a query running through time might be useful, e.g.:

select  * 
from    (
        select 
                ss.end_interval_time,
                res.resource_name, res.max_utilization, res.current_utilization 
        from 
                dba_hist_resource_limit res,
                dba_hist_snapshot       ss
        where 
                ss.end_interval_time between to_date('18-Mar-2021 10:00','dd-mon-yyyy hh24:mi')
                                and     to_date('18-Mar-2021 22:30','dd-mon-yyyy hh24:mi')
        and     res.snap_id = ss.snap_id
        and     res.resource_name in ('sessions','processes','transactions')
        )       piv
        pivot   (
                        avg(max_utilization)     as max,
                        avg(current_utilization) as cur
                for     resource_name in (
                                'sessions'      as sess,
                                'processes'     as proc,
                                'transactions'  as trns
                        )
                )
order by
        end_interval_time
/


END_INTERVAL_TIME          SESS_MAX   SESS_CUR   PROC_MAX   PROC_CUR   TRNS_MAX   TRNS_CUR
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
18-MAR-21 10.00.46.698          123         84         99         66          4          3
18-MAR-21 11.00.59.610          123         84         99         67          4          2
18-MAR-21 12.00.13.015          123         82         99         66          4          2
18-MAR-21 13.00.27.443          123         82         99         66          4          1
18-MAR-21 14.00.40.316          123         84         99         66          4          2
18-MAR-21 15.00.51.705          123         80         99         64          4          1
18-MAR-21 16.00.57.293          123         84         99         66          4          1
18-MAR-21 17.00.03.197          123         80         99         65          4          2
18-MAR-21 18.00.09.448          123         81         99         65          4          2
18-MAR-21 19.00.16.419          123         82         99         66          4          2
18-MAR-21 20.00.22.669          123         81         99         65          4          2
18-MAR-21 21.00.31.215          123         83         99         66          4          1
18-MAR-21 22.00.43.615          123         86         99         68          4          2

In my particular case there’s absolutely nothing interesting to see, but the sort of thing you might spot is a steady growth in the maximum number of sessions over a couple of hours one day, then a subsequent repeated decrease and increase (to that max) in the current number of sessions from then on. It’s always a little difficult when you have a statistic that is “maximum since startup” so you would have to be a little careful in interpreting the results of a query like this.

 

March 12, 2021

Distributed Sequences

Filed under: distributed,Execution plans,Oracle,Performance,Problem Solving — Jonathan Lewis @ 9:09 am GMT Mar 12,2021

A request for help came up some time ago on ODC reporting a query that was hanging when it included a sequence.nextval. In fact the intial “query” was an “insert as select” with a select that was a join of two remote tables.

Making the fairly automatic assumption that many people say “hanging” when they really mean “hasn’t finished yet” the first thought I had about the structure of the statement was that it was just a variation of the standard problem of distributed DML. I haven’t written anything previously about how using sequences can introduce the problem so here’s a note to demonstrate the issue and suggest a workaround:

We start with a database link and a few tables:


rem
rem     Script:         distributed_sequence.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2019
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem             18.3.0.0
rem             12.2.0.1
rem

rem     create public database link orcl@loopback using 'orcl';

define m_target=orcl@loopback

create sequence s1 cache 10000;
select s1.nextval from dual;

create table t1
segment creation immediate
nologging
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4    -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        rownum                          n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1
;

create table t2
nologging
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4    -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        rownum                          n2,
        lpad(rownum,10,'0')             v2,
        lpad('x',100,'x')               padding
from
        generator       v1
;

create table t3(
        id1     number(6,0),
        id2     number(6,0),
        n0      number(6,0),
        n1      number(6,0),
        n2      number(6,0),
        v1      varchar2(10),
        v2      varchar2(10)
)
segment creation immediate
;

create or replace view v1 as
select
        t1.id id1,
        t2.id id2,
        t1.n1,
        t2.n2,
        t1.v1,
        t2.v2
from
        t1, t2
where
        t2.id = t1.id
;

The command to create a public database link (one example of the many optional commands in my original source) has to be run by a suitably privileged schema as a one-off event. The definition of the substitution variable m_target that I’ll be using as my database link (again with many possibilities in my original script) means I only have to edit my choice of database link once in my script as I change Oracle instances. You’ll notice I’ve done my usual trick of using a loopback database link to emulate a distributed system.

I’ve then created two populated tables (t1, t2) and a third empty table (t3) that will be the target of an insert. I’ve also created a view (v1) that joins the first two tables and a sequence (s1) that I’ve primed with a single call to nextval. When I get to the tests I’ll be using t1, t2 and v1 as if they had been created in the remote database (referenced through my loopback database link) while t3 and s1 will be local objects.

So let’s run a couple of statements and see what their execution plans look like:


set serveroutput off

prompt  =======================
prompt  Insert without sequence
prompt  =======================

insert into t3 (id1, id2, n0, n1, n2, v1, v2)
select
        t1.id,
        t2.id,
        0,
        t1.n1,
        t2.n2,
        t1.v1,
        t2.v2
from
        t1@&m_target    t1,
        t2@&m_target    t2
where
        t2.id = t1.id
;

select * from table(dbms_xplan.display_cursor(format=>'-plan_hash'));

prompt  =======================
prompt  Insert WITH sequence
prompt  =======================

insert into t3 (id1, id2, n0, n1, n2, v1, v2)
select
        t1.id,
        t2.id,
        s1.nextval,
        t1.n1,
        t2.n2,
        t1.v1,
        t2.v2
from
        t1@&m_target    t1,
        t2@&m_target    t2
where
        t2.id = t1.id
;

select * from table(dbms_xplan.display_cursor(format=>'-plan_hash'));
commit;

The code joins t1 and t2, selects a couple of columns and inserts then into t3 without or with a call to s1.nextval (the local sequence). Here’s the output (with minor cosmetic changes) from executing this code under 19.3.0.0 (the effects are the same in 18.3.0.0 and 12.2.0.1):


=======================
Insert without sequence
=======================

10000 rows created.


SQL_ID  373nz810u3frv, child number 0
-------------------------------------
insert into t3 (id1, id2, n0, n1, n2, v1, v2) select  t1.id,  t2.id,
0,  t1.n1,  t2.n2,  t1.v1,  t2.v2 from  t1@orclpdb@loopback t1,
t2@orclpdb@loopback t2 where  t2.id = t1.id

-----------------------------------------------------------------
| Id  | Operation                | Name | Cost  | Inst   |IN-OUT|
-----------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |       |        |      |
|   2 |   REMOTE                 |      |       | ORCLP~ | R->S |
-----------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------
   2 - SELECT "A2"."ID","A1"."ID",0,"A2"."N1","A1"."N2","A2"."V1","A1"."
       V2" FROM "T1" "A2","T2" "A1" WHERE "A1"."ID"="A2"."ID" (accessing
       'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

Note
-----
   - cpu costing is off (consider enabling it)


=======================
Insert WITH sequence
=======================

10000 rows created.


SQL_ID  8jg23arujnh01, child number 0
-------------------------------------
insert into t3 (id1, id2, n0, n1, n2, v1, v2) select  t1.id,  t2.id,
s1.nextval,  t1.n1,  t2.n2,  t1.v1,  t2.v2 from  t1@orclpdb@loopback
t1,  t2@orclpdb@loopback t2 where  t2.id = t1.id

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       |       |    54 (100)|          |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |       |       |            |          |        |      |
|   2 |   SEQUENCE               | S1   |       |       |            |          |        |      |
|*  3 |    HASH JOIN             |      | 10000 |   937K|    54   (8)| 00:00:01 |        |      |
|   4 |     REMOTE               | T1   | 10000 |   468K|    26   (4)| 00:00:01 | ORCLP~ | R->S |
|   5 |     REMOTE               | T2   | 10000 |   468K|    26   (4)| 00:00:01 | ORCLP~ | R->S |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T2"."ID"="T1"."ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - SELECT /*+ OPAQUE_TRANSFORM */ "ID","N1","V1" FROM "T1" "A2" (accessing
       'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

   5 - SELECT /*+ OPAQUE_TRANSFORM */ "ID","N2","V2" FROM "T2" "A1" (accessing
       'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

The key thing to notice is that when we want to insert the local sequence as a column in the select list Oracle breaks the hash join into two separate accesses to the remote database and pulls all the data we might need from the two tables before trying to join them locally. When the query is “fully remote” the local database can let the remote database deal with the join, when the query is distributed – which is a side effect of introducing the sequence – the local site becomes the driving site and has to work out the least worst way of handling the join, which might be much slower than the remote join.  (It’s an odd little quirk that when the select is fully remote the optimizer thinks that it’s not using CPU costing. Possibly that’s because all the arithmetic happens somewhere else and the local cost of the query never gets above zero.)

This is one of those cases where we might work around the problem by creating a remote view to handle the join – hence the creation of the view v1; here’s a suitable statement and the resulting execution plan:

prompt  ==============================
prompt  Insert using view and sequence
prompt  ==============================

set serveroutput off

insert into t3 (id1, id2, n0, n1, n2, v1, v2)
select
        v1.id1,
        v1.id2,
        s1.nextval,
        v1.n1,
        v1.n2,
        v1.v1,
        v1.v2
from
        v1@&m_target v1
;

select * from table(dbms_xplan.display_cursor(format=>'-plan_hash'));
commit;



==============================
Insert using view and sequence
==============================

10000 rows created.


SQL_ID  4tz0rrqt87nb8, child number 0
-------------------------------------
insert into t3 (id1, id2, n0, n1, n2, v1, v2) select  v1.id1,  v1.id2,
s1.nextval,  v1.n1,  v1.n2,  v1.v1,  v1.v2 from  v1@orclpdb@loopback v1

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       |       |    27 (100)|          |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |       |       |            |          |        |      |
|   2 |   SEQUENCE               | S1   |       |       |            |          |        |      |
|   3 |    REMOTE                | V1   | 10000 |   937K|    27   (8)| 00:00:01 | ORCLP~ | R->S |
-------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT /*+ OPAQUE_TRANSFORM */ "ID1","ID2","N1","N2","V1","V2" FROM "V1" "V1"
       (accessing 'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

As you can see, the local optimizer doesn’t know enough about the remote view to be able to split it into components and make a mess of the execution plan, it simply sees a query against a “single table” and leaves the remote database to worry about optimising it. But, of course, we don’t always have the luxury of being able to create objects on someone else’s database, so what’s the alternative?

Try rewriting the query to use an inline view with the /*+ no_merge() */ hint:


prompt  =======================================
prompt  Insert from a no-merge inline view with
prompt  a sequence.nextval in the outer query
prompt  ========================================

set serveroutput off

insert into t3 (id1, id2, n0, n1, n2, v1, v2)
select
        id1, id2, s1.nextval, n1, n2, v1, v2
from    (
        select  /*+ no_merge */
                t1.id   id1,    
                t2.id   id2,
                0       n0,
                t1.n1,
                t2.n2,
                t1.v1,
                t2.v2
        from
                t1@&m_target    t1,
                t2@&m_target    t2
        where
                t2.id = t1.id
        )       v1
;

select * from table(dbms_xplan.display_cursor(format=>'-plan_hash'));
commit;


=======================================
Insert from a no-merge inline view with
a sequence.nextval in the outer query
========================================

10000 rows created.


SQL_ID  20z81g550tbsk, child number 0
-------------------------------------
insert into t3 (id1, id2, n0, n1, n2, v1, v2) select  id1, id2,
s1.nextval, n1, n2, v1, v2 from (  select /*+ no_merge */   t1.id id1,
 t2.id id2,   0 n0,   t1.n1,   t2.n2,   t1.v1,   t2.v2  from
t1@orclpdb@loopback t1,   t2@orclpdb@loopback t2  where   t2.id = t1.id
 ) v1

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       |       |    54 (100)|          |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |       |       |            |          |        |      |
|   2 |   SEQUENCE               | S1   |       |       |            |          |        |      |
|   3 |    VIEW                  |      | 10000 |   937K|    54   (8)| 00:00:01 |        |      |
|   4 |     REMOTE               |      |       |       |            |          | ORCLP~ | R->S |
-------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - SELECT /*+ NO_MERGE */ "A2"."ID","A1"."ID",0,"A2"."N1","A1"."N2","A2"."V1","A1"."V
       2" FROM "T1" "A2","T2" "A1" WHERE "A1"."ID"="A2"."ID" (accessing
       'ORCLPDB.LOCALDOMAIN@LOOPBACK' )


The hint has done exactly what we needed: it has passed the text of the inline view to the remote database for optimisation so the join happens remotely, and the sequence number is then included after the result set comes back from the remote database. The SQL is a little messier, of course, mostly thanks to the doubled appearance of the columns in the select list.

Summary

Using a local sequence in DML that accesses a remote database makes the optimizer treats the underlying query as a distributed query, and this may mean it can’t find an efficient execution path unless you do some re-engineering of the code. If you can manage to make an insert with a constant efficient then using that version of the code as an in-line no_merge view with one extra layer that brings the sequence into play may be all you need to do to make the DML operate efficiently.

March 8, 2021

Join Elimination redux

Filed under: Bugs,CBO,Join Elimination,Oracle,Transformations — Jonathan Lewis @ 12:58 pm GMT Mar 8,2021

This note is a followup to a post from a few years back (originally dating further back to 2012) where I described an inconsistency that appeared when join elimination and deferrable constraints collided. The bug resurfacted recently in a new guise in a question on the Oracle Developer forum with a wonderful variation on the symptons that ultimately gave a good clue to underlying issue. The post included a complete working example of the anomaly, but I’ll demonstrate it using a variation of my 2012/2017 code. We start with a pair of tables with referential integrity defined between them:

rem
rem     Script:         join_eliminate_bug_3.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Feb 2021
rem
rem     Last tested 
rem             19.11.0.0
rem             19.8.0.0 (LiveSQL)
rem

create table parent (
        id      number(4),
        name    varchar2(10),
        constraint par_pk primary key (id)
        deferrable initially immediate
)
;

create table child(
        id_p    number(4)       
                constraint chi_fk_par
                references parent,
        id      number(4),
        name    varchar2(10),
        constraint chi_pk primary key (id_p, id) 
)
;

insert into parent values (1,'Smith');

insert into child values(1,1,'Simon');
insert into child values(1,2,'Sally');

commit;

begin
        dbms_stats.gather_table_stats(user,'child');
        dbms_stats.gather_table_stats(user,'parent');
end;
/

You’ll notice that I’ve created the primary key constraint on parent as “deferrable initially immediate”. So let’s write some code that defers the constraint, inserts some duplicate data executes a join between the two tables:

set serveroutput off
set constraint par_pk deferred;

insert into parent (id,name) values (1,'Smith');

select
        /*+ initially immediate  PK */
        chi.*
from
        child   chi,
        parent  par
where
        par.id = chi.id_p
;

select * from table(dbms_xplan.display_cursor);

Since I’ve now got two rows with id = 1 in parent the query ought to return duplicates for every row in child where id_p = 1, but it doesn’t. Here’s the output from the query and the execution plan:

     ID_P         ID NAME
---------- ---------- ------------
         1          1 Simon
         1          2 Sally

2 rows selected.


PLAN_TABLE_OUTPUT
--------------------------------------------------
SQL_ID  gy6h8td4tmdpg, child number 0
-------------------------------------
select  /*+ initially immediate  PK */  chi.* from  child chi,  parent
par where  par.id = chi.id_p

Plan hash value: 2406669797

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| CHILD |     2 |    24 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

The optimizer has applied “join elimination” to take parent out of the transformed query, so no duplicates. Arguably this is the wrong result.

Let’s roll back the insert and change the experiment – let’s change the constraint on the parent primary key so that it’s still deferrable, but initially deferred then repeat the insert and query:

rollback;
alter table child drop constraint chi_fk_par;
alter table parent drop constraint par_pk;

alter table parent add constraint par_pk primary key (id) deferrable initially deferred;
alter table child add constraint chi_fk_par foreign key(id_p) references parent;

insert into parent (id,name) values (1,'Smith');

select
        /*+ initially deferred  PK */
        chi.*
from
        child   chi,
        parent  par
where
        par.id = chi.id_p
;

select * from table(dbms_xplan.display_cursor);

In this case we don’t need to “set constraint par_pk deferred”, it’s implicitly deferred by definition and will only be checked when we commit any transaction. Would you expect this to make any difference to the result? This is what we get:

      ID_P         ID NAME
---------- ---------- ------------
         1          1 Simon
         1          1 Simon
         1          2 Sally
         1          2 Sally

4 rows selected.


PLAN_TABLE_OUTPUT
--------------------------------------------------
SQL_ID  8gvn3mzr8uv0h, child number 0
-------------------------------------
select  /*+ initially deferred  PK */  chi.* from  child chi,  parent
par where  par.id = chi.id_p

Plan hash value: 1687613841

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS      |        |     2 |    30 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CHILD  |     2 |    24 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | PAR_PK |     1 |     3 |     0   (0)|          |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PAR"."ID"="CHI"."ID_P")

When the parent primary key is initially deferred then join elimination doesn’t take place – so we get two copies of each child row in the output. (This is still true even if we add the “rely” option to the parent primary key constraint).

Debug Analysis

As I said at the top of the article, this does give us a clue about the source of the bug. A check of the dictionary table cdef$ (constraint definitions) shows the following notes for column cdef$.defer:

  defer         number,                     /* 0x01 constraint is deferrable */
                                              /* 0x02 constraint is deferred */
                                /* 0x04 constraint has been system validated */
                                 /* 0x08 constraint name is system generated */
etc...

With my examples the “initially immediate” constraint reported defer = 5, for the “initially deferred” constraint it reported the value 7. It looks as if the optimizer code to handle join elimination look only at the static definition of the constraint (bit 0x02) and doesn’t consider the possibility that if bit 0x01 is set it should also check the session state to see if the constraint has been temporarily deferred.

Conclusion

If you are going to implement deferrable constraints be very careful about tracking exactly how you use them, and be aware that if you execute arbitrary queries in mid-transaction then you may find that the results are not exactly what you expect. In fact, though it’s not demonstrated here, different forms of SQL to that should express the same requirement may not give the same results.

Update (May 2021)

This buggy behaviour is still present in 19.11.0.0

March 4, 2021

use_nl_with_index

Filed under: Execution plans,Index skip scan,Indexing,Nested Loop,Oracle — Jonathan Lewis @ 3:59 pm GMT Mar 4,2021

One of the less well-known hints is the hint /*+ use_nl_with_index() */  (link to 19c reference manual) which appeared in the 10g timeline. I may be wrong but I don’t think I saw a good description of this hint in the manuals until the 19c manual supplied the following:

The USE_NL_WITH_INDEX hint will cause the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table but only under the following condition. If no index is specified, the optimizer must be able to use some index with at least one join predicate as the index key. If an index is specified, the optimizer must be able to use that index with at least one join predicate as the index key.

It looks like a fairly redundant hint, really, since it could easily (and with greater safely, perhaps) be replaced by the pair /*+ use_nl() index() */ with the necessary details of query block, object alias etc. on the hints. In fact I think I’ve only ever seen the hint “in the wild” once, and that was in an internal view definition where it had been used incorrectly (see this recent update to a note on one of the dynamic performance views that I wrote a few years ago).

The note I’ve just referenced prompted me to take a closer look at the hint to see how accurate the definition was. Here’s a data set I created for testing:

rem
rem     Script:         use_nl_with_index.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2021
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem 

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4    -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        mod(rownum,10)                  n10,
        mod(rownum,1000)                n1000,
        mod(rownum,2000)                n2000,
        lpad(mod(rownum,1000),10,'0')   v1000,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e5   -- > comment to avoid WordPress format issue
;

create table t2 as
select distinct
        n10, n1000, v1000
from
        t1
;

create index t1_i1000 on t1(n1000);
create index t1_i10_1000 on t1(n10,n1000);
create index t1_i2000 on t1(n2000);
create bitmap index t1_b1 on t1(n1000, n10);

I’ve set up the data to do a join between t2 and t1, and I’m going to hint a query to force the join order t2 -> t1, and thanks to the data pattern the default path should be a hash join. Once I’ve established the default path I’m going to use the use_nl_with_index() hint to see how it behaves with respect to the various indexes I’ve created. So here’s the query with the default path:

set autotrace traceonly explain

select  
        /*+ leading(t2 t1) */
        t1.*
from    t2, t1
where
        t2.n10 = 1
and     t1.n1000 = t2.n1000
;

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |  1318K|   259   (8)| 00:00:01 |
|*  1 |  HASH JOIN         |      | 10000 |  1318K|   259   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   100 |   700 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   100K|    12M|   252   (6)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."N1000"="T2"."N1000")
   2 - filter("T2"."N10"=1)

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

So the join order is as required, and the default is a hash join. The join predicate is t1.n1000 = t2.n1000, and if you examine the indexes I’ve created you’ll see I’ve got

  • t1_i1000 on t1(n1000) – the perfect index
  • t1_i10_1000 on t1(n10, n1000) – which could be used for a skip scan
  • t1_i2000 on t1(n2000) – which doesn’t include the column in the join predicate
  • t1_b1 on t1(n1000, n10) – which is a bitmap index

So here are the first batch of tests – all rolled into a single statement with optional hints included:

select  
        /*+ 
                leading(t2 t1) 
                use_nl_with_index(t1) 
--              use_nl_with_index(t1 t1_i1000)
--              use_nl_with_index(t1(n1000))
        */
        t1.*
from    t2, t1
where
        t2.n10 = 1
and     t1.n1000 = t2.n1000
;


Execution Plan
----------------------------------------------------------
Plan hash value: 3315267048

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          | 10000 |  1318K| 10133   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                |          | 10000 |  1318K| 10133   (1)| 00:00:01 |
|   2 |   NESTED LOOPS               |          | 10000 |  1318K| 10133   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | T2       |   100 |   700 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T1_I1000 |   100 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1       |   100 | 12800 |   101   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N10"=1)
   4 - access("T1"."N1000"="T2"."N1000")

If I don’t specify an index the optimizer picks the best possible index; alternatively I can specify the index on (n1000) by name or by description and the optimizer will still use it. So what do I get if I reference the index on (n2000):

select  
        /*+ 
                leading(t2 t1) 
                use_nl_with_index(t1(n2000))
        */
        t1.*
from    t2, t1
where
        t2.n10 = 1
and     t1.n1000 = t2.n1000
;


Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |  1318K|   259   (8)| 00:00:01 |
|*  1 |  HASH JOIN         |      | 10000 |  1318K|   259   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   100 |   700 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   100K|    12M|   252   (6)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."N1000"="T2"."N1000")
   2 - filter("T2"."N10"=1)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   3 -  SEL$1 / T1@SEL$1
         U -  use_nl_with_index(t1(n2000))

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


I’m back to the tablescan with hash join – and since I’m testing on 19.3.0.0 Oracle kindly tells me in the Hint Report that I have an unused hint: the one that can’t be used because the referenced index doesn’t have any columns that are join predicates.

So what about the skip scan option:

select  
        /*+ 
                leading(t2 t1) 
                use_nl_with_index(t1(n10, n1000))
--              use_nl_with_index(t1(n10))
--              index_ss(t1 (n10))
        */
        t1.*
from    t2, t1
where
        t2.n10 = 1
and     t1.n1000 = t2.n1000
;


Even though the index I’ve specified in the hint does contain a column in the join predicate the execution plan reports a full tablescan and hash join – unless I include an explicit index_ss() hint: but in that case I might as well have used the vanilla flavoured use_nl() hint. I did have a look at the 10053 (CBO) trace file for this example, and found that if I didn’t include the index_ss() hint the optimizer calculated the cost of using an index full scan (and no other option) for every single index on t1 before choosing the tablescan with hash join.

Finally, and without repeating the query, I’ll just note that when I referenced t1_b1 (n1000, n10) in the hint Oracle was happy to use the index in a nested loop join:

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       | 10000 |  1318K|  2182   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                 |       | 10000 |  1318K|  2182   (1)| 00:00:01 |
|   2 |   NESTED LOOPS                |       | 10000 |  1318K|  2182   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL          | T2    |   100 |   700 |     2   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS|       |       |       |            |          |
|*  5 |     BITMAP INDEX RANGE SCAN   | T1_B1 |       |       |            |          |
|   6 |   TABLE ACCESS BY INDEX ROWID | T1    |   100 | 12800 |  2182   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T2"."N10"=1)
   5 - access("T1"."N1000"="T2"."N1000")
       filter("T1"."N1000"="T2"."N1000")

Summary

The use_nl_with_index() hint generally works as described in the manuals – with the exception that it doesn’t consider an index skip scan as a valid option when trying to match the join predicate. That exception is one of those annoying little details that could waste a lot of your time.

Since it’s so easy to replace use_nl_with_index() with a pair of hints – including an index hint that could be an index_desc(), index_ss(), or index_combine() hint – I can’t come up with a good reason for using the use_nl_with_index() hint.

Next Page »

Website Powered by WordPress.com.