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
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
*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