Mauro Pagano's Blog

Leave a comment

Little help in creating SQL Plan Baselines

One of the main design goals behind SQLd360 is to have no installation nor “evidence” left in the database, i.e. there is no SQLd360 repository in the database while there is a SQLTXPLAIN one (this isn’t necessarily bad, it’s just a different approach).

As a consequence several little things SQLT provided are gone with SQLd360, for example few years ago (it’s been disabled by default for a while) SQLT generated a script to create a “custom” SQL Profile for the best performing plan it identified, something similar was happening for SQL Plan Baselines stored in SQL Tuning Set for quick implementation of baselines down the road.

There is a new standalone script shipped with SQLd360, sql/sqld360_create_sql_baseline.sql, that aims at providing the same functionality of SPM+STS provided by SQLT, using the idea of “no evidence left by default”.
The script is NOT executed as part of SQLd360, it can (and needs to) be manually executed at any time, which in turns means there is no need to run SQLd360 for this script to work.
The script requires three parameters:

  • SQL ID for the SQL of interest
  • Oracle Pack available in this database ([N]one, [D]iagnostic or [T]uning)
  • Plan Hash Value you wish to “freeze”

You can provide the parameters directly inline to SQL*Plus (assuming you know the PHV you want to enforce) or just enter them one by one when prompted, the script will list all the PHV it can find around (memory + history) with their performance, helping you in case you don’t recall which PHV you want.

The script does just two things:

  1. It create a SQL Tuning Set (named s_<<SQLID>>_<<PHV>>) and loads the plan details into it, the goal is to “freeze” it in time so info don’t get lost if the plan is aged out of memory, purged from AWR, etc.
  2. Provide copy&paste instructions to create a Baseline based on the plan in the STS either in the current system or in a remote one. The script DOES NOT execute such steps, only prints them at screen. This way you can read, understand, digest and validate them before YOU execute them.

Hopefully it will make it a little bit easier to play with SPM.

Feedbacks, correction, recommendations are welcome as usual!

Leave a comment

Presentations on Slideshare

Every once in a while I get asked if I can email the PPT for a session that I delivered. I always say YES (of course) so I figure why not be proactive and upload the material fot the presentations I delivered over the last several months. Under the “Pages” section on the right side of the page there is a new link “Presentations” that takes you to Slideshare.

It’s my first experience with Slideshare and I’m pretty sure I made mistakes along the way so if you see something wrong just let me know (and let me know how to fix it PLEASE 🙂 )

The list of presentation is probably incomplete so if you attended one and see that I forgot to upload it just let me know and I’ll fix that. Also every session comes with trace files / dumps / testcases built to support the investigations but I found no easy to way upload them so I’d still rely on the old “provided upon request, via email” for them.



Truncated CTAS text and SQL Plan Baselines

This is probably not earth-shattering (not that I ever blog earth-shattering things anyway) for many but it does answer a question I got today about “Do you think a truncated SQL text for CTAS affects SPM ability to give me the desired plan?”.

SQL text for CTAS is truncated as result of bug 17982832 (sister bugs 18705302 and20308798 affect 10046 and AWR respectively) but does this affect SPM? Can SPM match on the truncated text? Or maybe can SPM see the whole text and match on the original SQL? Those are the questions I wanted to answer.

As usual a test is worth a thousand expert opinions so here it goes:

SQL> create table test_shane as select * from dba_objects;
SQL> select sql_id, exact_matching_signature, sql_text from v$sql where sql_id = '30ywyzwvy6cqy';
------------- ---------------------------- ------------------------
30ywyzwvy6cqy	       6988945084141899327 create table test_sh

SQL text is truncated from above output and the signature (exact since baselines don’t do force_matching) is 6988945084141899327. Let’s create a baseline now

SQL> var n1 number
SQL> exec :n1 := dbms_spm.load_plans_from_cursor_cache('30ywyzwvy6cqy');
SQL> print :n1
SQL> select signature, sql_text from dba_sql_plan_baselines;
------------------------- ---------------------------------------
     15291506816473784520 create table test_sh

The baseline has been created with signature 15291506816473784520 which is different than the one from V$SQL so it sounds like the baseline used the wrong text to create the signature (or V$SQL was wrong and SPM was smarter, which is unlikely). We can verify it using DBMS_SQLTUNE API to compute the signature of both SQL texts, original and truncated.

SQL> select dbms_sqltune.sqltext_to_signature('create table test_sh') signature from dual;
SQL> select dbms_sqltune.sqltext_to_signature('create table test_shane as select * from dba_objects') signature from dual;

So V$SQL was right while SPM kind of used (I’m saying “kind of” because tracing the call to DBMS_SPM it seems like a different way is used to pull up the signature) the truncated text to compute and store the incorrect signature and as a consequence the baseline won’t be used because the correct signature won’t find any match in the SMB.
From 10053 from a hard parse

SPM: statement not found in SMB

Testing it in 12.2 where the SQL text is intact the baseline is created using the proper signature

----------------------- ------------------------------------------------------
    6988945084141899327 create table test_shane as select * from dba_objects

   - SQL plan baseline SQL_PLAN_61zdshtmgv3jzd9546056 used for this statement

… and the baseline is correctly used 🙂


Something new about SQL Plan Directives and 12.2

SQL Plan Directives (SPD) remind me of bind peeking about 10 years ago, a nice feature on paper that worked fine most of the times but caused some major headache on those occasions when it didn’t. Luckily for bind peeking social media wasn’t that popular 10y ago so it took a while to “make a name” for a troublemaking feature, nowadays a couple of blog posts and many re-tweets to get pretty popular, poor SPD!
DISCLAIMER: I happen to like a lot both features and think positively of them, I just admit the “transparent” behavior was a little too ambitious 😦

Just like for bind peeking (Adaptive Cursor Sharing), Oracle introduced some changes in 12.2 to mitigate the effect of SQL Plan Directives and Adaptive Dynamic Sampling (DS). Parameter OPTIMIZER_ADAPTIVE_FEATURES has been split in two, basically turning SPD off via parameter OPTIMIZER_ADAPTIVE_STATISTICS,  as Franck Pachot already explained here.

The goal of this post is look into what happens when we manually turn on OPTIMIZER_ADAPTIVE_STATISTICS. Focus will be on SQL Plan Directives alone instead of throwing DBMS_STATS in the mix too (especially since fix for bug 21171382 disables automatic column group creation based on info from SPD).

Let’s create a table with strong correlation across columns and see how 12.2 SPD behaves differently than 12.1

drop table tab1 purge;
create table tab1 (n1 number, n2 number, n3 number);
insert into tab1 select mod(rownum, 100), mod(rownum, 100), mod(rownum, 100) from dual connect by rownum <= 100000;
exec dbms_stats.gather_table_stats(user,'TAB1');

Table TAB1 has 100k rows with three numeric columns, all storing the same exact value and I’ll use the following SQL referencing all the three columns to lead the CBO into a trap (caused by lack of column group on the three columns).

select count(*) from tab1 where n1 = 1 and n2 = 1 and n3 = 1;

Let’s run the SQL a couple times flushing SPD and shared_pool after each execution just so that each change is persisted immediately and we can see the impact.

Run the SQL the first time and from DBA_SQL_PLAN_DIRECTIVES we have:

TYPE                    STATE      REASON                               NOTES 
----------------------- ---------- ------------------------------------ -------------------------------------------------------
                                                                         <spd_text>{EC(MPAGANO.TAB1)[N1, N2, N3]}</spd_text> 

Same result from both 12.1 and 12.2, nothing too outstanding happens behind the curtains either beside the recursive SQLs related to SPD now have a nice “/* QOSD */” comment in there so they are easier to spot.

Run the SQL again, the CBO recognizes it doesn’t have sufficient info to make a good decisions thus ADS is triggered and SPD <internal_state> is updated to MISSING_STATS, pretty much same behavior in both 12.1 and 12.2 except in 12.2 there is no RESULT_CACHE hint in the DS SQL.
This is where things get interesting, in 12.2 there is one additional directive created to store the result of DS:

TYPE                    STATE      REASON                               NOTES
----------------------- ---------- ------------------------------------ ----------------------------------------------------------------------------------------------------
                                                                         <spd_text>{(MPAGANO.TAB1, num_rows=100000) - (SQL_ID:7pjdz422db2xk, T.CARD=1000[-2 -2])}</spd_text>

which allows the result not to be flushed out and survive a restart of the database, thus reducing the amount of DS SQL executed.

The spd_text provides info about the original number of rows (100k) and the number of rows returned by DS (1k), and it includes a SQL ID (7pjdz422db2xk) that sounds like the DS one, even though it’s just a slight variation of it, let’s look into it.
The recursive DS SQL executed is 0nkvqpzha4x88

PARSING IN CURSOR #140370085840592 len=301 dep=1 uid=106 oct=3 lid=106 tim=588259148047 hv=3768743176 ad='73b99738' sqlid='0nkvqpzha4x88'
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "TAB1") */ 1 AS C1 FROM "TAB1" "TAB1" WHERE ("TAB1"."N1"=1) AND ("TAB1"."N2"=1) AND ("TAB1"."N3"=1)) innerQuery

but the fix for 22760704  says “Generate ADS stmt cache key without hints to make it determinist”, which makes it safe to guess the SQL ID stored is a stripped down version of the original SQL text, with the goal of increasing the chance of matching it back when retrieving info from the SPD DS result.

It seems the CBO tries to find DS result using the SQL text of the DS SQL that it would like to execute (SQL ID 0nkvqpzha4x88)

SPD: qosdGetFObjKeyFromStmt: sqlText = SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel  */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "TAB1")  */ 1 AS C1 FROM "TAB1" "TAB1" WHERE ("TAB1"."N1"=1) AND ("TAB1"."N2"=1) AND ("TAB1"."N3"=1)) innerQuery (objid = 8846687189427653554)
>> Single Tab Card adjusted from 0.100000 to 1000.000000 due to adaptive dynamic sampling

but internally it modifies the SQL to search for such stripped down version without the hints in the external query block, this is visible via trace[ADS]

kkoadsComputeSqlid: sql_id=8846687189427653554: newText=SELECT /* DS_SVC */  NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "TAB1")  */ 1 AS C1 FROM "TAB1" "TAB1" WHERE ("TAB1"."N1"=1) AND ("TAB1"."N2"=1) AND ("TAB1"."N3"=1)) innerQuery, startPos = 20, stopPos = 120, newTextLen = 203, sqlLen = 302

and this is the SQL_ID 7pjdz422db2xk stored in the SPD.

It’s too early to know if these changes will address the existing concerns, especially since many sites will run with this feature disabled (by default) but I really like the way the “new” SPD seems to work!!

In summary the most notable changes in 12.2 seems to be:

  • SPD still triggeres ADS but the result is stored inside SPD itself and persisted
  • Search in the SPD repository for DS results are made using a key that represent (a cleaned version of) the DS SQL that generated the result in the first place

As usual, feedbacks / corrections / additions are very much welcome!


Leave a comment

What I forgot and had to relearn about DESC indexes

The title for this blog post is probably more catchy than the post itself but the election is close so every sort of campaign is allowed, right?
This post is another one of those “I knew it, but I forgot and got bitten back” blog post so hopefully next time I see it I’ll be quicker in recognizing such behavior.

The goal of the SQL is to quickly return the Top-N rows  that match some filter condition(s), descendingly sorted by one of such columns. Pretty common requirement if you consider the filter/sort column to be a date one (“give me the last day worth of transactions, starting with the most recent ones”) and many people would solve using a DESC index on the date column.

drop table t purge;

create table t (pk number, owner varchar2(128), object_id number, data_object_id number, created date, object_name varchar2(128), object_type varchar2(23), large_column clob, random_stuff varchar2(20));

insert into t 
 select /*+ LEADING(A) */ rownum pk, a.owner, a.object_id, a.data_object_id, 
        (sysdate-31)+(rownum/65000) created, a.object_name, a.object_type,  
        to_clob(null) large_column, lpad('a',20,'a') random_stuff
   from dba_objects a,
        (select rownum from dual connect by rownum <= 20) b
  where rownum <= 2000000;

exec dbms_stats.gather_table_stats(user,'T');

create index i_desc on t(created desc);

var from_d varchar2(100);
var to_d varchar2(100);
var maxrows number;

exec :from_d := '20161001';
exec :to_d := '20161002';
exec :maxrows := '25';

SELECT data.*, ROWNUM AS rn 
  FROM (SELECT /*+ INDEX(t i_desc) */ * 
          FROM t 
         WHERE created BETWEEN to_date(:from_d,'YYYYMMDD') AND to_date(:to_d,'YYYYMMDD')
         ORDER BY created DESC) data
 WHERE ROWNUM < :maxrows

Plan hash value: 2665129471

| Id  | Operation                      | Name  | Starts | E-Rows|A-Rows|Buffers|
|   0 | SELECT STATEMENT               |       |      1 |       |    24|     50|
|*  1 |  COUNT STOPKEY                 |       |      1 |       |    24|     50|
|   2 |   VIEW                         |       |      1 |     27|    24|     50|
|*  3 |    FILTER                      |       |      1 |       |    24|     50|
|   4 |     TABLE ACCESS BY INDEX ROWID| T     |      1 |  65002|    24|     50|
|*  5 |      INDEX RANGE SCAN          | I_DESC|      1 |      3|    24|     26|

Predicate Information (identified by operation id):
1 - filter(ROWNUM<:MAXROWS)   
5 - access("T"."SYS_NC00010$">=SYS_OP_DESCEND(TO_DATE(:TO_D,'YYYYMMDD')) AND
    filter((SYS_OP_UNDESCEND("T"."SYS_NC00010$")<=TO_DATE(:TO_D,'YYYYMMDD') AND            

Execution plan has no SORT ORDER BY [STOPKEY in case case] thanks to the way values are stored in the index (DESC order) thus no blocking operation. As soon as 24 rows are retrieved from the index/table steps (aka they satisfy the filter conditions) they can be immediately returned to the user.

Your business goes well, data volume increase and so you decide to range partition table T by time and to make partition maintenance smooth you make index I_DESC LOCAL. SQL commands are the same as before except for the PARTITION clause in the CREATE TABLE and the LOCAL keyword in the CREATE INDEX.

drop table t purge;

create table t (pk number, owner varchar2(128), object_id number, data_object_id number, created date, object_name varchar2(128), object_type varchar2(23), large_column clob, random_stuff varchar2(20))
 partition by range (created) interval (numtodsinterval(7,'day')) 
  (partition p1  values less than (to_date('2016-09-12 00:00:00', 'yyyy-mm-dd hh24:mi:ss')));

insert into t 
 select /*+ LEADING(A) */ rownum pk, a.owner, a.object_id, a.data_object_id, 
        (sysdate-31)+(rownum/65000) created, a.object_name, a.object_type,  
        to_clob(null) large_column, lpad('a',20,'a') random_stuff
   from dba_objects a,
        (select rownum from dual connect by rownum <= 20) b
  where rownum <= 2000000;

exec dbms_stats.gather_table_stats(user,'T');

create index i_desc on t(created desc) local;

SELECT data.*, ROWNUM AS rn 
  FROM (SELECT /*+ INDEX(t i_desc) */ * 
          FROM t 
         WHERE created BETWEEN to_date(:from_d,'YYYYMMDD') AND to_date(:to_d,'YYYYMMDD')
         ORDER BY created DESC) data
 WHERE ROWNUM < :maxrows;

| Id |Operation                                      |Name   |Starts|E-Rows|A-Rows|Buffers|
|   0|SELECT STATEMENT                               |       |     1|      |    24|    855|
|*  1| COUNT STOPKEY                                 |       |     1|      |    24|    855|
|   2|  VIEW                                         |       |     1|    27|    24|    855|
|*  3|   SORT ORDER BY STOPKEY                       |       |     1|    27|    24|    855|
|*  4|    FILTER                                     |       |     1|      | 65001|    855|
|   5|     PARTITION RANGE ITERATOR                  |       |     1| 65002| 65001|    855|
|   6|      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|T      |     1| 65002| 65001|    855|
|*  7|       INDEX RANGE SCAN                        |I_DESC |     1|     2| 65001|    187|

Predicate Information (identified by operation id):
1 - filter(ROWNUM<:MAXROWS)
3 - filter(ROWNUM<:MAXROWS)  
7 - access("T"."SYS_NC00010$">=SYS_OP_DESCEND(TO_DATE(:TO_D,'YYYYMMDD')) AND

Unfortunately this time the plan DOES include a SORT ORDER BY STOPKEY and the effect is evident, ~65k rows are retrieved before the top 24 are returned back to the user.

Why is that? Let’s hold the question for a second and check first if an ASC index would make a difference here.
Do we have the same behavior if instead of scanning the I_DESC index using a regular “INDEX RANGE SCAN” we do a “INDEX RANGE SCAN DESCENDING” on an index defined as ASC?
Same SQL commands as before except the index is defined ASC and the hint now becomes INDEX_DESC.

create index i_asc on t(created) local;

SELECT data.*, ROWNUM AS rn 
  FROM (SELECT /*+ INDEX_DESC(t i_asc) */ * 
          FROM t 
         WHERE created BETWEEN to_date(:from_d,'YYYYMMDD') AND to_date(:to_d,'YYYYMMDD')
         ORDER BY created DESC) data
 WHERE ROWNUM < :maxrows;

| Id |Operation                             | Name  |Starts|E-Rows|Pstart|Pstop|A-Rows|
|   0|SELECT STATEMENT                      |       |     1|      |      |     |    24|
|*  1| COUNT STOPKEY                        |       |     1|      |      |     |    24|
|   2|  VIEW                                |       |     1|    27|      |     |    24|
|*  3|   FILTER                             |       |     1|      |      |     |    24|
|   4|    PARTITION RANGE ITERATOR          |       |     1|    27|  KEY |  KEY|    24|
|   5|     TABLE ACCESS BY LOCAL INDEX ROWID| T     |     1|    27|  KEY |  KEY|    24|
|*  6|      INDEX RANGE SCAN DESCENDING     | I_ASC |     1| 65002|  KEY |  KEY|    24|

Predicate Information (identified by operation id):
1 - filter(ROWNUM<:MAXROWS)     

Expected plan again, without SORT step, that returns 24 rows as soon as they are extracted from the index/table step.
So to answer the initial question, is there a difference?
Yes, when using a “INDEX RANGE SCAN DESCENDING” of I_ASC index the database didn’t include the SORT step. On the other hand when doing an “INDEX RANGE SCAN” of I_DESC index the SORT step is included and it affects the performance of the SQL.

Why when using the DESC index there is a SORT step?
I can’t imagine why the database needs to introduce that SORT step, beside restriction / limitations in the code (but this is based on limited knowledge so easy to be wrong here).
My guess is the additional complexity of the DESC index code / filters combined with bind variables limit the ability to recognize the SORT is unnecessary.

Reason for saying this it’s anytime Oracle does dynamic partitioning pruning (because of the binds, aka Pstart/Pstop KEY/KEY) the SORT step is present even though such step is not there when using “INDEX RANGE SCAN DESCENDING” on I_ASC index still in presence of binds. Also the SORT disappears when using “INDEX RANGE SCAN” on I_DESC index if the binds are replaced with literals.
One suspect was that the PARTITION RANGE ITERATOR step was consistently being executed in ASCENDING way, if that was the case then the SORT would make sense because the data would be returned “partially” sorted, meaning desc sorted only within each partition. But this is not the case, event 10128 (details here) show the partitions are accessed in DESCENDING order

Partition Iterator Information:
 partition level = PARTITION
 call time = RUN
 Partition iterator for level 1:
 iterator = RANGE [3, 4]
 index = 4
 current partition: part# = 4, subp# = 1048576, abs# = 4
 current partition: part# = 3, subp# = 1048576, abs# = 3

That’s why the (educated guesssed) conclusion that the SORT step is just caused my some limitations rather than being necessary here, corrections are very welcome here!!!
What just said adds to the very good reasons to question a DESC index on a single column to begin with, reported by Richard Foote here.

BTW apologies for the DDL not super-clean, they were just inherited from another example I was working on and came in handy 😀


Which Observations would you like to see in SQLd360?

SQLd360 v1617 finally includes a new “Observations” section (section 1F) that I pushed back for long, very long 🙂

If you are not familiar with SQLTXPLAIN then consider the reports in this section like “yellow flags”, specific items that are not necessarily causing direct troubles to your SQL but are still questionable and need further investigation / verification / blessing for such item to be kept in place.

There are many reasons why I pushed back for a long time, first one being the results can be easily misleading and make you believe the underlying cause is A while maybe it’s Z. Another reason is most of the observations ended up just being informative with no action taken against them, still you had to scroll hundreds of them.
Because of what just said, combined with the need to keep the checks “useful”, the current list of observations is intentionally short (and incomplete as of now), it includes only observations for:

  • Some system-wide settings, e.g. CBO parameters, OFE version, etc
  • Few plan and cursor-specific information, e.g. index referenced in some plan is now missing
  • Table statistics, e.g. partition that are empty according to stats

The list won’t grow much based on my ideas for the same reason it’s short now, I don’t want to implement checks I believe are important when 99% of the people don’t care about them.

That’s why this blog post, I need your feedback and ideas to implement what you care about 🙂
Let me know what you would like to have in the observation section and I’ll work on it!
Just keep in mind the goal is to keep that section relatively fast so super-complex checks that take 10 mins to execute are off the list.

Note: v1617 also turns off a couple of less-used features like TCB and AWR reports by default (can easily be enabled back via config file) so don’t be surprised if they don’t show up in column 5.

Leave a comment

How to find file and block# to dump in Exadata

Probably because of my past with Oracle Support, I find myself collecting traces and dumps on a regular basis. For example every time I see a row source operation that I would expect to perform only multiblock reads (e.g. Full Table Scan) doing single block ones I collect some block dumps to understand why that is happening. It’s just a matter of looking at the raw SQL trace file (or P1/P2 from ASH for that matter) for the unexpected single block reads, grab file# and block# and dump the block.

Single block reads in Exadata become “cell single block physical read” and the P1/P2 for the wait event don’t show the file#/block# but rather then cellhash# and diskhash# where the data came from. This is a little annoying to me because I can’t just grab cellhash#/diskhash# (plus bytes, that matches with the block size being a single block read) and dump that AS FAR AS I KNOW (corrections are very welcome here).

The way I usually work it around is using with ASM info enabling KFKIO tracing (trace[KFKIO] using the 11g new syntax) before running the “guilty SQL” so that each read now looks like this

2016-05-05 19:05:41.351021 : KFK_IODONE_OSS:iop:0x7f1841034000 bufp:0x1124ae000 op:1 au:120555 dsk:0x2 err:1 osderr:0 ossp:0x7f1841166fc8 ossp->errcode_oss_result:(nil) io elapsed time: 0 usec time waited on io: 0 usec
2016-05-05 19:05:41.351052 : kfk_iodone_oss_retry::in iop:0x7f1841034000
2016-05-05 19:05:41.351062 : kfk_iodone_oss_retry::out retry:0
2016-05-05 19:05:41.351071 : KFK:IODONE:iop:0x7f1841034000 wt:1 sstype:1 mastp:(nil) result:1 path:o/ au_offset:2678784 operation:1 reason:3347 iosize:8192 bufp:0x1124ae000
WAIT #139742149463208: nam='cell single block physical read' ela= 346 cellhash#=379339958 diskhash#=3782402265 bytes=8192 obj#=115376 tim=1462493141351085

What I care about is:

  • path:o/ -> where the data is stored
  • au:120555 -> the ASM allocation unit where my block is stored in
  • au_offset:2678784 -> how far in the allocation unit my block is

using this info I can go to the ASM instance (reason why ASM instance is becaused  X$KFFXP isn’t exposed in the RDBMS instance) and using the following SQL extract file name and block number

SELECT file_name, 
       xnum_kffxp * (&&au_size./&&db_block_size.) + &&au_offset./&&db_block_size. block_num
  FROM x$kffxp x, 
       v$asm_alias a
 WHERE (x.disk_kffxp, x.group_kffxp) IN (SELECT number_kfdsk, grpnum_kfdsk 
                                           FROM x$kfdsk 
                                          WHERE path_kfdsk = '&&path.')
   AND x.au_kffxp = &&au.
   AND x.number_kffxp = a.file_number
   AND x.incarn_kffxp = a.file_incarnation;

The SQL will ask for 5 parameters, 3 of which are already available while the other two are:

  • au_size -> Allocation Unit size -> V$ASM_DISKGROUP.ALLOCATION_UNIT_SIZE
  • db_block_size -> the DB_BLOCK_SIZE

So for example using the values from above plus 4194304 au_size and 8192 bytes block size the result will be

FILE_NAME                                            BLOCK_NUM
-------------------------------------------------- -----------
USERS.1155.861540195                                   3946823

Disclaimer: the SQL could take a little while since it needs to scan the list of extents.

I know it’s a little convoluted but as of now I haven’t found a more efficient way to identify file/block to dump starting from cellhash#/diskhash#.

Goes without saying I’d love to hear smarter ways to get it done 🙂

UPDATE: Stefan Koehler (who is awesome on top of being a friend) made a very valid point on Twitter, event 10200 can lead to the same info without having to go to the ASM instance. I discarded the event when I started looking at this because it was a little too verbose but then I ended up with KFKIO that is even more verbose!  😦
Stupid me and thanks to Stefan!

An output of event 10200 looks like this

ktrget2(): started for block  0x0005 : 0x05fc3947 objd: 0x0001c2b0
WAIT #139650546118344: nam='cell single block physical read' ela= 380 cellhash#=379339958 diskhash#=3782402265 bytes=8192 obj#=115376 tim=1462544482448025

where 05fc3947 is the DBA, 100415815 in decimal, and using DBMS_UTILITY we can extract file and block# from it

SELECT dbms_utility.data_block_address_file(100415815) file_n,
       dbms_utility.data_block_address_block(100415815) block_n
  FROM dual;

---------- ----------
        23    3946823