SAINORA

HomeBlogsPerformance Tuning

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

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

SQL> select table_name,last_analyzed from user_tables;

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> @?/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] ;

           

Published on 24th March 2012

Published by Gopinathan D