Recently, I spotted several sessions in a long “cursor: pin S wait on X” wait. The waiters and the mutex holder ( the blocker) executed the same query. The blocker was burning CPU and the 10046 trace was not showing any progress. After turning on the 10053 trace it became obvious that the cursor was\u00a0still parsed.\u00a0After 20 minutes the parsing was not completed yet! Over and over again, the optimizer took more time for the same operation – calculating selectivity from the extended statistics:<\/p>\n
ColGroup Usage:: PredCnt: 4 Matches Full: #1 Partial:<\/code><\/pre>\nI sampled the Oracle dedicated process with pstack and found out that the process spent most of the time in the function kkestGetMCSelInlist. The name implies that the function has something to do with in lists. Indeed, the query – coming from a software vendor – was quite unusual in this respect: it had 11 in-lists each containing 90 elements! The search on the bug database with the keyword kkestGetMCSelInlist returned a known bug 16825679 : PARSING NEVER ENDS. The suggested workaround – setting the undocumented parameter _optimizer_extended_stats_usage_control to 224 – reduced the parsing time from 30 minutes to a couple of seconds.<\/p>\n
What did happen here?<\/p>\n
The bug note doesn’t describe what exactly got changed after the new value was set. Therefore, I compared the 10053 traces with and without the workaround and found out that the extended statistics have not been used at all after setting the parameter:<\/p>\n
_optimizer_extended_stats_usage_control=default\r\nColGroup Usage:: PredCnt: 4 Matches Full: #1<\/span> Partial:\r\n\r\n_optimizer_extended_stats_usage_control=224\r\nColGroup Usage:: PredCnt: 4 Matches Full: Partial:\r\n=> no matches in this case\r\n<\/code><\/pre>\nBut, are the extended statistics completely disabled now or just in some cases? As I couldn’t find any details on this hidden parameter on Metalink, I decided to set up a test case in order to understand the impact of the change.<\/p>\n
First, to set the scene, I am creating a table with two columns and the extended statistics:<\/p>\n
CREATE TABLE t\r\n AS\r\n SELECT rownum n1, rownum AS n2\r\n FROM dual\r\n CONNECT BY level <= 1000; \r\n\r\nSELECT dbms_stats.create_extended_stats(\r\n ownname=>user, tabname=>'t', extension=>'(n1,n2)')\r\n FROM dual;\r\n\r\nexecute dbms_stats.gather_table_stats(user,'t') ;\r\n<\/code><\/pre>\nSecond, I’m running the explain plan for two queries with both columns in the where clause. The one query has the equal filter predicates, the other has an in-list.<\/p>\n
EXPLAIN PLAN FOR\r\n SELECT *\r\n FROM t\r\n WHERE n1 = 1 AND n2 = 1 ;\r\n\r\n--10053 trace: ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.001000\r\n\r\nEXPLAIN PLAN FOR\r\n SELECT *\r\n FROM t\r\n WHERE n1 = 42 AND n2 in ( 42 ,43) ;\r\n\r\n--10053 trace: ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.002000\r\n<\/code><\/pre>\nAs expected, the optimizer 10053 trace provides the evidence that the extended statics were used in both queries.<\/p>\n
Finally, both queries will be executed again, but this time with _optimizer_extended_stats_usage_control set to 224.<\/p>\n
alter session set \"_optimizer_extended_stats_usage_control\"=224 ;\r\n\r\nEXPLAIN PLAN FOR\r\n SELECT *\r\n FROM t\r\n WHERE n1 = 42 AND n2 in<\/span> ( 42 ,43) ;\r\n\r\n--10053: no lines with ColGroup Usage found in the trace!\r\n<\/span>\r\nEXPLAIN PLAN FOR\r\n SELECT *\r\n FROM t\r\n WHERE n1 = 1 AND n2 = 1 ;\r\n\r\n--10053 trace: ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.001000\r\n<\/code><\/pre>\nIn conclusion, when _optimizer_extended_stats_usage_control is set to 224 the extended events are not used just in the case of the in-list in the where clause. In the case of equal predicates, the extended statistics are used.<\/p>\n","protected":false},"excerpt":{"rendered":"
Excessive query parsing time with extended events and long in lists were observed on a 12c database. As a workaround set the undocumented parameter _optimizer_extended_stats_usage_control to 224. Continue Reading