There is yet another case when INSERT statement can be blocked.
According to this Oracle doesn`t acquire table locks on the child table during insert into parent table. But I`ve discovered something else.
Suppose you have a MASTER table and a DETAIL table with UNINDEXED foreign key constraint which references the MASTER table. Foreign key has been created with ON DELETE CASCADE option. And there are some rows in both tables.
Session 1 does the following:
delete from master where id=some_value;
There is no commit here.
Session 2:
delete from master where id=other_value;
Now Session 2 is stuck with TM enqueue request in mode=5 for DETAIL table. This is well known behavior.
But when Session 3 tries to insert a row into the MASTER table, it is stuck too with TM enqueue request in mode=3 for DETAIL table. By the way, mode=3 is used on Oracle version 11.2.0.2, and 10.2.0.4 shows mode=2 in this case.
Here is useful SQL to reproduce the case.
--Setup
drop table detail;
drop table master;
create table master as select * from all_objects where 1=2;
alter table master add constraint master_pk primary key (object_id);
create table detail as select * from all_objects where 1=2;
alter table detail add constraint detail_fk foreign key (object_id) references master on delete cascade;
insert into master select * from all_objects;
insert into detail select * from master;
commit;
--For Session 1
delete from master where object_id=100;
--For Session 2
delete from master where object_id=101;
--For Session 3
insert into master
(owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary/*for 11g, namespace, edition_name*/)
values
('a', 'x', null, 1000000, null, null, sysdate, sysdate, null, null, null, null, null/*, 1, null*/);
--For monitoring
select l.sid,
l.type,
l.lmode,
l.request,
l.block,
o.owner object_owner,
o.object_Name,
q.sql_text
from sys.all_objects o, v$lock l, v$session s, v$sql q
where l.sid = s.sid
and o.object_id(+) = l.id1
and s.sql_id = q.sql_id(+)
order by l.sid, o.object_Name;
Sometimes it is a rocket science.
2011/08/23
2011/07/27
Hint no_query_transformation in select statement with union
Recently I have worked on huge sql query which was made up from several unioned subqueries.
My colleagues and I managed to parallelise job of tuning that query. Each of us worked on a single subquery.
After we had finished I collected all rewritten subquery and tried to run the whole sql.
But something went wrong. It seemed that some of subqueries changed its plans after they had been united into one query.
After a while I realised the root cause of a problem.
One of colleagues used hint no_query_transformation for his tuned subquery.
I was surprised by the fact that the hint in one of subquery could affect other ones in query with union.
Here is a sample.
Suppose I have got tuned query
and plan
with plan
But united query
produces bad plan
The FILTER operation appears instead of HASH JOIN RIGHT ANTI
If I remove no_query_transformation hint the plan changes: my part becomes good, my colleague`s part becomes wrong (join order in this case)
The solution is to remove no_query_transformation hint and to add no_merge hint into subquery in FROM clause.In real query there was similar solution.
select * from t1
where object_id not in (select /*+hash_aj*/ object_id from t2)
union all
select t1.*
from (select /*+no_merge*/ t2.object_id from t2, t3 where t2.object_id>100000 and t2.object_id=t3.object_id) t23,
t1
where t1.object_id = t23.object_id;
My colleagues and I managed to parallelise job of tuning that query. Each of us worked on a single subquery.
After we had finished I collected all rewritten subquery and tried to run the whole sql.
But something went wrong. It seemed that some of subqueries changed its plans after they had been united into one query.
After a while I realised the root cause of a problem.
One of colleagues used hint no_query_transformation for his tuned subquery.
I was surprised by the fact that the hint in one of subquery could affect other ones in query with union.
Here is a sample.
Suppose I have got tuned query
select * from t1
where object_id not in (select /*+hash_aj*/ object_id from t2);
and plan
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5987 | 824K| 322 (3)| 00:00:04 |
|* 1 | HASH JOIN RIGHT ANTI| | 5987 | 824K| 322 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL | T2 | 42135 | 534K| 159 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 160 (2)| 00:00:02 |
-----------------------------------------------------------------------------
select /*+no_query_transformation*/ *
from (select t2.object_id from t2, t3 where t2.object_id>100000 and t2.object_id=t3.object_id) t23,
t1
where t1.object_id = t23.object_id;
with plan
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1248 | 481 (3)| 00:00:06 |
|* 1 | HASH JOIN | | 8 | 1248 | 481 (3)| 00:00:06 |
| 2 | VIEW | | 8 | 120 | 320 (2)| 00:00:04 |
|* 3 | HASH JOIN | | 8 | 520 | 320 (2)| 00:00:04 |
|* 4 | TABLE ACCESS FULL| T2 | 8 | 312 | 159 (2)| 00:00:02 |
|* 5 | TABLE ACCESS FULL| T3 | 8 | 208 | 160 (2)| 00:00:02 |
| 6 | TABLE ACCESS FULL | T1 | 48122 | 6626K| 160 (2)| 00:00:02 |
-----------------------------------------------------------------------------
But united query
select * from t1
where object_id not in (select /*+hash_aj*/ object_id from t2)
union all
select /*+no_query_transformation*/ t1.*
from (select t2.object_id from t2, t3 where t2.object_id>100000 and t2.object_id=t3.object_id) t23,
t1
where t1.object_id = t23.object_id;
produces bad plan
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2414 | 301K| 240K (2)| 00:48:03 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 161 (3)| 00:00:02 |
|* 4 | TABLE ACCESS FULL | T2 | 421 | 5473 | 159 (2)| 00:00:02 |
|* 5 | HASH JOIN | | 8 | 1128 | 481 (3)| 00:00:06 |
| 6 | VIEW | | 8 | 104 | 320 (2)| 00:00:04 |
|* 7 | HASH JOIN | | 8 | 208 | 320 (2)| 00:00:04 |
|* 8 | TABLE ACCESS FULL| T2 | 8 | 104 | 159 (2)| 00:00:02 |
|* 9 | TABLE ACCESS FULL| T3 | 8 | 104 | 160 (2)| 00:00:02 |
| 10 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 160 (2)| 00:00:02 |
------------------------------------------------------------------------------
The FILTER operation appears instead of HASH JOIN RIGHT ANTI
If I remove no_query_transformation hint the plan changes: my part becomes good, my colleague`s part becomes wrong (join order in this case)
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60970 | 9M| | 2026 (43)| 00:00:25 |
| 1 | UNION-ALL | | | | | | |
|* 2 | HASH JOIN RIGHT ANTI| | 60959 | 9M| 2016K| 1164 (1)| 00:00:14 |
| 3 | TABLE ACCESS FULL | T2 | 82571 | 1048K| | 287 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL | T1 | 60959 | 9405K| | 287 (1)| 00:00:04 |
|* 5 | HASH JOIN | | 11 | 2024 | | 862 (1)| 00:00:11 |
|* 6 | HASH JOIN | | 11 | 1881 | | 575 (1)| 00:00:07 |
|* 7 | TABLE ACCESS FULL | T1 | 12 | 1896 | | 287 (1)| 00:00:04 |
|* 8 | TABLE ACCESS FULL | T2 | 12 | 156 | | 287 (1)| 00:00:04 |
|* 9 | TABLE ACCESS FULL | T3 | 12 | 156 | | 287 (1)| 00:00:04 |
--------------------------------------------------------------------------------------
The solution is to remove no_query_transformation hint and to add no_merge hint into subquery in FROM clause.In real query there was similar solution.
select * from t1
where object_id not in (select /*+hash_aj*/ object_id from t2)
union all
select t1.*
from (select /*+no_merge*/ t2.object_id from t2, t3 where t2.object_id>100000 and t2.object_id=t3.object_id) t23,
t1
where t1.object_id = t23.object_id;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5995 | 825K| 803 (61)| 00:00:10 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN RIGHT ANTI| | 5987 | 824K| 322 (3)| 00:00:04 |
| 3 | TABLE ACCESS FULL | T2 | 42135 | 534K| 159 (2)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 160 (2)| 00:00:02 |
|* 5 | HASH JOIN | | 8 | 1128 | 481 (3)| 00:00:06 |
| 6 | VIEW | | 8 | 104 | 320 (2)| 00:00:04 |
|* 7 | HASH JOIN | | 8 | 208 | 320 (2)| 00:00:04 |
|* 8 | TABLE ACCESS FULL| T2 | 8 | 104 | 159 (2)| 00:00:02 |
|* 9 | TABLE ACCESS FULL| T3 | 8 | 104 | 160 (2)| 00:00:02 |
| 10 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 160 (2)| 00:00:02 |
------------------------------------------------------------------------------
2011/01/17
Has FIRST ROW optimization taken place
How to identify does the CBO use FIRST ROW optimization for a given query?
I ran into this question several days ago when I was tuning a pagination query.
I received a plan with COUNT STOPKEY operator which was right above VIEW operator.
Rows from 1 to 10:
I ran into this question several days ago when I was tuning a pagination query.
I received a plan with COUNT STOPKEY operator which was right above VIEW operator.
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 38 (100)| |
| 1 | NESTED LOOPS | | 34 | 70992 | 38 (0)| 00:00:01 |
|* 2 | VIEW | | 34 | 69428 | 4 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 34 | 68986 | 4 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| APP_USER_RATE | 34 | 1428 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_RATE_GETDESC | 34 | | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_IOT_TOP_805401 | 1 | 20 | 0 (0)| |
|* 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 46 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 1 | | 0 (0)| |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">=:B4)
3 - filter(ROWNUM<=DECODE(:B3,(-1),10000000000,:B3))
5 - filter( IS NOT NULL)
6 - filter("FLAG"=1)
7 - access("APP_ID"=:B1)
8 - access("APP_ID"=:B1 AND "EMPLOYEE_ID"=:B2)
filter("FLAG"=1)
9 - filter("E"."FLAG"=1)
10 - access("from$_subquery$_002"."EMPLOYEE_ID"="E"."EMPLOYEE_ID")
This fact urged me to doubt if FIRST ROW optimization has taken place.
As stated here
"Where you see the VIEW operator this is Oracle telling you that it has had to “suspend” operations and produce an intermediate result set by running the “sub-plan” under the VIEW".
According this I could not decide exactly would COUNT STOPKEY have any influence on execution of VIEW part. I thought up a way to check what was going on.
I compared A-Rows column for plans which were executed with different pagination range, i. e. first one was execited to receive rows from 1 to 10
and second one was executed to receive rows from 11 to 20. I observed that VIEW part for both plans had strongly correlated values in A-Rows column with pagination range. I believe that this fact can be used to prove FIRST ROW optimization occurrence.
Rows from 1 to 10:
---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------- | 1 | NESTED LOOPS | | 1 | 34 | 10 |00:00:00.01 | 26 | |* 2 | VIEW | | 1 | 34 | 10 |00:00:00.01 | 4 | |* 3 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 4 | | 4 | VIEW | | 1 | 34 | 10 |00:00:00.01 | 4 | |* 5 | FILTER | | 1 | | 10 |00:00:00.01 | 4 | |* 6 | TABLE ACCESS BY INDEX ROWID| APP_USER_RATE | 1 | 34 | 10 |00:00:00.01 | 3 | |* 7 | INDEX RANGE SCAN | IDX_RATE_GETDESC | 1 | 34 | 10 |00:00:00.01 | 1 | |* 8 | INDEX UNIQUE SCAN | SYS_IOT_TOP_805401 | 1 | 1 | 1 |00:00:00.01 | 1 | |* 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 10 | 1 | 10 |00:00:00.01 | 22 | |* 10 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 10 | 1 | 10 |00:00:00.01 | 12 | ----------------------------------------------------------------------------------------------------------------------Rows from 11 to 20:
---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------- | 1 | NESTED LOOPS | | 1 | 34 | 10 |00:00:00.01 | 26 | |* 2 | VIEW | | 1 | 34 | 10 |00:00:00.01 | 4 | |* 3 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 4 | | 4 | VIEW | | 1 | 34 | 20 |00:00:00.01 | 4 | |* 5 | FILTER | | 1 | | 20 |00:00:00.01 | 4 | |* 6 | TABLE ACCESS BY INDEX ROWID| APP_USER_RATE | 1 | 34 | 20 |00:00:00.01 | 3 | |* 7 | INDEX RANGE SCAN | IDX_RATE_GETDESC | 1 | 34 | 20 |00:00:00.01 | 1 | |* 8 | INDEX UNIQUE SCAN | SYS_IOT_TOP_805401 | 1 | 1 | 1 |00:00:00.01 | 1 | |* 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 10 | 1 | 10 |00:00:00.01 | 22 | |* 10 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 10 | 1 | 10 |00:00:00.01 | 12 | ----------------------------------------------------------------------------------------------------------------------At this point I decided that I had reached optimization goal for the query.
Subscribe to:
Posts (Atom)