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.
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.
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.
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?
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.
Hello!
Just interesting, what kind of histogram have tabsubpart$ table and how much is sample_size, num_rows for that table
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 stats:
I hope this formatting looks better.
– Bobby
As I see, there is no histogram on column pobj# though it is using in query and not evenly distributed. I expected to see frequency histogram with sample_size lower than num_rows and possible instability associated with frequency histogram. May be you dig something http://allthingsoracle.com/histograms-part-1-why/
Thanks for your comment. I’ll give this some thought.
– Bobby
Pingback: SQLT Data Explosion | Bobby Durrett's DBA Blog
Pingback: More work on parse time bug | Bobby Durrett's DBA Blog