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 18.104.22.168 bundle 20. Fun!
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.