Database side statistics analysis

To analyse the data base bottlenecks we have to generate the AWR report.

How to generate a report?
Sol:
 Select any monitor which we added.
 Right click on monitor.
 Select "reports" and select "Quick" It will display quick report pop up window.
 Select "thresholds" and select "options" according to our requirement.
 Select "general"->Graph.
 Navigate to "filter" and schedule settings.
 Select the report period.
 Select the report type (html/text/xml).
 Click on generate report file.

AWR report (Automatic Workload Repository):

 To communicate with remote server we need to install putty.
 In putty we need to provide remote server host name (or) IP address and we need to
provide remote server credentials.
 We have to enter a command {sqlplus"/as sysdba";}
 Click on enter which will prompt to sql prompt.
 In sql prompt we need to enter below one:
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.
 Click enter which will ask for "Report_type".
 Provide report type as html.
 Click enters which will ask for number of days.
 Provide number of dayClick enter.
It will show "snapshot ID's".
And ask for begin snap_IDProvide begin snap id and click enter.
And Ask for end "snap_ID".
Provide end snap_ID and click enter lt asks for report name.
Provide the report name with extensions (.htm1) click enterexit.

Database bottlenecks:


1. Low buffer hit ratio:
Process: Generated AWR report and found low buffer hit ratio which is reported 60%. BHR should be more than 95%.
Cause: Due to allocation of low memory to the buffer memory, triggers low BHR.
Recommendation: Recommended DB architect people to increase buffer memory allocation by redesigning oracle SGA.

2. Low index utilization
Low index utilization can’t monitor by AWR. With the help of DBA, we found index utilization issues and asked them to provide tuning opportunities in terms of index utilization.
Cause: Low index utilization causes delayed response time.
Recommendation: Redesign the index.

3. Full table scans
With the help of DBA, we found there are lot of full table scans.
Cause: Full table scan causes delayed response time.
Recommendation: Redesign the index and implement the index of which tables are frequently accessed.

4.High I/O operation queries

Drill down
Why queries are utilizing more CPU for any I/O operation?
How much CPU utilized by the I/O operation?
Recommendation: Recommended fine tune the queries of I/O operations.

5. High Elapsed running queries
Cause: AWR reported high elapsed running queries which causes the delayed response time.
Process: We gone through the queries which contains lot of binding variables, inner queries which causes the delayed response time.
Recommendation: Recommended DB people to rewrite the query and tuning the inner queries and binding variables.

6. High CPU utilized queries
Cause: AWR reported high CPU utilized queries which causes the delayed response time.
Process: Once we gone through the AWR report which contains more number of high CPU utilized queries, we segregated that into DB level and machine level.
Recommendation: Recommended DB people to tune the query which causes high CPU utilization.

7.DB time and DB CPU time
Cause: AWR reported high DB time and DB CPU time which causes the delayed response time. It should be minimal.
Recommendation: Recommended DB people to redesign the DB architecture.