Sunday, July 22, 2012

ORA-0060 – DEADLOCK

We all know deadlock is the situation where DBA cannot do anything and directly can be blamed on application saying – this is an application issue. Oracle writes the same in its trace file of deadlock. 

The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL.

I agree to above but we, DBA can help application developer in determining cause of the deadlock. Here are some samples to check the clause of the deadlock.

It is very important to analyze the deadlock graph especially blocker and waiter waits. In normal situations when it is purely an application coding issue, the waits would be row exclusive “X” when other cases this may change. Let’s see in the following examples.

Traditional Deadlock


create table u (id number, name varchar2(20));
insert into u values (1, 'OPERATION1');
insert into u values (2, 'OPERATION2');
commit;

SRSESSION 1SESSION 2
1update u set name='OPERATION_1' where id=1;
2update u set name='OPERATION_2' where id=2;
3update u set name='OPERATION_2' where id=2;
4update u set name='OPERATION_1' where id=1;
5DEADLOCK ERROR

Deadlock graph:


                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-00080009-00002c6b       21     133     X            24      14           X
TX-00020005-00002c70       24      14     X            21      133          X

session 133: DID 0001-0015-00000015 session 14: DID 0001-0018-00000018
session 14: DID 0001-0018-00000018 session 133: DID 0001-0015-00000015


Rows waited on:
  Session 133: obj - rowid = 000036A3 - AAADajAAEAAAAC0AAB
  (dictionary objn - 13987, file - 4, block - 180, slot - 1)
  Session 14: obj - rowid = 000036A3 - AAADajAAEAAAAC0AAA
  (dictionary objn - 13987, file - 4, block - 180, slot - 0)

----- Information for the OTHER waiting sessions -----


Session 14:
  sid: 14 ser: 35 audsid: 150059 user: 34/SANDY flags: 0x45
  pid: 24 O/S info: user: SYSTEM, term: LVNTW03526D3BS, ospid: 4268
    image: ORACLE.EXE (SHAD)
  client details:
    O/S info: user: ASIAPAC\N228937, term: LVNTW03526D3BS, ospid: 2700:1872
    machine: ASIAPAC\LVNTW03526D3BS program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
    update u set name='OPERATION_1' where id=1


----- End of information for the OTHER waiting sessions -----

Information for THIS session:


----- Current SQL Statement for this session (sql_id=4r2c68v92xnn2) -----
update u set name='OPERATION_2' where id=2


Deadlock trace file also gives you all waiters and holder sessions’ information. In above trace file section, the first highlighted query is of session that had caused the deadlock. The next highlighted query is of the session which was terminated by oracle and got “DEADLOCK DETECTED” error.

Case 1 – Insert in Primary key column

create table primary (id number primary key, name varchar2(20));
SRSESSION 1SESSION 2
1insert into primary values (1, 'Sandeep');
2insert into primary values (2, 'Redkar');
3insert into primary values (2, 'Redkar');
4insert into primary values (1, 'Sandeep');
5DEADLOCK ERROR

Deadlock graph:
                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-00030007-00002c81       24      14     X            21     133           S
TX-00060005-00002c9e       21     133     X            24      14           S

session  14: DID 0001-0018-00000013 session 133: DID 0001-0015-00000010
session 133: DID 0001-0015-00000010 session 14: DID 0001-0018-00000013

Rows waited on:
  Session 14: no row
  Session 133: no row

Note – This deadlock situation can be possible when using unique index also.


Solution – To avoid this deadlock, you can make use of sequences.

Case 2 – Insert in Primary key/Foreign key.

create table foreign (id number, addr varchar2(30), phone number);


alter table foreign add constraint fk_id foreign key (id) references primary(id);

insert into primary
select level, dbms_random.string('A',10)
from dual connect by level <= 100;
commit;

insert into foreign
select round(dbms_random.value(1,100)),
dbms_random.string('A',10),
round(dbms_random.value(1000000,9999999))
from dual connect by level<=1000;


commit;
SRSESSION 1SESSION 2
1update foreign set phone=9999999 where id=20;
2update foreign set phone=9999999 where id=10;
3update primary set id=50, name='Sandeep' where id=50;
4update primary set id=60, name='Sandy' where id=60;
5DEADLOCK ERROR

Deadlock graph:

                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TM-0000369e-00000000       21     133    SX   SSX      24      14    SX   SSX
TM-0000369e-00000000       24      14    SX   SSX      21     133    SX   SSX

session 133: DID 0001-0015-00000011 session 14: DID 0001-0018-00000014
session 14: DID 0001-0018-00000014 session 133: DID 0001-0015-00000011

Rows waited on:
  Session 133: no row
  Session 14: no row

Solution – In this situation, oracle needs TM lock when inserting into foreign key column. In earlier oracle releases (8i and prior) even when inserting into primary column, you have to have TM lock on foreign key table. This has been taken care in release 9i onwards. But issue with foreign key is still there.


To avoid this situation there are two solutions –
1. Create index on foreign key (Oracle by default creates index when defining primary key constraint. But oracle will never creates index when defining foreign key constraint)
2. Do not update primary key column, though with same/other value.

Case 3– Bitmap index – Part I

create table t (id number, gender varchar2(1));
create bitmap index bit_t_ind on t(gender);
SRSESSION 1SESSION 2
1insert into t values (1,'M');
2insert into t values (2,'F');
3insert into t values (4,'F');
4insert into t values (3,'M');
5DEADLOCK ERROR

Deadlock graph:

                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-00060021-00002c9f       24      14     X            21     133           S
TX-00050005-00002c7c       21     133     X            24      14           S


session 14: DID 0001-0018-00000015 session 133: DID 0001-0015-00000012
session 133: DID 0001-0015-00000012 session 14: DID 0001-0018-00000015


Rows waited on:
  Session 14: no row
  Session 133: no row

Solution – In OLTP system we should avoid bitmap index when table is highly used by the application for DML operations. This deadlock situation can be avoided by creating b-tree (normal) index based on the index selectivity. In the deadlock trace file, you can see “no rows” on “Rows waiting on” section. Most of the cases, this can be found when using bitmap index.

If you remember, we got “no rows” information when inserting into primary key columns. It is difficult to distinguish which had caused the deadlock (bitmap or primary key). Best way to check for which type of index exists on the table.

Case 4– Bitmap index – Part II


insert into t
select level, decode(mod(level,2),0,'M','F')
from dual connect by level<=100;

commit;


SRSESSION 1SESSION 2
1Insert into t values (1,'M');
2insert into t values (2,'F');
3insert into t values (4,'F');
4insert into t values (3,'M');
5DEADLOCK ERROR

Deadlock graph:

                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-0006001b-00002ca0       24      14     X            21     133           S
TX-00090021-00002c6c       21     133     X            24      14           S

session 14: DID 0001-0018-00000016 session 133: DID 0001-0015-00000013
session 133: DID 0001-0015-00000013 session 14: DID 0001-0018-00000016

Note – This is very similar situation, as previous case (Part I). If you observe, the trace file, in previous example, we got “no rows” and here we got the rows information even test case is same. Why is this difference? The difference between these two test cases is, in previous case the table was empty and not in the above case. Now you may think, how to distinguish whether this has caused because of bitmap issue? In Rows waited on section, below rowid information, there is object number given. If you check the object number you can find this is of bitmap index.

SQL> select owner, object_name, object_type from dba_objects where object_id=13984; 
 


OWNER  OBJECT_NAME          OBJECT_TYPE
------ -------------------- -------------------
SANDY  BIT_T_IND            INDEX


Case 5– Autonomous Transaction


create table d (v varchar2(20));
insert into d values ('S');
commit;

CREATE OR REPLACE PROCEDURE proc_del
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM D;
COMMIT;
END;
/
SRSESSION 1
1delete from d;
2exec proc_del
3DEADLOCK ERROR

Deadlock graph:
                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-00060005-00002c9f       21     133     X            21     133           X


session 133: DID 0001-0015-00000014 session 133: DID 0001-0015-00000014


Rows waited on:
Session 133: obj - rowid = 000036A1 - AAADahAAEAAAACsAAB
(dictionary objn - 13985, file - 4, block - 172, slot - 1)

Solution – This is example of autonomous transaction. Triggers are also form of autonomous transactions. This issue may also seen with update operation or combination of update and delete operation.


The solutions above given are just sample situations wherein Deadlock can happen. There are many other situations wherein Deadlock can occur. The deadlock graph gives the hint to identify the deadlock cause. Application desing needs to be changed for Transaction Level Deadlocks but other than this, there could be solutions to Deadlock. 

To sum up, though the deadlock is an issue of application, we can identify the cause and solution can provided to get rid of deadlock issue.

Saturday, December 19, 2009

Index column ordering and performance

When we are talking about why oracle is not picking up index, one of the well known reason is cardinality. When data is uniform, an index is selective only when cardinality is not very low. When data is not uniform, we can have histograms but here we will keep that feature apart.

So lets assume we want to create a composite index on two columns which both are part of the filter condition. One column (ID) is having high cardinality and another (Status) is having very low cardinality. Then what should be the column sequence for that index? The obivious answer is, for better performance first column should be one which is having high cardinality.

But in reality, any column sequence will give the same performance. To demonstrate lets create following testcase. We will create a table and will choose two columns object_id and status. The column object_id is distinct and update the status column such that it will only two values with same number of rows.


SQL> create table t as select * from dba_objects;

Table created.




SQL> update t set status=decode(mod(rownum,2),0,'VALID','INVALID');

50623 rows updated.




SQL> commit;

Commit complete.




SQL> select status, count(1) from t group by status;

STATUS COUNT(1)
------- ----------
INVALID 25312
VALID 25311




SQL> create index ind_t on t(object_id, status);

Index created.




SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.





SQL> select * from t where object_id=100 and status='INVALID';


Execution Plan
----------------------------------------------------------
Plan hash value: 4013845416

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=100 AND "STATUS"='INVALID')


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


The query executed with 4 consistent gets. Now lets create the index with reverse order and check the performance.


SQL> drop index ind_t;

Index dropped.




SQL> create index ind_t on t(status, object_id);

Index created.




SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.




SQL> select * from t where object_id=100 and status='INVALID';


Execution Plan
----------------------------------------------------------
Plan hash value: 4013845416

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("STATUS"='INVALID' AND "OBJECT_ID"=100)


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



Even after changing the column order, the query got executed with 4 consistent gets. So no performance degradation.

So now we have prooved that column order is having no performance on the query.

But this column order may cause performance degradation. Lets assume, In the application, we have some query only with object_id as a filter. If there is no individual index on object_id column, then oracle will use this index and here column ordering will impact.

We will execute the following query with index having order (object_id, status) and lets check the execution plan.



SQL> select * from t where object_id=100;


Execution Plan
----------------------------------------------------------
Plan hash value: 4013845416

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=100)


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



It is doing Index range scan with 4 consistent gets. Lets reverse the column order (status, object_id) and check the execution plan.


SQL> select * from t where object_id=100;


Execution Plan
----------------------------------------------------------
Plan hash value: 2246305531

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_T | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=100)
filter("OBJECT_ID"=100)


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


Oracle has opted for index skip scan and consistent gets are also double against our previous execution plan.

In the above example, other column is having only 4 distinct values, but if the distinct values are higher then oracle may ignore this index and will opt for Full table scan.

To sum up, when query is having multiple filter conditions, column order of a composite index on those columns does not matter. But it may have impact on other queries having not all filter conditions.

Friday, December 18, 2009

Query and Transitive Closure

I have got some of the comments on my last blog - "Query Performance & Join Conditions" which were asking about the transitive closure. The example that I have given was not for transitive closure. Oracle has introduced transitive

closure when the literal values are present for joining conditions. For example when I have join condition on two tables and same column of one of the table is having filter condition then other table will use the same filter condition.

To demonstrate, we will see the following test case.


SQL> create table t1 as select rownum "N", rownum*30 "M" from dual connect by level <=10000;

Table created.


SQL> create table t2 as select rownum+20 "R", rownum*50 "T" from dual connect by level <=10000;

Table created.



SQL> create index ind_t1 on t1(n);

Index created.



SQL> create index ind_t2 on t2(r);

Index created.



SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

PL/SQL procedure successfully completed.



SQL> set autot trace exp stat
SQL> select n, m, t
2 from t1, t2
3 where t1.n = t2.r
4 and t1.n=30;


Execution Plan
----------------------------------------------------------
Plan hash value: 863060763

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 4 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 16 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 8 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."N"=30)
6 - access("T2"."R"=30)




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

Check for "Predicate Information" in above-mentioned execution plan. When I have assigned n=30, it has also picked up r=30. Now one might have question, what is the use of this and will it impact performance?

Based on the input filter condition, oracle may opt for better execution plan.
To demonstrate, lets create t2 table with R column as 4 distinct values and execute the above query again.


SQL> create table t1 as select rownum "N", rownum*30 "M" from dual connect by level <=10000;

Table created.



SQL> create table t2 as select mod(rownum,4) "R", rownum*50 "T" from dual connect by level <=10000;

Table created.



SQL> create index ind_t1 on t1(n);

Index created.



SQL> create index ind_t2 on t2(r);

Index created.



SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

PL/SQL procedure successfully completed.



SQL> set autot trace exp stat
SQL> select n, m, t
2 from t1, t2
3 where t1.n = t2.r
4 and t2.r=3;

2500 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2470703826

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 37500 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 2500 | 37500 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 2500 | 17500 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."N"="T2"."R")
3 - access("T1"."N"=3)
4 - filter("T2"."R"=3)


As per the above plan, based on filter condition(N=3), oracle opted for index scan. This is because of transitive closure. Now what would have happen if transitive closure was not taken place.
For this, lets modify query as follows -


SQL> select n, m, t
2 from t1, (select /*+ no_merge */ * from t2 where r=3) t2
3 where t1.n = t2.r;

2500 rows selected.



Execution Plan
----------------------------------------------------------
Plan hash value: 157564253

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 90000 | 14 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 2500 | 90000 | 14 (8)| 00:00:01 |
| 2 | VIEW | | 2500 | 70000 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 2500 | 17500 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 10000 | 80000 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."N"="T2"."R")
3 - filter("R"=3)


If we execute same query, oracle would have opted for nested loops.

Conclusion : Transitive closure is the feature which works when we are using filter predicate in query with join condition. Oracle will consider transitive closure with all equility, non-equility predicates.

Tuesday, September 22, 2009

Query Performance & Join Conditions

Oracle database performance is measured by application queries those are having impact on host resources. Oracle generates plan as per the input values and that decides the resource usage effeciency.

While working on performance issue, one of the problem reported by the user that one of the module is not functioning and having some issue with query. When we traced the session found one of the query doing FTS and retriving few records only. This was one of the top resource consumer query in the trace file.

The query was looking very normal and checked for the indexes, statistics of the tables etc. Though the statistics were collected recently, it was not the problem with that.

After investigation, it was found that one of the condition was not correctly defined and the same has been confirmed by developer. When you have multi table join in the query, we have to be very careful about equility predicates.

Here I want to remind one of the fact in oracle -
As per mathematics rules, when we say a=b and b=c, we can conclude a=c. But in oracle this is not the case. Oracle never concludes a=c.

To illustrate this, let us create a test case -

Create two tables and insert some random records in it. Also create required indexes. As the query provided was from the production, here I am not using the same table names. Also I have picked up only the problematic part of the query.


SQL> create table cu_all (custid number, addr varchar2(200), ph number, cano number, acctype varchar2(10));

Table created.

SQL> create table ca_receipt (custid number, caamt number, cadt date, totbal number);

Table created.

SQL>
SQL> insert into cu_all
2 select lvl,
3 dbms_random.string('A',30),
4 round(dbms_random.value(1,100000)),
5 round(dbms_random.value(1,10000)),
6 dbms_random.string('A',10)
7 from (select level "LVL" from dual connect by level <=200000);

200000 rows created.

SQL> insert into ca_receipt
2 select round(dbms_random.value(1,10000)),
3 round(dbms_random.value(1,100000)),
4 sysdate - round(dbms_random.value(1,100000)),
5 round(dbms_random.value(1,100000))
6 from (select level "LVL" from dual connect by level <=500000);

500000 rows created.

SQL> create unique index pk_cu_all_ind on cu_all(custid);

Index created.

SQL> create index ind2_cu_all on cu_all(CANO);

Index created.

SQL> create index ind_ca_receipt_custid on ca_receipt(custid);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'CU_ALL', cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'CA_RECEIPT', cascade=>true);

PL/SQL procedure successfully completed.



Now let us execute the query with trace on. This is the similar query which was provided to me.



SQL> set autot trace
SQL> SELECT ca.*, cu.*
2 FROM ca_receipt CA,
3 cu_all CU
4 WHERE CA.CUSTID = CU.CUSTID
5 AND CA.CUSTID IN (SELECT CUSTID FROM cu_all START WITH custid = 2353
6 CONNECT BY PRIOR CUSTID = CANO)
7 ORDER BY ACCTYPE DESC;

289 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3186098611

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 81000 | 504 (2)| 00:00:07 |
| 1 | SORT ORDER BY | | 1000 | 81000 | 504 (2)| 00:00:07 |
|* 2 | HASH JOIN | | 1000 | 81000 | 503 (2)| 00:00:07 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1000 | 26000 | 112 (1)| 00:00:02 |
| 5 | VIEW | VW_NSO_1 | 20 | 100 | 21 (0)| 00:00:01 |
| 6 | HASH UNIQUE | | 20 | 180 | | |
|* 7 | CONNECT BY WITH FILTERING | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | CU_ALL | 1 | 9 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_CU_ALL_IND | 1 | | 1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | CONNECT BY PUMP | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| CU_ALL | 20 | 180 | 21 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IND2_CU_ALL | 20 | | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IND_CA_RECEIPT_CUSTID | 50 | | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | CA_RECEIPT | 50 | 1050 | 52 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | CU_ALL | 200K| 10M| 389 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CA"."CUSTID"="CU"."CUSTID")
7 - access("CANO"=PRIOR "CUSTID")
9 - access("CUSTID"=2353)
13 - access("CANO"=PRIOR "CUSTID")
14 - access("CA"."CUSTID"="CUSTID")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2249 consistent gets
25 physical reads
0 redo size
11748 bytes sent via SQL*Net to client
729 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
289 rows processed

If you look at the query, it seems to be normal one.

But the problem is here-

Query is having two tables CA and CU. From the inner CU table query, it fetches records and joins with CA table an CA table Joins with CU table using the same column.
Here the inner query joins with CA table and cardinality of the query gets changed. So it is opting FTS when joining to CU table again.
This is causing the performance bottleneck. So to resolve the issue, I have change the joining condition.

Now if we check, following is the proper execution plan. Also the consistents gets have been reduced to 797 against 2249 in original query.


SQL> SELECT ca.*, cu.*
2 FROM ca_receipt CA,
3 cu_all CU
4 WHERE CA.CUSTID = CU.CUSTID
5 AND CU.CUSTID IN (SELECT CUSTID FROM cu_all START WITH custid = 2353
6 CONNECT BY PRIOR CUSTID = CANO)
7 ORDER BY ACCTYPE DESC;

289 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3713271440

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 81000 | 133 (2)| 00:00:02 |
| 1 | SORT ORDER BY | | 1000 | 81000 | 133 (2)| 00:00:02 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1000 | 81000 | 132 (1)| 00:00:02 |
| 4 | NESTED LOOPS | | 20 | 1200 | 42 (3)| 00:00:01 |
| 5 | VIEW | VW_NSO_1 | 20 | 100 | 21 (0)| 00:00:01 |
| 6 | HASH UNIQUE | | 20 | 180 | | |
|* 7 | CONNECT BY WITH FILTERING | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | CU_ALL | 1 | 9 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_CU_ALL_IND | 1 | | 1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | CONNECT BY PUMP | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| CU_ALL | 20 | 180 | 21 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IND2_CU_ALL | 20 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | CU_ALL | 1 | 55 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_CU_ALL_IND | 1 | | 0 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IND_CA_RECEIPT_CUSTID | 50 | | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | CA_RECEIPT | 50 | 1050 | 52 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("CANO"=PRIOR "CUSTID")
9 - access("CUSTID"=2353)
13 - access("CANO"=PRIOR "CUSTID")
15 - access("CU"."CUSTID"="CUSTID")
16 - access("CA"."CUSTID"="CU"."CUSTID")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
797 consistent gets
1 physical reads
0 redo size
11748 bytes sent via SQL*Net to client
729 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
289 rows processed


Hence to sum up, oracle gives us the output based on our input values/conditions. Any query must have proper joining condition when multiple tables are involved.

Sunday, June 21, 2009

ORA-600 [ktadrprc-1]

During my visit to the one of the customer, I found following error while dropping old partition from partitioned table.

ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], []

When we search metalink, it was found that this error can occur when there is mismatch between seg$ and ind$ entries. There was a mismatch because of an index entry with the no-segment. This is the feature introduced by oracle to test the index selectivity without creating an index. Following is the test case for the same –

Create a partitioned table



SQL> create table partt (n number, dt date, v varchar2(20)) partition by range (dt)
2 (
3 partition p1 values less than (to_date('01-JAN-2009','DD-MON-YYYY')),
4 partition p2 values less than (to_date('02-JAN-2009','DD-MON-YYYY')),
5 partition p3 values less than (to_date('03-JAN-2009','DD-MON-YYYY'))
6 );

Table created.





Create one normal index and one nosegment index.



SQL> create index indpart on partt(n) local;

Index created.

SQL> create index indpartx on partt(v) local nosegment;

Index created.



Note that, virtual index entry cannot be found in dba_indexes but is available in dba_ind_columns.



SQL> select owner, index_name, table_name from dba_indexes where table_name='PARTT';

OWNER INDEX_NAME TABLE_NAME
---------- ------------------------------ ------------------------------
SYS INDPART PARTT

1 row selected.

SQL> select index_owner, index_name, table_owner, table_name, column_name
2 from dba_ind_columns where table_name='PARTT';

INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
------------ ------------ ------------ ------------ ------------
SYS INDPARTX SYS PARTT V
SYS INDPART SYS PARTT N

2 rows selected.





Now, when we try to drop one of the partitions, we will get the error.



SQL> alter table partt drop partition p1;
alter table partt drop partition p1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], []



Solution:

To resolve this error, when we drop the virtual index, the partition got dropped.



SQL> drop index indpartx;

Index dropped.

SQL> alter table partt drop partition p1;

Table altered.


Note :

The solution given above is valid for 10gR2 (tested) version. For previous versions (Oracle 8i and 9i), even dropping an index will give following ORA-600 error.



SQL> drop index indpartx;

drop index indpartx
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [4823], [], [], [], [], [], [], []


Whereas in 8.1.7.4 version while dropping partition, you may get following error –


SQL> alter table partt drop partition p1;
alter table partt drop partition p1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [4610], [], [], [], [], [], [], []



Conclusion:

Be aware of the creating virtual indexes on partitioned tables just for testing purpose. If it is very big production table, you may end up with unwanted troubles.

Monday, January 26, 2009

Missing Tables in Execution Plan

In Oracle 10g, one of the new features introduced for optimizer, named transformation. Using this features it may be possible that oracle internally removes some of the redundant tables those were involved only in join predicates.

To demonstrate the same following test case created –
1. Table with primary key.
2. Table with foreign key referencing the above created table.
3. Query that eliminates the primary key table and hence leads to faster execution.


SQL> create table pk_table (id number, name varchar2(20));

Table created.



SQL> alter table pk_table add constraint pk_id primary key(id);

Table altered.



SQL> create table fk_table (id number, phone number, addr varchar2(200));

Table created.



SQL> alter table fk_table add constraint fk_id foreign key (id) references pk_table (id);

Table altered.



SQL> begin
2 for x in 1..100 loop
3 insert into pk_table values (x, dbms_random.string('A',20));
4 end loop;
5 commit;
6* end;
SQL> /

PL/SQL procedure successfully completed.




SQL> begin
2 for x in 1..500 loop
3 insert into fk_table values (round(dbms_random.value(1,100)), dbms_random.value(1,100000), dbms_random.string('A',100));
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats (user,'PK_TABLE', cascade=>true);

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats (user,'FK_TABLE', cascade=>true);

PL/SQL procedure successfully completed.



SQL> select b.id, b.phone, b.addr
2 from pk_table a,
3 fk_table b
4 where a.id=b.id;



SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1108179040

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 62500 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FK_TABLE | 500 | 62500 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("B"."ID" IS NOT NULL)

13 rows selected.

As shown above, the primary key table is missing in the execution plan. The table PK_TABLE is redundant. As all of us know, every record of foreign key column must be present in primary key table. Hence to fetch data from foreign key table we need have to check primary table for that record.

Optimizer added one extra filter condition i.e. “B.ID is not null”. This is to ensure the data consistency when transformation takes place.

Starting from Oracle 11g, the optimizer has advanced to eliminate anti joins as well. For e.g. check the plan for following query in 10g and 11g.

Query
select * from fk_table b where not exists (select 1 from pk_table a where a.id=b.id);

Plan in 10g

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 2976039779

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 | 128 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| FK_TABLE | 500 | 62500 | 5 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_ID | 100 | 300 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."ID"="B"."ID")

Plan in 11g

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1108179040

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 125 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FK_TABLE | 1 | 125 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"."ID" IS NULL)

This elimination is applicable to view as well. That means, if my view is fetching all columns from primary and foreign key tables and query is written to access only foreign key columns from this view, then also optimizer will eliminate primary table.

Limitations

As this seems to be very interesting changes in optimizer, it is not applicable for the multi-column primary/foreign key columns.

To sum up, now optimizer itself takes care of such unnecessary joins to eliminate table access to improve the query performance.

Monday, September 15, 2008

Materialized view fast refresh and performance.

Oracle uses materialized view to replicate data to remote database in distributed environment. Also it can be used for cache expensive queries in a data warehouse environment. Many of the times, an issue is reported for low performance in fast or incremental refresh. There could be many of the reasons, bugs participating performance issues. One of them is to create an index on snaptime$$ column of corresponding MLOG$ table, which helps to fasten the fast refresh. It is required when MLOG$ table size grows due to any of the reason.

Recently when I was looking into one of such issue, it was found that following query was getting executed (internally by Oracle) in primary database.

Query

SELECT /*+ */ "A2"."CO_ID","A2"."TMCODE","A2"."SPCODE","A2"."SNCODE","A2"."CS_SEQNO",
"A2"."CS_CHANNEL_NUM","A2"."CS_STAT_CHNG","A2"."CS_ON_CBB","A2"."CS_DATE_BILLED",
"A2"."CS_REQUEST","A2"."SN_CLASS","A2"."CS_OVW_SUBSCR","A2"."CS_SUBSCRIPT",
"A2"."CS_OVW_ACCESS","A2"."CS_OVW_ACC_PRD","A2"."CS_OVW_ACC_FIRST","A2"."CS_ACCESS",
"A2"."CS_PENDING_STATE","A2"."CS_CHANNEL_EXCL","A2"."CS_DIS_SUBSCR","A2"."CS_ADV_CHARGE",
"A2"."CS_SRV_TYPE","A2"."SUBPAYER","A2"."USGPAYER","A2"."ACCPAYER","A2"."CS_ENTDATE",
"A2"."CS_OVW_LAST","A2"."INSTALL_DATE","A2"."TRIAL_END_DATE","A2"."CS_ADV_CHARGE_END_DATE",
"A2"."PRM_VALUE_ID","A2"."CURRENCY","A2"."CS_ADV_CHARGE_CURRENCY",
"A2"."REC_VERSION","A2"."SRV_SUBTYPE"
FROM "CONTR_SERVICES" "A2",
(SELECT DISTINCT "A3"."CO_ID" "CO_ID","A3"."SNCODE" "SNCODE","A3"."CS_SEQNO" "CS_SEQNO"
FROM "SYSADM"."MLOG$_CONTR_SERVICES" "A3"
WHERE "A3"."SNAPTIME$$">:1
AND "A3"."DMLTYPE$$"<>'D') "A1"
WHERE "A2"."CO_ID"="A1"."CO_ID"
AND "A2"."SNCODE"="A1"."SNCODE"
AND "A2"."CS_SEQNO"="A1"."CS_SEQNO";


Plan Table
-------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------
| SELECT STATEMENT | | 748K| 87M|1382913 | | |
| HASH JOIN | | 748K| 87M|1382913 | | |
| VIEW | | 751K| 27M| 3585 | | |
| SORT UNIQUE | | 751K| 16M| 3585 | | |
| TABLE ACCESS BY INDEX |MLOG$_CONTR_SER| 751K| 16M| 16 | | |
| INDEX RANGE SCAN |MLOGSNAPTIME | 751K| | 655 | | |
| TABLE ACCESS FULL |CONTR_SERVICES | 125M| 9G| 332467 | | |
-------------------------------------------------------------------------------------

Though there was an index available on SNAPTIME$$ table, the refresh was taking time because the base table size was huge (around 17GB) and it was doing FTS on it.

SQL> select owner, segment_name, bytes/1024/1024 "SIZE" from dba_segments
2 where segment_name='CONTR_SERVICES';

OWNER SEGMENT_NAME SIZE
---------- ------------------------------ ----------
SYSADM CONTR_SERVICES 17216.0156

The mlog$ table size was also very big (around 750 MB) and having millions of records. Oracle opted for FTS because of table statistics available on MLOG$_CONTR_SERVICES table. Following are the available stats –

SQL> select owner, table_name, partitioned, num_rows, blocks, global_stats
2 from dba_tables where table_name='MLOG$_CONTR_SERVICES';

OWNER TABLE_NAME PAR NUM_ROWS BLOCKS GLO
------------ ------------------------------ --- --------------- ---------- ---
SYSADM MLOG$_CONTR_SERVICES NO 22,531,900 95364 YES

Most of the cases, fast refresh materialized view refresh is based on primary key of the base table. If regular fast refresh is happening, the number of rows gets replicated are very less and ideally it should do index scan with primary key. In the above mentioned case, few thousands rows were suppose to replicate.

The customer was having dynamic analyze script wherein they were also analyzing mlog$ tables. To have better performance in fast refresh, we need to purge the mlog$ table regularly and stats on this table must be gathered when the table is empty. To prove this point, I have set customized stats on mlog table.

SQL> exec dbms_stats.set_table_stats ('SYSADM','MLOG$_CONTR_SERVICES',NUMROWS=>0, NUMBLKS=>0);

PL/SQL procedure successfully completed.
.
.
SQL> select owner, table_name, partitioned, num_rows, blocks, global_stats
2 from dba_tables where table_name='MLOG$_CONTR_SERVICES';

OWNER TABLE_NAME PAR NUM_ROWS BLOCKS GLO
------------ ------------------------------ --- --------------- ---------- ---
SYSADM MLOG$_CONTR_SERVICES NO 0 0 YES

After setting above stats, the plan got changed.

Plan Table
-------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 122 | 27 | | |
| NESTED LOOPS | | 1 | 122 | 27 | | |
| VIEW | | 1 | 39 | 24 | | |
| SORT UNIQUE | | 1 | 23 | 24 | | |
| TABLE ACCESS BY INDEX |MLOG$_CONTR_SER| 1 | 23 | 1 | | |
| INDEX RANGE SCAN |MLOGSNAPTIME | 1 | | 655 | | |
| TABLE ACCESS BY INDEX RO|CONTR_SERVICES | 125M| 9G| 3 | | |
| INDEX UNIQUE SCAN |PKCONTR_SERVICE| 125M| | 2 | | |
-------------------------------------------------------------------------------------

In this case, it was doing proper index scan on primary key and fast refresh get successfully completed within minutes.

To sum up, statistics play an important role and must have to be used in proper way. In materialized view refresh following things needs to be remembered

1. The MLOG$ table must be analyze when table is empty.
2. Regular MLOG$ purging is required, where size grows.

Sunday, July 27, 2008

Do we need stats on all columns?

Recently I had visited one of the customers for performance review. As per the current stats gathering policy, they use following method to gather stats on the tables.

dbms_stats.gather_table_stats (user, tabname, cascade=>true, method_opt=>'FOR ALL INDEXED COLUMNS 75');

As can be seen from the command, the input from method_opt is to gather stats on all the indexed columns with bucket size as 75. This means the stats on the other non-index columns will not be collected. This is a wrong practice and stats on the columns of the table are crucial, particularly if these are use in queries.

When I was discussed with the customer, the customer had following misconception.

1. To make decision for index scan; the stats on indexed columns are available.
2. When there is no index available on column, why oracle needs stats on it. It will use FTS for such queries.

This is purely a misconception, even if a column, use in a query, is not indexed; the correct cardinality is very crucial in determining a correct join order. To prove this point, I have created following small test case.

I created two tables (test1 and test2) and indexes on it.

SQL> create table test1 as
2 select mod(LVL, 3) ID, LVL VAL from
3 (select level "LVL" from dual connect by level <= 100000);

Table created.

SQL> create index ind_test1 on test1 (id);

Index created.

SQL> create table test2 as
2 select lvl "ID", dbms_random.string('A',10) "STR" from (select
3 level "LVL" from dual connect by level <=50000);

Table created.

SQL> create index ind_test2 on test2 (id);

Index created.

Let us generate optimizer statistics based on the policy as the customer used.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST1', cascade=>true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 75');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST2', cascade=>true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 75');

PL/SQL procedure successfully completed.

The stats on the table are


SQL> select owner, table_name, partitioned, num_rows, blocks
2 from dba_tables
3 where table_name in ('TEST1','TEST2')
4 and owner = 'TEST';

OWNER TABLE_NAME PAR NUM_ROWS BLOCKS
---------- ---------- --- ---------- ----------
TEST TEST1 NO 100000 186

TEST TEST2 NO 50000 156


break on table_name skip 1
select table_name, column_name,num_distinct, num_nulls, density
from dba_tab_columns
where table_name in ('TEST1','TEST2')
and owner='TEST'
order by table_name;

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_NULLS DENSITY
---------- ---------- ------------ ---------- ----------
TEST1 ID 3 0 .000005
VAL

TEST2 ID 50000 0 .00002
STR

Based on the input to dbms_stats, stats are collected only on indexed columns and not for other non-indexed columns.
The following query is used to check the execution plan.

SQL> set autot trace
SQL> select a.id, a.val, b.str
2 from test1 a,
3 test2 b
4 where a.id = b.id
5 and a.val = 40;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=667 Bytes=77372)
1 0 HASH JOIN (Cost=46 Card=667 Bytes=77372)
2 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=19 Card=1000 Bytes=16000)
3 1 TABLE ACCESS (FULL) OF 'TEST2' (Cost=17 Card=50000 Bytes=5000000)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
327 consistent gets
315 physical reads
0 redo size
489 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Now, let’s delete and gather new statistics on the same tables.

SQL> exec dbms_stats.delete_table_stats (user, 'TEST1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_table_stats (user, 'TEST2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST1', cascade=>true, method_opt=>'FOR ALL COLUMNS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST2', cascade=>true, method_opt=>'FOR ALL COLUMNS');

PL/SQL procedure successfully completed.

Let us check the stats again.

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_NULLS DENSITY
---------- ---------- ------------ ---------- ----------
TEST1 ID 3 0 .000005
VAL 100000 0 .00001

TEST2 ID 50000 0 .00002
STR 50000 0 .00002

The plan by the same query is

SQL> select a.id, a.val, b.str
2 from test1 a,
3 test2 b
4 where a.id = b.id
5 and a.val = 40;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=1 Bytes=22)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2' (Cost=2 Card=1 Bytes=15)
2 1 NESTED LOOPS (Cost=21 Card=1 Bytes=22)
3 2 TABLE ACCESS (FULL) OF 'TEST1' (Cost=19 Card=1 Bytes=7)
4 2 INDEX (RANGE SCAN) OF 'IND_TEST2' (NON-UNIQUE) (Cost=1 Card=1)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
183 consistent gets
174 physical reads
0 redo size
489 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

The comparison between both of the above plans is -

Plan when stats are gathered
Cardinality of TEST1 table
Consistent Gets
Only on indexed columns 1000 327
On all columns 1 183


Why such difference in both the execution plans?

Cardinality plays a very big role in opting an execution plan. In first case, as the stats are not available on VAL column of the table TEST1.

The cardinality when statistics are available only on indexed columns

The filter condition is on ID column of table TEST1 and as there are no stats available on this column, oracle will take the default selectivity as 1/100 and calculated cardinality as

num_rows*selectivity = 100000*(1/100) = 1000

As per the plan, the TEST1 is driving table and cardinality is very high and hence oracle opted the FTS for TEST2 table and cardinality is calculated as

num_rows = 50000

When stats are available on both the all column of the tables, for TEST1 table cardinality is calculated as

Num_rows*(“ID column selectivity” * “VAL column Selectivity)
= round (100000 * (1/3 * 1/100000))
= 0

As cardinality cannot be 0 it will consider as 1.

As the cardinality for TEST1 table is very low i.e. 1, TEST2 will be opted for index scan and hence cardinality is calculated as

Num_rows * (1/distinct)
= round (50000 * (1/50000))
= 1

From above discussion it is concluded that the stats are necessary on all columns. In the above-mentioned test case, we have calculated the stats with histograms but the same result can be found without histograms, as the column stats are getting updated.

To sum up, the stats on all columns are required for optimal execution plan.

Sunday, May 18, 2008

Functions and Performance.

Function plays an important role in development environment. The functions can be implicit (defined by oracle) or explicit (defined by user – PL/SQL code). Developers use the PL/SQL functions as they simplify the large code.

The function may accept the value and must return the value. The user-defined functions are easy to use but they may degrade the performance badly. Hence it is always recommended to use the functions only and only when they are unavoidable. I have demonstrated the performance benefits by the simple query over user-defined functions.

If the condition does not satisfy, function will return null value. It means the output rows will be the number of rows satisfied by the main query. For e.g. if suppose the main query returns 1000 rows and function satisfies only 400 rows, then the output will have 1000 rows, wherein the function column will show 600 null values and 400 value returned by the function. Hence we must have to use outer join whenever we are merging function into the simple query. The example below uses select sub-query that plays the role of function.


SQL> create table fn_tables as select * from dba_tables;

Table created.

SQL> create table fn_indexes as select * from dba_indexes;

Table created.

SQL> create index fn_ind_tables on fn_tables (table_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'FN_TABLES', cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user, 'FN_INDEXES', cascade=>true);

PL/SQL procedure successfully completed.

SQL> create or replace function fn_test (tabowner varchar2, tabname varchar2)
2 return varchar2 is
3 tbsname varchar2(200);
4 begin
5 select tablespace_name into tbsname
6 from fn_tables
7 where owner=tabowner
8 and table_name=tabname;
9 return tbsname;
10 end;
11 /

Function created.


SQL> set autot trace
SQL> select a.owner, a.index_name, a.status,
2 fn_test(a.owner, a.table_name) "TBS_NAME"
3 from fn_indexes a
4 /

1072 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2626245312

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1072 | 51456 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| FN_INDEXES | 1072 | 51456 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1097 recursive calls
0 db block gets
3308 consistent gets
0 physical reads
0 redo size
36706 bytes sent via SQL*Net to client
1162 bytes received via SQL*Net from client
73 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1072 rows processed


SQL> select a.owner, a.index_name, a.status,
2 (select tablespace_name from fn_tables b
3 where b.owner=a.owner
4 and a.table_name=b.table_name) "TBS_NAME"
5 from fn_indexes a
6 /

1072 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 321380953

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1072 | 51456 | 9| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| FN_TABLES | 1 | 29 | 2| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FN_IND_TABLES| 1 | | 1| 00:00:01 |
| 3 | TABLE ACCESS FULL | FN_INDEXES | 1072 | 51456 | 9| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"."OWNER"=:B1)
2 - access("B"."TABLE_NAME"=:B1)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1573 consistent gets
0 physical reads
0 redo size
36706 bytes sent via SQL*Net to client
1162 bytes received via SQL*Net from client
73 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1072 rows processed


SQL> select a.owner, a.index_name, a.status, b.tablespace_name "TBS_NAME"
2 from fn_indexes a,
3 fn_tables b
4 where b.owner(+) = a.owner
5 and b.table_name(+) = a.table_name
6 /

1072 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 893717710

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1072 | 82544 | 18 (6)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 1072 | 82544 | 18 (6)| 00:00:01 |
| 2 | TABLE ACCESS FULL | FN_TABLES | 879 | 25491 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | FN_INDEXES | 1072 | 51456 | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("B"."OWNER"(+)="A"."OWNER" AND
"B"."TABLE_NAME"(+)="A"."TABLE_NAME")


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
135 consistent gets
0 physical reads
0 redo size
36706 bytes sent via SQL*Net to client
1162 bytes received via SQL*Net from client
73 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1072 rows processed


Place all above required values in tabular format.


Query No. Query Criteria Cost Recursive Calls Consistent Gets
1 With Function 9 1097 3308
2 With Sub-Query 9 0 1573
3 With Simple Query 18 0 135


As shown in above table, though the cost of the third query is high, the number of consistent gets has been reduced tremendously. When we are using function, the recursive calls and consistent gets are very high. But when using sub-query both the values have been reduced but this query also suffers from performance compared to third query.

Recently I had got the following query from one of our customer. Due to confidentiality, I will not be posting the original query from the customer site but in this example I have made similar kind of query.


create or replace function get_minamount (cust number)
return number is
amt number;

begin
select min (amount) into amt
from contract
where customer=cust and status = 'a';

if (amt is null) then
select min (amount) into amt
from contract
where customer=cust and status = 'd';
end if;

if (amt is null) then
select min (amount) into amt
from contract
where customer=cust and status = 's';
end if;

Return amt;

End;
/


The main query is as follows.

select customer, name, address,
get_minamount (customer) "MIN_AMT"
from customer_master
where custcode is like 'Large%';


The customer_master is having millions of records and to execute this query it was taking around 8 Hrs. The given suggestion on this query is as follows.


select customer, name, address, min_amt, status
from (select a.customer, name, address, status,
min(case when status in ('a','s','d') then amount end)
over (partition by b.customer, b.status) "MIN_AMT",
rank() over (partition by b.customer
order by (case when status = 'a' and amount is not null then 1
when status = 's' and amount is not null then 2
when status = 'd' and amount is not null then 3
end), rownum) "RNK"
from customer_master a,
contract b
where a.customer = b.customer(+))
where rnk = 1;


After implementing the query the performance gain was very high. The modified query is taking around 20 minutes to execute against 8 Hrs.

To sum up, the user-defined functions may contribute in performance degradation of the query. The simple query will give major performance benefits against the query with functions. The performance of the query is inversely proportional to the number of consistent gets. Hence to improve the performance, we must minimize the consistent gets.

Sunday, May 4, 2008

Migration from Filesystem to ASM Files.

This is continuation to my previous blog, wherein we discussed about interacting with ASM and OS database file.

ASM is an integrated volume manager and file system for Oracle Database files. It simplifies, automates storage management, increase storage utilization and delivers predictable performance. Due to this many of us wants to migrate filesystem database to ASM. If the database is very large and critical, we may not be able to afford the migration downtime. In this blog, I would discuss the procedure to dramitically reduce downtime during the process of migration from raw/OS filesystem to ASM using Oracle Data Guard and RMAN. This blog will also be helpful to create standby database. This procedure explains how data guard can help to reduce the downtime of migration.

Following is the summarised procedure

1. Create standby (dataguard) of the production database.
2. Migrate the standby database to ASM
3. Do the switchover to migate the database to ASM.

As mentioned above, it seems, migration is very simple and yes it is. Below mentioned is the detailed procedure.

PREPARE FOR STANDBY DATABASE

In this demonstration, I have named primary instance as “ORA10G” and standby database as “ORA10GSTD”

Create a backup of the database using RMAN.

RMAN> connect target /

connected to target database: ORA10G (DBID=3970225046)

RMAN> backup database include current controlfile for standby;

Starting backup at 08-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\DATAFILES\ORA10G\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\DATAFILES\ORA10G\UNDOTBS01.DBF
input datafile fno=00003 name=D:\ORACLE\DATAFILES\ORA10G\SYSAUX01.DBF
input datafile fno=00004 name=D:\ORACLE\DATAFILES\ORA10G\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 08-APR-08
channel ORA_DISK_1: finished piece 1 at 08-APR-08
piece handle=D:\ORACLE\ORA10GHOME\DATABASE\01JDBCU5_1_1 tag=TAG20080408T230957 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 08-APR-08
channel ORA_DISK_1: finished piece 1 at 08-APR-08
piece handle=D:\ORACLE\ORA10GHOME\DATABASE\02JDBCVT_1_1 tag=TAG20080408T230957 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 08-APR-08


Make the tnsnames.ora changes in both (production and standby) servers to make connections between both the servers for communication.

Create and copy the parameter file and make appropriate changes for standby database.

SQL> create pfile from spfile;

We assume that, ASM instance is already created, mounted and ready for use. We are using diskgroup, named as ORADG. Also make sure this diskgroup should have sufficient space available.

Following are the standby parameters, which we have to modify.

Edit control_files parameter to appropriate location. This location should be ASM diskgroup.

control_files = ‘+ORADG\ORA10g\controlfiles\control01.ctl’

Add or edit DB_UNIQUE_NAME parameter to define the database uniqueness.

db_unique_name = ORA10GSTD ---- Instance name of Standby.

Add or edit LOG_ARCHIVE_CONFIG parameter to identify primary and standby database using unique name. The parameter value should be primary and standby instance name.
 
log_archive_config = ’dg_config=(ORA10G,ORA10GSTD)’

Set following parameters to point to ASM diskgroup.

db_create_file_dest = '+ORADG'
db_create_online_log_dest_1 = '+ORADG'

The other parameters that needs to change are –

instance_name = ORA10gSTD
fal_server = ora10g --- TNSNAMES entry for Primary
fal_client = ora10gSTD --- TNSNAMES entry for Standby

Create Password file

$ orapwd file=$ORACLE_HOME/dbs/orapw password=

INITIATE THE STANDBY DATABASE IN ASM

After confirmation ASM instance up and parametter, password file is prepared, now we initiate standby database.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 301989888 bytes
Fixed Size 1248672 bytes
Variable Size 96469600 bytes
Database Buffers 201326592 bytes
Redo Buffers 2945024 bytes

RMAN provides single command that initiates the standby database using source database information. Connect to the production database using RMAN and initiate standby database.

C:\> rman target sys/oracle@ora10g

Recovery Manager: Release 10.2.0.1.0 - Production

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORA10G (DBID=3970225046)

RMAN> connect auxiliary /

connected to auxiliary database: ORA10G (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate Db at 08-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=157 devtype=DISK

contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 08-APR-08
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\ORA10GHOME\DATABASE\02JDBCVT_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\ORA10GHOME\DATABASE\02JDBCVT_1_1 tag=TAG20080408T230957
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output filename=+ORADG/ora10g/controlfiles/control01.ctl
Finished restore at 08-APR-08

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to +ORADG in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-APR-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=157 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +ORADG
restoring datafile 00002 to +ORADG
restoring datafile 00003 to +ORADG
restoring datafile 00004 to +ORADG
channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\ORA10GHOME\DATABASE\01JDBCU5_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\ORA10GHOME\DATABASE\01JDBCU5_1_1 tag=TAG20080408T230957
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:46
Finished restore at 08-APR-08

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=651539654 filename=+ORADG/ora10gstd/datafile/system.257.651539549
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=651539654 filename=+ORADG/ora10gstd/datafile/undotbs1.258.651539549
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=651539654 filename=+ORADG/ora10gstd/datafile/sysaux.259.651539551
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=651539654 filename=+ORADG/ora10gstd/datafile/users.260.651539551
Finished Duplicate Db at 08-APR-08

We have successfully transferred files from production to standby database in ASM datagroup. Modify log_archive_dest_2 parameter so that archive logs file will transfer from primary to standby database. Then place the standby database recovery mode so archive logs can be applied automatically.

SQL> recover managed standby database disconnect;

DO SWITCHOVER TO MIGRATE DATABASE TO ASM

This is final step of the migration. We will do switchover to migrate the database to ASM. Check that, both primary and standby must be in sync.

On Primary Database –

Select max(sequence#) “MAXSEQ” from v$log_history;

MAXSEQ
---------
124

On Standby Database –

Select max(sequence#) “MAXSEQ” from v$log_history;

MAXSEQ
---------
124

As both primary and standby database, we can do switchover.

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 301989888 bytes
Fixed Size 1248672 bytes
Variable Size 96469600 bytes
Database Buffers 201326592 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.

We have completed ASM migration.