Let  us we discuss about what is performance tuning ? and Types of performance tuning,Resolving performance issues,Basic tuning tools ,Tuning methodology in this session  

Performance Tuning:

 - Performance Tuning  is the improvement of system performance .The motivation for such activity is called a performance problem, which can be real or anticipated.

 - Most systems will respond to increased load with some degree of decreasing

Performance.

 - Oracle Performance method  is iterative  and DBA Core business is to improve performance

 - Performance tuning comes with  experience and resolving real time bottle neck issues    

 I) Types of Performance Tuning:

 *Proactive  Tuning 

 - In design,development ,testing stage -In production => ADDM(Automatic Database Diagnostic Monitor) -In this proactive we have to check  the database design , product development  and test  that.

 - After the  product  launch if we have any issues in building level run the ADDM adviser it gives appropriate built in issue and recommendations to  DBA what is going on database background  

 *Reactive Tuning

 - In production the problem has occurred

II) Resolving Performance issues:

 - If any performance issue occurred it can be resolved  by below

resolvePFT

1)Tuning outside Database (DBOUTSIDE)

 - if any issues happened in performance level first  we need to  identify    the os level ,network ,storage  levels every thing is going  good or not

- Once  you  confirmed there is no issues on outside the db , then go  to inside the database level 2

2)Database tuning (DBINSIDE)

*Again we adopt the top down approach

   - Tune the design

   - Tune the sql queries

   - Tune the various memory structure of instance level

III) Methodology

  - Tuning Methodology to identify  the performance (problem ) issue where it was happened and resolve using below methodology

   OverView Methodology

    *Monitor   - To monitor the issue where the problem is  occurred

    *Diagnose - To identify root cause

    *Tune - To resolve the issue

1)Monitoring Tools

Montior tools  

     *user Feedbacks

       - To get  query about  user  performance issue

      - If user  raise the complaints that time only we know the issue

     *Dynamic Performance View

      - These views owned by SYS users

      - Reside in memory  values

      - All reads on these views are current reads

      - These cumulative values since  instance startup

      - We can refer these views "V$dollar view names"

     - By using this view we can collect  statistics  collect tune investigation

     dynamic views

  1)Instance Activity

  The statistics which  collect  information about the instance .

  instance activity

  - Over  all statistics are listed in V$STATNAME

      Eg:

    *Parse time CPU

    *Physical reads

    *User commits

  - Cumulative Instance activity

    *Session level

  - V$SESSTAT

    *System level

  - V$SYSSTAT  

2)Wait Events

  The event  for which process is waiting  to be over  before it processed  

  - Provides information about sessions that  had to wait or must wait for different reasons

  wait events

  * Over all wait events

- V$EVENT_NAME

  * Instance Level wait event in system level

- V$SYSTEM_EVENT

  * Session Level wait events waited in past

- V$session_event currently waiting

- V$session

3)Metrics

 - Rate of changes cumulative statistics

 - Computed real time  in MMON

 metrics

  *Alert Log

 - Time to perform archiving

 - Instance recovery start and complete time

 - Incomplete checkpoints -Checkpoints start and end time

  *Enterprise Manager

 - We can access the database through the browser

 - Start and stop database  

  Let us see one example of performance tunning   logged on sys user and check the user , account status from dba_users tables

   SQL>select username ,account_status from dba_users;

here we can choose scott and hr  users and then we can give the permission emp table of scott into hr

   SQL> Grant select ,update on scott.emp to hr;

here we logged into scott user and tried update the sal column of EMP

  SQL>update emp set sal=sal+5 where empno=7900;
     1 row updated

after this update we won't give commit or rollback next we go other session in that name of HR login as the same thing we do

  SQL>update emp set sal=sal+5 where empno=7900;

now HR is waiting for scott once we get  problem issue from development side first we ensure who are all in on-line

    SQL> select sid,serial#,username from V$session;

       SID    SERIAL# USERNAME
   ---------- ---------- ------------------------------
         1          5 SYS
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1
        11          1
        12          1
        13          1
        14          1
        15          1
        16          1
        20          2
        22          2
        24          3
        25         99
        29          3
        30         12
        31         16
        32         50
        33          3 HR
        34         15 SCOTT

  S-2: to wait out both user what is the status  

  SQL> select sid,event,seconds_in_wait,wait_time,state from V$session where sid in('33','34');

       SID EVENT                                                            SECONDS_IN_WAIT  WAIT_TIME STATE
       ---------- ---------------------------------------------------------------- --------------- ---------- -------------------
        33 enq: TX - row lock contention                                                829       0 WAITING
        34 SQL*Net message from client                                                  914       0 WAITING

  here the problem is "ENQ:TX- row lock contention" means that sid is waiting for someone

 S-3: let us find  cpu usage in system level  

    SQL> select  event,time_waited from V$system_event where event='enq: TX - row lock contention';

    EVENT                                                            TIME_WAITED
    ---------------------------------------------------------------- -----------
    enq: TX - row lock contention                                         154390

     SQL> /

     EVENT                                                            TIME_WAITED
    ---------------------------------------------------------------- -----------
    enq: TX - row lock contention                                         158326

  S-4: find which session is waiting for session level

   SQL> select sid,event,time_waited from V$session_event where event='enq: TX - row lock contention' and sid in ('33','34');

       SID EVENT                                                            TIME_WAITED
      ---------- ---------------------------------------------------------------- -----------
        33 enq: TX - row lock contention                                         175386

  S-5: find out how the user has been logged

     SQL> select event,service_name,total_waits,time_waited,average_wait,max_wait from V$service_event where event='enq: TX - row lock contention';
  
         EVENT                                                            SERVICE_NAME                  TOTAL_WAITS TIME_WAITED AVERAGE_WAIT    MAX_WAIT
    --------------------------------------------------------------- ---------------------------------------------------------------- ----------- ----------- ------------ ----------
           enq: TX - row lock contention                                    SYS$USERS                       1       190688       190688          0

S-6: to find the blocked  session user

        SQL> select  BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE, BLOCKING_SESSION ,username from V$session where sid in('33','34');

          BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION USERNAME
          ----------- ----------------- ---------------- ------------------------------
           VALID                       1               34 HR
          NO HOLDER                                      SCOTT

S-7: to find the object _id because using this id we can find which  has been trouble

       SQL> select ROW_WAIT_OBJ#   "Object_id" ,ROW_WAIT_FILE# "realtive fileno", ROW_WAIT_BLOCK#   "Block Number" from V$session where sid in('33');

                   Object_id realtive fileno Block Number
                  ---------- --------------- ------------
                    73181               4          151

S-8: to find the table problem occur table and owner

      SQL> select owner,object_type,object_name,data_object_id from dba_objects where object_id='73181';

      OWNER                          OBJECT_TYPE         OBJECT_NAME                                                DATA_OBJECT_ID
     ------------------------------ ------------------- ---------------------------------------------------------------------------------------------------------------------------        ----- --------------
          SCOTT                          TABLE               EMP                                                         73181

S-9:   to find out user details

 


       SQL> select machine,osuser,username ,sid,service_name,logon_time from V$session where sid in ('33','34');

          MACHINE                                                          OSUSER                         USERNAME        SID SERVICE_NAME                                                                  LOGON_TIM
---------------------------------------------------------------- ------------------------------ ------------------------------ ---------- ---------------------------------------------------------------- ---------
         goldengatesource                                                 oracle                         HR               33 SYS$USERS                                                                  12-MAR-16
        goldengatesource                                                 oracle                         SCOTT            34 SYS$USERS 

 

S-10: to sql_id for sql query which has be issued

     SQL> select sql_id from V$session where sid in ('33','34');

         SQL_ID
       -------------
        1dc2pgg0uh57f

S-11:   finally we found the issue query which has be given hr user  now we ensure whether user scott is going to commit or kill that session get the conformation  application team .

     SQL> select sql_fulltext from  V$sql where sql_id='1dc2pgg0uh57f';

      SQL_FULLTEXT
     --------------------------------------------------------------------------------
      update scott.emp set sal=sal-3 where empno=7900