Development

Its a wild place to sit in many respects, not least as it raises the observation that Americans are oddly alienated not just from the culture, but the details of their history. Washington and Hamilton are now portrayed as mild and democratic "founding fathers" of our present order. That perspective from the standpoint of our fourth Republic is borderline mad, as each of the Republics have been a radically new form of government that arose under the specific sociological and economic conditions of its era. But none so unusual to today than the first.

Washington himself is best understood as a warlord who took Cinncinatus as his template (as his contemporaries often noted). And I think we fail to note how he looked at war - Washington was noted for pushing for government payment for scalpers. He waged war not just on the British and their proxies but also on American reticent over whiskey taxes. Those Americans, by the way, burnt down homes and tarred and feathered government officials over the taxes. Look up what that entails....

As for Hamilton: an elitist nationalist who called democracy a "disease" and "great beast". He would have deplored nothing so much as MAGA-ism, but also the bizarre managerial liberalism that is hegemonic in the West.

Some observations over lunch, which wasn't half bad...

In the previous part, I forgot to mention one important detail: if you want to export or extract RTSM (Real-Time SQL Monitoring) reports directly in XML format for further analysis, you can simply use the following functions:

• dbms_sql_monitor.report_sql_monitor_xml() or
  dbms_sqltune.report_sql_monitor_xml() — for reports still present in gv$sql_monitor.
• dbms_auto_report.report_repository_detail_xml() — for reports already stored in history (AWR), under dba_hist_reports.

To format these XML reports into TEXT, HTML, or Active HTML, you can use:

dbms_report.format_report(
    report      IN xmltype,
    format_name IN varchar2
)

Setting format_name => 'ACTIVE' produces the Active HTML version.

The Main Topic: How to Parse RTSM XML Reports

Starting with Oracle 19.16, table SQL_MACROs became available, enabling a very elegant way to encapsulate XML parsing logic inside SQL macros.

For convenience, I updated the package PKG_RTSM and added the following SQL macro functions to parse various sections of an RTSM XML report:

• function rtsm_xml_macro_report_info(xmldata xmltype) return varchar2 SQL_MACRO;
• function rtsm_xml_macro_plan_info(xmldata xmltype) return varchar2 SQL_MACRO;
• function rtsm_xml_macro_plan_ops(xmldata xmltype) return varchar2 SQL_MACRO;
• function rtsm_xml_macro_plan_monitor(xmldata xmltype) return varchar2 SQL_MACRO;

(These declarations can be found in the package header in the uploaded source file pkg_rtsm.)

This means you can use them directly in SQL, for example:

select *
from pkg_rtsm.rtsm_xml_macro_plan_ops(:YOUR_XML_REPORT) ops;

Returns the main metadata of the RTSM report, such as:

• sql_id, sql_exec_id, sql_exec_start
• rep_date, inst_count, cpu_cores
• con_name, platform information, optimizer environment
• SQL text, execution statistics, activity samples, and more

In addition, the function exposes two extremely useful columns:

DBMS_REPORT.FORMAT_REPORT(XMLDATA,'TEXT'  )  as RTSM_REPORT_TEXT
DBMS_REPORT.FORMAT_REPORT(XMLDATA,'ACTIVE') as RTSM_REPORT_ACTIVE

These allow you to obtain the formatted TEXT or Active HTML version of the report directly from SQL without extra steps.

This logic is fully visible in the macro implementation in the package body.

Returns essential information about the execution plan, including:

• has_user_tab
• db_version
• parse_schema
• Full (adaptive) PHV and normal final plan hash value (plan_hash_full, plan_hash, plan_hash_2)
• peeked_binds
• xplan_stats
• qb_registry
• outline_data
• hint_usage

This macro extracts the <other_xml> block attached to the root plan operation (id="1").

Returns the full list of plan operations, including:

• operation id, name, options, depth, position
• object information
• cardinality, bytes, cost
• I/O and CPU cost
• access and filter predicates

This essentially exposes the plan as a SQL-friendly dataset.

This is the most important macro for analyzing performance metrics.
It returns all operations from the plan monitor section, together with all runtime statistics, including:

• starts, cardinality, memory usage, temp usage
• I/O operations and spilled data
• CPU and I/O optimizer estimates
• Monitoring timestamps (first_active, last_active)
• Activity samples by class (CPU, User I/O, Cluster, etc.)

Most importantly, it computes:

ROUND(100 * RATIO_TO_REPORT(NVL(wait_samples_total,0)) OVER (), 3)
    AS TIME_SPENT_PERCENTAGE

This is an analogue of “Activity%” in the Active HTML report — showing what percentage of sampled activity belongs to each plan step.

The full implementation, with all xmltable parsing logic, is available in the uploaded code file pkg_rtsm.

You can download the latest version of the package here:

https://github.com/xtender/xt_scripts/blob/master/rtsm/parsing/pkg_rtsm.sql

When you work with a large number of Real-Time SQL Monitor (RTSM) reports in the ACTIVE format (the interactive HTML report with JavaScript), it quickly becomes inconvenient to open them one by one in a browser. Very often you want to load them into the database, store them, index them, and analyze them in bulk.

Some RTSM reports are easy to process — for example, those exported directly from EM often contain a plain XML payload that can be extracted and parsed with XMLTABLE().

But most ACTIVE reports do not store XML directly.
Instead, they embed a base64-encoded and zlib-compressed XML document inside a <report> element.
These reports typically look like this:

<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
  <script ...>
     var version = "19.0.0.0.0";
     ...
  </script>
 </head>
 <body onload="sendXML();">
  <script id="fxtmodel" type="text/xml">
   <!--FXTMODEL-->
   <report db_version="19.0.0.0.0" ... encode="base64" compress="zlib">
     <report_id><![CDATA[/orarep/sqlmonitor/main?...]]></report_id>
       eAHtXXtz2ki2/38+hVZ1a2LvTQwS4pXB1GJDEnYc8ALOJHdrSyVA2GwAYRCOfT/9
       ...
       ffUHVA==
   </report>
   <!--FXTMODEL-->
  </script>
 </body>
</html>

At first glance it’s obvious what needs to be done:

1. Extract the base64 block
2. Decode it
3. Decompress it with zlib
4. Get the original XML <sql_monitor_report>...</sql_monitor_report>

And indeed — if the database had a built-in zlib decompressor, this would be trivial.

Unfortunately, Oracle does NOT provide a native zlib inflate function.

• UTL_COMPRESS cannot be used — it expects Oracle’s proprietary LZ container format, not a standard zlib stream.
• There is no PL/SQL API for raw zlib/DEFLATE decompression.
• XMLType, DBMS_CRYPTO, XDB APIs also cannot decompress zlib.

Because the RTSM report contains a real zlib stream (zlib header + DEFLATE + Adler-32), Oracle simply cannot decompress it natively.

The only reliable way to decompress standard zlib inside the database is to use Java.
A minimal working implementation looks like this:

InflaterInputStream inflaterIn = new InflaterInputStream(in);

InflaterInputStream with default constructor expects exactly the same format that RTSM uses.

I created a tiny Java helper ZlibHelper that inflates the BLOB directly into another BLOB.
It lives in the database, requires no external libraries, and works in all Oracle versions that support Java stored procedures.

Source code: https://github.com/xtender/xt_scripts/blob/master/rtsm/parsing/ZlibHelper.sql

PL/SQL API: PKG_RTSM

On top of the Java inflater I wrote a small PL/SQL package that:

1. Extracts and cleans the base64 block
2. Decodes it into a BLOB
3. Calls Java to decompress it
4. Returns the resulting XML as CLOB
5. Optionally parses it with XMLTYPE

Package here:
pkg_rtsm.sql
https://github.com/xtender/xt_scripts/blob/master/rtsm/parsing/pkg_rtsm.sql

This allows you to do things like:

  xml:=xmltype(pkg_rtsm.rtsm_html_to_xml(:blob_rtsm));

Or load many reports, store them in a table, and analyze execution statistics across hundreds of SQL executions.

Fun fact: with zero qualifications other than interest and some undergrad courses in philosophy and intellectual history (shout out to some stellar professors for stimulating my interest including Ellen Kennedy, Charles Kahn and Alan Kors), I applied to do my doctorate under Searle at Berkeley. Apparently my enthusiasm was insufficient grounds for induction and I was not accepted. Such is life. I did however find Searle's work stimulating for decades after and often raise the Chinese Room argument with colleagues in the AI space.

Memory eternal.

Porcupines everywhere "down east."

The blood of the martyrs is the seed of the Church.

 I sat upon the shore

 There are a handful of living thinkers that have made me re-think fundamental presuppositions that I held consciously (or not) for some time in my early life. Each, in his own way, a genius - but in particular a genius in re-shaping the conceptualization of an intellectual space for me. Until yesterday they were in no particular order, Noam Chomsky, David Bentley Hart, John Milbank, Michael Hudson, Alain de Benoist and Alasdair MacIntyre. We recently lost Rene Girard. Now MacIntyre is no longer with us. His precise analytics, pulling insights from thinkers ranging from Aristotle to Marx, was rarely matched in the contemporary world. The hammer blow that After Virtue was to so many of my assumptions and beliefs is hard to describe - my entire view of the modern project, especially around ethics, was undone. But it was also his wisdom about the human animal and what really mattered in terms of being a human being that set him apart.

For the Oracle performance tuning and troubleshooting Telegram channel https://t.me/ora_perf, I developed a simple helpful Telegram bot. It simplifies common Oracle database tasks directly within Telegram.

Here’s what the bot can do:

• /faq add: Reply with !faq add {description} to save a message.
• /faq list: Lists all FAQ articles.
• /faq list [search words]: Search FAQ by keywords.
• /faq {N}: Shows FAQ article number N.
• /faq replace {N}: Updates FAQ article N.

/oerr: Shows details of Oracle errors

/oerr ora 29024

29024, 00000, "Certificate validation failure"
// *Cause:  The certificate sent by the other side could not be validated. This may occur if
//          the certificate has expired, has been revoked, or is invalid for another reason.
// *Action: Check the certificate to determine whether it is valid. Obtain a new certificate,
//          alert the sender that the certificate has failed, or resend.

/param: Finds Oracle parameters by mask.

/param %cnt%

  PAR_N  NAME                                 VAL_N  VALUE    IS_DEFAULT
-------  ---------------------------------  -------  -------  ------------
   5231  _approx_cnt_distinct_gby_pushdown        1  OFF      FALSE
   5231  _approx_cnt_distinct_gby_pushdown        2  FORCE    FALSE
   5231  _approx_cnt_distinct_gby_pushdown        3  CHOOSE   FALSE

/hint: Lists Oracle hints by mask

/hint 19

/fix_control: Lists fix controls by keyword and version.

/fix_control count 8.1

   BUGNO    VALUE  SQL_FEATURE                      DESCRIPTION                                                       OFE         EVENT  DEF
--------  -------  -------------------------------  ----------------------------------------------------------------  --------  -------  -----
16954950        1  QKSFM_ACCESS_PATH_16954950       take into account empty partitions when prorating cost            12.1.0.2        0  Y
 3120429        1  QKSFM_ACCESS_PATH_3120429        account for join key sparsity in computing NL index access cost   10.1.0.3        0  Y
 6897034        1  QKSFM_ACCESS_PATH_6897034        index cardinality estimates not taking into account NULL rows     10.2.0.5        0  Y
 9456688        1  QKSFM_ACCESS_PATH_9456688        account for to_number/to_char cost after temp conversion          11.2.0.2        0  Y
14176203        1  QKSFM_CARDINALITY_14176203       Account for filter sel while computing join sel using histograms  11.2.0.4        0  Y
14254052        1  QKSFM_CARDINALITY_14254052       amend accounting for nulls in skip scan selectivity calculation   11.2.0.4        0  Y
16486095        1  QKSFM_CARDINALITY_16486095       Do not count predicate marked for no selectivity                  12.2.0.1        0  Y
23102649        1  QKSFM_CARDINALITY_23102649       correction to inlist element counting with constant expressions   12.2.0.1        0  Y
11843512        1  QKSFM_CBO_11843512               null value is not accounted in NVL rewrite                        11.2.0.3        0  Y
 1403283        1  QKSFM_CBO_1403283                CBO do not count 0 rows partitions                                8.1.6       10135  Y
22272439        1  QKSFM_CBO_22272439               correction to inlist element counting with bind variables         12.2.0.1        0  Y
25090203        1  QKSFM_CBO_25090203               account for selectivity of non sub subquery preds                 18.1.0          0  Y
 5483301        1  QKSFM_CBO_5483301                Use min repeat count in freq histogram to compute the density     10.2.0.4        0  Y
 5578791        1  QKSFM_CBO_5578791                do not discount branch io cost if inner table io cost is already  11.1.0.6        0  Y
 6694548        1  QKSFM_CBO_6694548                Account for chained rows when computing TA by ROWID from bitmap   10.2.0.4        0  Y
27500916        1  QKSFM_COMPILATION_27500916       only count one with clause reference from connect by              19.1.0          0  Y
10117760        1  QKSFM_CURSOR_SHARING_10117760    cardinality feedback should account for bloom filters             11.2.0.3        0  Y
 9841679        1  QKSFM_CVM_9841679                do not set col count for OPNTPLS                                  11.2.0.3        0  Y
26585420        1  QKSFM_DBMS_STATS_26585420        cap approx_count_distinct with non nulls                          18.1.0          0  Y
17760686        1  QKSFM_DYNAMIC_SAMPLING_17760686  Account for BMB blocks when dynamic sampling partitioned ASSM ta  12.1.0.2        0  Y

This bot helps streamline database maintenance and troubleshooting tasks. Join ora_perf to try it and share your feedback!

There is a common misconception that partition pruning does not help in the case of global indexes and only works with local indexes or full table scans (FTS).

It is understandable how this misconception arose: indeed, when operations like PARTITION RANGE ITERATOR, PARTITION RANGE SINGLE, etc., appear in execution plans, partition pruning becomes strongly associated with local indexes and FTS.

It is also clear why this is the most noticeable case: the exclusion of partitions in PARTITION RANGE ITERATOR operations is hard to miss, especially since there is a dedicated line for it in the execution plan.

However, this is not all that partition pruning can do. In fact, this way of thinking is not entirely valid, and I will demonstrate this with some simple examples.

Let’s assume we have a table tpart consisting of 3 partitions with the following columns:

• pkey – the partitioning key. For simplicity, we will store only 1, 2, and 3, with each value corresponding to a separate partition.
• notkey – a column for testing the global index, filled sequentially from 1 to 3000.
• padding – a long column used to make table block accesses more noticeable.

create table tpart (pkey int, notkey int,padding varchar2(4000))
  partition by range(pkey)
  (
     partition p1 values less than (2)
    ,partition p2 values less than (3)
    ,partition p3 values less than (4)
  );

We insert 3,000 rows into the table so that:

• Partition p1 contains 1,000 rows with pkey=1 and notkey values from 1 to 1000.
• Partition p2 contains 1,000 rows with pkey=2 and notkey values from 1001 to 2000.
• Partition p3 contains 1,000 rows with pkey=3 and notkey values from 2001 to 3000.

insert into tpart(pkey,notkey,padding)
  select ceil(n/1000) pkey, n, rpad('x',4000,'x') from xmltable('1 to 3000' columns n int path '.');
commit;

SQL> select pkey,min(notkey),max(notkey) from tpart group by pkey order by 1;

      PKEY MIN(NOTKEY) MAX(NOTKEY)
---------- ----------- -----------
         1           1        1000
         2        1001        2000
         3        2001        3000

We start with a simple query that applies partition pruning. It should return nothing, since notkey values between 2500-2600 are in the third partition (pkey=3), but we explicitly specify pkey=1:

select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;

SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;

  COUNT(*)
----------
         0

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  0qzh3zxgpc65z, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=1

Plan hash value: 3052279832

-------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |      1 |        |      1 |00:00:00.01 |    1007 |
|   1 |  SORT AGGREGATE         |       |      1 |      1 |      1 |00:00:00.01 |    1007 |
|   2 |   PARTITION RANGE SINGLE|       |      1 |      1 |      0 |00:00:00.01 |    1007 |
|*  3 |    TABLE ACCESS FULL    | TPART |      1 |      1 |      0 |00:00:00.01 |    1007 |
-------------------------------------------------------------------------------------------

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

   3 - filter(("NOTKEY">=2500 AND "NOTKEY"<=2600 AND "PKEY"=1))

Here, we can clearly see that partition pruning worked due to the PARTITION RANGE SINGLE operation.

Now, let’s create a global index on notkey (without including the partitioning key) and repeat the query:

SQL> create index ix_tpart on tpart(notkey);

Index created.

SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;

  COUNT(*)
----------
         0

1 row selected.

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
SQL_ID  0qzh3zxgpc65z, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=1

Plan hash value: 494535298

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |          |      1 |        |      1 |00:00:00.01 |       2 |      1 |
|   1 |  SORT AGGREGATE                             |          |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART    |      1 |      1 |      0 |00:00:00.01 |       2 |      1 |
|*  3 |    INDEX RANGE SCAN                         | IX_TPART |      1 |    102 |    101 |00:00:00.01 |       2 |      1 |
---------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PKEY"=1)
   3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)

Here’s the key observation: the Buffers column for the second row remains 2, meaning there were no logical reads from the table despite that the execution plan suggests that here we had to access and filter out 101 rows from the partition by the filter predicate “filter(PKEY=1)”.

To verify, let’s run the same query with pkey=3:

select count(*) from tpart where notkey between 2500 and 2600 and pkey=3

Plan hash value: 494535298

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |          |      1 |        |      1 |00:00:00.01 |     103 |
|   1 |  SORT AGGREGATE                             |          |      1 |      1 |      1 |00:00:00.01 |     103 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART    |      1 |    102 |    101 |00:00:00.01 |     103 |
|*  3 |    INDEX RANGE SCAN                         | IX_TPART |      1 |    102 |    101 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PKEY"=3)
   3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)

Here, it is clearly visible that accessing 101 rows from the table required 101 logical reads.

Overall, it immediately becomes apparent that in the previous example with pkey=1, partition pruning worked and helped us avoid approximately 100 LIO to partition blocks. However, to make this even more evident, let’s modify the predicate "pkey=1" to a more complex equivalent that disables partition pruning: pkey=pkey/pkey.

SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=pkey/pkey;

  COUNT(*)
----------
         0

1 row selected.

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  0q59p4akv4cm0, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=pkey/pkey

Plan hash value: 4115825992

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |          |      1 |        |      1 |00:00:00.01 |     103 |
|   1 |  SORT AGGREGATE                             |          |      1 |      1 |      1 |00:00:00.01 |     103 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART    |      1 |     34 |      0 |00:00:00.01 |     103 |
|*  3 |    INDEX RANGE SCAN                         | IX_TPART |      1 |    102 |    101 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PKEY"="PKEY"/"PKEY")
   3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)

And voilà! Here we see 101 logical reads from the table in exactly the same query but with partition pruning disabled.

For clarity, let’s compare:

select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;         = 2 LIO
select count(*) from tpart where notkey between 2500 and 2600 and pkey=pkey/pkey; = 103 LIO

In fact, this could have been easily noticed if I had used format=ALL or ADVANCED, or at least included +predicate (that’s why I always suggest to use format=>’advanced -qbregistry’):

SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=2;

  COUNT(*)
----------
         0

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate +partition');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fw7yx554pvv4n, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=2

Plan hash value: 494535298

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |          |      1 |        |       |       |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE                             |          |      1 |      1 |       |       |      1 |00:00:00.01 |       2 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART    |      1 |      1 |     2 |     2 |      0 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                         | IX_TPART |      1 |    102 |       |       |    101 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PKEY"=2)
   3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)

As you can now easily see, the Pstart/Pstop columns appear in the plan. This indicates that partition pruning works in the TABLE ACCESS BY ROWID operation, as Oracle determines which partition a row belongs to based on its ROWID and automatically filters out those that do not satisfy our partition key condition.

At this point, it becomes clear why the title of this blog post is somewhat misleading—the key issue is not the global index but rather the TABLE ACCESS BY ROWID operation. This can be demonstrated in an even simpler way:

Let’s obtain the ROWID and object number for a row where notkey=2300:

SQL> select rowid,dbms_rowid.rowid_object(rowid) obj# from tpart where notkey=2300;

ROWID                    OBJ#
------------------ ----------
AAAU42AAMAAB9eNAAA      85558

Now, let’s query using this ROWID:

SQL> select pkey,notkey from tpart where rowid='AAAU42AAMAAB9eNAAA';

      PKEY     NOTKEY
---------- ----------

         3       2300

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate +partition');


-------------------------------------
select pkey,notkey from tpart where rowid='AAAU42AAMAAB9eNAAA'

Plan hash value: 2140892464

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |      1 |        |       |       |      1 |00:00:00.01 |       1 |      1 |
|   1 |  TABLE ACCESS BY USER ROWID| TPART |      1 |      1 | ROWID | ROWID |      1 |00:00:00.01 |       1 |      1 |
-----------------------------------------------------------------------------------------------------------------------

We see that Oracle determines from the ROWID which specific partition needs to be accessed, confirming that our row is in the partition where pkey=3.

Now, let’s add the predicate "pkey=1", which will make the row not satisfy the partition key condition:

SQL> select * from tpart where rowid='AAAU42AAMAAB9eNAAA' and pkey=1;

no rows selected

SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate +partition');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  1br7yh35w3sdv, child number 0
-------------------------------------
select * from tpart where rowid='AAAU42AAMAAB9eNAAA' and pkey=1

Plan hash value: 3283591838

----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |      1 |        |       |       |      0 |00:00:00.01 |
|*  1 |  TABLE ACCESS BY USER ROWID| TPART |      1 |      1 |     1 |     1 |      0 |00:00:00.01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("PKEY"=1)

Here, it becomes absolutely clear that no logical reads occurred because partition pruning worked, and Oracle did not access the third partition. Instead, it checked the partition key condition and immediately rejected the rowid without accessing any blocks.

Thus, the key takeaway is not about indexes at all, but rather about TABLE ACCESS operations. Partition pruning can still be effective within TABLE ACCESS operations—even without any indexes or explicit PARTITION operations (PARTITION [RANGE|HASH|LIST] [ITERATOR|ALL|SINGLE], etc.).

This works because Oracle only needs to check which partition a given ROWID belongs to in order to filter out unnecessary partitions efficiently.

PS. Read more about non-partioned indexes and partition pruning in details in this great series from Richard Foote: https://richardfoote.wordpress.com/2018/10/09/hidden-efficiencies-of-non-partitioned-indexes-on-partitioned-tables-part-ii-aladdin-sane/

This old, old technique of barrel curing tobacco in Louisiana - making perique - just produces some of the most interesting fig-meets-plum-meets-pepper tastes imaginable. Blend that with some bright virginias and there's something super special that results. 

Rarely do I want to purchase every book a publisher puts out. But there are exceptions to every rule.

 OK, so someone asked me why I had not included three bands in my Underground Americana post that seemed to fit the Americana profile: Wayfarer, Blackbraid and Wolves in the Throne Room. I guess the easiest answer is because they have had enough mainstream exposure that I didn't think of them as "underground." I'll say this about both Wayfarer and Blackbraid - their music is more polished and less experimental than the three bands I listed, but its probably fair to say that they are still far from the collective consciousness of American music listeners, so I will in fact take a moment to comment on them all in response.

Wayfarer - in some ways these guys are analogous to Grave Pilgrim: both bands take up themes in American history, though Wayfarer is more directly trying to evoke a kind of spirit of the "Old West." If you haven't heard them, they pull in influences that range from atmospheric black metal, sludge, to the alt country-ish "Denver sound." The album you want to listen to is American Gothic. I mean, its a really, really solid and original work - I like it more than their previous albums and even more so as an ex Denver resident. So, yes, they should get a mention. Wayfarer, by the way, is *way* more polished than Grave Pilgrim, so if you were put off by the rawness of the album I posted a link to, this is much different music stylistically (Grave Pilgrim remains one of my favorite rock bands recording today).

That brings me to Blackbraid. This is a one man act from the Adirondacks, which combines hard charging early black metal influences with native American themes and an attempt to evoke the surrounding landscapes of upstate NY. Its like a band made especially for me - I used to solo camp, trout fish and grouse hunt the Adirondacks in my earlier days as often as I could. The whole region has a special feel and I'd be happy if I was stuck there in a cabin for many months at a time. I don't listen to metal albums often, but Blackbraid II has probably been the one I have played on repeat more than anything else for a long while. Personal favorite: The Wolf that Guides the Hunters Hand. His cover of Bathory's A Fine Day to Die is better than the original - that may or may not be saying a lot depending on your point of view, but its a cool cover. By the way, you can't compare them to Pan Native American Front, the take on native American experience is completely different, so is the music.

Lastly, Wolves in the Throne Room. I realize they get credit for pushing local acts to try to express the Cascadian landscape sonically, but there music is consistently barely listenable, the occultish themes they weave in are just stupid, and to add insult to injury everyone I have known that has seen them live has said the shows are terrible. So the reason I didn't highlight them is simple: I don't think much of them and don't understand their appeal. Two Hunters is their best album, though.

 C Рождеством Христовым! Славим Его!

 Some new books to read for the Feast.