Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Extending in-lists

A well known “limitation” of Oracle is that you can only have 1000 elements within an IN-LIST style expression in your SQL predicates. I use the quotes because I’ve always found that if you are heading into the territory where you need more than 1000 items in an IN-LIST, then it is often indicative of something else being wrong. For example, if the code is backing a user interface, then where in the design phase did someone not go “Whoa…we are going to have 1000 select-able elements on the screen?”

In any event, you can find many tricks and techniques out there on the intertubes about how workaround this issue, such as:

My preference is for the last one because once you use a temporary table, suddenly all of the power of SQL is at your fingertips. I had an AskTOM question recently which gave me an opportunity to display this. The requester had an IN-LIST set of values, but the values could contain the standard wild-cards. Using a temporary table approach, this becomes trivial with a small adjustment to the join condition.

Here’s my first solution using a familiar XMLTABLE technique.


SQL> create table t ( x varchar2(50));

Table created.

SQL> insert into t values ('stringasd');

1 row created.

SQL> insert into t values ('blah');

1 row created.

SQL> insert into t values ('more data');

1 row created.

SQL> insert into t values ('blah blah');

1 row created.

SQL> insert into t values ('some stuff with qwe in it');

1 row created.

SQL> insert into t values ('more with a double match on qwe');

1 row created.

SQL>
SQL> with
  2  conditions as
  3    ( select 'string%,more%,%qwe%' wildcard_list from dual ),
  4  rowstyle as
  5  (
  6  select trim(column_value) inlist_item
  7  from   conditions,
  8         xmltable(('"'||replace(wildcard_list, ',', '","')||'"'))
  9  )
 10  select *
 11  from   rowstyle;

INLIST_ITEM
--------------------
string%
more%
%qwe%

3 rows selected.

SQL>
SQL>
SQL> with
  2  conditions as
  3    ( select 'string%,more%,%qwe%' wildcard_list from dual ),
  4  rowstyle as
  5  (
  6  select trim(column_value) inlist_item
  7  from   conditions,
  8         xmltable(('"'||replace(wildcard_list, ',', '","')||'"'))
  9  )
 10  select *
 11  from   rowstyle r,
 12         t
 13  where t.x like r.inlist_item;

INLIST_ITEM          X
-------------------- ----------------------------------------
string%              stringasd
more%                more data
%qwe%                some stuff with qwe in it
more%                more with a double match on qwe
%qwe%                more with a double match on qwe

5 rows selected.

SQL>
SQL>

XMLTABLE has its limitations in terms of handling special characters as you can see from below:



SQL> set define off
SQL> with
  2  conditions as
  3    ( select 'string%,more%,%abc&xyz%' wildcard_list from dual ),
  4  rowstyle as
  5  (
  6  select trim(column_value) inlist_item
  7  from   conditions,
  8         xmltable(('"'||replace(wildcard_list, ',', '","')||'"'))
  9  )
 10  select *
 11  from   rowstyle r,
 12         t
 13  where t.x like r.inlist_item;
with
*
ERROR at line 1:
ORA-19112: error raised during evaluation:
XVM-01003: [XPST0003] Syntax error at '"%abc'
1   "string%","more%","%abc&xyz%"
-                    ^

but this does not rule out the temporary table technique – we just need to use a different mechanism to convert our list into rows. Here’s another common technique, using a regular expression and a hierarchy CONNECT BY syntax.



SQL> with
  2  conditions as
  3    ( select 'string%,more%,%abc&xyz%' wildcard_list from dual ),
  4  rowstyle as
  5  (
  6  select trim(w) inlist_item
  7  from   conditions c,
  8         lateral(
  9           select trim(regexp_substr(c.wildcard_list, '[^,]+',1,level)) w
 10           from   dual
 11           connect by level <= regexp_count(c.wildcard_list,'[^,]+')
 12           )
 13  )
 14  select *
 15  from   rowstyle r
 16  /

INLIST_ITEM
--------------------
string%
more%
%abc&xyz%

3 rows selected.

With the special characters issue fixed, it is then a simple join with the LIKE to obtain the desired result.



SQL> with
  2  conditions as
  3    ( select 'string%,more%,%abc&xyz%' wildcard_list from dual ),
  4  rowstyle as
  5  (
  6  select trim(column_value) inlist_item
  7  from   conditions c,
  8         lateral(
  9           select regexp_substr(c.wildcard_list, '[^,]+',1,level) column_value
 10           from   dual
 11           connect by level <= regexp_count(c.wildcard_list,'[^,]+')
 12           )
 13  )
 14  select *
 15  from   rowstyle r,
 16         t
 17  where t.x like r.inlist_item;

INLIST_ITEM          X
-------------------- ----------------------------------------
string%              stringasd
more%                more data
more%                more with a double match on qwe

3 rows selected.

So before you look at the “tricks” to allow more than 1000 items in an IN-LIST, do not be too quick to dismiss the value of converting the values to rows, and therefore opening up all the power of SQL to perform your processing.

Quiz Night

Upgrades cause surprises – here’s a pair of results from a model that I constructed more than 15 years ago, and ran today on 12.2, then modified and ran again, then ran on 11.2.0.4, then on 12.1.0.2. It’s very simple, I just create a table, gather stats, then update every row.

rem
rem     Script:         update_nochange.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2019
rem
rem     Last tested 
rem             12.2.0.1 

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

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

execute snap_my_stats.start_snap

update t1 set small_vc = upper(small_vc);

execute snap_my_stats.end_snap

The calls to package snap_my_stats are my little routines to calculate the change in the session activity stats (v$sesstat, joined to v$statname) due to the update. Here are a a few of the results for the test using the code as it stands:


Name                                    Value
----                                    -----
redo entries                               36
redo size                             111,756
undo change vector size                53,220

You’ll notice, however, that the CTAS has an option commented out to create the small_vc column using lpad(rownum,‘0’) rather than lpad(rownum,‘x’). This is what the redo stats look like if I use ‘0’ instead of ‘x’:


Name                                    Value
----                                    -----
redo entries                              909
redo size                             223,476
undo change vector size                68,256

What – they’re different ?!  (and it’s reproducible).

Running the test on 12.1.0.2 or 11.2.0.4, both variants of the code produce the smaller number of redo entries (and bytes) and undo – it’s only 12.2.0.1 that shows a change. (I haven’t got around to testing 18.3 and 19.3 yet.)

Tonight’s quiz:

Figure out what’s happening in 12.2.0.1 to give two different sets of undo and redo figures.

If that problem is too easy – extrapolate the test to more complex cases to see when the difference stops appearing, and see if you can find any cases where this new feature might cause existing applications to break.

I’ll supply the basic answer in 48 hours.

Update (a few hours early)

The question has been answered in the comments – it’s an optimisation introduced in 12.2 that attempts to reduce the amount of undo and redo by minimising the work done for “no change” updates to data.  In principle – but we don’t yet know the rules and limitations – if an update does not change the column values Oracle 12.2 will not “save the old values in an undo record and log the new values in a redo change vector”, it will simply lock the row, to produce a minimal redo change vector.

Unfortunately Oracle goes into “single row” mode to lock rows, while it can do “block-level – i.e. multi-row/array” processing if it uses the “change” mechanism.  Inevitably there are likely to be cases where the 12.2 optimisation actually produces a worse result in terms of volume of redo, or contention for redo latches.

If we modify the code to dump the redo generated by the two different updates we can see more clearly what Oracle is doing:

alter session set tracefile_identifier = 'UPD';

column start_scn new_value m_start_scn
select to_char(current_scn,'999999999999999999999999') start_scn from v$database;

update t1 set small_vc = upper(small_vc);
commit;

column end_scn new_value m_end_scn
select to_char(current_scn,'999999999999999999999999') end_scn from v$database;

alter system dump redo scn min &m_start_scn scn max &m_end_scn;

Then, after running the test we can dump the list of redo op codes from the trace file:

First when we do the “no-change” update (with lots of repetitions deleted):

grep -n OP orcl12c_ora_21999_UPD.trc | sed 's/CON_ID.*OP/ OP/' | sed 's/ ENC.*$//'

129:CHANGE #2 MEDIA RECOVERY MARKER  OP:17.28
138:CHANGE #1  OP:11.4
147:CHANGE #2  OP:5.2
150:CHANGE #3  OP:11.4
159:CHANGE #4  OP:11.4
168:CHANGE #5  OP:11.4
177:CHANGE #6  OP:11.4
...
2458:CHANGE #189  OP:5.1
2474:CHANGE #190  OP:5.1
2490:CHANGE #191  OP:5.1
2506:CHANGE #192  OP:5.1
2525:CHANGE #1  OP:5.1
2541:CHANGE #2  OP:11.4
2553:CHANGE #1  OP:5.1
2569:CHANGE #2  OP:11.4
...
27833:CHANGE #1  OP:5.1
27849:CHANGE #2  OP:11.4
27861:CHANGE #1  OP:5.1
27877:CHANGE #2  OP:11.4
27889:CHANGE #1  OP:5.4

The dump starts with a large redo record (192 change vectors) that started life in the private redo buffer, and then switch to the standard “paired change vectors” in the public redo buffer. The 11.4 vectors are “lock row piece” while the 5.1 vectors are the “generate undo”. Counting the 11.4 and 5.1 lines there are exactly 1,000 of each – every row has been individually locked.

Now for the “real change” update:

grep -n OP orcl12c_ora_22255_UPD.trc | sed 's/CON_ID.*OP/ OP/' | sed 's/ ENC.*$//'

126:CHANGE #2 MEDIA RECOVERY MARKER  OP:17.28
135:CHANGE #1  OP:11.19
281:CHANGE #2  OP:5.2
284:CHANGE #3  OP:11.19
430:CHANGE #4  OP:11.19
576:CHANGE #5  OP:11.19
...
5469:CHANGE #41  OP:5.1
5573:CHANGE #42  OP:5.1
5726:CHANGE #43  OP:5.1
5879:CHANGE #44  OP:5.1
6035:CHANGE #1  OP:5.1
6188:CHANGE #2  OP:11.19
6337:CHANGE #1  OP:5.1
6490:CHANGE #2  OP:11.19
...
15029:CHANGE #2  OP:11.19
15101:CHANGE #1  OP:5.1
15177:CHANGE #2  OP:11.19
15249:CHANGE #1  OP:5.4

It’s a much smaller trace file (ca. 15,249 lines compared to ca. 27889 lines), and the table change vectors are 11.19 (Table array update) rather than 11.4 (table lock row piece). Counting the op codes we get 52 of each of the 11.19 and 5.1. If we want a little more information about those vectors we can do the following:


egrep -n -e "OP:" -e "Array Update" orcl12c_ora_22255_UPD.trc | sed 's/CON_ID.*OP/ OP/' | sed 's/ ENC.*$//' 

126:CHANGE #2 MEDIA RECOVERY MARKER  OP:17.28
135:CHANGE #1  OP:11.19
140:Array Update of 20 rows:
281:CHANGE #2  OP:5.2
284:CHANGE #3  OP:11.19
289:Array Update of 20 rows:
430:CHANGE #4  OP:11.19
435:Array Update of 20 rows:
576:CHANGE #5  OP:11.19
581:Array Update of 20 rows:
...
5469:CHANGE #41  OP:5.1
5481:Array Update of 13 rows:
5573:CHANGE #42  OP:5.1
5585:Array Update of 20 rows:
5726:CHANGE #43  OP:5.1
5738:Array Update of 20 rows:
5879:CHANGE #44  OP:5.1
5891:Array Update of 20 rows:
6035:CHANGE #1  OP:5.1
6047:Array Update of 20 rows:
6188:CHANGE #2  OP:11.19
6193:Array Update of 20 rows:
6337:CHANGE #1  OP:5.1
6349:Array Update of 20 rows:
...
14953:CHANGE #1  OP:5.1
14965:Array Update of 9 rows:
15029:CHANGE #2  OP:11.19
15034:Array Update of 9 rows:
15101:CHANGE #1  OP:5.1
15113:Array Update of 9 rows:
15177:CHANGE #2  OP:11.19
15182:Array Update of 9 rows:
15249:CHANGE #1  OP:5.4

As you can see, the 11.19 (table change) and 5.1 (undo) change vectors both report that they are are structured as array updates. In most cases the array size is 20 rows, but there are a few cases where the array size is smaller. In this test I found one update with an array size of 13 rows and three updates with an array size of 9 rows.

Summary

Oracle has introduced an optimisation for “no change” updates in 12.2 that tries to avoid generating quite so much undo and redo; however this may result in some cases where an “array update” change vector turns into several “single row lock” change vectors, so when you upgrade to 12.2 (or beyone) you may want to check any large update mechanism you run to see if your system has benefited or been penalised to any significant extent by this change. The key indicator will be an increase in the value of the session/system stats “redo entries” and “redo size”.

Speaker info for Perth, Australia

I have just got back from the Groundbreakers Latin America tour, and the travelling was made so much easier with the information provided by the organizers. So with the APAC tour coming up, I felt duty bound to give some hopefully useful information about my home town in Perth.

Flying in/out

There are two airport terminals in Perth. In times gone by, these were known as the Domestic and International terminals because, as the names suggest, domestic flights within Australia landed at one, and flights from overseas landed at the other. Hence locals will still refer to the terminals as “domestic” and “international”. However, due to all sorts of politics and bickering between airlines, times have changed.

The “Domestic” airport is now officially Terminal 3 and 4, and takes all Qantas and Jetstar flights (both domestic and international). The “International” airport is now Terminal 1 and 2, which takes all of the other airlines (including Tiger, Virgin for domestic) and all the major international carriers. Compounding all of this is that if your Qantas international flight (normally Terminal 4) is being operated by a partner (Emirates) then it might take off from Terminal 1.

Confused? </p />
</p></div>

    	  	<div class=

Working with AWR: Old Statistics and Costs in Current Execution Plans

This is a blog about a little trap, into which it is easy to fall when analysing performance problems using ASH data and execution plans from AWR.
I will illustrate it with a real example taken from a production PeopleSoft system. A number of sessions were waiting on blocking locks.  I used ASH data to find statements running while holding a blocking lock.  That led me to a particular SQL ID and plan have value.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SQL_ID        SQL_PLAN_HASH_VALUE   ASH_SECS
------------- ------------------- ----------
29210
5st32un4a2y92 2494504609 10670
652mx4tffq415 1888029394 7030
artqgxug4z0f1 8450529 580
7qxdrwcn4yzhh 3723363341 270

So, I used DBMS_XPLAN.DISPLAY_AWR to extract the execution plan from the AWR repository.

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

I can see the statement, the execution plan, the costs of each operation in the plan, and the value of the bind variable. This is a statement that has consumed a lot of execution time, it isn't executing that frequently, yet the cost of statement is quite low (only 22). That, in itself, does not mean there is a problem here, but it is perhaps a suggestion of an inconsistency.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SQL_ID 5st32un4a2y92
--------------------
SELECT 'X' FROM PS_CDM_LIST WHERE CONTENTID = :1

Plan hash value: 2494504609

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 22 (100)| |
| 1 | INDEX FAST FULL SCAN| PS_CDM_LIST | 1 | 5 | 22 (10)| 00:00:01 |
------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 17776

Familiarity with the application is an advantage here. The value of CONTENTID comes from a sequence number (albeit generated from a table, not an Oracle sequence because this is PeopleSoft). The system has been running for a while, and Process Instances and Context IDs were well in excess of 1,000,000, so why is the bind variable only 17776? That is another alarm bell.
I can extract the current execution plan from the library cache, if the statement is in memory, using DBMS_XPLAN.DISPLAY_CURSOR().

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

Plan hash value: 2494504609

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3178 (9)| 00:00:05 |
|* 1 | INDEX FAST FULL SCAN| PS_CDM_LIST | 1 | 6 | 3178 (9)| 00:00:05 |
------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 10897657

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CONTENTID"=TO_NUMBER(:1))

DBMS_XPLAN.DISPLAY_CURSOR() shows the current bind variable value. It is immediately obvious it has moved on since the plan was captured by AWR. If the statement is not in memory, I could generate a new execution plan using EXPLAIN PLAN FOR, that reparses the statement, and then extract the execution plan with DBMS_XPLAN.DISPLAY(). However, this will not give me the bind variable value.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">explain plan for SELECT 'X' FROM PS_CDM_LIST WHERE CONTENTID = :1
/

Explained.

select * from table(dbms_xplan.display(null,null,'ADVANCED'));

The execution plan has the same plan hash value, so it is the same plan. However, the cost has increased significantly (from 22 to 3178). This reflects how the table has grown over time, and that is the problem. The full scan of the index was fast when the table was small, but as the table has grown over time, the scan is also taking longer. By the time I came to look at it, it was quite significant.
In this case, we created a new index on PS_CDM_LIST leading on CONTENTID to satisfy this query. The result was to improve the performance of this statement, and a reduce in time spent holding blocking locks which has further benefits.

What is going on here? 

This is SQL statement is a regular part of the application. The SQL ID doesn't change because it uses a bind variable. The execution plan hasn't changed either because the optimizer can't find a better option. The execution plan we obtained from AWR was the first such plan captured for that combination of SQL ID and plan hash value. AWR may, in fact, is very likely to, have captured that plan again subsequently. However, it did not update the plan in AWR, and the old plan was never purged.
The original cost (22) was the cost then, not now. The value of the bind variable (17776) was the value then not now!
So how do you know you are looking at a plan with old costs and bind variables? You don't.  There is nothing explicit to warn you to go and check.
However, if things are not making sense, see if the cost for the same plan is close to the costs stored in AWR.
If you use EXPLAIN PLAN FOR you may get a different plan, in which case you will need to use the hints in the outline section of the execution plan (not shown above) to force it to produce the same execution plan before you can compare costs.

Truncate AWR tables (unsupported)

When WRH$ tables grow too large so that they cannot be purged

This is no supported, please look at the My Oracle Support notes for a supported way to purge AWR when going too big, like re-creating AWR (needs to start the database in restricted mode) or purging with the normal procedure (can be long as it runs a delete). And do not copy-paste my statements as this is just an example.

When some tables grow too large, the purge job does not work correctly (because some things like the partitioning are done at the end). Then SYSAUX grows. And worse: the next upgrade may take hours if it has to change something on the AWR tables.

Upgrade time

Here is an example of an upgrade from 11g to 19c which took hours. Here is how I open the upgrade logs with “less” for the Top-10 longest statement execution:

eval $( grep -n "^Elapsed:" catupgrd*.log | sort -rk2 | awk -F: 'NR<10{print "less +"$2" "$1";"}' )

Or this one to display them in order:

tac catupgrd*.log | awk '/^Elapsed:/{print x;x="";e=$2}e>"00:01:00"{x=e" "NR" "$0"\n"x}' | sort -r | more

Many hours were spend on updating AWR tables like a new index on WRH$_EVENT_HISTOGRAM, add a column on WRH$_SQLSTAT to count obsoletes, add In-memory columns in WRH$_SEG_STAT, and many new indexes. If AWR became too large, you should do something before the upgrade. For system.logmnrc_gtlo that will probably be another blog post.

Check AWR size

Here is how to check the size of AWR before the upgrade:

sqlplus / as sysdba @ ?/rdbms/admin/awrinfo

If the query never returns, then maybe it is too large…

Baselines

First I check that I didn’t explicitly create AWR baseline to keep old snapshots:

select * from dba_hist_baseline where baseline_type<>'MOVING_WINDOW';

If there are some, then what I’ll do will lose son information someone wanted to retain, so check before.

Create new partitions

For the partitioned ones, I can force the creation of a new partition so that I can, later, truncate only the old ones without losing the recent AWR snapshots:

alter session set "_swrf_test_action" = 72;

This is the workaround when the partitions were not created automatically.

Truncate all old partitions

After a while (run a few dbms_workload.create_snapshot) I truncate those old partitions. Here is how I generate the drop statement:

select 'alter table '||object_name||' truncate partition '||subobject_name||' update global indexes;'
from dba_objects where object_name like 'WRH$%' and object_type = 'TABLE PARTITION' and created;

I mention CREATED

But now remains a few big tables that cannot be partitioned.

Look at large tables in SYS for WRH$

If I am not sure about the statistics gathering, I run it explicitly in order to see the recent number of rows (but this can take long):

exec DBMS_STATS.GATHER_DICTIONARY_STATS;

I’m interested in those larger than 1 million rows. For the ones that are partitioned, I have truncated large partitions only. But for the non-partitioned ones, I’ll truncate the whole table — and then lose everything.

select owner,table_name,dbms_xplan.format_number(num_rows) num_rows,object_type,partition_name,(select count(*) from dba_tab_partitions p where s.owner=p.table_owner and s.table_name=p.table_name) part from dba_tab_statistics s where owner in ('SYS','SYSTEM') and table_name like 'WRH$%' and num_rows>1e6 order by s.num_rows;

Here is the result. Use the same query without the ‘WRH$’ pattern in order to see everything that may cause problems at upgrade time.

The column PART is the number of partitions. Those with 0 are not partitioned and then the truncate will remove all data.

Truncate large non-partitioned ones

Some tables are not partitioned and I truncate the largest ones (which I’ve identified from the query above). I prefer to limit them because:

  • Fresh snapshot information will be lost
  • Inconsistency (snapshots with no data)
-- this is my example, you may have different tables
--
truncate table WRH$_TABLESPACE_SPACE_USAGE update global indexes;
truncate table WRH$_EVENT_HISTOGRAM update global indexes;
truncate table WRH$_MUTEX_SLEEP update global indexes;
truncate table WRH$_ENQUEUE_STAT update global indexes;
truncate table WRH$_SYSMETRIC_SUMMARY update global indexes;
truncate table WRH$_BG_EVENT_SUMMARY update global indexes;
truncate table WRH$_SYSMETRIC_HISTORY update global indexes;
truncate table WRH$_SQL_BIND_METADATA update global indexes;
truncate table WRH$_SQL_PLAN update global indexes;

This is fast, but if you need to run this, better do it when there’s no snapshot gathering. And do not rely on my list: chose the largest ones you have.

Note that truncating WRH$_SQL_PLAN will remove all old SQL Plans. I usually rarely need to look at an old plan (better to tune the current one rather than looking at the past) but they may help sometimes to get the plan, with its outlines, that worked before. So, do not do that when you have performance instabilities. Or ensure that you have a SQL Tuning Set containing the critical queries.

Use regular purge

Now I want everything to be consistent. I determine the earliest snapshot I have that is fully consistent (gathered after my truncate table statements):

select dbid,min(snap_id) from WRH$_SQL_PLAN group by dbid;

I choose this table because it was the last one I truncated.

Then I run the supported purge procedure for those snapshots:

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(0,177189,28047622);

This brings me back to a consistent set of data. And this should not take long because but I ensured that there is no more than one million rows in each table.

I split again to start clean:

alter session set "_swrf_test_action" = 72;

Maybe reduce the retention

As I had AWR growing too much, I reduced the retention:

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(8);
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>8*60*24);

I changed the baseline duration as it cannot be larger than the retention.

If you need larger retention, then maybe you should set up a Centralized AWR Warehouse.

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars !!

It’s with great excitement that I announce I’ll finally be returning to London, UK in March 2020 to run both of my highly acclaimed seminars. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and […]

My OpenWorld!

Just a couple of weeks away, and my oh my, I am going to be busy at OpenWorld this year!

Of course, first cab off the block will be the OpenWorld swim! It it September and marvellously warm in San Fran, so what better year to come along and meet new friends, and get free coffee and donuts! Just grab a towel from the hotel and head down to the marina!

image

Register at the link below, so we know how many donuts to buy!!! Smile

https://www.facebook.com/events/2464419823596577/

 

Once we’re into the conference week, I’ve got some standard full length sessions, and the hot, quick, rapid-fire theatre style sessions for those with less patience! Here’s a peek at what I’ll be covering:

Ten Amazing SQL Features (session)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=dev4700

Sick and tired of writing thousands of lines of middle-tier code and still having performance problems? Let’s become fans once again of the database by being reintroduced to just how powerful the SQL language really is! Coding is great fun, but we do it to explore complex algorithms, build beautiful applications, and deliver fantastic solutions for our customers, not just to do boring data processing. By expanding our knowledge of SQL facilities, we can let all the boring work be handled via SQL rather than a lot of middle-tier code—and get performance benefits as an added bonus. This session highlights some SQL techniques for solving problems that would otherwise require a lot of complex coding.

The Best Database Feature Ever Invented (theatre)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=tht4798

For more than 20 years, the single feature that has set Oracle Database apart from all others is its “read consistency” implementation. We’ve all heard the mantra: Readers don’t block writers; writers don’t block readers. But such flexibility can lead developers down a rabbit hole of performance problems, incorrect code, and a corrupted data. This session peels back the covers on the most amazing feature that the database has ever had.

How to guarantee Slower and Less Secure Applications (theatre)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=tht4796

No matter how sophisticated the framework, at the very center of it, you are most probably doing data access with SQL. Do that right, and your application has a great chance of being a success. But what if you don’t like your job? What if you are looking for a change? This session covers the worst SQL processing approaches to make sure that you’ll be out of a job, and your company out of business in record time.

The Assassins Guide (theatre)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=tht4795

Kill someone’s session and they might lose vital data and/or their temper. But that’s not your fault right? After all, they were the ones slamming the server or locking other people out. But perhaps there are better methods at your disposal. This short session gives you a range of other options to harmlessly dispose of those pesky server killers.

Flashback – not just for DBAs (session)

https://events.rainfocus.com/widget/oracle/oow19/catalogow19?search=dev4702

Everyone thinks of Oracle Flashback as just for those “Oh no!” moments. But it is time for re-think. With the modern development paradigms of test-driven development, continuous integration and rapid deployment, Flashback might be the perfect technology for developers not just DBAs.

 

And of course, there will be fresh new look AskTOM stickers for your phone and laptop, so please come say “Hi” before or after sessions.

asktom_logo_new_template_fill

Ssshhhhh…. Bonus session coming!

Yes…it is still in planning, but with any luck, I will get even another session to share with you all!

See you at OpenWorld !!!

Oracle Database 19c Automatic Indexing: Default Index Column Order Part I (Anyway Anyhow Anywhere)

The next thing I was curious about regarding Automatic Indexing was in which order would Oracle by default order the columns within an index. This can be a crucial decision with respect to the effectiveness of the index (but then again, may not be so crucial as well). Certainly one would expect the index column […]

Announcement: New “Oracle Indexing Internals and Best Practices” Webinar – 19-23 November 2019 in USA Friendly Time Zone

I’m very excited to announce a new Webinar series for my highly acclaimed “Oracle Indexing Internals and Best Practices” training event, running between 19-23 November 2019 !! Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function […]

Little things worth knowing: keeping enq: TM enqueue at bay during direct path inserts

Direct path inserts are commonly found in processing where data are shifted from one source to another. There are many permutations of the theme, this post details the way SQL Loader (sqlldr) behaves.

I have previously written about sqlldr and concurrent direct path inserts. The behaviour for releases <= 12.1 is described in the first post, the most welcome changes in 12.2 went into the second post. Since the fastest way of doing something is not to do it at all, I thought it might be worth demonstrating a useful technique to keep the dreaded TM enqueue at bay. Please note that these do not only apply to sqlldr, inserts with the append hint for example behave the same way in my experience.

I used Oracle 19.4.0 on Oracle Linux 7 for this post, the content should apply to older database releases, too. The data model is once more provided by Swingbench, I’m using the SH benchmark this time. As I said on twitter yesterday, @TanelPoder scripts are simply too good to afford writing my own, so I’m using them in this post where possible.

Please note that I’m using partitioning in this post, which is a cost option on top of Enterprise Edition. Always ensure your system is properly licensed for features you use.

Preparations

Let’s assume for a moment that you would like to load another batch of records into the (Swingbench) sh.sales table. The table is range partitioned on time_id, and split into quarterly partitions – at least for the later ones. On my system I count 68 partitions, with the latest containing records up to January 1st 2014. I added a couple of partitions for the next 2 quarters to allow for the new data load.

SQL> select partition_name, partition_position,high_value 
  2  from user_tab_partitions where table_name = 'SALES' 
  3   and partition_position >= 68;

PARTITION_NAME     PARTITION_POSITION HIGH_VALUE
----------------- ------------------- -----------------------------------------------------------------------------------
SALES_Q4_2013                      68 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')   
SALES_Q1_2014                      69 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')   
SALES_Q2_2014                      70 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')   

I created CSV files of the data to be loaded by simply adding 3 and 6 months to the last populated partition.

spool /u01/app/oracle/admin/ORCL/sqlldr/sales_q1_2014.csv
select /*csv*/ 
 prod_id,
 cust_id,
 add_months(time_id,3) time_id,
 channel_id,
 promo_id,
 quantity_sold,
 seller,
 fulfillment_center,
 courier_org,
 tax_country,
 tax_region,
 amount_sold 
from sales partition (sales_q4_2013);

spool off
spool  /u01/app/oracle/admin/ORCL/sqlldr/sales_q2_2014.csv

select /*csv*/ 
 prod_id,
 cust_id,
 add_months(time_id,6) time_id,
 channel_id,
 promo_id,
 quantity_sold,
 seller,
 fulfillment_center,
 courier_org,
 tax_country,
 tax_region,
 amount_sold 
from sales partition (sales_q4_2013);

spool off
exit

Using the csv “hint” and executing this little script in sqlcl allows me to spool the files into the desired format (I made sure I have plenty of space available in /u01/app/oracle/admin). Now let’s try loading the data. As always, I use a control file although there might be better ways of doing this. It contains the following instructions:

options (direct=true,skip=1)
load data
append into table sales
fields terminated by ',' optionally enclosed by '"'
date format "dd-mon-yy"
trailing nullcols
(
  PROD_ID,                                                                        
  CUST_ID,                                                                        
  TIME_ID,                                                                        
  CHANNEL_ID,                                                                     
  PROMO_ID,                                                                       
  QUANTITY_SOLD,                                                                  
  SELLER,                                                                         
  FULFILLMENT_CENTER,                                                             
  COURIER_ORG,                                                                    
  TAX_COUNTRY,                                                                    
  TAX_REGION,                                                                     
  AMOUNT_SOLD
)

Test #1

Now let’s load the data! Since I’m not the most patient person I launched 2 sessions in parallel using a screen (1) session

$ sqlldr /@sh control=load_sales.ctl data=sales_q1_2014.csv log=load_q1.log bad=load_q1.bad direct_path_lock_wait=true

$ sqlldr /@sh control=load_sales.ctl data=sales_q2_2014.csv log=load_q2.log bad=load_q2.bad direct_path_lock_wait=true

While monitoring the load process I was surprised to see session 2 waiting:

SQL> select sid,seq#,event,state from v$session where program like '%sqlldr%';

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1228        578 SQL*Net more data from client                                    WAITED SHORT TIME
      1470         97 enq: TM - contention                                             WAITING

2 rows selected.

Each process should insert data into separate partitions, so why is there a wait? Maybe there is a clue in v$session_wait:

SQL> @sw 1470

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT P1                  P2                  P3                  P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------- ------------------- ------------------- ------------------------------------------
   1470 WAITING enq: TM - contention                             98           6 name|mode=          object #= 78758     table/partition= 0  0x544D0006: TM mode 6
                                                                                0x00000000544D0006


1 row selected.

I noticed that p3 mentions a table or partition flag, which triggered my interest. After a little bit of research I found this section in the documentation: 12.3.2 Direct Path Load of a Partitioned or Subpartitioned Table which provides some clues. Object# 78758 is the table in my database:

SQL> @oid 78758

owner                     object_name                    object_type        SUBOBJECT_NAME                 CREATED             LAST_DDL_TIME       status    DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ ------------------------------ ------------------- ------------------- --------- --------------
SH                        SALES                          TABLE                                             2019-08-29 15:14:53 2019-08-29 23:06:27 VALID

What if I tried to insert into a partition straight away?

Test #2

Let’s try that with the following 2 control files. The main differences are a) referencing the input file directly and b) the specification of the target partition in the append clause:

$ cat load_sales_q1_2014.ctl
options (skip=1,direct=true)
load data
infile  /u01/app/oracle/admin/ORCL/sqlldr/sales_q1_2014.csv
append into table sales partition (sales_q1_2014)
fields terminated by ',' optionally enclosed by '"'
date format "dd-mon-yy"
trailing nullcols
( 
  PROD_ID,
  CUST_ID,
  TIME_ID,
  CHANNEL_ID,
  PROMO_ID,
  QUANTITY_SOLD,
  SELLER,
  FULFILLMENT_CENTER,
  COURIER_ORG,
  TAX_COUNTRY,
  TAX_REGION,
  AMOUNT_SOLD
)

$ cat load_sales_q2_2014.ctl
options (skip=1,direct=true)
load data
infile  /u01/app/oracle/admin/ORCL/sqlldr/sales_q2_2014.csv
append into table sales partition (sales_q2_2014)
fields terminated by ',' optionally enclosed by '"'
date format "dd-mon-yy"
trailing nullcols
( 
  PROD_ID,
  CUST_ID,
  TIME_ID,
  CHANNEL_ID,
  PROMO_ID,
  QUANTITY_SOLD,
  SELLER,
  FULFILLMENT_CENTER,
  COURIER_ORG,
  TAX_COUNTRY,
  TAX_REGION,
  AMOUNT_SOLD
)

The slightly adjusted commands for sqlldr are as follows:

$ sqlldr /@sh control=load_sales_q1_2014.ctl log=load_q1.log bad=load_q1.bad direct_path_lock_wait=true

$ sqlldr /@sh control=load_sales_q2_2014.ctl log=load_q2.log bad=load_q2.bad direct_path_lock_wait=true

With the change in place I couldn’t notice any TM enqueues when running these in parallel:

SQL> select sid,seq#,event,state from v$session where program like '%sqlldr%';

no rows selected

SQL>
SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr%'

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251        627 direct path write temp                                           WAITED KNOWN TIME
      1488        475 PGA memory operation                                             WAITED SHORT TIME

SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr%'

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251        819 direct path write temp                                           WAITING
      1488        664 direct path write temp                                           WAITING

SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr%'

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251       1070 SQL*Net more data from client                                    WAITED SHORT TIME
      1488        906 direct path write temp                                           WAITED KNOWN TIME

SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr%'

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251       1332 db file sequential read                                          WAITED SHORT TIME
      1488       1143 library cache lock                                               WAITING

SQL> r
  1* select sid,seq#,event,state from v$session where program like '%sqlldr

       SID       SEQ# EVENT                                                            STATE
---------- ---------- ---------------------------------------------------------------- -------------------
      1251       1824 db file sequential read                                          WAITED SHORT TIME
      1488       1372 db file sequential read                                          WAITING

That looks ok, and switching back through both screen sessions I can see both invocations of sqlldr completed ok:

$ sqlldr /@sh control=load_sales_q1_2014.ctl log=load_q1.log bad=load_q1.bad direct_path_lock_wait=true

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Aug 29 22:44:53 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 652437.

Table SALES, partition SALES_Q1_2014:
  652437 Rows successfully loaded.

Check the log file:
  load_q1.log
for more information about the load.

$ sqlldr /@sh control=load_sales_q2_2014.ctl log=load_q2.log bad=load_q2.bad direct_path_lock_wait=true

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Aug 29 22:44:54 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 652437.

Table SALES, partition SALES_Q2_2014:
  652437 Rows successfully loaded.

Check the log file:
  load_q2.log
for more information about the load.

Summary

When you are trying to load data into a table concurrently using direct path inserts, you will have to wait on a TM enqueue unless your table is partitioned and you are specifying different partitions as the target in each process. More generally speaking, Oracle will hold a TM enqueue on the segment you are loading into, so 2 processes attempting a direct path insert into a (sub) partition will equally have to serialise.

NB: if you are using direct path mode for sqlldr and your target segment is indexed, make sure to read chapter 12 in the Database Utilities Guide to learn about the implications of direct path load and index maintenance.