DBA Responsibility in Performance & Tuning

-

 

From USER satisfaction point of view, the major DBA responsibilties are

  • Data Protection
    - (user accounts/passwords, quota, roles, privileges etc)
  • Data Accountability
    - (audit, redologs/archives, backups)
  • Data High Available
    - (DR solutions thru Cluster & Replication solutions)
  • Data High Security
    - (table level, column level, row level, remote, network security)
  • "Ensure to fast THROUPUT and/or RESPONSE TIME" --- which is important from PERFORMANCE TUNING point of view.

Performance Tuning goal
  • Performance Tuning goal is to see the best THROUPUT and  RESPONSE  TIME.
  • Performance Tuning is a 'Iterative and Continuous process'. Yesterday fix/finding is not all the times standard for tomorrow issue.

    Because...
    Everyday is unique to the database. Its frequent data changes, increase and decrease in volume size, statistics changes, new deployments etc. It involves, I/O fluctuations relating to Disk, IP and network etc. 
DBA usually experience and fix the performance and tuning issues in two appraoches:
  1. Reactive Tuning appraoch:
    The main indication of any database related issue is that system runs sudden slow or hang. When an issue is raised in PRODUCTION which needs immeidate fix, the immediate step of DBA is to find where is the issue. The best approach is to check AWR, ADDM and ASH reports. The findings give a start how to fix it and also it may help DBA to know who is to contact for fix?

    DBA would need to check whether database enabled AWR, ADDM and ASH report. Becuase these are not come by default, they would need to be setup.

    Each report is helpful to figure out different related issues.
    • AWR will help to determine system level performance issues such as buffer busy, latches and enques, cluster related findings, I/O related findings.
    • ADDM will help out to see the Top SQL which are causing performance issues; and it provides suggestions and recommendations to avoid them.
    • ASH will help to determine 'Transient perofrmance realted issues'.

  2. Proactive Tuning appraoch:
    DBA can avoid coming and existing issues praoctively. He can do it in two phases.
    • After a temporary fix is done, DBA has to continue working on it in order to fix it on permanent basis.
    • Before implementing any application/change in database, DBA can validate it whether it is good in all. 
Possible INDICATIONS & CAUSES for the performance isseus:
  1. Increasing DEAD LOCKS & BLOCKED LOCKS exetremely
  2. LATCHES/ENQUEUES causes waiting time
  3. Mostly, other great impacts are -
    • Backups,
    • Export/Imports,
    • After deployments situations and
    • New database configuration chagnes etc. 

All issues which are causing performance issues can be categorized into two:
  1. Bad Database configuration - at installation time or upgrade/migration time.
  2. Bad Application design - bad SQL and PL/SQL scripts. 

Possible Frequent Questions to be asked/studied?
  1. When did the instance first start running so slowly?
  2. How did you first notice the instance was going slowly?
  3. Was SQL load increased or changed recently?
  4. Was there a major structural change made to the Database recently?
  5. Were any events or parameters recently added, deleted or changed?
  6. Was there any backupground processes like Backups, Export/imports?
  7. List any other versions you have tried in which the results are reasonably fast?
  8. Any recent changes to the OS or Hardware?
  9. Any Hardware or OS related errors recently reported? 

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more