ASH buffer size

An undocumented feature is shown in modern Oracle version alert log
For example:
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 33554432 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info;

Before/After Trigger efficiency

Something changes continuously... and trigger efficiency too :).
Please compare Oracle statement about triggers (see "Note" about trigger efficiency)
Oracle 9i, Oracle 10g and Oracle 11g.

I`ve tested this on 11.1.0.7 (the only database version in my hands now).
Its behavior matches 11g and 9i documents.


I used triggers with empty body like this:

create or replace trigger t1_tr_bu
before update on t1
for each row
begin
null;
end;
/

create or replace trigger t1_tr_au
after update on t1
for each row
begin
null;
end;
/


and receive these results:
without triggers



1 row updated.




Statistics
----------------------------------------------------------
 0  recursive calls
 1  db block gets
 7  consistent gets
 0  physical reads
352  redo size
674  bytes sent via SQL*Net to client
591  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed

with before trigger




1 row updated.




Statistics
----------------------------------------------------------
 0  recursive calls
 2  db block gets
 7  consistent gets
 0  physical reads
296  redo size
674  bytes sent via SQL*Net to client
591  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed

with after trigger

1 row updated.

Statistics

----------------------------------------------------------

 0  recursive calls

 1  db block gets

 7  consistent gets

 0  physical reads

 0  redo size

674  bytes sent via SQL*Net to client

591  bytes received via SQL*Net from client

 3  SQL*Net roundtrips to/from client

 1  sorts (memory)

 0  sorts (disk)

 1  rows processed

Struggling with ORA-00980

If you are struggling with ORA-00980, and all things seem to be ok. Even you can use synonym with dblink in ordinary sql. But in pl/sql it raises ORA-00980.
As last resort you can try to check global name for databases from both side of dblink. They both must not match each other.
Here is query to check global name: select * from global_name;

TM enqueue with mode=3

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;

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 an example.
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 |
-----------------------------------------------------------------------------

And my colleague has got another one

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 |
------------------------------------------------------------------------------

In real query there was similar solution.

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.

------------------------------------------------------------------------------------------------------------
| 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.

Quick search of queries without binds

Reading the Oracle Performance tuning guide put in my head an idea about how to find queries which are used literals instead of binds. We can use fact that such queries usually share their plans.

SQL> set pages 9999
SQL> set lines 140
SQL> column sql_text format a80
SQL> select plan_hash_value, sql_text, sql_id, executions
  2    from v$sql x
  3   where plan_hash_value in (select plan_hash_value
  4                               from v$sql
  5                              where plan_hash_value != 0
  6                                and upper(trim(sql_text)) not like '%DUAL'
  7                                and parsing_schema_name not like 'SYS%'
  8                              group by plan_hash_value
  9                             having count(distinct sql_id) > 1)
 10   order by x.plan_hash_value
 11  /

PLAN_HASH_VALUE SQL_TEXT                                                                         SQL_ID        EXECUTIONS
--------------- -------------------------------------------------------------------------------- ------------- ----------
        5300452 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P 1uzhrfn574t12          1
                RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)


        5300452 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE c4pc3jhzjcmc7          1
                R(PRODUCT)) AND   ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPE
                R(ATTRIBUTE) = 'ROLES')

     1023777010 select count(1) from scott.test x where x.object_id=5651                         2ck17fz7g1wz0          1
     1023777010 select count(1) from scott.test x where x.object_id=132                          fpkm8ganm036t          1
     1950795681 select count(1) from scott.test x where x.object_type='TABLE'                    25gm9vc4cgh6r          1
     1950795681 select count(1) from scott.test x where x.object_type='VIEW'                     dmpf0r95suww4          1

6 rows selected.

Of course, you can receive either false negative or false positive results with this method. On the one hand, for example, there are a lot of cases where queries can share plans, i. e. they have common "from" and "where" clauses but different "select" clauses. On the other hand, data distribution patterns can influence CBO`s decisions and lead it to create different plans.