Read and monitor Oracle related blogs and news sources, all in one place.
Oracle 12c is around the corner and due that I have received many questions from fellow DBAs about what was new about Backup and Recovery at 11g I have decided to write a small white paper about it.
Hope you will enjoy reading it as much I enjoyed writing it.
drop procedure is_not_really_gone_but;
select object_name || ' ' || object_type
as "Continues on"
from user_objects_ae
where object_name =
'IS_NOT_REALLY_GONE_BUT';
Continues on
-----------------------------------
IS_NOT_REALLY_GONE_BUT NON-EXISTENT
create table is_not_really_gone_but (
until_replaced number,
by_a_non_editioned_object varchar2(1)
default 'Y',
which_we_then_drop varchar2(1)
);
drop table is_not_really_gone_but;
select (more...)
A reader of this blog, VijayS, asked me to share the script I use to demo ITL deadlocks that I mentioned in this comment. Since other readers might be interested, here is the script.
SET TERMOUT ON FEEDBACK ON VERIFY OFF SCAN ON ECHO ON @connect SELECT * FROM v$version (more...)
I’ve previously written about manually rewriting an OR condition into a UNION ALL using LNNVL.
This is a description of a performance issue observed in the real world from the optimizer coming up with a CONCATENATION operation against many child operations including an INLIST operator and other children which then (more...)
My site uses a 3rd party SQL monitoring tool which collects data based on the Oracle view v$sqlstats. The tool collects data for all sql statements which have been executed since the previous collection using the last_active_time column. A few months ago we noticed (after an upgrade to 11g) we (more...)
The following query is a simple outer join between T1 and T2 and the default, unhinted execution plan that I get from 11.2.0.1 (11.1.0.7 and 10.2.0.4 show similar results):
create table t1
as
select
rownum as id
, rpad('x', 100) as filler
, case when rownum > 1e6 then rownum end as null_fk
from
dual
connect by
level <= 1e6
;
exec dbms_stats.gather_table_stats(null, 't1')
create table t2
as
select
rownum as id
, rpad('x', 100) as filler
from
dual
connect by
level <= 1e6
;
exec dbms_stats.gather_table_stats(null, 't2')
create /*unique*/ index t2_idx on t2 (id);
The optimizer preferred a Nested Loop join albeit the fact that the number of estimated loop iterations is pretty large. Notice in particular the cost column: Although the inner rowsource is estimated to be started 1000K times, the cost of doing so corresponds to just a single execution.For reference here is a cost estimate for a similar operation that corresponds to the expected costing model:
select
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.null_fk = t2.id (+)
;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 202M| 4204 (1)| 00:00:51 |
| 1 | NESTED LOOPS OUTER | | 1000K| 202M| 4204 (1)| 00:00:51 |
| 2 | TABLE ACCESS FULL | T1 | 1000K| 101M| 4202 (1)| 00:00:51 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 106 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."NULL_FK"="T2"."ID"(+))
I now had to force the Nested Loop join via a hint, because by default other join methods were preferred by the optimizer. The cost of a single iteration of the loop has now increased to 3, although the access is exactly the same as before (T2 random table access via index lookup of T2.ID), and the cost of the Nested Loop join corresponds to the known formula: Estimated starts times the cost of a single iteration, which is 3000K in this case here, plus the 4200 of the Full Table Scan for accessing the outer row source, plus CPU costing overhead.So what makes the difference between the two? It's the data. The column name chosen for the column in T1 already reveals what's so special: The join column used (NULL_FK) is actually NULL for all rows.The optimizer takes this into account and assumes that none of those rows from the driving row source will actually match a row of the inner row source - in fact the lookup to the inner row source could be short-circuited in some way, since a NULL value by definition isn't supposed to find a match for this join. I haven't investigated to what extent the runtime engine does this, however in the Rowsource Statistics the inner row source is started the expected number of times, although no logical I/O is recorded for it, but some CPU time, so at least some work seems to be done there.Modifying the test case so that more of the FKs are actually non-null shows that the cost calculation is scaled accordingly. In fact the cost calculation is more or less the same than that of a corresponding inner join that could filter out those driving rows with NULL values in the join column.The overall performance of the execution plan is quite decent, so although it looks quite unusual it performs pretty well.In the second part I'll show another interesting, unexpected join execution plan that however can cause real performance problems.
select /*+ use_nl(t1 t2) */
t1.filler as t1_filler
, t2.filler as t2_filler
from
t1
, t2
where
t1.id = t2.id (+)
;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 202M| 3006K (1)| 10:01:21 |
| 1 | NESTED LOOPS OUTER | | 1000K| 202M| 3006K (1)| 10:01:21 |
| 2 | TABLE ACCESS FULL | T1 | 1000K| 101M| 4200 (1)| 00:00:51 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 106 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID"(+))
I just stumbled upon this bug reference on My Oracle Support:
Bug 13262857 Enh: provide some control over DBMS_STATS index clustering factor computation
This enhancement was long due. Previously, when computing the clustering factor during gathering statistics, the value was incremented, whenever the row was not found in the same block as the previous row. Now, it is finally possible to determine how many blocks should be considered when computing clustering factor. The patch delivers an improved DBMS_STATS package body that can be used to set preferences with value TABLE_CACHED_BLOCKS.
The flaw in the over-simplistic and pessimistic original computation was (more...)
I’ve worked on 24×7 systems for more than a decade, and I have a real dislike of downtime. For one, it can be a real pain to agree any downtime with the business, and while RAC can and does help when you do work in a rolling fashion, there is still risk.
The promise of online patching has been a long one, and it is only recently that I dipped my toe in the water with them. Unfortunately, they are not a panacea, and in this blog posting I’m going to share some of the downsides.
Of course not all (more...)
Three items within:
Part 1 – Subquery materialisation not possible
Part 2 – Materialised views
Part 3 – Lock scripts
Part 1 – Subquery materialisation not possible
Yesterday I was reminder of a familiar issue, officially listed as a bug, that can occur when combining subquery materialisation with distributed transactions.
I had previously tuned a query for a colleague, the best approach for which was using implicit materialised subquery factoring (implicit because it wasn’t hinted as /*+ materalize */ but was referenced multiple times in the main query) and I had provided such a solution, without even a thought (more...)
I recently investigated an IO performance “spike” on a large 11.2.0.3 transactional system and I thought I would cover some interesting issues found. I am going to take the approach of detailing the observations made from our production and test systems and avoid attempting to cover how other versions of Oracle behave. The investigation also uncovers a confusing database statistic which we are currently discussing with Oracle Development so they can decide if this is an Oracle coding bug or a documentation issue.
We run a simple home grown database monitor which watches database wait events and sends an email alert if it detects either a (more...)
RSS