9b4m3fr3vf9kn and an4593dzvqr4v

You got to love Exadata and subpartitioned tables.  We have these queries on one particular table that are taking forever to parse.  Sometimes a simple explain plan on the simplest possible query against the table takes 20 or 30 seconds.  It seems that the time is spent primarily on sql_id an4593dzvqr4v which is this internal query:

select obj#, dataobj#, subpart#, hiboundlen, hiboundval, ts#, file#,
 block#, pctfree$, pctused$, initrans, maxtrans, flags, analyzetime,
 samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln,
 length(bhiboundval), bhiboundval 
from tabsubpart$ 
where pobj# = :1 
order by subpart#

Gross.  Appears to look up information about a partition’s subpartitions and for some reason it likes to do this over and over again during a parse.  A similar popular query appears, namely, 9b4m3fr3vf9kn:

select obj#, dataobj#, subpart#, hiboundlen, hiboundval, flags, ts#,
 file#, block#, pctfree$, initrans, maxtrans, analyzetime, 
samplesize, rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey,
 clufac, spare2, length(bhiboundval), bhiboundval 
from indsubpart$ 
where pobj# = :1 
order by subpart#

I haven’t delved into this one but it appears to have to do with index subpartitions.  Of course it goes without saying that this is our second largest table and don’t bother telling me to regather stats because you can’t just willy-nilly regather stats on production tables without testing no matter what people may tell you.

But, if one of you gurus out there have any great ideas let me know.  I’d love to delete and regather stats but the system is in use and the table is large.  What I really want to do is set a small estimate percentage and abandon the 11g auto sample size but I haven’t convinced anyone of this yet.

Sometimes we see this when the subpartitions are being compressed and that makes total sense, but we see it during the work week also and that has no rhyme or reason to it since there is no updating or DDL that corresponds to the high parse times and the large numbers of executions of 9b4m3fr3vf9kn and an4593dzvqr4v.

Lest I forget, this is Exadata 11.2.0.2 bundle 20.  Fun!

– Bobby

P.S.  The one Oracle support entry related to these sql statements is this one:

Bug 16694856 : EXADATA: SUBPARTITION WITH ‘LIBRARY CACHE LOCK’ ON RAC + INDEX OPERATION

It specifically mentions an4593dzvqr4v and 9b4m3fr3vf9kn.  But, we are seeing the same results without any index operations.  Anyway, I’ve got a case open with Oracle support.

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

10 Responses to 9b4m3fr3vf9kn and an4593dzvqr4v

  1. Narendra says:

    Bobby,

    I don’t know much about exadata so I am just “throwing” idea. If the table in question is not too “volatile” i.e. the data in the table does not radically change frequently, would it help if you just set the stats manually on the table and index? Of course that is possible if you have a fair idea about the pattern of data in this table or have some kind of baseline representative statistics available.

    • Bobby says:

      Thanks for your comment. It is a good point that setting the table’s stats manually wouldn’t take as long, although with thousands of subpartitions it can still take a while to manually update them And the table is growing daily.

  2. Narendra says:

    Since you say the query takes a long time to parse and based on the 2 suspect queries that you have posted, would it be too expensive even to recollect statistics on data dictionary (not the table or index) ? I am assuming the data dictionary stats collection should be a lot cheaper than the actual table or data in question. Does that make sense or am I just shooting in the dark?

    • Bobby says:

      It is a good question about the dictionary stats. We just have the Oracle delivered stats gathering job running so the stats on the dictionary tables should be whatever that job does but it may not take as long to regather dictionary stats. But, we have tens of thousands of sub partitions so maybe dictionary stats would take longer than on a database with fewer dictionary objects. Thanks again for your comments.

  3. Hello!

    Just interesting, what kind of histogram have tabsubpart$ table and how much is sample_size, num_rows for that table

    • Bobby says:

      Sorry, my first reply wasn’t formatted well. I’m going to try the pre tag and see if it will look better:

      column stats:

      TABLE_NAME                     COLUMN_NAME                    LO                             HI                             NUM_DISTINCT NUM_BUCKETS    DENSITY  NUM_NULLS AVG_COL_LEN LAST_ANALYZED       SAMPLE_SIZE DATA_DEFAULT
      ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------ ----------- ---------- ---------- ----------- ------------------- ----------- ------------------------------
      TABSUBPART$                    OBJ#                           745185                         4121106                              154514           1 6.4719E-06          0           6 2013-09-22 10:53:49      154514
      TABSUBPART$                    DATAOBJ#                       745198                         4139053                              152160           1 .000006572          0           6 2013-09-22 10:53:49      154514
      TABSUBPART$                    POBJ#                          745184                         4121107                                1164           1 .000859107          0           6 2013-09-22 10:53:49      154514
      TABSUBPART$                    SUBPART#                       1                              1721                                    241         192 3.2457E-06          0           4 2013-09-22 10:53:49        8472
      TABSUBPART$                    FLAGS                          0                              16534                                    15          13 3.2457E-06          0           4 2013-09-22 10:53:49        8472
      TABSUBPART$                    TS#                            1                              423                                     139         137 3.2457E-06          0           4 2013-09-22 10:53:49        8472
      TABSUBPART$                    FILE#                          2                              1024                                     10           7 3.2457E-06          0           4 2013-09-22 10:53:49        8472
      TABSUBPART$                    BLOCK#                         529                            597150225                            114104         254 .000143369          0           7 2013-09-22 10:53:49        8472
      TABSUBPART$                    PCTFREE$                       0                              10                                        3           1 .333333333          0           3 2013-09-22 10:53:49      154514
      TABSUBPART$                    PCTUSED$                       0                              40                                        2           1         .5          0           3 2013-09-22 10:53:49      154514
      TABSUBPART$                    INITRANS                       1                              1                                         1           1          1          0           3 2013-09-22 10:53:49      154514
      TABSUBPART$                    MAXTRANS                       255                            255                                       1           1          1          0           4 2013-09-22 10:53:49      154514
      TABSUBPART$                    ANALYZETIME                    2010/10/19 16:2:3              2013/9/15 13:20:46                    37872         254  .00022477      54505           6 2013-09-22 10:53:49        5538
      TABSUBPART$                    SAMPLESIZE                     1                              15094372                              12528           1 .000079821     114633           2 2013-09-22 10:53:49       39881
      TABSUBPART$                    ROWCNT                         0                              15094372                              16848           1 .000059354      54505           3 2013-09-22 10:53:49      100009
      TABSUBPART$                    BLKCNT                         0                              277789                                 2230           1  .00044843      54505           3 2013-09-22 10:53:49      100009
      TABSUBPART$                    EMPCNT                         0                              0                                         1           1          1      54505           2 2013-09-22 10:53:49      100009
      TABSUBPART$                    AVGSPC                         0                              0                                         1           1          1      54505           2 2013-09-22 10:53:49      100009
      TABSUBPART$                    CHNCNT                         0                              0                                         1           1          1      54505           2 2013-09-22 10:53:49      100009
      TABSUBPART$                    AVGRLN                         0                              1133                                    505           1 .001980198      54505           2 2013-09-22 10:53:49      100009
      TABSUBPART$                    SPARE1                                                                                                  0           0          0     154514           0 2013-09-22 10:53:49
      TABSUBPART$                    SPARE2                                                                                                  0           0          0     154514           0 2013-09-22 10:53:49
      TABSUBPART$                    SPARE3                                                                                                  0           0          0     154514           0 2013-09-22 10:53:49
      TABSUBPART$                    HIBOUNDLEN                     0                              22                                       10           5 3.2457E-06          0           3 2013-09-22 10:53:49        8472
      TABSUBPART$                    HIBOUNDVAL                     Hi                             Hi
      TABSUBPART$                    BHIBOUNDVAL                    Hi                             Hi                                        0           0          0          0          87 2013-09-22 10:53:49      154514
      

      table stats:

      OWNER                TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED       DEGREE     INSTANCES
      -------------------- ------------------------------ ---------- ---------- ----------- ----------- ------------------- ---------- ----------
      SYS                  TABSUBPART$                        154514       2980         161      154514 2013-09-22 10:53:50          1          1
      

      I hope this formatting looks better.

      – Bobby

  4. Pingback: SQLT Data Explosion | Bobby Durrett's DBA Blog

  5. Pingback: More work on parse time bug | Bobby Durrett's DBA Blog

Leave a Reply to Volodimir Volodimirovich P Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.