Hello Tom, Chris, Conner, and all the other researchers!
I have a few questions and a scenario I'm hoping you can help me with. All of this is done with setting parameters at the session level only. This is also done on Star schema reporting tables that are dropped and recreated daily with column groups, and histograms being recreated from the prior day's table's usage. I am working on coding to copy SQL Directives like I do with the column groups and histograms. The daily table recreate may be part of the problem which wouldn't honestly surprise me.
Let's start with the questions if you would please.
1. Is there a difference in behavior between setting OPTIMIZER_ADAPTIVE_STATISTICS at the session versus system level? Other than the scope of the behavior of course. Should I expect a session where I've set the parameter to TRUE to behave exactly as it would when the parameter is set to TRUE at the system level?
2. What does a SQL Plan Directive tell DBMS_STATS it needs to do other than auto creating column groups for tables? Is it pointing out columns needing histograms above and beyond what is in COL_USAGE or giving it additional information such as the dynamic stats stored with the directive to store at the table level?
3. What is the relationship between the parameters OPTIMIZER_DYNAMIC_SAMPLING & OPTIMIZER_ADAPTIVE_STATISTICS? I've noticed when I set OPTIMIZER_DYNAMIC_SAMPLING to 11 and OPTIMIZER_ADAPTIVE_STATISTICS to TRUE that queries show AUTO sampling but no SQL Directives ever in use. Performance varies but that may have something to do with the scenario below. If I set OPTIMIZER_DYNAMIC_SAMPLING back to 2 the Notes section shows SQL Directives in use again along with AUTO dynamic sampling if the Directive requests it.
4. I know, I think I know anyhow, that SQL Directives are first created in memory in each instance and are only visible on that instance until they are written to disk. Does writing it to disk insure the SQL Directives are usable by other instances in a RAC or are SQL Directives still instance specific even after being written? I wouldn't think so given DBA_SQL_PLAN_DIRECTIVES has no INST_ID column in it.
The scenario. I don't think I can send you the query but I can send part of the explain plans.
I have an OBIEE generated query which I've been testing the last few days that behaves...oddly. Since the tables under it are recreated every morning the query has to recreate SQL Directives before it'll use them. Once they're in place though it'll only use them a few times before it goes back to the original query and refuses to budge, never returning data. This morning I exported the SQL Directives before doing the table recreate and then reimported the SQL Directives to the new tables. This caused the query to use the SQL Directives right away, which was cool. But after a few runs the query went back to the original plan and never returned data.
My simple test case:
1. Logon to instance x and run
ALTER SESSION SET OPTIMIZER_ADAPTIVE_STATISTICS = TRUE;
2. Run query and note which plan it's using and note if the plan shows being re-optimizable in GV$SQL.
3. Run the query again 2-4 more times and note that it gets faster and faster and then returns to the very original plan and stops returning data.
4. Flush the SQL Directive to disk.
5. Log into other 3 instances and retry steps 1-4 with the identical behavior occurring.
Findings:
1. No matter which instance I start on, I can rely on the query choosing the worst plan and never returning data. It doesn't matter if I flush SQL Directives on the same or different instances or gather stats on the tables under the query after flushing the SQL Directives, it always starts at the worst plan.
2. The plans will continue to evolve to a certain point where the query returns data quickly at which point it reverts back to the very first plan and stops returning data.
3. If I log out and back in on the same instance I can recreate the behavior.
4. If I stay logged in and flush the SQL from the Shared Pool I can start the process again in the same session and it'll do the exact same thing.
5. In GV$SQL IS_REOPTIMIZABLE will start at N then flip to Y and when it goes back to the original plan it'll go back to N again and stay there until I flush the SQL_ID from the Shared pool.
Is this normal behavior for adaptive queries or am I doing something wrong? From reading the SQL Tuning Guide and some of Nigel & Maria's blogs on these features, I should get consistent behavior from the query once the Directives are on disk and stats have been regathered. That's not what I'm seeing though which is what makes me wonder if the behavior is different when setting OPTIMIZER_ADAPTIVE_STATISTICS at the session level.
Let me know what you think and if you have any advice and thanks for you time and consideration!!
This is the original plan that it starts and returns to. I don't have enough space to put the rest of the plans in so not sure how useful this will be.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | O/1/M |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2651 (100)| | | | | | |
| 1 | SORT GROUP BY | | 1 | 107 | 2651 (3)| 00:00:01 | | | 73728 | 73728 | |
| 2 | VIEW | | 1 | 107 | 2651 (3)| 00:00:01 | | | | | |
| 3 | HASH GROUP BY | | 1 | 134 | 2651 (3)| 00:00:01 | | | 1699K| 1699K| |
| 4 | NESTED LOOPS | | 1 | 134 | 2647 (3)| 00:00:01 | | | | | |
| 5 | NESTED LOOPS | | 20 | 134 | 2647 (3)| 00:00:01 | | | | | |
| 6 | MERGE JOIN CARTESIAN | | 1 | 82 | 2603 (3)| 00:00:01 | | | | | |
|* 7 | HASH JOIN | | 1 | 62 | 2600 (3)| 00:00:01 | | | 3221K| 3221K| 1/0/0|
| 8 | VIEW | VW_NSO_1 | 1 | 19 | 1906 (2)| 00:00:01 | | | | | |
|* 9 | FILTER | | | | | | | | | | |
| 10 | HASH GROUP BY | | 1 | 41 | 1906 (2)| 00:00:01 | | | 2668K| 2247K| 1/0/0|
| 11 | VIEW | | 3 | 123 | 1906 (2)| 00:00:01 | | | | | |
| 12 | HASH GROUP BY | | 3 | 123 | 1906 (2)| 00:00:01 | | | 12M| 4673K| 1/0/0|
| 13 | VIEW | VW_ORE_337BDF51 | 45 | 1845 | 1905 (2)| 00:00:01 | | | | | |
| 14 | UNION-ALL | | | | | | | | | | |
|* 15 | HASH JOIN | | 11 | 957 | 625 (2)| 00:00:01 | | | 19M| 7045K| 1/0/0|
| 16 | NESTED LOOPS | | 11 | 561 | 614 (2)| 00:00:01 | | | | | |
|* 17 | INDEX RANGE SCAN | IDX_TIME_D_X_9 | 30 | 240 | 2 (0)| 00:00:01 | | | 1028K| 1028K| |
| 18 | PARTITION RANGE AND | | 1 | 43 | 22 (0)| 00:00:01 |KEY(AP)|KEY(AP)| | | |
| 19 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MAINT_F_X | 1 | 43 | 22 (0)| 00:00:01 |KEY(AP)|KEY(AP)| | | |
|* 20 | INDEX RANGE SCAN | IDX_MAINT_F_X_3 | 1 | | 18 (0)| 00:00:01 |KEY(AP)|KEY(AP)| 1028K| 1028K| |
| 21 | PARTITION RANGE SINGLE | | 1 | 12 | 3 (0)| 00:00:01 | KEY | KEY | | | |
|* 22 | INDEX RANGE SCAN | IDX_REF_PMRA_GARM_1 | 1 | 12 | 3 (0)| 00:00:01 | KEY | KEY | 1028K| 1028K| |
| 23 | INDEX STORAGE FAST FULL SCAN | IDX_CUSTOMER_D_X_2 | 1 | 36 | 0 (0)| | | | 1028K| 1028K| |
|* 24 | HASH JOIN | | 15 | 1305 | 602 (2)| 00:00:01 | | | 19M| 7045K| 1/0/0|
| 25 | NESTED LOOPS | | 15 | 765 | 587 (2)| 00:00:01 | | | | | |
|* 26 | INDEX RANGE SCAN | IDX_TIME_D_X_9 | 28 | 224 | 2 (0)| 00:00:01 | | | 1028K| 1028K| |
| 27 | PARTITION RANGE AND | | 1 | 43 | 23 (0)| 00:00:01 |KEY(AP)|KEY(AP)| | | |
| 28 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MAINT_F_X | 1 | 43 | 23 (0)| 00:00:01 |KEY(AP)|KEY(AP)| | | |
|* 29 | INDEX RANGE SCAN | IDX_MAINT_F_X_3 | 1 | | 18 (0)| 00:00:01 |KEY(AP)|KEY(AP)| 1028K| 1028K| |
| 30 | PARTITION RANGE SINGLE | | 1 | 12 | 3 (0)| 00:00:01 | KEY | KEY | | | |
|* 31 | INDEX RANGE SCAN | IDX_REF_PMRA_GARM_1 | 1 | 12 | 3 (0)| 00:00:01 | KEY | KEY | 1028K| 1028K| |
| 32 | INDEX STORAGE FAST FULL SCAN | IDX_CUSTOMER_D_X_2 | 1 | 36 | 0 (0)| | | | 1028K| 1028K| |
|* 33 | HASH JOIN | | 19 | 1653 | 669 (2)| 00:00:01 | | | 21M| 7045K| 1/0/0|
| 34 | NESTED LOOPS | | 19 | 969 | 650 (2)| 00:00:01 | | | | | |
|* 35 | INDEX RANGE SCAN | IDX_TIME_D_X_9 | 31 | 248 | 2 (0)| 00:00:01 | | | 1028K| 1028K| |
| 36 | PARTITION RANGE AND | | 1 | 43 | 23 (0)| 00:00:01 |KEY(AP)|KEY(AP)| | | |
| 37 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MAINT_F_X | 1 | 43 | 23 (0)| 00:00:01 |KEY(AP)|KEY(AP)| | | |
|* 38 | INDEX RANGE SCAN | IDX_MAINT_F_X_3 | 1 | | 18 (0)| 00:00:01 |KEY(AP)|KEY(AP)| 1028K| 1028K| |
| 39 | PARTITION RANGE SINGLE | | 1 | 12 | 3 (0)| 00:00:01 | KEY | KEY | | | |
|* 40 | INDEX RANGE SCAN | IDX_REF_PMRA_GARM_1 | 1 | 12 | 3 (0)| 00:00:01 | KEY | KEY | 1028K| 1028K| |
| 41 | INDEX STORAGE FAST FULL SCAN | IDX_CUSTOMER_D_X_2 | 1 | 36 | 0 (0)| | | | 1028K| 1028K| |
|* 42 | INDEX STORAGE FAST FULL SCAN | IDX_CUSTOMER_D_X_3 | 1297K| 53M| 680 (4)| 00:00:01 | | | 1028K| 1028K| |
| 43 | BUFFER SORT | | 181 | 3620 | 1924 (3)| 00:00:01 | | | 73728 | 73728 | |
|* 44 | INDEX STORAGE FAST FULL SCAN | IDX_TIME_D_X_6 | 181 | 3620 | 3 (0)| 00:00:01 | | | 1028K| 1028K| |
| 45 | PARTITION RANGE AND | | 20 | | 19 (6)| 00:00:01 |KEY(AP)|KEY(AP)| | | |
|* 46 | INDEX RANGE SCAN | IDX_MAINT_F_X_3 | 20 | | 19 (6)| 00:00:01 |KEY(AP)|KEY(AP)| 1028K| 1028K| |
| 47 | PARTITION RANGE SINGLE | | 1 | 12 | 3 (0)| 00:00:01 | KEY | KEY | | | |
|* 48 | INDEX RANGE SCAN | IDX_REF_PMRA_GARM_1 | 1 | 12 | 3 (0)| 00:00:01 | KEY | KEY | 1028K| 1028K| |
|* 49 | TABLE ACCESS BY LOCAL INDEX ROWID | MAINT_F_X | 1 | 52 | 44 (3)| 00:00:01 | 1 | 1 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- this is an adaptive plan
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
- 2 Sql Plan Directives used for this statement
I reached out to Nigel Bayliss, optimizer PM about this. Here are his comments:
1. It’s the same.
2. Tells the optimizer to use dynamic stats. Also, it will initiate column group creation if DBMS_STATS AUTO_STAT_EXTENSIONS is ON.
3. SQL plan directives initiate dynamic sampling 11. If you are enabling 11 already, SPDs don’t add anything.
4. All instances once written.
I have an OBIEE generated query which I've been testing the last few days that behaves...oddly.
If the query is not dynamic, I’d just create a SQL plan baseline.
Also, check to see if DBMS_STATS AUTO_STAT_EXTENSIONS is ON. You might be getting extended stats without realizing it. The default is OFF.
4. Flush the SQL Directive to disk.
I’m not sure exactly when, but you might need to flush the shared pool at some point to be sure you are using a new plan when you want to use a new plan from scratch.
Is this normal behavior for adaptive queries or am I doing something wrong?
The mechanisms are not foolproof. I would usually aim to understand why your plan is so hard to optimize OR stabilize the plan with SQL plan baselines. Sometimes queries are written in a way that makes accurate cardinality estimation nearly impossible. Maybe the query can be written in a different way. Maybe all you need to do is to enable dynamic sampling 11 for this one query and rely on that. Then you wouldn’t need SQL plan directives.