Oakies Blog Aggregator

Here is a little note about the SYS.STATS_TARGET$ table used by the automatic statistics gathering job run at maintenance window, or when running it manually with:

exec dbms_auto_task_immediate.gather_optimizer_stats

This table is not documented and has no view on it, so those are only my guesses about what I observed, and comments are welcome. Basically, this table is used by the Auto Stats job to list the tables to process, from one execution to the other.

Note that in 12c the same information is updated into DBA_OPTSTAT_OPERATION_TASKS and visible through DBMS_STATS.REPORT_STATS_OPERATIONS. But I still use STATS_TARGET$ so see in real-time what is currently processed.

Columns description

STATUS

When the Auto Stats job lists the objects to process, they are in state PENDING (STATUS=0).

Then, when it is its turn to be processed, the START_TIME is set to current timestamp and it can be SKIPPED (STATUS=4) or processed IN PROGRESS (STATUS=1)

Then, when processing ends the END_TIME is set to current timestamp and the status can be COMPLETED (STATUS=2) if successful, or FAILED (STATUS=3) in case of error. If it ends before completion at the end of the maintenance window, the IN PROGRESS and PENDING become TIMED OUT (STATUS=5)

Note that START_TIME and END_TIME have been introduced in 12c

STALENESS

This is similar, but more precise, than the STALE column in DBA_TAB_STATISTICS. Rather than YES/NO we have here an evaluation of staleness (comparing the modifications from DBA_TAB_MODIFICATIONS with the number of rows) in a logarithmic scale between -1.0 and 1.0 where the lowest is the more stale.

TYPE# and OBJ#

This is the OBJECT_TYPE and OBJECT_ID from DBA_OBJECTS. The decode of TYPE# to OBJECT_TYPE is in the DBA_OBJECTS view on OBJ$ definition. Rather than hardcoding it in my queries, I get it from:

select /*+ RESULT_CACHE (SYSOBJ=TRUE)*/ 
 distinct type#,object_type 
 from (select object_id obj#,object_type from dba_objects)
 natural join sys.obj$

OSIZE

This is the estimated object size, from the known number of blocks and blocksize, and can be used to estimate roughly the time it takes to gather statistics.

BO# and PART#

Those are the physical identifiers for partitions and subpartitions, tables: it makes the links with the parent object, and between index and table, probably for the purpose of CASCADE gathering.

FLAGS

This is a bitmap with some information about the staleness and the status. Here is my decode (which may be wrong as it is not documented)

ltrim(
   case when bitand(flags,1)=1 then ',RETRY' end 
|| case when bitand(flags,2)=2 then ',NON-SEGMENT' end 
|| case when bitand(flags,4)=4 then ',?' end 
|| case when bitand(flags,8)=8 then ',TIMED OUT' end 
|| case when bitand(flags,16)=16 then ',?' end 
|| case when bitand(flags,32)=32 then ',GATHER GLOBAL' end 
|| case when bitand(flags,64)=64 then ',GATHER PARTITION' end 
|| case when bitand(flags,128)=128 then ',MISSING COL STATS' end 
|| case when bitand(flags,256)=256 then ',STALE STATS' end 
|| case when bitand(flags,512)=512 then ',NO STATS' end 
|| case when bitand(flags,1024)=1024 then ',HAS DIRECTIVES' end 
|| case when bitand(flags,2048)=2048 then ',MISSING EXTENSION' end 
|| case when bitand(flags,4096)=4096 then ',MISSING HISTOGRAM' end 
|| case when bitand(flags,8192)=8192 then ',CASCADED' end 
|| case when bitand(flags,16384)=16384 then ',REPORTING RUN' end 
|| case when bitand(flags,32768)=32768 then ',FIXED TABLE' end 
|| case when bitand(flags,65536)=65536 then ',SYNOPSIS MISMATCH' end
,',') flags

RETRY is for the gathering which was IN PROGRESS and was stopped with TIMED OUT.

SID, SERIAL#

The last session running the Auto Stats (i.e PENDING, IN PROGRESS, TIMED OUT) is identified here so you can join with V$SESSION or even V$ACTIVE_SESSION_HISTORY to get more information afterward about the duration (which is END_TIME-START_TIME). You can also find the long-running ones in V$SQL_MONITOR.

Query examples

details

Here is an example while the Auto Stats job is running. This shows which table is currently gathered (IN PROGRESS), already done (COMPLETED) or to be done (PENDING). The flags indicate that the previous execution was TIMED OUT, and which is global level gathering.

I also join with V$SESSION in order to see the currently running job (and its login time) as I’m in 11g without the START_TIME:

Here is the query I used for this output:

select logon_time
--,start_time,end_time,end_time-start_time duration
--,start_time-lag(end_time)over(partition by sid,serial# order by start_time) after_previous,
,object_type,owner,object_name,subobject_name,sid||','||serial# "sid/ser#",round(osize/1024/1024/1024) GBytes
,rtrim(case status when 0 then 'PENDING' when 1 then 'IN PROGRESS' when 2 then 'COMPLETED' when 3 then 'FAILED'
                   when 4 then 'SKIPPED' when 5 then 'TIMEOUT' end) STATUS,status status#
,case staleness when -100 then 'MISSING' when -99 then null 
 else rtrim('STALE '||lpad(' ',round(2*(1+(staleness))),'+')) end staleness
,ltrim(
   case when bitand(flags,1)=1 then ',TIMED OUT' end 
|| case when bitand(flags,2)=2 then ',NON-SEGMENT' end 
|| case when bitand(flags,4)=4 then ',4' end 
|| case when bitand(flags,8)=8 then ',TIMED_OUT' end 
|| case when bitand(flags,16)=16 then ',16' end 
|| case when bitand(flags,32)=32 then ',GATHER GLOBAL' end 
|| case when bitand(flags,64)=64 then ',GATHER PARTITION' end 
|| case when bitand(flags,128)=128 then ',MISSING CSTATS' end 
|| case when bitand(flags,256)=256 then ',STALE STATS' end 
|| case when bitand(flags,512)=512 then ',NO STATS' end 
|| case when bitand(flags,1024)=1024 then ',HAS DIRECTIVES' end 
|| case when bitand(flags,2048)=2048 then ',MISSING EXTENSION' end 
|| case when bitand(flags,4096)=4096 then ',MISSING HISTOGRAM' end 
|| case when bitand(flags,8192)=8192 then ',CASCADED' end 
|| case when bitand(flags,16384)=16384 then ',REPORTING RUN' end 
|| case when bitand(flags,32768)=32768 then ',FIXED TABLE' end 
|| case when bitand(flags,65536)=65536 then ',SYNOPSIS MISMATCH' end
,',') flags
from (
select *
from STATS_TARGET$
natural left outer join (select /*+ result_cache */ distinct type#,object_type from (select object_id obj#,object_type from dba_objects) natural join sys.obj$)
natural left outer join (select object_id obj#,owner,object_name,subobject_name from dba_objects)
natural left outer join (select sid,serial#,program,sql_id,logon_time from gv$session)
left outer join (select bo#,part#,hiboundval,analyzetime,obj# part_obj# from sys.tabpart$) using(bo#,part#)
) v 
--order by end_time desc nulls last, start_time desc nulls last
order by logon_time desc nulls last,status#
/

summary

Here is another query which checks the global status while the Auto Stats job is running:

select logon_time,status,staleness,count(*),sum(GBytes) GBytes
,object_type,round(100*sum(GBytes)/max(TotGB)) "%"
from (
select logon_time,object_type,round(osize/1024/1024/1024) GBytes,sum(osize/1024/1024/1024)over(partition by logon_time) TotGB
,rtrim(case status when 0 then 'PENDING' when 1 then 'IN PROGRESS' when 2 then 'COMPLETED' when 3 then 'FAILED' when 4 then 'SKIPPED' when 5 then 'TIMEOUT' end) STATUS
,case staleness when -100 then 'MISSING' when -99 then null else 'STALE' end staleness
from STATS_TARGET$
natural left outer join (select /*+ result_cache */ distinct type#,object_type from (select object_id obj#,object_type from dba_objects) natural join sys.obj$)
natural left outer join (select sid,serial#,program,sql_id,logon_time from gv$session)
)
group by logon_time,object_type,status,staleness having logon_time is not null 
order by logon_time nulls last,status,object_type,staleness;

LOGON_TIME        STATUS      STALENE   COUNT(*)     GBYTES
----------------- ----------- ------- ---------- ----------
15-JAN-2019 08:44 COMPLETED   STALE          288       2177
15-JAN-2019 08:44 IN PROGRESS STALE            1        210
15-JAN-2019 08:44 PENDING     MISSING      58744      18311
15-JAN-2019 08:44 PENDING     STALE         2567       8579
15-JAN-2019 08:44 PENDING                     55          0

Processing order

As this table is used to list the tables to process, the columns are used to order it. We know that the Auto Stats starts with the most stale (and MISSING is the first there). But before that, the tables are listed before the partitions, and partitions before subpartition. And tables are processed before indexes.

It’s been a work in progress for quite some time, with many of my customers asking when will it be completed. Well, I’m very excited to announce that I have finally completed my new 2 day “Oracle Diagnostics and Performance Tuning” seminar and that it’s ready to be presented. I already have a number of […]

Recently, Franck Pachot tweeted the following:
https://fritshoogland.files.wordpress.com/2019/01/tweet-franck.png?w=84&... 84w, https://fritshoogland.files.wordpress.com/2019/01/tweet-franck.png 310w" sizes="(max-width: 168px) 100vw, 168px" />
This is a very clever way of using Brendan Gregg’s flame graphs for Oracle database process flame graphs and using my ora_functions project database.

However, the awk script uses a full match using f[$2]:
– f is an array with the function name annotations filled using {f[$1]=$2;next}.
– // means to only work on lines that have in it.
– The lines have the oracle function stuck to , for example: kcbgtcr.
– The first sub() function (substitute); which is sub(“”,”& “) places a space after “” so it looks like: kcbgtcr; “&” means “the first argument of the sub function”.
– FS=” ” means the field separator is a space.
– The next sub() function; which is sub(“”,””f[$2]”&”) substitutes on the pattern, and uses the f array using the second field ($2) of the input, which now is the oracle function thanks to the field separator and the previous sub() function.

But…the function names in the database/csv file are build up on layers. I done this deliberately to avoid having to type all the layer names for a function when entering new ones, but more importantly so I can actually identify the code layers in the oracle executable. Because of the layers, I don’t need the full function descriptions, I can still get an understanding what is going on by looking in what layer a function is called.

To get the layers in the function names, I added a script in my ora_functions repository called ‘annotate_flamegraph.awk’, which takes the function name csv file actually exactly like Franck did, but now builds up the annotation of every function in an existing flamegraph. It simply takes an existing flamegraph SVG file with oracle database functions as an input, and outputs the annotated version on STDOUT, so this is how it’s run to get an annotated SVG:

$ ./annotate_flamegraph.awk dt_11856.txt_1.fold.svg > dt_11856.txt_1.fold.annotated.svg

This is how it looks like (please mind these are snapshots from flamegraph output in a browser):
https://fritshoogland.files.wordpress.com/2019/01/flamegraph-annotation1... 600w, https://fritshoogland.files.wordpress.com/2019/01/flamegraph-annotation1... 150w" sizes="(max-width: 300px) 100vw, 300px" />
(click on the graph to see it in the original size)
If you hoover over a row in the flamegraph, it will show the full information at the bottom row, in this case I hoovered over ttcpip.
If you click a row, it will zoom in to the row. The next picture is a good way to show that the function layers and the stack make it obvious what is going on, while most of the functions do not have the full annotation:
https://fritshoogland.files.wordpress.com/2019/01/flamegraph-annotation3... 600w, https://fritshoogland.files.wordpress.com/2019/01/flamegraph-annotation3... 150w" sizes="(max-width: 300px) 100vw, 300px" />
(click on the graph to see it in the original size)

Again, thanks to Franck for the inspiration. I hope this will be useful for investigating what oracle is doing!

After having published my first article of 2019 I have received feedback I felt like including. With a nod to @fritshoogland, @ChrisAntognini and @FranckPachot.

In the previous post I showed you output of Tanel Poder’s ashtop.sql as proof that direct path reads can occur even if all you do is look up data by primary key. This script touches v$active_session_history, and I’m not getting tired of mentioning that you need to license the system in scope for Enterprise Edition and the Diagnostics Pack to do so.

What I haven’t shown you in more detail in the previous article is what causes these direct path reads. This is what I’ll try to do next. I have started by repeating my test case but added the gather_plan_statistics hint to the statement, resulting in a different SQL ID. Yesterday I noticed that Safari doesn’t show the scroll bar at the bottom of this code snippet other browsers show, I hope output isn’t garbled. I’m using the universal excuse here “it works on my laptop”.

SQL> @xia a66axyw2g6yjb
Display Execution plan in advanced format for sqlid a66axyw2g6yjb

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a66axyw2g6yjb, child number 0
-------------------------------------
select /*+ gather_plan_statistics CLOB standalone */ *  from
martin.orders_clob  where order_id = :1

Plan hash value: 3284193906

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |       |     3 (100)|          |      0 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS_CLOB    |      1 |      1 |   137 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ORDERS_CLOB |      1 |      1 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / ORDERS_CLOB@SEL$1
   2 - SEL$1 / ORDERS_CLOB@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "ORDERS_CLOB"@"SEL$1" ("ORDERS_CLOB"."ORDER_ID"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (NUMBER): 519990

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

   2 - access("ORDER_ID"=:1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ORDER_ID"[NUMBER,22], "ORDERS_CLOB"."ORDER_CLOB"[LOB,4000], "ORDERS_CLOB"."ORDER_DATE"[TIMESTAMP WITH LOCAL TIME
       ZONE,11], "ORDERS_CLOB"."ORDER_MODE"[VARCHAR2,8], "ORDERS_CLOB"."CUSTOMER_ID"[NUMBER,22],
       "ORDERS_CLOB"."ORDER_STATUS"[NUMBER,22], "ORDERS_CLOB"."ORDER_TOTAL"[NUMBER,22], "ORDERS_CLOB"."SALES_REP_ID"[NUMBER,22],
       "ORDERS_CLOB"."PROMOTION_ID"[NUMBER,22], "ORDERS_CLOB"."WAREHOUSE_ID"[NUMBER,22], "ORDERS_CLOB"."DELIVERY_TYPE"[VARCHAR2,15],
       "ORDERS_CLOB"."COST_OF_DELIVERY"[NUMBER,22], "ORDERS_CLOB"."WAIT_TILL_ALL_AVAILABLE"[VARCHAR2,15],
       "ORDERS_CLOB"."DELIVERY_ADDRESS_ID"[NUMBER,22], "ORDERS_CLOB"."CUSTOMER_CLASS"[VARCHAR2,30],
       "ORDERS_CLOB"."CARD_ID"[NUMBER,22], "ORDERS_CLOB"."INVOICE_ADDRESS_ID"[NUMBER,22]
   2 - "ORDERS_CLOB".ROWID[ROWID,10], "ORDER_ID"[NUMBER,22]


57 rows selected.

After a few minutes of executing the statement repeatedly, direct path reads are clearly visible again

SELECT
    COUNT(*),
    event,
    session_state
FROM
    v$active_session_history ash
WHERE
    sql_id = 'a66axyw2g6yjb'
    AND sample_time > SYSDATE - 15 / 1440
GROUP BY
    event,
    session_state
ORDER BY
    1 DESC;

  COUNT(*) EVENT                                    SESSION
---------- ---------------------------------------- -------
      1592                                          ON CPU
        24 direct path read                         WAITING
         6 cursor: pin S                            WAITING
         4 SQL*Net more data to client              WAITING

So far so good. Getting back to the original question: what’s causing these? ASH provides the answer to that question as well.

SELECT
    COUNT(*),
    event,
    session_state,
    sql_plan_operation
    || ' '
    || sql_plan_options AS what,
    CASE
        WHEN wait_class IN(
            'Application',
            'Cluster',
            'Concurrency',
            'User I/O'
        )THEN object_name
        ELSE 'undef'
    END AS obj
FROM
    v$active_session_history ash,
    dba_objects o
WHERE
    ash.current_obj# = o.object_id
    AND sql_id = 'a66axyw2g6yjb'
GROUP BY
    event,
    session_state,
    sql_plan_operation
    || ' '
    || sql_plan_options,
    CASE
        WHEN wait_class IN(
            'Application',
            'Cluster',
            'Concurrency',
            'User I/O'
        )THEN object_name
        ELSE 'undef'
    END
ORDER BY 1 desc;

  COUNT(*) EVENT                                    SESSION WHAT                           OBJ
---------- ---------------------------------------- ------- ------------------------------ ------------------------------
      3006                                          ON CPU  SELECT STATEMENT               undef
       223                                          ON CPU  INDEX UNIQUE SCAN              undef
       126                                          ON CPU  TABLE ACCESS BY INDEX ROWID    undef
        50 direct path read                         WAITING SELECT STATEMENT               SYS_LOB0000081921C00002$$
         8 cursor: pin S                            WAITING SELECT STATEMENT               SYS_LOB0000081921C00002$$
         8 SQL*Net more data to client              WAITING SELECT STATEMENT               undef
         1                                          ON CPU                                 undef

7 rows selected.

I think you might be getting the idea by now … I can link the segment back to DBA_LOBS, and … it’s the LOB column of ORDERS_CLOB.

SELECT
    table_name,
    column_name,
    segment_name,
    cache,
    securefile
FROM
    dba_lobs
WHERE
    segment_name = 'SYS_LOB0000081921C00002$$';

TABLE_NAME      COLUMN_NAME     SEGMENT_NAME                   CACHE      SEC
--------------- --------------- ------------------------------ ---------- ---
ORDERS_CLOB     ORDER_CLOB      SYS_LOB0000081921C00002$$      NO         YES

QED.

Another option is to query session statistics. I looked at one of the sessions using snapper4.sql but the output was too wide to paste it as text. I’ll resort to the traditional way of doing this:

SELECT
     name,
     value
 FROM
     v$sesstat st
     NATURAL JOIN v$statname sn
 WHERE
     st.sid = 128
     AND st.value <> 0
     AND REGEXP_LIKE(name, '(physical|securefile).*read.*')
 ORDER BY
     sn.name;

NAME                                          VALUE
----------------------------------------- ---------- 
 physical read IO requests                    106836
 physical read bytes                       875200512
 physical read total IO requests              106836
 physical read total bytes                 875200512
 physical reads                               106836
 physical reads direct                        106836
 physical reads direct (lob)                  106836
 securefile direct read bytes              875200512
 securefile direct read ops                   106836
 9 rows selected.

I admit this was a rather manual way of extracting this piece of information, you might be able to get the same data with an ASH report (provided the number of I/O samples against that segment is significant enough to show up). In this context I’d like to recommend planx.sql you can find on Carlos Sierra’s and Mauro Pagano’s github repository. It’s small, lightweight, and incredibly useful.