TutorialDBA - Support | Training | Consultant

Performance optimization of databases is one of the routine tasks of DBAs. This task becomes very easy when you have Oracle Enterprise Manager tool installed. You can generate various reports from AWR and analyze the health of the database and then tune it accordingly. This is normal in every DBA’s life. But what if you don’t have the OEM installed? This is very much possible in a test environment. In this case, you need to generate the reports manually. You need to query the database to extract the data requried for analysis. For such requirements, we have the below query to find out “Top 5 wait events in the database”. The query gives Date, Event_Name and Total_Waits. Query to find Top 5 wait events in database: select Day, Event_name, Total_wait from ( select day, event_name, sum(event_time_waited) total_wait, row_number() over (partition by day order by sum(event_time_waited) desc) rn from ( SELECT to_date(to_char(begin_interval_time,’dd/mm/yyyy’),’dd/mm/yy...

Here is a data dictionary query that will display the contents of an Oracle tablespace: You can use the following script to display the contents of a specified tabledpace: select * from (select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) meg from dba_segments where tablespace_name = 'SYSAUX' order by blocks desc); This query will also display the contents of a tablespace: break on file_id skip 1 column file_id heading "File|Id" column tablespace_name for a15 column object for a15 column owner for a15 column MBytes for 999,999 select tablespace_name, 'free space' owner, /*"owner" of free space */ ' ' object, /*blank object name */ file_id, /*file id for the extent header*/ block_id, /*block id for the extent header*/ CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ from dba_free_space where tablespace_name like '&ta...

This section contains detailed guidance for evaluating each section of an AWR report.  An AWR report is very similar to a STATSPACK report, and it contains vital elapsed-time information on what happened during particular snapshot range.  The data in an AWR or STATSPACK report is the delta, or changes, between the accumulated metrics within each snapshot. The main sections in an AWR report include: Report Summary :   This gives an overall summary of the instance during the snapshot period, and it contains important aggregate summary information. Cache Sizes (end):   This shows the size of each SGA region after AMM has changed them.  This information can be compared to the original  init.ora  parameters at the end of the AWR report. Load Profile:   This important section shows important rates expressed in units of per second and transactions per second. Instance Efficiency Percentages:   With a target of 100%, these are high-level ...

The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will explore these AWR tables and expose their secrets. The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will explore these AWR tables and expose their secrets. We have the following AWR tables for SQL tuning. dba_hist_sqlstat dba_hist_sql_summary dba_hist_sql_workarea dba_hist_sql_plan dba_hist_sql_workarea_hstgrm These simple tables represent a revolution in Oracle SQL tuning and we can now employ time-series techniques to optimizer SQL with better results than ever before. Let's take a closer look at these views. dba_hist_sqlstat This view is very similar to the  v$sql  vi...

The AWR report is a great tool for monitoring day by day database activities for DBA. The use of Statspack/AWR report helps the DBA quickly identify the possible cause or database load. The AWR report mainly contains the following sections. ·           Database instance details ·           Database Memory statistics. ·           Top 5 wait events. ·           Top SQL’s order by execution time and elapsed time. ·           SQL’s order by physical reads. ·           SQL’s order by buffer reads and gets. ·           Tablespace information and hit ratio on the table spaces. ·           Initialization Parameter’s The purpose of these two tools is the same bu...

How to read an AWR report Oracle  AWR  SQL Tuning Scripts Oracle  AWR  sysaux tablespace Interpreting RAC  AWR  reports Oracle STATSPACK vs.  AWR AWR  script for physical disk reads Oracle transportable  AWR  snapshots Oracle Creating an  AWR  Report Oracle  AWR  disk file statistics The Oracle AWR report is the evolution of the STATSPACK report in Oracle 10g, and is invoked as follows: $ORACLE_HOME/rdbms/admin/awrrpt.sql $ORACLE_HOME/rdbms/admin/awrrpti.sql $ORACLE_HOME/rdbms/admin/awrinput.sql The AWR report was originally developed for Oracle Technical Support, and the sections of an AWR report are not well-documented.  Understanding the AWR report also requires significant Oracle AWR experience.   Here are details  on reading the main sections of a STATSPACK and AWR report.

The best feature of STATSPACK is that it stores Oracle performance information in a set of 25 tables that can be used to develop historical trends. By interrogating these tables, Oracle professionals can gain tremendous insight into the relative performance of their databases. The STATSPACK utility is the evolution of Oracle's   utlbstat.sql   and   utlestat.sql   utilities. In Oracle7, these utilities were used to gather an elapsed time report of Oracle performance. In Oracle8, this concept has been enhanced by STATSPACK to allow for the capture of the elapsed time report into a set of tables. As seem below, the 25 STATSPACK tables provide a complete picture of everything that's going on within the Oracle database.   The STATSPACK schema contains several control tables. The stats$parameter tables controls the thresholds for collection of detailed information, and a table called stats$level_description provides information regarding the level of detail colle...

Most of the time end user complaining that the database is slow as high performance is common expectation for end user. The database itself is never slow or fast in most of the case session connected to the database slow down when they receive unexpected hit. To resolve session performance issue you need to identify unexpected hit and remove it. As we know an oracle database is always one of the 3 states: Idle:   Waiting for the task. Processing:   Doing some useful task. Waiting:   Waiting for something, a block to come from disk or lock to be released. Sometimes the situation is session is waiting for resource and another session trying to update that record and many other such scenarios. Our goal is to find and eliminate that type of session. Update pay_employee_personal_info Set amount = 4000 Where employee_number = 5205; Do not issue a commit after this update operation. That means you are forcing the session to get and hold a lock on the first row o...