Performance Tuning Basics

on

Oracle Performance Tuning Intro

At higher end level for already running/existing database can be tuned as:

  1. SQL Tuning and
  2. Instance Tuning 

"Performance Planing" is a way where tuning of database for newly Creating/Designing a database.

 When a database is to be tuned 

  • Proactive Tuning = Proactive Monitoring+Bottleneck Eliminations: This is done before any performance issues occur, To avoid performance problems prior.
  • Reactive Tuning: This is done when any performance issues occur, To resolve performance problems occurred.

Oracle Architecture in terms of Performance Tuning

In oracle architecture what components and where performance tuning can be done:

  • Operating system Dependencies and Networking - Tuning: memory, CPU, swap size, I/O bandwidth, 
    • If oracle process is running on swap size then its shows performance issues, 
    • monitor - memory usage on system, CPU usage on system, swap size usage on system, load average on system
  • Background process involved in Tuning 
  • Storage and Memory(SGA,PGA,UGA) Dependencies in tuning
Performance Tuning Tools
  • Explain Plan Of SQL Statements
  • Tracing SQL Execution
  • Tkprof For Trace analysis
  • AUTO TRACE in SQL
  • V$ Dynamic Performance Views used 
  • OEM
  • AWR, ADDM, ASH Reports
  • SQL Tuning Advisors and Baselines, Tuning Sets.
  • OS Monitoring.
SQL Tuning 
  • Execution/ Explain plans
  • SQL Tuning sets and Base lines, Hashes
  • Tuning table access
  • Tuning JOINS
  • Sorting and grouping activities
  • PL-SQL tuning notes
  • Parallelism in SQL
  • Optimizer and controlling it.
Instance Tuning 
  • Monitoring instance usage 
I/O Tuning

Reports and analysis: 
    Generating reports, Reading reports and analyzing them.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Performance Tuning Tools

EXPLAIN PLAN:

1. To see the execution plan for a query:
SQL> explain plan for  <sql statement>; --- which will record explain plan in plan_table to get that execution plan we can query that table or run below PL-SQL block.
SQL> select * from table (dbms_xplan.display());
2. To get the exact values of plan
Get the sql_id of the query using:
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '% <give the sql query here>%';
Use the sql_id got from the above in below query
SQL> select * from table(dbms_xplan.display_cursor ('ff98t6jyqpf41','0','TYPICAL -BYTES'));

TRACING:

End to End oracle application tracing: 

Enabling tracing at different levels using:
  • Tracing using Client Identifier
  • Tracing using service module and action
  • Tracing at session level
  • Tracing entire database or instance 
  • Specific SQL tracing
All these trace files are stored in user_dump to get the path of user_dump use below in sql command line
    SQL> show parameter user_dump

Set a trace file identifier to identify the trace files generated using the below we can set it

    SQL> alter session set tracefile_identifier=name

Tracing Using Client Identifier

    The CLIENT_ID_TRACE_ENABLE procedure used to enable tracing globally for the database for a given client identifier, refer below example:

    Enable tracing for a client identifier:
        SQL> EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'OE.OE', waits => TRUE, binds => FALSE); 

    Disable tracing for a client identifier:
        SQL> EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'OE.OE'); 

Tracing for Service, Module, and Action

    The SERV_MOD_ACT_TRACE_ENABLE procedure enables SQL tracing for a given combination of service name, module, and action globally for a database, unless an instance name is specified in the procedure, module name may or may not be included, refer below example:

    Enable tracing for a service or action or module: 
        SQL> EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'ACCTG', waits => TRUE, binds => FALSE, instance_name => 'inst1');

    Disable tracing for a service or action or module:
        SQL> EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name  => 'ACCTG', instance_name => 'inst1'); 

Tracing at Session level

    The SESSION_TRACE_ENABLE procedure enables the trace for a given database session identifier (SID), on the local instance, to enable we need the SID, Serial number to get the these values use below query
       SQL> select sid, serial#, username from v$session;

    Enable tracing for a specific session:
            SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60, waits => TRUE, binds => FALSE);
            SQL> EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);  --- Enabling trace for self session

    Disable tracing for a specific session:
        SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60); 
        SQL> EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE(); --- Disabling trace for the invoked session 

Tracing for Entire Instance or Database 

    The DATABASE_TRACE_ENABLE procedure enables SQL tracing for a given instance or an entire database. Tracing is enabled for all current and future sessions. For example:      

    Enable tracing for an instance
        SQL> EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits => TRUE, binds => FALSE, instance_name => 'inst1'); --- Overrides all other session level traces.

    Disable tracing for an instance
        SQL> EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name => 'inst1'); 

Starting a trace using login trigger:

    If charan user logs into the database then this trigger will be triggered and PL-SQL block which enables session tracing will be executed 

CREATE OR REPLACE TRIGGER trace_login_trigger 
AFTER LOGON ON DATABASE
BEGIN
IF USER = 
'CHARAN' THEN
EXECUTE IMMEDIATE 'alter session set 
tracefile_identifier=CHARAN'; DBMS_SESSION.session_trace_enable 
(waits => TRUE,
binds => FALSE,
plan_stat => 'all_executions'
);
END IF; 
END;

Viewing Enabled Traces for End-to-End Tracing

tkprof  is a command line utility used to read the trace files we also have options like sys=no to avoid sys runned queries.
trcsess is a command-line utility to consolidate tracing information from several trace files, then run TKPROF on the result.

AUTOTRACE:

    Its options its just like along with the query we will get the output & explain plan & statics and also no of rows based on the options we set

  • set autotrace on: will enable the auto trace and when a query is fired we will get the output & explain plan & statics and also no of rows 
  • set autotrace on statistics: will enable the autotrace but when a query fired we will get the output and statics
  • set autotrace on explain: will enable the autotrace but when a query fired we will get the output and explain plan.
  • set autotrace traceonly: will enable the autotrace but when a query fired we will get the no of rows & explain plan & statics
  • set autotrace off: will disable the auto trace and when a query is fired we will get the output only

V$ VIEWS:

These views are dynamic tables its has the info from directly from the memory, as the historical info cannot be seen if its restarted as these are dynamic views
V$SYSSTAT
V$SESSION
V$SESSTAT
V$PROCESS
V$SQL, V$SQL_PLAN
Wait interface tables:
V$SYSTEM_EVENT
SQL> set linesize 200;
SQL> column wait_class format a25;
SQL> select WAIT_CLASS, event, total_waits,round(TIME_WAITED_MICRO/1000) as total_ms from v$system_event where wait_class <> 'Idle' order by wait_class; --- to get non idle waits in a database.
V$SESSION_EVENT
V$SESSION
V$SESSION_WAIT
Time model tables:
V$SYS_TIME_MODEL : for whole database
V$SESS_TIME_MODEL : for an individual session 

AWR, ADDM, ASH REPORTS:

AWR(Automatic Workload Repositary): 
    This data is both in memory and stored in the database, The statistics collected and processed by AWR includes:
  • Object statistics that determine both access and usage statistics of database segments
  • Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views 
  • Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views
  • SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time 
  • ASH statistics, representing the history of recent sessions activity
Enabled by default; 
statistics_level = typical/all to enable, 
statistics_level = basic to disbale; 
DBMS_WORKLOAD_REPOSITORY/awrrpt 

Retension period is 8 days by default for awr snapshots 

            To generate AWR report: SQL> @?/rdbms/admin/awrrpt

ADDM (Automatics Database Diagnostics Monitor):
    Based on the snapshot it will recommend what action need to be taken  

            To generate ADDM report: SQL> @?/rdbms/admin/addmrpt

ASH (Active Session History):
    We need to give the time between which the session details all will be captured between that time slots.
            To generate ASH report: SQL> @?/rdbms/admin/ashrpt

SQL TUNING ADVISOR AND BASELINES:

Automatic SQL Tuning advisor
        The database can automatically tune SQL statements by identifying problematic statements and implementing recommendations using SQL Tuning Advisor during system maintenance windows. When run automatically, SQL Tuning Advisor is known as the Automatic SQL Tuning Advisor.
Manual SQL Tuning advisor

To get the report of a sql: SQL> @?/rdbms/admin/sqltrpt the plsql block asks for sql-id get sql-id before running this plsql block.

    To enable automatic SQL tuning, use the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
BEGIN 
DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); 
END;
/

  To disable automatic SQL tuning, use the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); 
END;
/

Statics and optimizer  

OS MONITORING:

To identify top 10 PID's and commands using CPU on Linux servers:
ps -eo pcpu,pid -o args= | sort -k1 -r | head -10 --- PID's
ps -eo pcpu,pid -o comm= | sort -k1 -r | head -10 --- Commands

To get the total CPU usage:
sar -P ALL 1 2 |grep 'Average.*all' |awk -F" " '{print 100.0 -$NF}'

Comments

Post a Comment