Snappy title, huh?
Aka: Why a sql plan baseline may be no guarantee of stability.
The other day, a problematic plan flip…
Cue much discussion about plan flips etc.
My thoughts on stability are that the priority for most application owners is stability and predictability but that does not tally with the defaul CBO behaviour and potentially you have to turn off a helluva lot to even get close.
I have (more...)
Plan degradations on upgrade are normal.
This one’s no different.
On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.
First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.
What does FIRST_ROWS mean?
From 11g doco:
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
If any sort of (more...)
Below is a SQL statement from a performance problem I was looking at the other day.
This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.
You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)
It is well-known that AWR, and Statspack before, take snapshots of V$ views (or rather the underlying objects) to produce the data in AWR.
It is also well-known that, when considering sql and its statistics in the shared pool, if something big hitting happens but the big-hitter is no longer in the shared pool by the time of the snapshot, then it can’t be recorded in your AWR picture of activity.
But like many things (more...)
Here’s a query which I find useful in order to have a very quick comparison across AWR snapshots of the high level time model statistics.
The numbers should match those in the associated section in the AWR report.
If you feel compulsed, obsessively, with tuning then you may see some blips here and there which then encourage you to dive into the AWR detail for that snapshot.
Or quite often I get in in the (more...)
The database engine determines the maximum disk I/O size used during multiblock reads (for example, full table scans or index fast full scans) by multiplying the values of the
db_file_multiblock_read_count initialization parameters. The
db_file_multiblock_read_count initialization parameter can be set explicitly, or, as of version 10.2, it’s also possible to instruct the database engine to automatically configure it. For the latter, simply don’t set it.
About the value which is automatically determined by (more...)
I had an interesting encounter with latch: cbc contention early this week. During my oncall I received page for Load of 206.81 exceeded threshold of 150. After I logged into server , the server load average was continously increasing and all the top PIDs were of oracle processes. After logging into database, I saw multiple sessions waiting on latch: cache buffer chains wait event
load average: 258.52, 244.27, 226.15
select username,sql_id,event,count(*) (more...)
ORDIM – The Oracle Multimedia component might be in INVALID state after the database upgrade to 12c. While I was testing the upgrade on test server, received below errors after the catctl.pl completion.
Serial Phase #:69 Files: 1 Use of uninitialized value $gsRTInclusion in concatenation (.) or string at catctl.pl line 1500.
Phases [0-73] End Time:[2015_08_08 08:06:45]
Grand Total Time: 4848s
*** WARNING: ERRORS FOUND DURING UPGRADE ***
In this post I would like to describe a behavior of Oracle Database that, at least for me, isn’t obvious at all. Actually, it’s something that I can’t explain why it works in that way.
Let’s start by setting the scene by describing the schema I’m using for the following tests. As you can see from the image, there are three tables: one table (PARENT) that is referenced by two other tables (CHILD1 and CHILD2). (more...)
A recent case at a client reminded me of something that isn't really new
but not so well known - Oracle by default performs evaluation at the latest possible point in the execution plan.So if you happen to have expressions in the projection of a simple SQL statement that runs parallel
it might be counter-intuitive that by default Oracle won't evaluate the projection in the Parallel Slaves but in the Query Coordinator
- even (more...)
My thoughts on SQL plan management decision points:
SQL Patches are also available and not covered in the above flowchart.
Note to self because it’s just one of those date/timezone-related topics which just doesn’t seem to stick…
Epoch/Unix time – See https://en.wikipedia.org/wiki/Unix_time
Unix time (also known as POSIX time or erroneously as Epoch time) is a system for describing instants in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,
Firstly when converting from Oracle date or timestamp – we need to (more...)
Continuing from the previous part
- which was about the Temp Table Transformation and join cardinality estimates - using the same simple table setup here is a slight variation of the previously used query to demonstrate the potential impact on single table cardinality estimates
explain plan for
cte as (
select /* inline */ id from t1 t
where 1 = 1
*/ * from cte a, cte (more...)
Having published recently two notes
about the Temp Table Transformation highlighting the heuristics based decision and other weaknesses, for example regarding the projection of columns, it's time to publish some more notes about it.The transformation can also have significant impact on cardinality estimates
, both join and single table cardinality.Looking at the difference in the join cardinality estimates of following simple example:
create table t1
rownum as id
, mod(rownum, 10) (more...)
I’ve never really spent time on evolution because most of the time I use baselines for emergency sql plan management and not with the added value of controlled evolution with which the feature was conceived.
But some observations on sql plan baseline evolution originating from the questions:
- What does evolution actually do?
- What information does it use?
- What happens with DML?
Starting point – one baselined plan
1. FTS plan in memory from SQL which (more...)
Heuristic Temp Table Transformation - 2 Some time ago
I've demonstrated the non-cost based
decision for applying the temp table transformation when using CTEs (Common Table/Subquery Expressions). In this note I want to highlight another aspect of this behaviour.Consider the following data creating a table with delibrately wide columns:
create table a
rownum as id
, rownum as id2
, rpad('x', 4000) as large_vc1
, rpad('x', 4000) as large_vc2
, rpad('x', 4000) (more...)
Execution Plan Instability – What is the problem?
The Oracle Optimizer is a complex piece of software and with every release it becomes more complex.
In the beginning, the Optimizer was rule-based.
The Optimizer had a ranked list of heuristics used to optimize a query, picking the lowest ranked rule available.
This rule-based mode, whilst still in use with some internal Oracle dictionary queries, has been unsupported since version 10.1.
This means that (more...)
This is just a heads-up for those thinking about using Edition Based Redefinition (EBR)
and enabling it on an existing schema with objects. Although EBR isn't exactly a new feature its current adoption level is probably not that high (which probably changes in future as Oracle E-Business Suite uses EBR now as part of their default upgrade procedure as far as I understood).I was recently contacted by someone who enabled EBR on an existing (more...)
In general it is known that Function-Based Indexes (FBIs) can no longer be used by the optimizer if the expression contains literals and CURSOR_SHARING = FORCE / SIMILAR (deprecated) turns those literals into bind variables. Jonathan Lewis described the issue quite a while ago here
in detail.In a recent OTN thread
this issue was raised again, but to my surprise when I played around with a test case that mimicked the OP's problem query (more...)
Recently I came across “ORA-00353: log corruption near block change time” error.
Errors in file /oracle/app/diag/rdbms/test/test/trace/test_ms00_54284.trc (incident=1490189):
ORA-00353: log corruption near block change time
ORA-00353: log corruption near block 4631374 change 263470473806052 time 04/16/2015 06:53:16
ORA-00312: online log 43 thread 3: '+REDO02/test/onlinelog/group_43.258.875922665'
ORA-00312: online log 43 thread 3: '+REDO01/test/onlinelog/group_43.258.875922633'
Incident details in: /oracle/app/diag/rdbms/test/test/incident/incdir_1490189/test_ms00_54284_i1490189.trc
Thu Apr 16 08:14:42 2015
Sweep [inc]: completed
Thu Apr 16 08:14:55 2015
LOGMINER: WARNING: error (more...)