Oracle PerformanceTuning

One of the most and COMPLEX task is tuning the database. COMPLEX is not the correct word, if we understand the environment(ie. functionality)  and we know how to tune a query, then everything is simple. Advanced tuning can be learned only through experience and with good environment setup.

Actually 75% of the database is self tuned. Our job is just to do the remaining.

Scenario 1 ,  Lets have the problematic query , how will you tune it?
Eg.

  1  select e.empno,
  2        d.deptno
  3        from
  4        emp e,
  5        dept d
  6             where
  7*            e.deptno=d.deptno;

First you need to get the execution plan for the query. To get plan you can do the following.

Step 1:First check all the tables in the query are RECENTLY analyzed using the below query.
(here it is emp and dept)

SQL> select last_analyzed from dba_tables where table_name in ('EMP','DEPT');

Like,

SQL> select last_analyzed from user_tables;

LAST_ANAL
--------------



4 rows selected.

   If the result is null or the date is not current then analyze the table.

SQL > ANALYZE TABLE <table_name> COMPUTE STATISTICS;

or use

SQL > exec dbms_stats.gather_table_stats('SCOTT','EMP');

Note :  Lets discuss more about DBMS_STATS in another post.

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
DEPT
EMP
BONUS
SALGRADE

SQL> analyze table dept compute statistics;    

Table analyzed.

SQL> select table_name,last_analyzed from user_tables;

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
DEPT                           05-APR-12
EMP
BONUS
SALGRADE


SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.

Step 2: Check PLAN_TABLE exists in your schema else create using the below script.

SQL> select table_name,last_analyzed from user_tables;
SQL> @?/rdbms/admin/utlxplan.sql

SQL> set timing on time on

 To see the query elapsed time.

Step 3 : There are two ways to see the execution plan.
 1. set autotrace on
 2. using explain plan for ...

1.

 SQL > set autotrace on

 Just execute the query , you will get 3 outputs.
  Query result
  execution plan
  Statistics

  1  select e.empno,
  2        d.deptno
  3        from
  4        emp e,
  5        dept d
  6             where
  7*            e.deptno=d.deptno
SQL> /

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7499         30
      7521         30
      7566         20
      7654         30
      7698         30
      7782         10
      7788         20
      7839         10
      7844         30
      7876         20

     EMPNO     DEPTNO
---------- ----------
      7900         30
      7902         20
      7934         10

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3074306753

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   546 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |    14 |   546 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   364 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |    13 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   3 - access("E"."DEPTNO"="D"."DEPTNO")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         32  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        654  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

If you want to see the statistics, Just give

SQL > set autotrece traceonlyexplain

 You can see the execution plan.

SQL> set autotrace traceonly explain
SQL>  select e.empno,
          d.deptno
          from
          emp e,
          dept d
               where
             e.deptno=d.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 3074306753

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   546 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |    14 |   546 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   364 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |    13 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

  3 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement
SQL>

You can stop the trace using.

 SQL> set autotrace off

2. USING EXPLAIN PLAN FOR ...

SQL> explain plan for select e.empno,
          d.deptno
          from
          emp e,
          dept d
               where
             e.deptno=d.deptno;

SQL>

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3074306753

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   546 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |    14 |   546 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   364 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |    13 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------


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

    3 - access("E"."DEPTNO"="D"."DEPTNO")


Note
-----
   - dynamic sampling used for this statement

19 rows selected.

What is execution plan?
It the access path for a query execution.

When a query is executed from the client, it does three things.
1. Parsing
2. Exection
3. fetching

1. PARSING
Parsing has 6 stages, they are

1. systax checking
     - Just checks the syntax
2. Semantic checking
     - Checks the objects are available and accessible.
3. View Merging
     - rewrites the query so that it uses tables instead if views.
4. Statement transformation
     - Breaks the query to simple queries.
5. Optimization
     - Check the best access path with use of CBO or RBO.
6. Query Execution Plan Generation

 After taking the best access path, it is stored with the query in the library cache. Queries are stored in the

library cache by using a hash value.

 If a query is called again , it is hashed using hashing algorithm and checks in the library cache, if found it uses
the execution plan else it does the parsing(6 stages).


2. EXECUTION
   Query Execution Plan Execution

3. FETCHING
   Reads the data. It may be physical reads, logical reads and consistent reads.

How does oracle reads data?
There are two ways.
1. sigle blocks read
2. multi-block read

Blocks are read using the following techniqs.
Full tables scan
Index scan
Rowid

What is rowsource, predicate,tuples?
  Rowsource is a function that does some action and returns set of records.
  predicate is where clause from a query.
  tuples are rows from table.

What is driving table and probing table?
  Driving table is the one that will be scanned first and the key rows are retrieved. The result will be compared to  the probing table to get the exact result.

for example.

SQL> select e.empno,
          d.deptno
          from
          emp e,
          dept d
               where
             e.deptno=d.deptno;

if the table emp is scanned first by using the predicate e.deptno and the result is used to look for data in dept table using d.deptno.
Here emp is driving table and dept is probing table.

What are joining methods?
   There are three types of joining methods.
 
1. Sort merge
2. Nested loop
3. Hash join.

How will you interpret the execution plan?
Execution plan should be read from right most top.


What will you see in the execution plan?
We need to see the cost and bytes of accessing each table. Try to reduce the cost.
 Like,Check any full table scans and force to use index etc.

How will you monitor the index creation?

select OPNAME,SOFAR,TOTALWORK,TIME_REMAINING from v$session_longops
 where sofar<>totalwork;

How will you view the DML queries that are currently executing?

SQL> select
a.sql_text,
s.sql_id,
t.ubablk
from v$transaction t,
v$session s,
v$sqlarea a
where t.addr=s.taddr
and a.sql_id=s.sql_id;

Note : Since the above query is referring V$TANSACTION , it take only DML statements.

What is RBO?
RBO is Rule based optimizer that uses 15 set of rules to create the execution plan.
RBO Path 1: Single Row by Rowid
RBO Path 2: Single Row by Cluster Join
RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
RBO Path 4: Single Row by Unique or Primary Key
RBO Path 5: Clustered Join
RBO Path 6: Hash Cluster Key
RBO Path 7: Indexed Cluster Key
RBO Path 8: Composite Index
RBO Path 9: Single-Column Indexes
RBO Path 10: Bounded Range Search on Indexed Columns
RBO Path 11: Unbounded Range Search on Indexed Columns
RBO Path 12: Sort Merge Join
RBO Path 13: MAX or MIN of Indexed Column
RBO Path 14: ORDER BY on Indexed Column
RBO Path 15: Full Table Scan

SQL> conn scott/tiger

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report


SQL> conn sys/sys as sysdba

@?/sqlplus/admin/plustrce.sql

SQL>
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;
grant select on v_$sesstat to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> grant select on v_$statname to plustrace;
grant select on v_$statname to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> grant select on v_$mystat to plustrace;
grant select on v_$mystat to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off
SQL> grant plustrace to scott;

Grant succeeded.

reconnect scott.

SQL> conn scott/tiger
Connected.
SQL> set autotrace on
SQL>

Scenario : My daily job has a update statement that normally runs every 10 mins. But today it takes more that 30 mins but still it hangs. How will i trouble shoot it. 


Answer :

Terminal one :

SQL> SELECT sys_context('USERENV', 'SID') FROM DUAL
  2  /

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
39


SQL> c/ao/ao as
  1* create table ao as select * from all_objects
SQL> /

Table created.

SQL> update ao set owner='S';

9383 rows updated.

Terminal two :  

SQL> SELECT sys_context('USERENV', 'SID') FROM DUAL
  2  /

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
25

SQL> update ao set owner='H' ;

It hangs .....

Terminal three :

SQL> l
  1*  select sid,EVENT,SECONDS_IN_WAIT from v$session_wait order by 3
SQL> /

       SID EVENT                                    SECONDS_IN_WAIT
---------- ---------------------------------------- ---------------
        44 rdbms ipc message                                      0
        23 SQL*Net message to client                              0
        35 ARCH wait on ATTACH                                   72
        45 rdbms ipc message                                    252
        46 rdbms ipc message                                    255
        33 rdbms ipc message                                    261
        36 rdbms ipc message                                    270        25 enq: TX - row lock contention                        546
        39 SQL*Net message from client                          561
        42 rdbms ipc message                                    810
        26 Streams AQ: qmn slave idle wait                      822
        30 Streams AQ: qmn coordinator idle wait                849
        38 Streams AQ: waiting for time management              852
           or cleanup tasks

        48 rdbms ipc message                                    852
        41 rdbms ipc message                                    864
        40 rdbms ipc message                                    864
        49 pmon timer                                           867
        43 smon timer                                           867
        47 rdbms ipc message                                    868

19 rows selected.

  1* select * from v$locked_object
SQL> /

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USE OS_USER_NA PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ -----------
         0          0          0       9766         25 SYS        oracle     5958                   3
         5         20         31       9766         39 SYS        oracle     5855                   3

Note : In first 3 columns you find values  0 0 0 and sid is 25, which means this session is hanging (waiting session) ..., The session 39 is holding(lock holding) session.

SQL> select sid,serial# from v$session where sid=39;

       SID    SERIAL#
---------- ----------
        39          3

To kill the sesison from database :

SQL> alter system kill session '39,3';
Killing from OS level :

SQL> select PADDR from v$session where sid=39;

PADDR
--------
52522C34

SQL> select PID,SPID,ADDR from v$process where addr='52522C34';

       PID SPID         ADDR
---------- ------------ --------
        12 5887         52522C34

[oracle@oraclehost ~]$ ps -ef|grep 5887|grep -v grep
oracle    5887  5855  0 06:33 ?        00:00:02 oraclekart581 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

[oracle@oraclehost ~]$ kill -9 5887

Also , You can use dba_blockers and dba_waiters.

SQL> @?/rdbms/admin/catblock.sql;

Note : This package will create two views

dba_blockers
dba_waiters

SQL>  select HOLDING_SESSION from dba_blockers;

HOLDING_SESSION
---------------
             39


SQL> select * from dba_waiters;


WAITING_SESSION HOLDING_SESSION LOCK_TYPE    MODE_HELD       MODE_REQUE   LOCK_ID1   LOCK_ID2
--------------- --------------- ------------ --------------- ---------- ---------- ----------
             25              39 Transaction  Exclusive       Exclusive      327700         31

How will you increase the performance of insert statement?
Ex  query is
insert into table1 select * from ext_table;
Ans :
First,
1. drop the indexes except primary key
2. Disable the foreign Keys
3. Use Hints like APPEND,NOLOGGING, PARALLEL

1.  Check whether you can disable your foreign keys (ie make sure that it is reliable data)
command to disable the constraint.

SQL > alter table tablename disable constraint constraint_name;

2. drop the indexes except primary key

  drop index index_name;

3. use nologging   and append hints

insert /*+ APPEND NOLOGGING */  into table1 select * from ext_table;

4.  Incase you are inserting into partition table
Note : check your total cpu count (total cpu count=CPU*Core) , In this case i have 4 CPU with QUAD core.(TCPU=4*4=16 CPUs).
So you can use 8 to 12 cpus for ur query. Don't use all of them, because it may lead to CPU exhaust and the OS Server may get down.

SQL > insert /*+ APPEND NOLOGGING parallel(table1,4) */  into table1 select/*+ parallel(ptable,4) * from partition_table ptable;

Once Done. If you are confident about the data , you can enable the constraint using...

SQL > alter table tablename enable constraint constraint_name novalidate;

5. If you dropped the index , recreate the index using

SQL > create index index_name on table_name(column_name) nologging parallel(4) [local] ;