There seems to me to be a relatively simple choice.
Either you except that the Oracle Optimizer has a wealth of complicated strategies and, in this complex effort to get the best executions it can, will inevitably either get it wrong sometimes (or take some extra executions to realise it’s wrong).
Or you stick your head in the sand and raise a bug for every unexpected poor execution or plan flip.
But let’s say that above else you wanted Plan Stability.
This is an widespread desire.
What would be the best strategy?
And to what lengths would you take it?
SQL Plan Management features are designed to give you some degree of stability.
You could baseline statements which have given you a problem in the past.
What if that is not deemed adequate?
So, on the one hand, you could baseline everything you could find.
Taking a long history of AWR and taking regularly snaps of V$SQL, you could put all in a SQL Plan Baseline.
But, depending on your application, you might regularly get new SQL appear unprotected by a baseline.
In one Hibernate application I know, a change in the application domain model often results in the table aliases changing, meaning that there is lots of brand new, but very familiar, SQL.
So, you then become dependant on having a reliable testing infrastructure which will generate nearly all the SQL you’re likely to get in advance of production.
In addition, you might want multiple plans in your baseline – for ACS for example – and then, once that extra bindset awareness is flushed out of memory, you then need a couple of executions to rediscover it (unless you use a SQL Patch to inject BIND_AWARE into the specific sql statements).
It’s certainly no guarantee of stability.
What is the alternative?
I like to argue that most problematic executions stem from two sources:
- Badly written SQL
- Using poor combinations of features – e.g. bind variable peeking + histograms
The other day I made a comment to someone that “if you’re heavily reliant on ACS, perhaps you should rethink whether you should be using literals rather than bind variables”.
So, you might then take the position that plan instability stems from increasing complexity in the optimizer.
In which case, maybe a viable strategy might be to turn off much of the complexity:
- Bind Variable Peeking
- Cardinality feedback
- Adaptive Cursor Sharing
- Dynamic Sampling
- Adaptive direct path reads
- 12c adaptive execution plans
- Anything with the word “adaptive” in it?
- Default Stats job and/or default method_opt histogram behaviour
This seems quite radical to many. Perhaps justifiably.
Perhaps it would be nice if there were new optimizer strategies – OPTIMIZER_MODE? – perhaps “AGGRESSIVE_PERFORMANCE” (maybe “ADAPTIVE_PERFORMANCE_WITH_THE_ODD_HICCUP” ?) vs “PERFORMANCE_STABILITY” which were an umbrella of many of the features above.
To what extent are you concerned with performance stability, above all else?
How many of the optimizer features in each release are actually aligned with that widespread concern?
A quick fly through the options for conditional uniqueness.
Requirement #1: I want uniqueness on a column but only under certain conditions.
For example, I have an active flag and I want to make sure there is only one active record for a particular attribute but there can be many inactive rows.
create table t1 (col1 number not null ,col2 varchar2(24) not null ,is_active number(1) not null ,constraint pk_t1 primary key (col1) ,constraint ck_t1_is_active check (is_active in (1,0)));
Solution #1: A unique index on an expression which evaluates to null when the condition is not met.
create unique index i_t1 on t1 (case when is_active = 1 then col2 end); unique index I_T1 created. insert into t1 values(1,'SHAGGY',1); 1 rows inserted. insert into t1 values(2,'SHAGGY',1); SQL Error: ORA-00001: unique constraint (I_T1) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.
Only one active SHAGGY allowed.
But multiple inactives allowed:
insert into t1 values(2,'SHAGGY',0); 1 rows inserted. insert into t1 values(3,'SHAGGY',0); 1 rows inserted.
Solution #2: A virtual column with a unique constraint
drop index i_t1; index I_T1 dropped. alter table t1 add (vc_col2 varchar2(24) generated always as (case when is_active = 1 then col2 end)); table T1 altered. alter table t1 add constraint uk_t1 unique (vc_col2); table T1 altered.
Note that now we have a virtual column we have to be very aware of insert statements with no explicit column list:
insert into t1 values(4,'SCOOBY',1); SQL Error: ORA-00947: not enough values 00947. 00000 - "not enough values"
Unless we’re lucky enough to be on 12c and use the INVISIBLE syntax:
alter table t1 add (vc_col2 varchar2(24) invisible generated always as (case when is_active = 1 then col2 end));
But as this example is on 126.96.36.199:
insert into t1 (col1, col2, is_active) values(4,'SCOOBY',1); 1 rows inserted. insert into t1 (col1, col2, is_active) values(5,'SCOOBY',1); SQL Error: ORA-00001: unique constraint (UK_T1) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. insert into t1 (col1, col2, is_active) values(5,'SCOOBY',0); 1 rows inserted. insert into t1 (col1, col2, is_active) values(6,'SCOOBY',0); 1 rows inserted.
Requirement #2: Sorry we forgot to tell you that we insert the new row first and the update the old one to be inactive so we need deferred constraint (hmmm!)
In which case, you can’t have deferred uniqueness on an index so the only option is the virtual column.
alter table t1 drop constraint uk_t1; table T1 altered. alter table t1 add constraint uk_t1 unique (vc_col2) deferrable initially deferred; table T1 altered. insert into t1 (col1, col2, is_active) values(7,'FRED',1); 1 rows inserted. insert into t1 (col1, col2, is_active) values(8,'FRED',1); 1 rows inserted. commit; SQL Error: ORA-02091: transaction rolled back ORA-00001: unique constraint (.UK_T1) violated 02091. 00000 - "transaction rolled back" *Cause: Also see error 2092. If the transaction is aborted at a remote site then you will only see 2091; if aborted at host then you will see 2092 and 2091. *Action: Add rollback segment and retry the transaction. insert into t1 (col1, col2, is_active) values(7,'FRED',1); 1 rows inserted. insert into t1 (col1, col2, is_active) values(8,'FRED',1); 1 rows inserted. update t1 set is_active = 0 where col1 = 7; 1 rows updated. commit; committed.
See previous post on similar approach for conditional foreign key
According to http://en.wikipedia.org/wiki/Perfect_storm:
A “perfect storm” is an expression that describes an event where a rare combination of circumstances will aggravate a situation drastically.
The other day we had a bit of a panic when AUD$ space grew by 45GB in 15 minutes and set off some alerts.
The audit_trail parameter was set to DB, EXTENDED.
Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available.
So, we’re logging SQL statements.
If in DBA_STMT_AUDIT_OPTS we have this:
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE --------- ---------- ------------- -------- ---------- SELECT TABLE NOT SET BY ACCESS
BY ACCESS means:
Oracle Database records separately each execution of a SQL statement, the use of a privilege, and access to the audited object. Given that the values for the return code, timestamp, SQL text recorded are accurate for each execution, this can help you find how many times the action was performed.
Now, given developer read-only access to production, if we then add these sequence of events:
1.A generated sql statement with 17,000 UNION statements each accessing the same table with a different id, e.g.
select * from t1 where id = 1 union select * from t1 where id = 2 union ... select * from t1 where id = 17000;
*no need to comment on whether this is a sensible query to be writing :)
2. The (foolish) developer then setting this statement off in prod.
3. After a few minutes, the developer sensibly realising that running this in prod might not be a good idea and cancelling.
4. Unluckily (for the developer), the sensible cancel operation on the foolish sql statement causing a ORA-01013 sql failure which will then be audited.
5. The audit settings above meaning that 17,000 entries will be written into AUD$, one each for the 17,000 references to T1 in the SQL statement above and each of the 17,000 entries containing the 17,000 line long SQL statement.
The additional 45GB was all taken up by the AUD$.SQLTXT LOB.
To me, this was new information – I didn’t expect AUDIT to write down each and every reference to the table from a single execution – a list of DISTINCT objects in the statement would have been my expectation. And it is a concern that given just SELECT access, it’s so easy to generate significant volumes of data.
Should you wish to play around with this scenario, steps are:
1. If you need to change audit_trail param, it’s not dynamic, you’ll need to restart:
ALTER SYSTEM SET audit_trail=DB,EXTENDED scope=spfile;
2. Setup SELECT auditing BY ACCESS for failures (for testing, you can set it up for success as well of course):
AUDIT SELECT TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;
3. As a non-SYS user, create a table to select from:
CREATE TABLE t1 AS SELECT * FROM dba_objects;
4. Generate a large SQL statement with lots of table references:
set serveroutput on declare l_sql clob; begin dbms_output.put_line('select * from t1 where object_id = 1'); for i in 2 .. 10000 loop dbms_output.put_line(' union select * from t1 where object_id = '||i); end loop; end; /
5. Kick it off and then cancel the execution.
6. Wait a significant amount of time for the background session to write the audit data to AUD$. For this smaller/simpler SQL above, it took my underpowered VM several minutes to write down 4GB of audit data.
6. Check out the growth of your AUD$ table (or rather the associated LOB):
select segment_name, sum(bytes) from dba_segments where segment_name in ('AUD$') or segment_name in (select segment_name from dba_lobs where table_name = 'AUD$') group by segment_name;
Is this an appropriate response?
Others have raised this before – in fact I think I’ve raised the question before – but my initial search on Oracle Support did not turn up an answer so I thought I would raise a question again.
Question only: Null predicates in DBA_HIST_SQL_PLAN
Everyone knows that the FILTER_PREDICATES and ACCESS_PREDICATES columns in DBA_HIST_SQL_PLAN are not populated.
If I remember correctly, this was originally a bug in 9i and statspack and has been carried forward into AWR.
As a result, any execution plan fetched using DBMS_XPLAN.DISPLAY_AWR has no predicate information
The statement which populates the underlying data has hardcoded NULLS.
Why do these continue to be null through all versions?
Is there any plan to address this?
I even provided the AWR statement with the hardcoded NULLS:
INSERT INTO wrh$_sql_plan sp (snap_id, dbid, sql_id, plan_hash_value, id, operation, options, object_node, object#, object_owner, object_name, object_alias, object_type, optimizer, parent_id, depth, position, search_columns, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, other, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, remarks, timestamp, other_xml) SELECT /*+ ordered use_nl(p) PARAM(‘_flush_plan_in_awr_sql’, 1) PARAM(‘_cursor_plan_unparse_enabled’, ‘FALSE’) */ :lah_snap_id lsnid, :dbid dbid, p.sql_id, p.plan_hash_value, p.id, p.operation, p.options, p.object_node, p.object#, p.object_owner, p.object_name, p.object_alias, p.object_type, p.optimizer, p.parent_id, p.depth, p.position, p.search_columns, p.cost, p.cardinality, p.bytes, p.other_tag, p.partition_start, p.partition_stop, p.partition_id, p.other, p.distribution, p.cpu_cost, p.io_cost, p.temp_space, NULL access_predicates, NULL filter_predicates, NULL projection, p.time, p.qblock_name, p.remarks, p.timestamp, p.other_xml FROM x$kewrattrnew atn, x$kewrtsqlplan spn, v$sql_plan p WHERE atn.str1_kewrattr = spn.sqlid_kewrspe AND atn.num1_kewrattr = spn.planhash_kewrspe AND spn.sqlid_kewrspe = p.sql_id AND spn.planhash_kewrspe = p.plan_hash_value AND spn.childaddr_kewrspe = p.child_address
Please provide 10046 showing the issue
blah, blah, blah
instructions for doing a trace
blah, blah, blah
Might as well have been an automated response or “did you try turning it off and on again?”