Showing posts with label troubleshooting. Show all posts
Showing posts with label troubleshooting. Show all posts

Wednesday, June 29, 2016

New Version Of XPLAN_ASH Utility

A new version 4.23 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version comes only with minor changes, see the change log below.

Here are the notes from the change log:

- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" of the last 255 samples or so and updates them with the time waited, so these wait events are not "in-flight"

- Removed some of the clean up code added in 4.22 to the beginning of the script, because it doesn't really help much but spooled script output always contained these error messages about non-existent column definitions being cleared

- The "Concurrent I/O" sections will now also be skipped in LIMITED_ASH mode

- Some more fixes to the I/O figures in the "Activity Timeline based on ASH" - the spreading introduced in 4.22 needed some further refinement (see 4.22 change log for more details)

Sunday, January 17, 2016

DML Operations On Partitioned Tables Can Restart On Invalidation

It's probably not that well known that Oracle can actually rollback / re-start the execution of a DML statement should the cursor become invalidated. By rollback / re-start I mean that Oracle actually performs a statement level rollback (so any modification already performed by that statement until that point gets rolled back), performs another optimization phase of the statement on re-start (due to the invalidation) and begins the execution of the statement from scratch. Note that this can happen multiple times - actually it's possible to end up in a kind of infinite loop when this happens, leading to statements that can run for very, very long (I've seen statements on Production environments executing for several days although a single execution would only take minutes).

The pre-requisites to meet for this to happen are not that complex or exotic:

- The target table to manipulate needs to be partitioned

- The cursor currently executing gets invalidated - either by running DDL (typically think of partition related operations) - or simply by gathering statistics on one of the objects involved in the statement

- The DML statement hasn't touched yet one of the partitions of the target table but attempts to do so after the cursor got invalidated

When the last condition is met, the statement performs a rollback, and since it got invalidated - which is one of the conditions to be met - another optimization phase happens, meaning that it's also possible to get different execution plans for the different execution attempts. When the execution plan is ready the execution begins from scratch.

According to my tests the issue described here applies to both conventional and direct-path inserts, merge statements (insert / update / delete) as well as serial and parallel execution. I haven't explicitly tested UPDATE and DELETE statements, but the assumption is that they are affected, too.

The behaviour is documented in the following note on MOS: "Insert Statement On Partitioned Tables Is RE-Started After Invalidation (Doc ID 1462003.1)" which links to Bug "14102209 : INSERT STATEMENT' IS RESTARTING BY ITSELF AFTER INVALIDATION" where you can also find some more comments on this behaviour. The issue seems to be that Oracle at that point is no longer sure if the partition information compiled into the cursor for the partitioned target table is still correct or not (and internally raises and catches a corresponding error, like "ORA-14403: Cursor invalidation detected after getting DML partition lock", leading to the re-try), so it needs to refresh that information, hence the re-optimization and re-start of the cursor.

Note that this also means that the DML statement might already have performed modifications to other partitions but after being invalidated attempts to modify another partition it hasn't touched yet - it just needs an attempt to modify a partition not touched into yet by that statement.

It's also kind of nasty that the statement keeps running the potentially lengthy query part after being invalidated only to find out it needs to re-start after the first row is attempted to be applied to a target table partition not touched yet.

Note that applications typically run into this problem, when they behave like the following:

- There are longer running DML statements that take typically several seconds / minutes until they attempt to actually perform an modification to a partitioned target table

- They either use DBMS_STATS to gather stats on one of the involved tables, typically using NO_INVALIDATE=>FALSE, which leads to an immediate invalidation of all affected cursors

- And/Or they perform partition related operations on one of the tables involved, like truncating, creating or exchanging partitions. Note that it is important to point out that it doesn't matter which objects gets DDL / stats applied, so it's not limited to activity on the partitioned target table being modified - any object involved in the query can cause the cursor invalidation

In principle this is another variation of the general theme "Don't mix concurrent DDL with DML/queries on the same objects". Doing so is something that leads to all kinds of side effects, and the way the Oracle engine is designed means that it doesn't cope very well with doing so.

Here is a simple test case for reproducing the issue, using INSERTs in this case here (either via INSERT or MERGE statement):
create table t_target (
id number(*, 0) not null,
pkey number(*, 0) not null,
filler varchar2(500)
)
--segment creation immediate
partition by range (pkey) --interval (1)
(
  partition pkey_0 values less than (1)
, partition pkey_1 values less than (2)
, partition pkey_2 values less than (3)
, partition pkey_3 values less than (4)
);

create table t_source
compress
as
select 1 as id, rpad('x', 100) as filler
from
(select /*+ cardinality(1e3) */ null from dual connect by level <= 1e3),
(select /*+ cardinality(1e0) */ null from dual connect by level <= 1e0)
union all
select 1 as id, rpad('y', 100) as filler from dual;

-- Run this again once the DML statement below got started
exec dbms_stats.gather_table_stats(null, 't_source', no_invalidate=>false)

exec dbms_stats.gather_table_stats(null, 't_target', no_invalidate=>false)

----------------------------------------------------------------------------------------------------------------------------------
-- INSERT example                                                                                                               --
-- Run above DBMS_STATS calls or any other command that invalidates the cursor during execution to force re-start of the cursor --
----------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on

-- alter session set tracefile_identifier = 'insert_restart';

-- alter session set events '10046 trace name context forever, level 12';

-- exec sys.dbms_monitor.session_trace_enable(waits => true, binds => true/*, plan_stat => 'all_executions'*/)

insert /* append */ into t_target (id, pkey, filler)
select * from (
select /*+
           use_hash(a b)
           no_eliminate_oby
       */
       a.id, 1 as pkey, a.filler
from t_source a, t_source b
where a.id = b.id
and (
regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm')
or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100))
)
order by a.id
)
union all
select * from (
select /*+
           use_hash(a b)
           no_eliminate_oby
       */
       a.id, 2 as pkey, a.filler
from t_source a, t_source b
where a.id = b.id
and (
regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm')
or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100))
)
order by a.id
)
union all
select * from (
select /*+
           use_hash(a b)
           no_eliminate_oby
       */
       a.id, 3 as pkey, a.filler
from t_source a, t_source b
where a.id = b.id
and (
regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm')
or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100))
)
order by a.id
)
;

-- exec sys.dbms_monitor.session_trace_disable

----------------------------------------------------------------------------------------------------------------------------------
-- MERGE example                                                                                                                --
-- Run above DBMS_STATS calls or any other command that invalidates the cursor during execution to force re-start of the cursor --
----------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on

merge /* append */ into t_target t
using (
select * from (
select /*+
           use_hash(a b)
           no_eliminate_oby
       */
       a.id, 1 as pkey, a.filler
from t_source a, t_source b
where a.id = b.id
and (
regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm')
or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100))
)
order by a.id
)
union all
select * from (
select /*+
           use_hash(a b)
           no_eliminate_oby
       */
       a.id, 2 as pkey, a.filler
from t_source a, t_source b
where a.id = b.id
and (
regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm')
or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100))
)
order by a.id
)
union all
select * from (
select /*+
           use_hash(a b)
           no_eliminate_oby
       */
       a.id, 3 as pkey, a.filler
from t_source a, t_source b
where a.id = b.id
and (
regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
--or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm')
or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100))
)
order by a.id
)
) s
on (s.id = t.id)
when not matched then
insert (id, pkey, filler) values (s.id, s.pkey, s.filler)
;
The idea of the test case is to maximise the time until each UNION ALL branch produces data to insert by performing an inefficient HASH JOIN (that in fact generates a Cartesian product and needs to apply a costly REGEXP filter on that huge intermediate result) and forcing a sort on the join result, so rows will only be handed over to the parent operations until all rows were processed in the join operation - and each branch generates data for a different partition of the target table. Typically it should take several seconds per branch to execute (if you need more time just un-comment the additional REGEXP_REPLACE filters), so you should have plenty of time to cause the invalidation from another session.

This means during the execution of each branch invalidating the cursor (for example by executing either of the two DBMS_STATS calls on the source or target table using NO_INVALIDATE=>FALSE) will lead to a re-start of the statement at the next attempt to write into a new target partition, possibly rolling back rows already inserted into other partitions.

Diagnostics


If you run the provided INSERT or MERGE statement on newer versions of Oracle that include the SQL_EXEC_START and SQL_EXEC_ID in V$ACTIVE_SESSION_HISTORY (or V$SESSION for that matter) and invalidate the cursor during execution and before a partition of the target table gets inserted for the first time then you can see that these entries change as the statement restarts.

In such cases the INVALIDATIONS and LOADS increase in V$SQL accordingly and the OBJECT_STATUS changes from INVALID_UNAUTH to VALID again with each re-start attempt. In newer versions where you can configure the "plan_stat" information for SQL trace to "all_executions" you'll find STAT lines for each execution attempt dumped to the trace file, but only a single final EXEC line, where the elapsed time covers all execution attempts.

The oldest version I've tested was 10.2.0.4, and that one showed already the re-start behaviour, although I would be inclined to think that this wasn't the case with older versions. So if anybody still runs older versions than 10.2.0.4 I would be interested to hear whether the behaviour reproduces or not.

Monday, December 28, 2015

New Version Of XPLAN_ASH Utility

A new version 4.22 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version primarily addresses an issue with 12c - if the HIST mode got used to pull ASH information from AWR in 12c it turned out that Oracle forgot to add the new "DELTA_READ_MEM_BYTES" columns to DBA_HIST_ACTIVE_SESS_HISTORY - although it got officially added to V$ACTIVE_SESSION_HISTORY in 12c. So now I had to implement several additional if/then/else constructs to the script to handle this inconsistency. It's the first time that the HIST view doesn't seem to reflect all columns from the V$ view - very likely an oversight rather than by design I assume.

Apart from that the I/O figures (Read Bytes / Write Bytes etc.) in the "Activity Timeline" make more sense for those cases where a process hasn't been sampled for several sample points (see below for more details).

Also in case an execution plan could not be found it is now made more obvious with a corresponding message that you might be able to pull the execution plan from AWR by using different ASH modes (MIXED / HIST).

Here are the notes from the change log:

- Fixed a funny bug that in 12c they have forgotton to add the DELTA_READ_MEM_BYTES to DBA_HIST_ACTIVE_SESS_HISTORY, so in HIST mode with 12c prior XPLAN_ASH versions could error out with invalid column name

- Change the way the I/O figures are treated in the "Activity Timeline based on ASH". Now the I/O per second is spread over the (previous) samples covered by DELTA_TIME. This should give a smoother representation of the I/O performed and much closer to what you see in Real-Time SQL Monitoring reports. The difference to prior versions is only visible in cases where a session wasn't sampled for quite a while and hence has a DELTA_TIME spanning multiple previous sample points. This also means that the I/O related columns in the "Activity Timeline based on ASH" now show only the PER SECOND values, no longer to the totals like prior versions

- Added a SET NULL "" in the configuration and initialization section for SQL*Plus environments that use a non-default SET NULL setting. This screwed up some internal switches so that XPLAN_ASH for example thought it's running in a S-ASH repository

- Added a note to the end of the output if no execution plan could be found and falling back to retrieving plan operation details from ASH. Also added the note to use MIXED or HIST ASH source option if no execution plan could be found in CURR mode, so execution plan has been purged from Shared Pool in the meanwhile

- Cloned the "cleanup" section from the end to the beginning of the script to ensure no current SQL*Plus environment settings influence the script execution. This is particularly relevant if the script execution gets cancelled before the final cleanup section is reached or some other, previous scripts left a mess behind

Thursday, January 22, 2015

New Version Of XPLAN_ASH Utility - In-Memory Support

A new version 4.21 of the XPLAN_ASH utility is available for download. I publish this version because it will be used in the recent video tutorials explaining the Active Session History functionality of the script.

As usual the latest version can be downloaded here.

This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and 11.2.0.1).

As an extra however, this version now differentiates between general CPU usage and in-memory CPU usage (similar to 12.1.0.2 Real-Time SQL Monitoring). This is not done in all possible sections of the output yet, but the most important ones are already covered.

So if you already use the 12.1.0.2 in-memory option this might be helpful to understand how much of your CPU time is spent on in-memory operations vs. non in-memory. Depending on your query profile you might be surprised by the results.

Here are the notes from the change log:

 - Forgot to address a minor issue where the SET_COUNT determined per DFO_TREE (either one or two slave sets) is incorrect in the special case of DFO trees having only S->P distributions (pre-12c style). Previous versions used a SET_COUNT of 2 in such a case which is incorrect, since there is only one slave set. 12c changes this behaviour with the new PX SELECTOR operator and requires again two sets.

- For RAC Cross Instance Parallel Execution specific output some formatting and readability was improved (more linebreaks etc.)

- Minor SQL issue fixed in "SQL statement execution ASH Summary" that prevented execution in 10.2 (ORA-32035)

- The NO_STATEMENT_QUEUING hint prevented the "OPTIMIZER_FEATURES_ENABLE" hint from being recognized, therefore some queries failed in 11.2.0.1 again with ORA-03113. Fixed

- "ON CPU" now distinguishes between "ON CPU INMEMORY" and "ON CPU" for in-memory scans

Sunday, December 21, 2014

New Version Of XPLAN_ASH Utility

A new version 4.2 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

There were no too significant changes in this release, mainly some new sections related to I/O figures were added.

One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings for CMD.EXE under Windows for example you might have to adjust accordingly to prevent ugly line wrapping.

Here are the notes from the change log:

- New sections "Concurrent activity I/O Summary based on ASH" and "Concurrent activity I/O Summary per Instance based on ASH" to see the I/O activity summary for concurrent activity

- Many averages and medians now also have accompanying minimum and maximum values shown. This isn't as good as having histograms but gives a better idea of the range of values, and how potentially outliers influence the average and deserve further investigations

- Bug fixed: When using MONITOR as source for searching for the most recent SQL_ID executed by a given SID due to some filtering on date no SQL_ID was found. This is now fixed

- Bug fixed: In RAC GV$ASH_INFO should be used to determine available samples

- The "Parallel Execution Skew ASH" indicator is now weighted - so far any activity level per plan line and sample below the actual DOP counted as one, and the same if the activity level was above
The sum of the "ones" was then set relative to the total number of samples the plan line was active to determine the "skewness" indicator

Now the actual difference between the activity level and the actual DOP is calculated and compared to the number of total samples active times the actual DOP
This should give a better picture of the actual impact the skew has on the overall execution

- Most queries now use a NO_STATEMENT_QUEUING hint for environments where AUTO DOP is enabled and the XPLAN_ASH queries could get queued otherwise

- The physical I/O bytes on execution plan line level taken from "Real-Time SQL Monitoring" has now the more appropriate heading "ReadB" and "WriteB", I never liked the former misleading "Reads"/"Writes" heading

Sunday, June 29, 2014

New Version Of XPLAN_ASH Utility

A new version 4.1 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.

Here are the notes from the change log:

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in case you want to use your own custom monitoring repository that copies data from GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR in order to keep/persist monitoring data. The tables need to have at least those columns that are used by XPLAN_ASH from the original views

- The "Activity Timeline based on ASH" for RAC Cross Instance Parallel Execution shows an additional line break for the GLOBAL summary

- Fixed various bugs related to some figures when analyzing Cross Instance RAC Parallel Execution

- The new "GLOBAL" aggregation level for Cross Instance RAC Parallel Execution (see version 4.0 change log below) is now also shown in the "Information on Parallel Degree based on ASH" section

- The "Parallel Distribution ASH" column on execution plan line level now can show process information taken from Real-Time SQL Monitoring for those processes that are not found in ASH samples.
This effectively means that with monitoring information available for every plan line every involved process will now be shown along with its ASH sample count and rows produced

So some processes will show up now with a sample count of 0.

The idea behind this is to provide more information about row distribution even for those lines/processes that are not covered by the ASH samples.
Previously the rowcount produced was only shown for those processes covered in ASH samples

The new behaviour is default - if you find the output messy you can return to previous behaviour (show only rowcounts for processes found in ASH samples) by setting the new configuration switch "show_monitor_rowcount" to any other value than the default of "YES"

- The "Real-Time SQL Monitoring" information on execution plan line level now includes the read and write request information ("ReadReq", "WriteReq")

- The I/O figures based on ASH now include the new "DELTA_READ_MEM_BYTES" information that was added in 12c. This applies to the following sections:
  - SQL Statement I/O Summary based on ASH
  - Parallel Worker activity overview based on ASH
  - Activity Timeline based on ASH

The "Read Mem Bytes" seems to correspond to the "logical read bytes from cache" statistics, so any direct path reads are not covered by this value

- Added some more verbose description in the "Note" sections how to handle long lines. XPLAN_ASH now does a SET TRIMSPOOL ON if you want to spool the output to a file

- Whenever the output referred to DFOs this was changed to "DFO TREE", which is the correct term

- The "Parallel Worker activity overview based on ASH" section now shows a blank line between the sections which should make this section more readable

- Adaptive plans are now supported by XPLAN_ASH

Note they don't work well with previous versions, the formatting of the inactive lines breaks and the overall information can be misleading if you don't add manually the "ADAPTIVE" formatting option

If XPLAN_ASH detects an adaptive plan, it will always force the ADAPTIVE formatting option.
This also means that Adaptive plans for the time being won't work with SASH as SASH doesn't collect the OTHER_XML column from GV$SQL_PLAN
You could manually add that column to SASH_SQLPLANS and add the column to the "sash_pkg.get_sqlplans" procedure - this is a CLOB column, but INSERT / SELECT should work I think
The view SASH_PLAN_TABLE needs also to be modified to select the OTHER_XML column instead of a dummy NULL

Although this output is less readable than the "faked" output that shows only the plan operations that are actually in use, it is the only simple way how ASH/MONITOR data can be related to execution plan lines, as these hold the information with the actual plan line, not the one that is made up by DBMS_XPLAN.DISPLAY* based on the DISPLAY_MAP information in the OTHER_XML column

Hence I decided for the time being to use the same approach as 12c Real-Time SQL Monitoring and always show the full/adaptive shape of the plan

Another challenge for XPLAN_ASH with adaptive plans is the possibly changing PLAN_HASH_VALUE during execution.

XPLAN_ASH extracts the PLAN_HASH_VALUE from ASH/MONITOR when trying to get the plan from DBA_HIST_SQL_PLAN.

Hence XPLAN_ASH now needs to take care to extract the most recent PLAN_HASH_VALUE, previously it didn't matter as it wasn't supposed to change during execution. This seems to work based on my tests, but it's something to keep in mind

- The new "gather stats on load" 12c feature implies for INSERT...SELECT statements that the cursor will immediately be invalidated/removed from the Library Cache after (successful) execution. So now such
INSERT...SELECT behave like CTAS which also gets removed immediately. This is a pity as you won't be able to pick up the plan from the Library Cache after the execution completes using XPLAN_ASH (or any other tool using DBMS_XPLAN.DISPLAY*).

Although V$SQL_PLAN_MONITOR might keep plan for some time after the execution, it can't be used as input to DBMS_XPLAN.DISPLAY*, hence this isn't a viable workaround. In principle however this isn't a good thing as the SQL and plan information might be missing from AWR / STATSPACK reports due to the
immediate invalidation/removal.

At the time being the only viable workaround known to me for this is to prevent the "gather stats on load" feature either via parameter "_optimizer_gather_stats_on_load" or hint "no_gather_optimizer_statistics", or via using pre-12c optimizer feature settings which implicitly disables the feature which is of course not
really a good workaround as the feature itself might be rather desirable

Saturday, March 29, 2014

New Version Of XPLAN_ASH Utility

A minor update 4.01 to the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

These are the notes from the change log:

- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data

- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part of the PX_FLAGS column) on 11.2.0.2+

- Since version 4.0 added from 11.2.0.2 on the PX *MAX* DOP in the "SQL statement execution ASH Summary" based on the new PX_FLAGS column of ASH it makes sense to add a PX *MIN* DOP in the summary to see at one glance if different DOPs were used or not

- The "Active DOPs" column in the "Activity Timeline based on ASH" was extended/modified: The number in parantheses is no longer the simple count of samples but the Average Active Sessions (AAS) per DFO / bucket.

From 11.2.0.2 it now shows also the DOP of the DFO in brackets, so the
output could look now like this:

1[16] (14.5)

which means DFO 1 at a DOP of 16 had an AAS value of 14.5 for this time
bucket. If there are multiple DFOs active in the time bucket, they are
separated by commas:

1[16] (3.5),2[4] (1.5)

which means DFO 1 at a DOP of 16 had an AAS value of 3.5 and DFO 2 at a
DOP of 4 had an AAS value of 1.5 for this time bucket

A new version 4.1 is already underway that includes new 12c features, so stay tuned.

Monday, December 2, 2013

New Version Of XPLAN_ASH Utility

A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post includes links to video tutorials explaining what the tool is about.

As usual the latest version can be downloaded here.

The new version comes with numerous improvements and new features. The most important ones are:

  • Real-Time SQL Monitoring info included
  • Complete coverage including recursive SQL
  • Improved performance
  • 12c compatible
  • Simplified usage

Although I unfortunately haven't managed yet to publish the remaining parts of the video tutorial based on the previous version, I hope now that the new version is out to come up with the most interesting parts soon.

You can watch this short video on my Youtube channel that demonstrates the most important improvements.

Tuesday, February 7, 2012

Nice Additions For Troubleshooting

This is just a short note that Oracle has added several nice details to 11.2.0.1 and 11.2.0.2 respectively that can be helpful for troubleshooting.

ASH, PGA Memory And TEMP Consumption

Since 11.2.0.1 the V$ACTIVE_SESSION_HISTORY view (that requires Enterprise Edition plus Diagnostic License) contains the PGA_ALLOCATED and TEMP_SPACE_ALLOCATED columns.

In particular the latter closes an instrumentation gap that always bothered me in the past: So far it wasn't easy to answer the question which session used to allocate TEMP space in the past. Of course it is easy to answer while the TEMP allocation was still held by a session by looking at the corresponding V$ views like V$SORT_USAGE, but once the allocation was released answering questions like why was my TEMP space exhausted three hours ago was something that couldn't be told by looking at the information provided by Oracle.

So these two columns now should allow to easily analyze PGA memory and TEMP space issues that happened in the past.

Time Model And I/O Delta

Furthermore there are two new classes of columns: TM_DELTA_TIME / TM_DELTA_CPU_TIME / TM_DELTA_DB_TIME as well as the DELTA_TIME plus the remaining DELTA_*_IO_* columns. These allow to analyze which sessions contributed to what extend to the overall Time Model statistics DB_TIME as well as the IO related statistics. Again a nice addition that allows for more sophisticated analysis based solely on ASH data. In the past such analysis was only possible when persisting the session level statistics.

Reasons For Non-Shared Cursors

So far the V$SQL_SHARED_CURSOR view represented a user-friendly output of a bitmask (taken from X$KKSCS) telling why child cursors couldn't be shared, although unfortunately many of the "reasons" are not documented very well. With every release Oracle adds more columns to the view trying to keep up with the ever growing number of reasons why child cursors couldn't be shared.

With now more than 60 possible documented reasons determining why a child cursor wasn't shared becomes more and more cumbersome and can only be done reasonably by using some kind of script that turns this view into some more human-readable format, for example like Dion Cho did here or MOS note "High SQL Version Counts - Script to determine reason(s) ID 438755.1".

In 11.2.0.2 another new column has been introduced: REASON.

This column is in XML format and seems to be an attempt to allow for a more straightforward understanding why a cursor wasn't shared. I don't know if there are any Oracle tools that already process this column, but at present it looks like there is still room for improvement. First of all the XML generated isn't even valid XML, so creating a XMLType based on the contents regularly fails with parsing errors, mainly due to a missing root node of the document in my cases.

But also the generated content seems to be questionable, for example 11.2.0.2 added the new column USE_FEEDBACK_STATS indicating that a new child cursor was deliberately created due to the new Cardinality Feedback feature. Nevertheless the REASON column only shows a generic "OptimizerEnvironmentMismatch" reason. Although this isn't wrong it would helpful if the column at least showed the same level of information that could be told from looking at the individual columns.

Nevertheless the new column seems to be a move into the right direction. In particular it looks like that it not only states the different reasons but also further details for each reason that might allow getting a better understanding why cursors weren't shared. At present these details sometimes look quite cryptic, however time will tell if these will be more user-friendly or someone will come up with further explanations what these mean.

Certainly I've already seen cases where the REASON column explicitly stated optimizer related parameters that were different and hence allowed a straightforward understanding why cursors weren't shared rather than just indicating a Y in one of the remaining columns.

Thursday, November 24, 2011

How To Cancel A Query Running In Another Session

This is not really anything new - in fact Tanel Poder has already blogged about it a while ago. Tanel has specifically covered the handling of "urgent" TCP packets and how this could be used to signal a "cancel" to another process, however this only works on Unix environments and not with Windows SQL*Plus clients. In Tanel's article it is also mentioned that there is an officially documented way of doing this via the Resource Manager if you happen to have an Enterprise Edition license.

In my quick tests however the call to DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS using "CANCEL_SQL" as consumer group only errors out with ORA-29366 saying that the specified consumer group is invalid.

So ideally there should be an approach that is independent from client or server O/S or license details, and indeed there is one, however it is using an undocumented event and therefore is unsupported and can only be used at your own risk.

If you set event 10237 ("ORA-10237: simulate ^C (for testing purposes)") in a session to any level greater 0 then any currently running and future execution will be "cancelled", so once the cancellation was successful the event needs to be unset otherwise the session will be in an unusable state cancelling any further attempts (applies even if the "lifetime 1" clause is used instead of "forever" when using ORADEBUG to set the event).

So a simple script like the following should be sufficient to cancel a current execution in another session without the need to kill the session.


--------------------------------------------------------
--
-- simulate_control_c.sql
--
-- Purpose:
--
-- Sets event 10237 in a session to simulate
-- pressing CONTROL+C for that session
--
-- Allows to cancel a running SQL statement from
-- a remote session without killing the session
--
-- If the session is stuck on the server side
-- which means that it can't be killed this
-- probably won't help either
--
-- Requirements:
--
-- EXECUTE privilege on SYS.DBMS_SYSTEM
-- SELECT privilege on V$SESSION
--
-- Usage:
--
-- @simulate_control_c <SID>
--
-- Note:
--
-- The usage of that event is undocumented
-- Therefore use at your own risk!
-- Provided for free, without any warranties -
-- test this before using it on anything important
--
-- Other implementation ideas:
--
-- The following code is supposed to achieve the same on Enterprise Edition
-- and enabled Resource Manager in a documented way
-- In all versions tested (10.2.0.4, 11.1.0.7, 11.2.0.1, 11.2.0.2) I get however
-- ORA-29366 and it doesn't work as described
-- Note that the official documentation doesn't explicitly mention CANCEL_SQL as
-- valid consumer group for this call

-- begin
-- sys.dbms_resource_manager.switch_consumer_group_for_sess(
-- <sid>,<serial#>,'CANCEL_SQL'
-- );
-- end;
--
-- When running on Unix KILL -URG sent to the server process
-- should also simulate a Control-C
-- This doesn't work with Windows SQL*Plus clients though
--
-- See Tanel Poder's blog post for more info
-- http://blog.tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/
--
-- Author:
--
-- Randolf Geist
-- http://oracle-randolf.blogspot.com
--
-- Versions tested:
--
-- 11.2.0.1 Server+Client
-- 10.2.0.4 Server
-- 11.2.0.2 Server
--
--------------------------------------------------------

set echo off verify off feedback off

column sid new_value v_sid noprint
column serial# new_value v_serial noprint

-- Get details from V$SESSION
select
sid
, serial#
from
v$session
where
sid = to_number('&1')
and status = 'ACTIVE'
;

declare
-- Avoid compilation errors in case of SID not found
v_sid number := to_number('&v_sid');
v_serial number := to_number('&v_serial');
v_status varchar2(100);
-- 60 seconds default timeout
n_timeout number := 60;
dt_start date := sysdate;
begin
-- SID not found
if v_sid is null then
raise_application_error(-20001, 'SID: &1 cannot be found or is not in STATUS=ACTIVE');
else
-- Set event 10237 to level 1 in session to simulate CONTROL+C
sys.dbms_system.set_ev(v_sid, v_serial, 10237, 1, '');
-- Check session state
loop
begin
select
status
into
v_status
from
v$session
where
sid = v_sid;
exception
-- SID no longer found
when NO_DATA_FOUND then
raise_application_error(-20001, 'SID: ' || v_sid || ' no longer found after cancelling');
end;

-- Status no longer active
-- then set event level to 0 to avoid further cancels
if v_status != 'ACTIVE' then
sys.dbms_system.set_ev(v_sid, v_serial, 10237, 0, '');
exit;
end if;

-- Session still active after timeout exceeded
-- Give up
if dt_start + (n_timeout / 86400) < sysdate then
sys.dbms_system.set_ev(v_sid, v_serial, 10237, 0, '');
raise_application_error(-20001, 'SID: ' || v_sid || ' still active after ' || n_timeout || ' seconds');
end if;

-- Back off after 5 seconds
-- Check only every second from then on
-- Avoids burning CPU and potential contention by this loop
-- However this means that more than a single statement potentially
-- gets cancelled during this second
if dt_start + (5 / 86400) < sysdate then
dbms_lock.sleep(1);
end if;
end loop;
end if;
end;
/


It is particularly useful in Windows environments where the SQL*Plus executable by default doesn't allow cancelling a current execution by pressing Control+C - it works only while fetching or pressing it a second time, terminating the whole SQL*Plus client.

Note that Tanel's method is probably able to cancel queries that this approach cannot cancel because the URGENT signal handler under Unix effectively causes an interrupt to the running process executing the corresponding handler code whereas the event set here has to be actively checked by the code of the running process.

Monday, November 14, 2011

Star Transformation And Cardinality Estimates

If you want to make use of Oracle's cunning Star Transformation feature then you need to be aware of the fact that the star transformation logic - as the name implies - assumes that you are using a proper star schema.

Here is a nice example of what can happen if you attempt to use star transformation but your model obviously doesn't really correspond to what Oracle expects:


drop table d;

purge table d;

drop table t;

purge table t;

create table t
as
select
rownum as id
, mod(rownum, 100) + 1 as fk1
, 1000 + mod(rownum, 10) + 1 as fk2
, 2000 + mod(rownum, 100) + 1 as fk3
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000
;

exec dbms_stats.gather_table_stats(null, 't')

create bitmap index t_fk1 on t (fk1);

create bitmap index t_fk2 on t (fk2);

create bitmap index t_fk3 on t (fk3);

create table d
as
select
rownum as id
, case when rownum between 1 and 100 then 'Y' else 'N' end as is_flag_d1
, case when rownum between 1001 and 1010 then 'Y' else 'N' end as is_flag_d2
, case when rownum between 2001 and 2100 then 'Y' else 'N' end as is_flag_d3
, rpad('x', 100) as vc1
from
dual
connect by
level <= 10000
;

exec dbms_stats.gather_table_stats(null, 'd', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 IS_FLAG_D1, IS_FLAG_D2, IS_FLAG_D3');


This is a simplified example of a model where multiple, potentially small, dimensions are stored in a single physical table and the separate dimensions are represented by views that filter the corresponding dimension data from the base table.

So we have a fact table with one million rows and a "collection" dimension table that holds three dimensions, among others.

In order to enable the star transformation bitmap indexes on the foreign keys of the fact table are created.

The dimension table has histograms on the flag columns to tell the optimizer about the non-uniform distribution of the column data.

Now imagine a query where we query the fact table (and possibly do some filtering on the fact table by other means like other dimensions or direct filtering on the fact table) but need to join these three dimensions just for displaying purpose - the dimensions itself are not filtered so the join will not filter out any data.

Let's first have a look at an execution plan of such a simply query with star transformation disabled:


select /*+ no_star_transformation */
count(*)
from
t f
, (select * from d where is_flag_d1 = 'Y') d1
, (select * from d where is_flag_d2 = 'Y') d2
, (select * from d where is_flag_d3 = 'Y') d3
where
f.fk1 = d1.id
and f.fk2 = d2.id
and f.fk3 = d3.id
;

SQL> explain plan for
2 select /*+ no_star_transformation */
3 count(*)
4 from
5 t f
6 , (select * from d where is_flag_d1 = 'Y') d1
7 , (select * from d where is_flag_d2 = 'Y') d2
8 , (select * from d where is_flag_d3 = 'Y') d3
9 where
10 f.fk1 = d1.id
11 and f.fk2 = d2.id
12 and f.fk3 = d3.id
13 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display(format => 'BASIC +ROWS +PREDICATE'));
Plan hash value: 77569906

----------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | HASH JOIN | | 940K|
|* 3 | TABLE ACCESS FULL | D | 100 |
|* 4 | HASH JOIN | | 945K|
|* 5 | TABLE ACCESS FULL | D | 100 |
|* 6 | HASH JOIN | | 950K|
|* 7 | TABLE ACCESS FULL| D | 10 |
| 8 | TABLE ACCESS FULL| T | 1000K|
----------------------------------------------

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

2 - access("F"."FK3"="D"."ID")
3 - filter("IS_FLAG_D3"='Y')
4 - access("F"."FK1"="D"."ID")
5 - filter("IS_FLAG_D1"='Y')
6 - access("F"."FK2"="D"."ID")
7 - filter("IS_FLAG_D2"='Y')


So clearly the optimizer got it quite right - the join to the dimensions is not going to filter out significantly - the slight reduction in rows comes from the calculations based on the histograms generated.

But now try the same again with star transformation enabled:


SQL> explain plan for
2 select /*+ star_transformation opt_param('star_transformation_enabled', 'temp_disable') */
3 count(*)
4 from
5 t f
6 , (select * from d where is_flag_d1 = 'Y') d1
7 , (select * from d where is_flag_d2 = 'Y') d2
8 , (select * from d where is_flag_d3 = 'Y') d3
9 where
10 f.fk1 = d1.id
11 and f.fk2 = d2.id
12 and f.fk3 = d3.id
13 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display(format => 'BASIC +ROWS +PREDICATE'));
Plan hash value: 459231705

----------------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | HASH JOIN | | 9 |
|* 3 | HASH JOIN | | 9 |
|* 4 | HASH JOIN | | 10 |
|* 5 | TABLE ACCESS FULL | D | 10 |
| 6 | TABLE ACCESS BY INDEX ROWID | T | 10 |
| 7 | BITMAP CONVERSION TO ROWIDS| | |
| 8 | BITMAP AND | | |
| 9 | BITMAP MERGE | | |
| 10 | BITMAP KEY ITERATION | | |
|* 11 | TABLE ACCESS FULL | D | 100 |
|* 12 | BITMAP INDEX RANGE SCAN| T_FK1 | |
| 13 | BITMAP MERGE | | |
| 14 | BITMAP KEY ITERATION | | |
|* 15 | TABLE ACCESS FULL | D | 100 |
|* 16 | BITMAP INDEX RANGE SCAN| T_FK3 | |
| 17 | BITMAP MERGE | | |
| 18 | BITMAP KEY ITERATION | | |
|* 19 | TABLE ACCESS FULL | D | 10 |
|* 20 | BITMAP INDEX RANGE SCAN| T_FK2 | |
|* 21 | TABLE ACCESS FULL | D | 100 |
|* 22 | TABLE ACCESS FULL | D | 100 |
----------------------------------------------------------

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

2 - access("F"."FK3"="D"."ID")
3 - access("F"."FK1"="D"."ID")
4 - access("F"."FK2"="D"."ID")
5 - filter("IS_FLAG_D2"='Y')
11 - filter("IS_FLAG_D1"='Y')
12 - access("F"."FK1"="D"."ID")
15 - filter("IS_FLAG_D3"='Y')
16 - access("F"."FK3"="D"."ID")
19 - filter("IS_FLAG_D2"='Y')
20 - access("F"."FK2"="D"."ID")
21 - filter("IS_FLAG_D1"='Y')
22 - filter("IS_FLAG_D3"='Y')


What an astonishing result: Not only Oracle will try now to access all rows of the fact table by single-block random I/O, which by itself can be a disaster for larger real-life fact tables, in particular when dealing with Exadata features like Smart Scans which are only possible with multi-block direct-path reads, but furthermore if this was part of a more complex execution plan look at the cardinality estimates: They are off by five orders of magnitude - very likely a receipt for disaster for any step following afterwards.

The point here is simple: The Star Transformation calculation model obviously doesn't cope with the "collection" of dimensions in a single table very well, but assumes a dimensional model where each dimension is stored in separate table(s). If you don't adhere to that model the calculation will be badly wrong and the results possibly disastrous.

Here the Star Transformation assumes a filtering on dimension tables that are effectively no filter but this is something the current calculation model is not aware of. If you put the three dimensions in separate tables no "artificial" filter is required and hence the calculation won't be mislead.

Of course one could argue that the star transformation optimization seems to do a poor job since the normal optimization based on the same input data produces a much better estimate, but at least for the time being that's the way this transformation works and the model chosen better reflects this.

Tuesday, October 11, 2011

Parallel Downgrade

There are many reasons why a parallel execution might not run with the expected degree of parallelism (DOP), beginning with running out of parallel slaves (PARALLEL_MAX_SERVERS or PROCESSES reached), PARALLEL_ADAPTIVE_MULTI_USER, downgrades at execution time via the Resource Manager, or the more recent features like PARALLEL_DEGREE_LIMIT or the Auto DOP introduced in Oracle 11.2.

However what do you do if you've already checked all these possibilities but still see a downgrade occurring? You can always enable the parallel execution tracing facility (see for example the MOS document ID 444164.1 "Tracing Parallel Execution with _px_trace. Part I" for details how to use it) via the "_px_trace" parameter in the session, and if you see there that parallel slaves are getting acquired but released again immediately then possibly followed by an error message raised then you might want to have a look at the ancient Profile setting SESSIONS_PER_USER. This setting is probably mostly known and used to limit the number of concurrent sessions that a particular user is able to perform, but it is probably forgotten or mostly unknown that this profile setting also will be respected by the parallel execution and each parallel slave started will count towards this limit. Actually up to Oracle 9.2.0.7 you could end up with an ORA-12805 (parallel query server died unexpectedly) error in such a case rather then seeing a downgrade occurring as described in bug 4041253.

So the next time you see an otherwise unexplainable downgrade or think about using the SESSIONS_PER_USER Profile limit, and the user is supposed to make use of Parallel Execution, consider those implications.

Sample px_trace snippet from 10.2.0.5 when downgrading to serial due to SESSIONS_PER_USER Profile setting:

kxfrSysInfo
DOP trace -- compute default DOP from system info
# instance alive = 1 (kxfrsnins)
kxfrDefaultDOP
DOP Trace -- compute default DOP
# CPU = 4
Threads/CPU = 2 ("parallel_threads_per_cpu")
default DOP = 8 (# CPU * Threads/CPU)
default DOP = 8 (DOP * # instance)
kxfrSysInfo
system default DOP = 8 (from kxfrDefaultDOP())
kxfralo
DOP trace -- requested thread from best ref obj = 8 (from kxfrIsBestRef(
))
kxfralo
threads requested = 8 (from kxfrComputeThread())
kxfralo
adjusted no. threads = 8 (from kxfrAdjustDOP())
kxfralo
about to allocate 8 slaves
kxfrAllocSlaves
DOP trace -- call kxfpgsg to get 8 slaves
kxfpgsg
num server requested = 8
num server requested = 8 KXFPLDBL/KXFPADPT/ load balancing:on adaptive:o
n
kxfpiinfo
inst[cpus:mxslv]
1[4:80]
kxfpclinfo
inst(load:user:pct:fact)aff
1(1:0:100:400)
kxfpAdaptDOP
Requested=8 Granted=8 Target=32 Load=1 Default=8 users=0 sets=1
load adapt num servers requested to = 8 (from kxfpAdaptDOP())
kxfpgsg
getting 1 sets of 8 threads, client parallel query execution flg=0x30
Height=8, Affinity List Size=0, inst_total=1, coord=1
Insts 1
Threads 8
kxfpg1sg
q:000007FF4656C058 req_threads:8 nthreads:8 #inst:1 normal
kxfpg1srv
trying to get slave P000 on instance 1 for q:000007FF4656C058
slave P000 is local
found slave P000 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 0 flg:30
free descriptor found dp:000007FF49680318
Allocated slave P000 dp:000007FF49680318 pnum:0 flg:4
Got It. 1 so far.
kxfpg1srv
trying to get slave P001 on instance 1 for q:000007FF4656C058
slave P001 is local
found slave P001 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 1 flg:30
free descriptor found dp:000007FF49680398
Allocated slave P001 dp:000007FF49680398 pnum:1 flg:4
Got It. 2 so far.
kxfpg1srv
trying to get slave P002 on instance 1 for q:000007FF4656C058
slave P002 is local
found slave P002 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 2 flg:30
free descriptor found dp:000007FF49680418
Allocated slave P002 dp:000007FF49680418 pnum:2 flg:4
Got It. 3 so far.
kxfpg1srv
trying to get slave P003 on instance 1 for q:000007FF4656C058
slave P003 is local
found slave P003 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 3 flg:30
free descriptor found dp:000007FF49680498
Allocated slave P003 dp:000007FF49680498 pnum:3 flg:4
Got It. 4 so far.
kxfpg1srv
trying to get slave P004 on instance 1 for q:000007FF4656C058
slave P004 is local
found slave P004 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 4 flg:30
free descriptor found dp:000007FF49680518
Allocated slave P004 dp:000007FF49680518 pnum:4 flg:4
Got It. 5 so far.
kxfpg1srv
trying to get slave P005 on instance 1 for q:000007FF4656C058
slave P005 is local
found slave P005 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 5 flg:30
free descriptor found dp:000007FF49680598
Allocated slave P005 dp:000007FF49680598 pnum:5 flg:4
Got It. 6 so far.
kxfpg1srv
trying to get slave P006 on instance 1 for q:000007FF4656C058
slave P006 is local
found slave P006 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 6 flg:30
free descriptor found dp:000007FF49680618
Allocated slave P006 dp:000007FF49680618 pnum:6 flg:4
Got It. 7 so far.
kxfpg1srv
trying to get slave P007 on instance 1 for q:000007FF4656C058
slave P007 is local
found slave P007 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 7 flg:30
free descriptor found dp:000007FF49680698
Allocated slave P007 dp:000007FF49680698 pnum:7 flg:4
Got It. 8 so far.
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF465615A8 action=1 slave=
0 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46561D68 action=1 slave=
2 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46562148 action=1 slave=
3 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46562CE8 action=1 slave=
4 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF4655FE68 action=1 slave=
5 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46562ED8 action=1 slave=
6 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46560058 action=1 slave=
7 inst=1
kxfpg1sg
got 1 servers (sync), returning...
kxfpgsg
serial - too few slaves alloc'd
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46561988 action=1 slave=
1 inst=1
kxfplsig
signaling OER(10387) in serial 4609

Sunday, May 22, 2011

ASSM bug reprise - part 2

Introduction

In the first part of this post I've explained some of the details and underlying reasons of bug 6918210. The most important part of the bug is that it can only be hit if many row migrations happen during a single transaction. However, having excessive row migrations is usually a sign of poor design, so this point probably can't be stressed enough:

If you don't have excessive row migrations the bug can not become significant

Of course, there might be cases where you think you actually have a sound design but due to lack of information about the internal workings it might not be obvious that excessive row migrations could be caused by certain activities.

One popular feature that might cause such trouble is compression. The most important thing that you need to know about compression is this:

Compression and subsequent significant updates do not work very well together

The main reason for this is that Oracle stores any compressed data after an update as uncompressed (although it is still a "compressed" row/block format the data itself is stored uncompressed). Note that this also holds true for the "advanced" OLTP compression option, as we will see shortly.

Given this fact it might become obvious that updates to compressed data can cause excessive row migrations, because:

- The row data stored in uncompressed format will usually require a lot more space than the original compressed format, hence the row will no longer fit into the place of the block where it originally resided. It needs to be moved somewhere else within the same block (and the block might have to be "re-organized" in order to allow for sufficient contiguous space, which adds CPU overhead to the operation), and if there isn't sufficient space in the block available it will have to be migrated to a different block with sufficient space

- By default the "basic" compression option of Oracle implicitly sets the PCTFREE of the block to 0, however you can change this by explicitly defining a PCTFREE. The "OLTP" compression leaves PCTFREE at the default of 10

These PCTFREE settings, in particular the default of 0 used by "basic" compression, do not leave a lot of free space in the block for further row growth, so it becomes clear that without any non-default PCTFREE settings anything that performs updates to more than just a couple of rows per block of compressed data will lead to row migrations.

Of course you'll appreciate at least with "basic" compression that does not attempt to re-compress the blocks any application updating more than a couple of compressed rows must be called bad design, since it uses the feature in a way that it was not intended for.

Things look differently, from a general point of view at least, with "OLTP" compression since it promises to re-compress the data also during conventional DML and therefore should allow subsequent updates without suffering from too many or even excessive row migrations. Unfortunately, I couldn't confirm this in the tests that I've performed.

Basic Compression

So, let's start with a simple variation of the original test case, by introducing "basic" compression and have a look at the results.

By the way, all the tests have been performed using a 8K ASSM tablespace.


set echo on timing on

drop table t1;

purge table t1;

CREATE TABLE t1
(pkey varchar2(1),
v1 varchar2(255),
v2 varchar2(255)
)
compress
pctfree 0
TABLESPACE &&tblspace;

INSERT /*+ append */ INTO t1
SELECT '1' as pkey,
to_char((mod(rownum, 1) + 1), 'TM') || 'BL' AS v1,
'BLUBB' /*null*/ AS v2
FROM dual CONNECT BY LEVEL <= 50000
/

commit;

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');
END;
/

create index t1_idx on t1 (substr(v1, 1, 1)) TABLESPACE &tblspace;

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

truncate table chained_rows;

analyze table t1 list chained rows;

select count(*) from chained_rows;

column file_no new_value file_no
column block_no new_value block_no

select
dbms_rowid.rowid_relative_fno(rowid) as file_no
, dbms_rowid.rowid_block_number(rowid) as block_no
from
t1
where
rownum <= 1;

alter session set tracefile_identifier = 'before_update';

alter system checkpoint;

alter system dump datafile &file_no block &block_no;

pause Press Enter to continue...

exec mystats_pkg.ms_start

-- Counters not updated in 11g
-- execute snap_kcbsw.start_snap

/*
alter session set tracefile_identifier = 'space_layer';

alter session set events '10320 trace name context forever, level 3';
alter session set events '10612 trace name context forever, level 1';
*/

UPDATE /*+ full(t1) */ t1 SET v2 = v1
where substr(v1, 1, 1) = '1';

/*
alter session set events '10320 trace name context off';
alter session set events '10612 trace name context off';
*/

commit;

set serveroutput on size 1000000 format wrapped
set linesize 120
set trimspool on

-- Counters not updated in 11g
-- execute snap_kcbsw.end_snap

exec mystats_pkg.ms_stop(1)

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');
END;
/

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

truncate table chained_rows;

analyze table t1 list chained rows;

select count(*) from chained_rows;

alter session set tracefile_identifier = 'after_update';

alter system checkpoint;

alter system dump datafile &file_no block &block_no;

accept rdba prompt 'Enter forwarding ROWID found in block dump: '

column rdba new_value rdba

-- Remove any potential leading and trailing unnecessary stuff
select
substr('&rdba',
case
when instr('&rdba', '0x') = 0
then 1
else instr('&rdba', '0x') + 2
end,
case
when instr('&rdba', '.') = 0
then 32767
else instr('&rdba', '.') -
case
when instr('&rdba', '0x') = 0
then 0
else instr('&rdba', '0x') + 2
end
end
) as rdba
from
dual
;

select
dbms_utility.data_block_address_file(to_number('&rdba', rpad('X', length('&rdba'), 'X'))) as file_no
, dbms_utility.data_block_address_block(to_number('&rdba', rpad('X', length('&rdba'), 'X'))) as block_no
from
dual
;

alter session set tracefile_identifier = 'migrated_rows';

alter system dump datafile &file_no block &block_no;


If you look closely at the example you'll notice that from an "innocent" application point of view the update will actually shorten the row in size - but of course only if you do not take into account the effects described above about compressed data.

This is what a sample block dump looks like right after the insert:


.
.
.
data_block_dump,data header at 0x551007c
===============
tsiz: 0x1f80
hsiz: 0x5d6
pbl: 0x0551007c
76543210
flag=-0------
ntab=2
nrow=728
frre=-1
fsbo=0x5d6
fseo=0x113e
avsp=0xc
tosp=0xc
r0_9ir2=0x0
mec_kdbh9ir2=0x1
76543210
shcf_kdbh9ir2=----------
76543210
flag_9ir2=--R----C
fcls_9ir2[4]={ 0 32768 32768 32768 }
0x1e:pti[0] nrow=1 offs=0
0x22:pti[1] nrow=727 offs=1
0x26:pri[0] offs=0x1f71
0x28:pri[1] offs=0x1f6c
0x2a:pri[2] offs=0x1f67
0x2c:pri[3] offs=0x1f62
.
.
.
block_row_dump:
tab 0, row 0, @0x1f71
tl: 15 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 1] 31
col 1: [ 3] 31 42 4c
col 2: [ 5] 42 4c 55 42 42
bindmp: 02 d7 03 c9 31 cb 31 42 4c cd 42 4c 55 42 42
tab 1, row 0, @0x1f6c
tl: 5 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 1] 31
col 1: [ 3] 31 42 4c
col 2: [ 5] 42 4c 55 42 42
bindmp: 2c 00 01 03 00
tab 1, row 1, @0x1f67
tl: 5 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 1] 31
col 1: [ 3] 31 42 4c
col 2: [ 5] 42 4c 55 42 42
bindmp: 2c 00 01 03 00
.
.
.


You'll notice that the rows have been compressed very efficiently (see the "symbol table" as "tab 0") and are just reported to allocate 5 bytes each.

It is quite interesting to note that the rows are actually stored with no gap in between, without compression usually Oracle will allocate at least 9 bytes per row (the minimum row size required for migrated rows) even if the actual row is less than 9 bytes in length. I'm not sure why Oracle does this since it means even more work in case of row migrations. Since Oracle doesn't store more rows in the block than dictated by the minimum row size of 9 bytes even with compression enabled this effect is a bit puzzling. You can tell that from the block header - although there is still physically free space in the block (free space begin offset: fsbo=0x5d6, free space end offset: fseo=0x113e but available space: avsp=0xc) it is reported with almost no space available.

In contrast this is what a block looks like where uncompressed very small rows are stored:


.
.
.
data_block_dump,data header at 0x610a07c
===============
tsiz: 0x1f80
hsiz: 0x5c6
pbl: 0x0610a07c
76543210
flag=--------
ntab=1
nrow=730
frre=-1
fsbo=0x5c6
fseo=0x5d6
avsp=0x10
tosp=0x10
0xe:pti[0] nrow=730 offs=0
0x12:pri[0] offs=0x1f7b
0x14:pri[1] offs=0x1f72
0x16:pri[2] offs=0x1f69
0x18:pri[3] offs=0x1f60
0x1a:pri[4] offs=0x1f57
.
.
.
block_row_dump:
tab 0, row 0, @0x1f7b
tl: 5 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 1] 31
tab 0, row 1, @0x1f72
tl: 5 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 1] 31
tab 0, row 2, @0x1f69
tl: 5 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 1] 31
tab 0, row 3, @0x1f60
tl: 5 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 1] 31
tab 0, row 4, @0x1f57
.
.
.


Although the rows effectively require less than 9 bytes the gap between each row is exactly 9 bytes and the available space corresponds to the free space offsets (fsbo=0x5c6, fseo=0x5d6 and avsp=0x10).

Back to our "basic" compression example: Of course we know that the update decompresses the affected rows which means that they will allocate more than these 5 bytes and hence eventually will cause row migrations. Since the decompressed row size is still very small and below the limits outlined in part 1 we'll run again into the basic bug - the blocks holding the migrated rows can take more rows than ITL entries are available leading to the "free space search" anomaly.

OLTP Compression

Let's change the script and use OLTP compression instead. Additionally we'll give Oracle a little room to manoeuvre so that it should be able to store an uncompressed row and re-compress it when required. I do this by creating and populating the table with PCTFREE 10 but switching to PCTFREE 0 right before the update. Since we do not expect that many row migrations with OLTP compression the bug shouldn't be a problem.


.
.
.
CREATE TABLE t1
(pkey varchar2(1),
v1 varchar2(255),
v2 varchar2(255)
)
compress for all operations
pctfree 10
TABLESPACE &&tblspace;
.
.
.
alter table t1 pctfree 0;

UPDATE /*+ full(t1) */ t1 SET v2 = v1
where substr(v1, 1, 1) = '1';
.
.
.


So the expectation according to the general description of how OLTP compression works would be that whenever Oracle hits the PCTFREE limit it attempts to re-compress the block. Actually a more accurate description is that the compression is supposed to take place whenever Oracle re-organizes the block to coalesce free space in the block to maximize the contiguous chunks of available space. Oracle 10g added a statistic for this called "heap block compress", see e.g. Jonathan Lewis' blog post about the heap block compression.

But if you run this script in 11.1.0.7 base release, the results will not be as expected: The table has the same size afterwards as with "basic" compression, and we apparently still hit the bug - the update still takes millions of session logical reads. In fact, looking at the block dumps and statistics it looks like that no HSC compression has taken place at all, although we see a lot of occurrences of "heap block compress". You can confirm this by looking for statistics beginning with "HSC..." after the update - apart from the "HSC Heap/Compressed Segment Block Changes" no other HSC related statistics show up.

If you follow one of the migrated rows, you'll see that even the rows that have been migrated to a new block are not compressed (and not even stored in compressed block format) - so we hit again the same basic "no ITLs left but still free space" problem in the blocks holding the migrated rows.

If you run the same script in 11.2.0.1 or 11.2.0.2 the results look slightly different: The original blocks are still not re-compressed, so we seem to end up with the same number of migrated rows, but at least the blocks holding the migrated rows get compressed and therefore the resulting segment will be smaller in size. It also interesting to note that the number of ITL entries in compressed blocks seems to be limited to 20 - probably controlled by the parameter "_limit_itl" as Jonathan Lewis recently has found out.

OLTP Compression Block Re-Compression

I have to admit that I really had a hard time convincing Oracle in any of the versions tested to perform a re-compression of a block that wasn't manipulated by inserts (or inserts caused by row migrations). In fact this is the only circumstance where I could see the HSC compression effectively work. Most importantly it doesn't seem to work in my cases when updating rows that grow in size - leading exactly to our problem: Excessive row migrations when updating many rows.

I've tried different variations of DML patterns (small / large transactions, mixtures of insert, deletes, updates etc. among those the most straightforward one - updating a column to the same value which should make re-compression a "no-brainer"), and to me it looks like that OLTP re-compression attempts are only triggered by inserts into a block, updates never seem to trigger a re-compression.

Oracle performs a row migration rather than attempting to re-compress the block. It might be a deliberate design decision since the OLTP compression, apart from the obvious CPU overhead, also seems to be write a full pre-compression block image to undo/redo, since the changes done to the block by the compression apparently can not be represented by discrete change vectors. Therefore minimizing the compression attempts by simply migrating rows when necessary could be a deliberate choice - the undo/redo overhead of the OLTP compression can be significant.

Also, since it is called "OLTP" compression, one could assume that a OLTP workload consists of a general mixture of inserts, updates and deletes and therefore not compressing on updates shouldn't be too relevant. This seems also to be confirmed when reading this section of the "Concepts" guide which could be interpreted that only inserts trigger a re-compression of a block.

ASSM Bug variation #1

Of course, things could be worse, and that's what exactly is going to happen with OLTP compression in 11.1.0.7 base release: Increasing the row length or PCTFREE should prevent the basic bug from happening even with excessive row migrations. So let's repeat the test case, this time using the trick we used in part 1 to prevent the bug: Setting PCTFREE at 50 right before the update - this will mark the blocks with the migrated rows as full long before the ITL slots can become depleted.


.
.
.
alter table t1 pctfree 50;

UPDATE /*+ full(t1) */ t1 SET v2 = v1
where substr(v1, 1, 1) = '1';
.
.
.


But look at the results:


.
.
.
50000 rows updated.

Elapsed: 00:00:24.16.
.
.
STAT..session logical reads 9,641,591
.
.
.


Even worse than before - even longer runtime, even more "session logical reads" and more CPU consumption.

Looking at the blocks holding the migrated rows we can clearly tell that they don't meet the prerequisites for the basic "ITL is full but free space left" problem - so something else must be going on leading to similar symptoms.


.
.
.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.01b.00000767 0x00c015bc.0298.21 --U- 89 fsc 0x0000.002641bf
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
.
.
.
0x5a 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x5b 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
bdba: 0x024000ea
data_block_dump,data header at 0x2ea08bc
===============
tsiz: 0x1740
hsiz: 0xc4
pbl: 0x02ea08bc
76543210
flag=--------
ntab=1
nrow=89
frre=-1
fsbo=0xc4
fseo=0x10a5
avsp=0xfe1
tosp=0xfe1
0xe:pti[0] nrow=89 offs=0
0x12:pri[0] offs=0x172d
0x14:pri[1] offs=0x171a
0x16:pri[2] offs=0x1707
.
.
.


When enabling the debug events 10320 and 10612 you'll notice that there is a similar pattern of "...didnot like..." messages as with the basic bug, but in addition there are other entries that don't show up in the basic case: "Compressable Block...". To me it looks like Oracle actually considers these blocks as candidates for further inserts if they were compressed, but somehow this compression never happens, so the blocks are left in that state and are considered and rejected for inserts over and over again leading to the same symptoms as for the basic bug, but obviously for different reasons.


.
.
.
KDTGSP: seg:0x2400083 mark_full:0 pdba:0x02400085
kdtgsp: calling ktspgsp_cbk w/ kdt_bseg_srch_cbk() on 73997
Enter cbk---------------
[ktspisc]: Rejected: BlockDBA:0x02400085
ktspfsrch: Returns: BlockDBA:0x024000e8
kdt_bseg_srch_cbk: examine dba=10.0x024000e8
kdt_bseg_srch_cbk:Compressable Block dba=10.0x024000e8 avs=832 afs=0 tosp=832 full=0
kdt_bseg_srch_cbk: failed dba=10.0x024000e8 avs=832 afs=0 tosp=832 full=0
ktspfsrch:Cbk didnot like 0x024000e8
ktspfsrch: Returns: BlockDBA:0x024000eb
kdt_bseg_srch_cbk: examine dba=10.0x024000eb
kdt_bseg_srch_cbk:Compressable Block dba=10.0x024000eb avs=832 afs=0 tosp=832 full=0
kdt_bseg_srch_cbk: failed dba=10.0x024000eb avs=832 afs=0 tosp=832 full=0
ktspfsrch:Cbk didnot like 0x024000eb
ktspfsrch: Returns: BlockDBA:0x024000ee
kdt_bseg_srch_cbk: examine dba=10.0x024000ee
kdt_bseg_srch_cbk:Compressable Block dba=10.0x024000ee avs=832 afs=0 tosp=832 full=0
kdt_bseg_srch_cbk: failed dba=10.0x024000ee avs=832 afs=0 tosp=832 full=0
ktspfsrch:Cbk didnot like 0x024000ee
ktspfsrch: Returns: BlockDBA:0x024000f1
kdt_bseg_srch_cbk: examine dba=10.0x024000f1
kdt_bseg_srch_cbk: found dba=10.0x024000f1 avs=3511 afs=0 tosp=3511 full=1
Exit cbk ------
ndba:0x024000f1
.
.
.


This assumption is actually confirmed in various bug descriptions on My Oracle Support. In particular bugs 9341448, 6009614, 9667930, 9708484 and 9667930 seem to apply. Note that there seems to be another related bug 8287680 for inserts. Also document 1101900.1 applies. According to these bug descriptions this variation of the bug is actually fixed in some patch sets (as opposed to the "basic" bug in part 1 that is only fixed in the 11.2 release) - and also a one-off patch (9667930) seems to be available.

It is also interesting to note that this update anomaly is also documented in the "Master Note for OLTP Compression [ID 1223705.1]" on My Oracle Support - search for "Test #5" and review the runtimes mentioned in the table for that test, Scenario #3 / #5.

Note that this problem does not show up if the test case gets repeated with basic compression instead - it will not attempt to re-compress the blocks and therefore can only be affected by the basic variation of the bug.

ASSM Bug variation #2

But, it is not over yet - finally we come to the oddest variation of the bug in 11.1.0.7 base release: Combining basic compression with partitioning can lead to the same symptoms as with the just outlined "re-compression" bug with OLTP compression. Oracle apparently sometimes (it is not always reproducible) starts to consider blocks for re-compression with the same dire results although basic compression gets used.

Again a slight variation of the script gets used: I'll use basic compression with PCTFREE 10 but introduce a very simple partitioning schema. Also the V2 column gets updated to the same value - all this is done to prevent running into the basic bug because the migrated rows will be too large for that.


set echo on timing on

drop table t1;

purge table t1;

CREATE TABLE t1
(pkey varchar2(1),
v1 varchar2(255),
v2 varchar2(255)
)
partition by range(pkey)
(
partition p1 values less than (2),
partition p2 values less than (3)
)
compress
pctfree 10
TABLESPACE &&tblspace;

INSERT /*+ append */ INTO t1
SELECT '1' as pkey,
to_char((mod(rownum, 1) + 1), 'TM') || 'BL' AS v1,
'BLUBB' /*null*/ AS v2
FROM dual CONNECT BY LEVEL <= 50000
/

commit;

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');
END;
/

create index t1_idx on t1 (substr(v1, 1, 1)) TABLESPACE &tblspace;

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

truncate table chained_rows;

analyze table t1 list chained rows;

select count(*) from chained_rows;

column file_no new_value file_no
column block_no new_value block_no

select
dbms_rowid.rowid_relative_fno(rowid) as file_no
, dbms_rowid.rowid_block_number(rowid) as block_no
from
t1
where
rownum <= 1;

alter session set tracefile_identifier = 'before_update';

alter system checkpoint;

alter system dump datafile &file_no block &block_no;

pause Press Enter to continue...

exec mystats_pkg.ms_start

-- Counters not updated in 11g
-- execute snap_kcbsw.start_snap

/* This will generate a huge tracefile
alter session set tracefile_identifier = 'space_layer';

alter session set events '10320 trace name context forever, level 3';
alter session set events '10612 trace name context forever, level 1';
*/

-- Uncomment this to prevent the bug
-- alter system flush shared_pool;

UPDATE /*+ full(t1) */ t1 SET v2 = v2
where substr(v1, 1, 1) = '1';

/*
alter session set events '10320 trace name context off';
alter session set events '10612 trace name context off';
*/

commit;

set serveroutput on size 1000000 format wrapped
set linesize 120
set trimspool on

-- Counters not updated in 11g
-- execute snap_kcbsw.end_snap

exec mystats_pkg.ms_stop(1)

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');
END;
/

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

truncate table chained_rows;

analyze table t1 list chained rows;

select count(*) from chained_rows;

alter session set tracefile_identifier = 'after_update';

alter system checkpoint;

alter system dump datafile &file_no block &block_no;

accept rdba prompt 'Enter forwarding ROWID found in block dump: '

column rdba new_value rdba

-- Remove any potential leading and trailing unnecessary stuff
select
substr('&rdba',
case
when instr('&rdba', '0x') = 0
then 1
else instr('&rdba', '0x') + 2
end,
case
when instr('&rdba', '.') = 0
then 32767
else instr('&rdba', '.') -
case
when instr('&rdba', '0x') = 0
then 0
else instr('&rdba', '0x') + 2
end
end
) as rdba
from
dual
;

select
dbms_utility.data_block_address_file(to_number('&rdba', rpad('X', length('&rdba'), 'X'))) as file_no
, dbms_utility.data_block_address_block(to_number('&rdba', rpad('X', length('&rdba'), 'X'))) as block_no
from
dual
;

alter session set tracefile_identifier = 'migrated_rows';

alter system dump datafile &file_no block &block_no;


Now if you run this script under 11.1.0.7 (base release) for several times, you might be able to spot similar symptoms: The updates takes suddenly very long with the same diagnosis output - in particular the 10320 and 10612 debug events show the same "Compressable Block" lines. Although we don't use advanced compression!

Interestingly the problem in this case can be prevented by invalidating information that is cached in the Shared Pool: ALTERing the table right before the update or brute force flushing the Shared Pool will prevent the problem from showing up. By clearing this information the confusion about re-compressing the blocks seems not to pop up. I have to admit however that I don't have a sound explanation why this invalidation of Shared Pool contents prevents this variation of the bug.

Summary

- Mixing compression with a significant number of updates is a bad idea in general

- OLTP block re-compression seems only to be triggered with inserts - updates will lead to row migrations rather than block re-compression

- Due to that behaviour you can end up with more row migrations than assumed even with OLTP compression

- In 11.1.0.7 (base release) something is going wrong with the re-compression attempts of OLTP compression leading to dire results when row migrations happen and an ASSM tablespace gets used. This is a documented bug, a good starting point is document 1101900.1.

- Using basic compression with partitioning can sometimes lead to the same symptoms as described in those bugs for OLTP compression

You should be very careful when using OLTP compression in release 11.1 and monitor the row migration rate - the database might have to work much harder than expected. You should certainly consider applying the one-off patch 9667930 or a patch set that contains the mentioned bug fixes if you plan to use OLTP compression in 11.1 (and ASSM).

Note that I could not reproduce any of these oddities in 11.2 - apart from the fact that updates never seem to trigger a re-compression. However, some of the bug descriptions found seem to suggest that 11.2 could be affected by some of the mentioned bugs as well.

Monday, May 16, 2011

ASSM bug reprise - part 1

This was meant to be published shortly after my latest quiz night post as an explanatory follow up, but unfortunately I only managed to complete this note by now.

There is a more or less famous bug in ASSM (see bug 6918210 in MOS as well as Greg Rahn's and Jonathan Lewis' post) in versions below 11.2 that so far has been classified as only showing up in case of a combination of larger block sizes (greater the current default of 8K) and excessive row migrations. With such a combination it was reproducible that an UPDATE of the same data pattern residing in an ASSM tablespace caused significantly more work than doing the same in a MSSM tablespace, because apparently ASSM had problems finding suitable blocks to store the migrated rows.

In this post I'll show some diagnosis output of the bug and possible explanations what seems to go wrong under the covers.

Although the bug has been identified a long time ago it is still relevant, because:

- The bugfix is only included in the 11.2 versions. For all other versions supporting ASSM there is no one-off patch available, and it is also not fixed in any of the available patch sets, for example the quite recent 10.2.0.5 patch set still allows to reproduce the issue. This means that obviously a significant code change has been introduced in 11.2 that could not be made available as patch in the previous releases. It also means that as long as you're not yet on 11.2 it is possible to run into the bug

- The bug can show up with any block size as I'll demonstrate shortly, so it is not limited to larger block sizes as outlined in the original descriptions

- In 11.1 in combination with other features like basic or advanced (OLTP, for all operations) compression a variation of the bug is possible to show up even when not meeting the criteria for the original basic bug. In particular the OLTP compression introduced in Oracle 11.1 (and only in 11.1, since in 11.2 the ASSM bug is fixed) can exhibit the bug even in not so obvious cases where the pattern of modification would not necessarily suggest a row migration. This will be covered in a follow-up post

Here is a short demo that allows to reproduce the bug in any block size just by using a very small row size and a PCTFREE of 0. Of course you'll need to use an ASSM tablespace to reproduce the issue. If you use a MSSM tablespace, you'll notice a significant difference in the amount of work performed.

Note that the script uses Adrian Billington's "MY_STATS" package which can be downloaded for free here. It also uses Jonathan Lewis' logical I/O analysis snapper script, which is available from here. Note that the latter script does not give meaningful results from 11.1 on. From 11.1 on you need to use Tanel Poder's "buffer gets profiler" which works a bit differently but allows to show similar information about buffer gets. It is called "bufprof.sql" and is part of Tanel's awesome "tpt_public" scripts collection which can be found here.


set echo on timing on

drop table t1;

purge table t1;

CREATE TABLE t1
(pkey varchar2(1),
v1 varchar2(255),
v2 varchar2(255)
)
nocompress
pctfree 0
TABLESPACE &&tblspace;

-- Modify below MOD(ROWNUM, 1) to control the
-- the number of rows updated
-- MOD(ROWNUM, 1) will update every row
-- MOD(ROWNUM, 2) will update every second row etc.
INSERT /*+ append */ INTO t1
SELECT '1' as pkey,
to_char((mod(rownum, 1) + 1), 'TM') || 'BL' AS v1,
null AS v2
FROM dual CONNECT BY LEVEL <= 50000
/

commit;

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');
END;
/

create index t1_idx on t1 (substr(v1, 1, 1)) TABLESPACE &tblspace;

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

truncate table chained_rows;

analyze table t1 list chained rows;

select count(*) from chained_rows;

-- This is only supported from 11.2 on
/*
select
count(*)
, sys.dbms_compression.get_compression_type(user, 'T1', rowid) as comp_type
from
t1
group by
sys.dbms_compression.get_compression_type(user, 'T1', rowid);
*/

column file_no new_value file_no
column block_no new_value block_no

select
dbms_rowid.rowid_relative_fno(rowid) as file_no
, dbms_rowid.rowid_block_number(rowid) as block_no
from
t1
where
rownum <= 1;

alter session set tracefile_identifier = 'before_update';

-- In 11.2 the block dumps are only showing disk content
-- alter system checkpoint;

alter system dump datafile &file_no block &block_no;

pause Press Enter to continue...

exec mystats_pkg.ms_start

execute snap_kcbsw.start_snap

/* Enabling this will generate a huge tracefile
alter session set tracefile_identifier = 'space_layer';

alter session set events '10320 trace name context forever, level 3';
alter session set events '10612 trace name context forever, level 1';
*/

-- Uncomment this to prevent the bug
-- alter table t1 pctfree 50;

UPDATE /*+ full(t1) */ t1 SET v2 = v1
where substr(v1, 1, 1) = '1';

commit;

set serveroutput on size 1000000 format wrapped
set linesize 120
set trimspool on

execute snap_kcbsw.end_snap

exec mystats_pkg.ms_stop(1)

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');
END;
/

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

truncate table chained_rows;

analyze table t1 list chained rows;

select count(*) from chained_rows;

-- This is only supported from 11.2 on
/*
select
count(*)
, sys.dbms_compression.get_compression_type(user, 'T1', rowid) as comp_type
from
t1
group by
sys.dbms_compression.get_compression_type(user, 'T1', rowid);
*/

alter session set tracefile_identifier = 'after_update';

-- In 11.2 the block dumps are only showing disk content
-- alter system checkpoint;

alter system dump datafile &file_no block &block_no;

accept rdba prompt 'Enter forwarding ROWID found in block dump: '

column rdba new_value rdba

-- Remove any potential leading and trailing unnecessary stuff
select
substr('&rdba',
case
when instr('&rdba', '0x') = 0
then 1
else instr('&rdba', '0x') + 2
end,
case
when instr('&rdba', '.') = 0
then 32767
else instr('&rdba', '.') -
case
when instr('&rdba', '0x') = 0
then 0
else instr('&rdba', '0x') + 2
end
end
) as rdba
from
dual
;

select
dbms_utility.data_block_address_file(to_number('&rdba', rpad('X', length('&rdba'), 'X'))) as file_no
, dbms_utility.data_block_address_block(to_number('&rdba', rpad('X', length('&rdba'), 'X'))) as block_no
from
dual
;

alter session set tracefile_identifier = 'migrated_rows';

-- In 11.2 the block dumps are only showing disk content
-- alter system checkpoint;

alter system dump datafile &file_no block &block_no;


If the bug is hit, then the UPDATE will take several seconds and consume mostly CPU. The statistics will show millions of "session logical reads" for the update of the 50,000 rows.

Up to version 10.2 the output from Jonathan's script will show something similar to this:


SQL> execute snap_kcbsw.end_snap
---------------------------------
Buffer Cache - 15-May 13:11:55
Interval:- 16 seconds
---------------------------------
Why0 Why1 Why2 Other Wait
---- ---- ---- ----------
.
.
.
1,037,665 0 0 0 ktspfwh10: ktspscan_bmb
.
.
.
4,813,907 0 0 0 ktspbwh1: ktspfsrch
.
.
.
---- ---- ---- ----------
6,200,305 0 0 0 Total: 61 rows


Running a stack profile while the update is executing using e.g. Tanel's OSTACKPROF script will show an output similar to this:


Below is the stack prefix common to all samples:
------------------------------------------------------------------------
Frame->function()
------------------------------------------------------------------------
# 43 ->000000007702652D
# 42 ->OracleThreadStart()
# 41 ->opimai()
# 40 ->opimai_real()
# 39 ->sou2o()
# 38 ->opidrv()
# 37 ->opiodr()
# 36 ->opiino()
# 35 ->opitsk()
# 34 ->ttcpip()
# 33 ->opiodr()
# 32 ->kpoal8()
# 31 ->opiexe()
# 30 ->updexe()
# 29 ->updThreePhaseExe()
# 28 ->updaul()
# 27 ->qerupFetch()
# 26 ->updrow()
# 25 ->kauupd()
# 24 ->kdusru()
# 23 ->kduurp()
# 22 ->kdumrp()
# 21 ->kdtInsRow()
# 20 ->kdtgrs()
# 19 ->kdtgsp()
# 18 ->ktspgsp_main()
# 17 ->ktspscan_bmb()
# 16 ->ktspfsrch()
# ...(see call profile below)
#
.
.
.


Tanel's Buffer Gets Profiler (old V1.02 version, the current V1.03 version crashed my 11.1.0.7 SYSDBA session) shows an output similar to this:


SID KCBBFWHY KCBBFSO_FLG TO_CHAR(K KCBBFCM KCBBFSO_OWN DEC KCBWHDES TOTAL_SAMPLES ACTIVE_PCT
---------- ---------- ----------- --------- ---------- ---------------- --- ------------------------------ ------------- ----------
141 0 1 100001 0 000007FF1A3FF8D0 CR kdswh11: kdst_fetch 1000 3160
141 0 1 100001 0 000007FF1E1C7F18 CR kdswh11: kdst_fetch 1000 3160
141 0 1 0 0 000007FF1E1C7F18 CUR ktspbwh1: ktspfsrch 942 2976.72
141 0 1 4000000 2 000007FF1E1C7F18 CUR ktspbwh1: ktspfsrch 41 129.56
141 0 1 0 0 000007FF1E1C7F18 CUR ktspfwh10: ktspscan_bmb 11 34.76
141 0 1 0 0 000007FF1E1C9EE8 CR kdswh11: kdst_fetch 2 6.32
141 0 1 1000 -2.147E+09 000007FF1E1C9EE8 CUR ktuwh02: ktugus 1 3.16
141 0 1 0 2 000007FF1E1C7F18 CUR kduwh01: kdusru 1 3.16
141 0 1 100001 0 000007FF1E1C9EE8 CR kdswh11: kdst_fetch 1 3.16
141 0 1 0 2 000007FF1E1C7F18 CUR ktspbwh1: ktspfsrch 1 3.16


So the function names "ktspfsrch" and "ktspscan_bmb" are common to all diagnosis output. They belong to the internal "kts" space management layer and seem to suggest that Oracle spends most of the time searching for "free space" where to put the migrated row.

So the question is why does Oracle apparently spend so much time searching for a suitable block when migrating a row? A look into the block dump that you get by entering the forwarding ROWID found in the original block after the update (search for "nrid" in the block dump and paste the hex representation of the ROWID at the prompt shown) reveals that Oracle has used up all available ITL slots in the block (for each row migrated into a block a new ITL is allocated) but there is still free space left in the block:


.
.
.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02f.0000045c 0x008011f7.03d6.41 --U- 167 fsc 0x0000.00335363
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
.
.
.
0xa7 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0xa8 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0xa9 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000

data_block_dump,data header at 0x11a5540c
===============
tsiz: 0xff0
hsiz: 0x160
pbl: 0x11a5540c
bdba: 0x0240015a
76543210
flag=--------
ntab=1
nrow=167
frre=-1
fsbo=0x160
fseo=0x38b
avsp=0x22b
tosp=0x22b
.
.
.


Above example is taken from an 8K block. Depending on the block size there is a hard limit on the number of ITL slots since Oracle seems to limit the space allocated by the ITL entries to approx. 50% of the block or 255 entries, whatever is reached first. So we get the following maximum number of ITL slots per block size:

2K: 41 (* 24 bytes = 984 bytes)
4K: 83 (* 24 bytes = 1992 bytes)
8K: 169 (* 24 bytes = 4056 bytes)
16K: 255 (* 24 bytes = 6120 bytes)
32K: 255 (* 24 bytes = 6120 bytes)

As you can see from above, the hard limit of 169 as defined for 8K blocks has been reached (0xA9 = 169), but from a free space "perspective" the block is not full yet (avsp=0x22b, given a PCTFREE of 0).

The basic problem of the bug seems to be that blocks in such a state are still considered as possible candidates for inserts of migrated rows, but have then to be rejected as Oracle recognizes that no further ITL entries can be added. The more blocks get into this state, the harder Oracle has to work to find a block that really can be used for storing the migrated row.

This can also be confirmed by running a trace with event 10320 (Enable data layer (kdtgrs) tracing of space management calls) and 10612 (Prints debug information for auto-space managed segments) enabled. This will generate a huge trace file with contents like the following:


.
.
.
kdt_bseg_srch_cbk: examine dba=10.0x024000e0
kdt_bseg_srch_cbk: failed dba=10.0x024000e0 avs=0 afs=5274 tosp=555 full=1
ktspfsrch:Cbk didnot like 0x024000e0
ktspfsrch: Returns: BlockDBA:0x024000e4
kdt_bseg_srch_cbk: examine dba=10.0x024000e4
kdt_bseg_srch_cbk: failed dba=10.0x024000e4 avs=0 afs=5274 tosp=555 full=1
ktspfsrch:Cbk didnot like 0x024000e4
ktspfsrch: Returns: BlockDBA:0x024000e8
kdt_bseg_srch_cbk: examine dba=10.0x024000e8
kdt_bseg_srch_cbk: failed dba=10.0x024000e8 avs=0 afs=5274 tosp=555 full=1
ktspfsrch:Cbk didnot like 0x024000e8
ktspfsrch: Returns: BlockDBA:0x024000ec
kdt_bseg_srch_cbk: examine dba=10.0x024000ec
kdt_bseg_srch_cbk: found dba=10.0x024000ec avs=7215 afs=0 tosp=7215 full=1
.
.
.


The longer the UPDATE executes, the more "...didnot like..." messages will appear per row to migrate.

Since the problem also shows up when performing for example single-row updates, it can be seen that the number of "db block gets" performed per row increases with the number blocks that are filled with migrated rows and therefore are checked over and over again.

So the question of hitting the bug or not simply depends on the number of rows that can be migrated into a single block before the block is full and no longer considered for inserts. If the number of migrated rows that fit into the block is larger than the maximum number of ITL entries, the problem will show up when using a combination of an ASSM tablespace and a pre-11.2 version.

Of course from the figures above it becomes obvious that the problem can arise only if a migrated row is very small: Up to the 8K block size it needs a migrated row size of less than approx. 23 bytes (excluding the two bytes for the row directory entry), but only if the PCTFREE is set to 0. With larger PCTFREE settings the migrated row needs to be even smaller which is very unlikely since each migrated row already requires to hold the "head ROWID" that takes up 6 bytes, plus the 3 bytes for the column count, lock and flag byte. This means just 13 bytes left for data, which again need to hold a column length byte for every column stored.

This explains also why it is more likely to hit the problem with block sizes larger than 8K since there the maximum number of ITL slots (255) allocate less than 50% of the block and therefore the migrated rows can be larger and yet more than 255 might fit into a single block.

Given this information there are some interesting variations of the test case possible. If you for example increase the PCTFREE of the table right before the UPDATE (by uncommenting the "alter table t1 pctfree 50", depending on the block size a much lower PCTFREE setting will be sufficient to prevent the bug), you still will end up with the same number of rows migrated but the bug doesn't show up simply because less migrated rows fit into each block and therefore the maximum number of ITL slots won't be reached before the block fills up.

Summary

The purpose of this post was to explain the basic underlying principles of the bug which therefore can happen with any block size.

Of course you might now think "so, what?" since it needs quite an exotic setup to reproduce the issue in a default 8K block size (very small rows, PCTFREE close to 0, excessive row migrations) or larger non-default block sizes (16K / 32K) and still quite small migrated rows.

Up to 10.2 you're probably right - that's the end of the story there. However in an upcoming post I'll demonstrate that in 11.1 when throwing other features into the mix like compression and partitioning a nasty variation of the bug can become much more relevant to practical, real-life cases.