Tuesday, July 16, 2019

Performance Tuning In Oracle 11g - Part 1



 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 bottleneck 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 everything is going  good or not
- Once  you  confirmed there are 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 happened and resolve using the below methodology
    *Monitor   - To monitor the issue where the problem has occurred
    *Diagnose - To identify the root cause
    *Tune - To resolve the issue
1)Monitoring Tools
Montior tools  
     *user Feedbacks
       - To get a query about the 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 to these views "V$dollar view names"
     - By using this view we can collect  statistics  collect tune investigation
  1)Instance Activity
  The statistics which collect information about the instance.
  - Overall 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
  * Overall 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 in cumulative statistics
 - Computed real-time  in MMON
  *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 the 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 to 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 to other sessions 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 been 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 been 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 


Credit-https://ampersandacademy.com

No comments:

Post a Comment