Introduction of Performance Tuning:

 

Performance tuning plays an important role in the DBA life. Performance tuning refers in the following areas:

1. Instance tuning

2. SQL tuning

3. Memory, Storage (I/O)

4. Network tuning

5. OS tuning

For OS Tuning and Network tuning DBAs are only a part of the tuning team but not 100 responsible. In these cases we would take help of server admins for OS level tuning and  Network admin for network level.

DBAs are 100% responsible for INSTANCE, DATABASE, MEMORY, STORAGE level tuning

Tools  used in Performance tuning:

1. Explain Plan of SQL Statements

2. Tracing

3. TK PROF

4. AUTO Trace – sqls executed only in the SQL Prompt

In this article we will discuss the use of the above tools one by one.

1. Explain Plan of SQL Statement:

Explain plan is the method of identifying the execution plan which is generated by the optimizer whenever there is a new SQL running in the instance.

When you execute an SQL statement, it should pass through the optimizer or the explain plan which is already exist. If the execution plan is already exist then it will directly uses the execution plan to fetch the data in the database else it will create a new execution plan for the particular SQL statement when a explain plan command is issued so that next time you run the same statement, it can use the existing plan.

Syntax:

Here in the syntax, statement_id and table_name are optional but if you want the explain plan for several statements to be stored in the table PALN_TABLE, you have to give a unique statement_id so that at the later point of time you can check the explain plans of the statements in the table PLAN_TABLE.

By default the explain plan of the statement will be stored in the PLAN_TABLE. You can also create your own PLAN_TABLE by using the script utlxplan.sql, which is in $ORACLE_HOME/rdbms/admin. From 10G oracle creates the table globally for all the users. As this table is global temporary table, we can see the plans inserted into the table by other sessions, and our plan will be disappeared once the session ends.

As I mentioned earlier that the PLAN_TABLE is accessible by all the users and the session, Let us see how is it possible.

Now let us create a tablespace and a user.

Now we have 2 methods of seeing the execution plan.

Method 1: Run select statement on top of the plan table and choose what are the columns for the output. But in this case there would be a lot of columns in a table and there are some mandatory columns as well without which we cannot map the output, hence it is difficult to choose the particular columns for the output.

SQL> select rtrim(lpad(‘ ‘ ,2*LEVEL) || rtrim(operation) || ‘ ‘|| rtrim(options) || ‘ ‘ || object_name) EXECUTION_PLAN, cost, cardinality from plan_table connect by prior id=parent_id start with id=0;

Method 2: Using the statement “select * from table(dbms_xplan.display());”

This is just put in the structured and readable format.

 

Here the problem with the “dbms_xplan_plan” is that the execution plan is not the actual execution plan which includes the bind variables.

Now to see the actual execution plan for the statement:

The output is not from the not from PLAN_TABLE but from dynamic performance views  (v$SQL_PLAN, V$SQL_PLAN_STATICS). And this output is same as previous in which we have executed using the dbms_xplan.display() and finding in PLAN_TABLE.

But the difference is in the PLAN_TABLE out put, the CPU utilised to execute the statement is ‘0’ which is not true as every statement which is executed will consume some cpu. Where as here in the actual output you can see the CPU utilised to execute the statement is 100 with the cost being same ‘3’.

Hence we have to user DBMS_XPLAN.display_cursor to get a better execution plan.

1. Tracing SQL execution

Tracing is very useful to know which statement is problematic when a bunch of sql statements are being executed as part of a transaction.

There are two types of tracing methods.

1. End-to-End application tracing

2. SQL tracing.

End-to-End application tracing: Here application is nothing but the Database.

a. Tracing the Client identifier

b. Tracing Service, Module and Action

c. Tracing for session

d. Tracing for entire Database.

a. Tracing the Client identifier:  DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE procedure enables event 10046 trace for all sessions with the specified client ID.

Here in this example ‘ID1’ is the client id that needs to be traced. Binds and waits are set to be TRUE which means both the bind variables and the wait events are included in the tracing. If we set to FALSE then they are out of tracing.

b. Tracing for Service, Module and Action:  SERV_MOD_ACT_TRACE_ENABLE Procedure enables the trace of service name, module, action for all the sessions unless an instance name is specified.

SERV_MOD_ACT_TRACE_DISABLE Procedure disables the trace of service name, module, action for all the sessions

c. Tracing at session level: If you want to enable the trace at the session level we require SID and SERIAL# of the particular session.We can get these by using the statement.

d. Tracing entire database: DATABASE_TRACE_ENABLE is the procedure used to trace the SQLs for complete database.

Here as all the trace files are placed in one location user_dump_dest, it is a bit difficult to find the trace file that you have generated as by default the naming convention would be “Dbname_ora_osprocesid.trc”. So we can use the tracefile identifier to identify the trace file.

Now this will enable the trace of our own session and the trace file will have the extension “sessiontrace” so that we can easily identify the trace file.

By using the above statement we can check if the tracing is enabled or not.

 

3. TKPROF: TKPROF is used to analyse the trace which is generated.

Below are the tkprof options that we can use

4. Autotrace:

Using the Autotrace we can see the out of the statement suppressed with the Execution Plan and the execution Statistics. This is used only when the queries are executed in the sql prompt.