Physical Memory Load : 71.70 %
Environment Variable (PATH) :
Environment Variable (TNS_ADMIN) : TNS_ADMIN is not a declared environment variable.
The Sql Tuning Set name is small_sh_sts_4.It is on 10.2.0.2.0 Database server.
It has 4 sqls .
select sql_id, substr(sql_text,1, 15) text
This article documents the use of SQL tracing in the various Oracle tools. This is not to be confused with SQL*Net tracing which shows all database operations sent through the SQL*Net connections. In all cases, the trace files will be deposited in the location specified by the init.ora parameter USER_DUMP_DEST. It should be noted that this is given a default value under UNIX, which can be seen from inside SQL*DBA. This is not set by default in a PC/Netware environment, so must be explicitly set. Otherwise, no trace files will be produced. Under VMS, this parameter may be overwritten by setting the ORA_DUMP logical. In all cases, ensure that the write permissions are set correctly for the destination directory. Also, for more readable output, the tkprof utility can be invoked on the trace file. The use of tkprof is documented in Appendix B of the Application Developers Guide for Oracle 7 and in Section 7 of the Performance Tuning Guide for Oracle 6. The trace facility may be turned on at the database level, by inserting the following line in the init.ora file: sql_trace = true This will trace all database activity whilst the database is running. Only use this method if all transactions need to be monitored, since a large number of trace files can be generated in this way. In order to take effect, the parameter needs to be added to the init.ora file and the database restarted. Remember to remove the parameter and restart the instance, once the required trace information has been collected. SQL*Plus ======== Tracing can be set by issuing the command: SQL> alter session set sql_trace true; Once the transactions requiring tracing have executed, tracing can be turned off by issuing the command: SQL> alter session set sql_trace false; PL/SQL ====== In Oracle 6, trace may be invoked within SQL*Plus as above, prior to running the PL/SQL script. In Oracle 7 the same method may be used, or the stored procedure dbms_session.set_sql_trace() may be invoked with the syntax: dbms_session.set_sql_trace(true); This may be inserted at the first line of the PL/SQL script. If this package or procedure cannot be found, then it may need to be created by the dba running the dbmsutil.sql script as the user sys. This dbms_session package is documented on page A-2 of the Version 7 Application Developers Guide. It is advisable to turn off tracing. From within SQL*Plus: SQL> alter session set sql_trace false; From within PL/SQL after the transactions requiring tracing have executed: dbms_session.set_sql_trace(false); Either of these statements will ensure that no more trace output than is necessary is generated. Forms 2.3 ========= Tracing can be set by creating a user defined trigger called trace_on. For example, containing only the line: alter session set sql_trace true This trigger can be called from a key-startup trigger as follows: #exemacro exetrg trace_on; Once this trigger has fired, then all further database actions will be traced and recorded in the trace file. This can be useful if errors are occurring saving the form to the database, as tracing can be invoked inside the form followed by the save/load/generate operation that results in error(s) and all database actions will still be stored in the trace file. Forms 3 ======= There is a statistics option for a runform session, which can either be invoked from the options screen of the designer or by using the -s flag when calling runform from the operating system prompt. This statistics option is documented on page 23-5 of the SQL*Forms 3 Reference Manual. If it becomes necessary to trace the actions when a form is saved/loaded to/from the database, then a form must be run from the designer with statistics on and then the database action performed. If the session is running against an Oracle 7 database then an alternative to using the statistics option is to use: dbms_session.set_sql_trace(true); This is to be used as a line inside a trigger or PL/SQL procedure, usually the pre-form trigger. Forms 4 ======= There is a statistic option which is documented on page 1-18 of the Forms 4 Reference Manual Volume 1 (packaged procedure dbms_session). The parameter 'set_sql_trace()' may also be invoked inside a when-new-form-instance trigger, similarly to the method described in the previous section. Graphics 2 ========== Create the following PL/SQL program unit: procedure set_trace is begin dbms_session.set_sql_trace(true); end; Next, define the open display trigger to invoke this procedure. If an open display trigger has already been defined, then just adding the following line will invoke the trace facility: dbms_session.set_sql_trace(true); However, note that much more sophisticated debugging methods are available within the PL/SQL debugger. Reportwriter 1.1 ================ Tracing can be invoked from inside a report by creating a field with the following settings: Name -> sql_trace Source -> &SQL alter session set sql_trace true Group -> REPORT Width -> 40 Ensure that the field label is deleted, the skip field is set on, and that there is no reference to this field on the text screens. Running the report will enable the tracing, which will continue until the reports session is ended. However, there is a known BUG 190946, which states that setting alter session set sql_trace true inside a report produces a trace file that only has a trace of the alter session statement. This bug was first noted in version 188.8.131.52.1 and is fixed in version 184.108.40.206. This basically means that a report cannot set a trace onto itself, and hence the following workaround is suggested: First, create and execute a dummy trace report and then execute the real report. This means that tracing can only be done from within the designer, as both reports cannot be run with a single runrep command (and therefore in the same session). To create the trace report follow these steps: 1. Create a report with 1 field and 1 query as in: Field: Name -> sql_trace Source -> &SQL alter session set sql_trace true Group -> Report Width -> 40 Query: Anything, for example, select 'a' from dual. It may be preferable to ensure that this report writes to a null file to ensure that there is no output generated. 2. Each time a report is to be traced, follow these instructions: a. Start the designer b. Execute this trace report c. Execute the report to be traced. This method has the advantage that the report being traced does not have to be altered in any way. Bear in mind that once the sql_trace has been enabled, it will remain enabled until the end of the reports session. This means that in the event of multiple reports beginning debug it is advisable to exit the reports designer after each report has been traced, thus resulting in a separate trace file for each report. Reports 2 ========= There are 2 methods for setting sql_trace here. Firstly, use in the Before-Report trigger: dbms_session.set_sql_trace() Secondly, create a function column at report level, with a formula of: srw.do_sql('alter session set sql_trace true') and a return type of char. Ensure that this column is not displayed on the report layout. Precompilers ============ Trace can be invoked by including the following line in the source program: exec sql alter session set sql_trace true; Tracing can also be turned off by using: exec sql alter session set sql_trace false;
Quick Reference to Using TKPROF ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TKPROF allows you to analyse a trace file to determine where time is being spent and what query plans are being used on SQL statements. 1 - Set TIMED_STATISTICS if required at database level. 2 - Get SQL_TRACE output for the session you wish to monitor (See Note:15160.1 for how to set this in various tools) 3 - Find the appropriate trace file (In USER_DUMP_DEST, default $ORACLE_HOME/rdbms/log on Unix). You can find the most recent trace files on Unix with the command: ls -ltr This will list the most recent files LAST 4 - Run tkprof on the trace file thus: tkprof tracefile outfile [explain=user/password] [options...] TKPROF Options ~~~~~~~~~~~~~~ print=integer List only the first 'integer' SQL statements. insert=filename List SQL statements and data inside INSERT statements. sys=no TKPROF does not list SQL statements run as user SYS. record=filename Record statements found in the trace file. sort=option Set of zero or more of the following sort options: prscnt number of times parse was called prscpu cpu time parsing prsela elapsed time parsing prsdsk number of disk reads during parse prsqry number of buffers for consistent read during parse prscu number of buffers for current read during parse prsmis number of misses in library cache during parse execnt number of execute was called execpu cpu time spent executing exeela elapsed time executing exedsk number of disk reads during execute exeqry number of buffers for consistent read during execute execu number of buffers for current read during execute exerow number of rows processed during execute exemis number of library cache misses during execute fchcnt number of times fetch was called fchcpu cpu time spent fetching fchela elapsed time fetching fchdsk number of disk reads during fetch fchqry number of buffers for consistent read during fetch fchcu number of buffers for current read during fetch fchrow number of rows fetched userid userid of user that parsed the cursor
The purpose of RMAN performance tuning is to identify the bottlenecks for a given backup or restore job and use RMAN commands, initialization parameters, or adjustments to physical media to improve overall performance.
As database sizes continue to grow unabated, with tens to hundreds of terabytes prevalent in customer’s environments, server and storage systems have also dramatically increased in resource, network, and I/O capacity to support required database backup and restore windows. Against this backdrop, RMAN performance tuning becomes more important than ever.
Welcome to the Oracle Performance Diagnostic Guide .
This guide is intended to help you resolve query tuning, hang/locking, and slow database issues.
The guide is not an automated tool but rather seeks to show methodologies, techniques, common causes, and solutions to performance problems.
In prior releases, we joined v$session_wait with the v$session to determine the sessions waits.
AWR report is broken into multiple parts.
This provides information the instance name , number,snapshot ids,total time the report was taken for and the database time during this elapsed time.
Elapsed time= end snapshot time - start snapshot time
Database time= Work done by database during this much elapsed time( CPU and I/o both add to Database time).If this is lesser than the elapsed time by a great margin, then database is idle.Database time does not include time spend by the background processes.
2)Cache Sizes : 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.
3)Load Profile: This important section shows important rates expressed in units of per second and
transactions per second.This is very important for understanding how is the instance behaving.This has to be compared to base line report to understand the expected load on the machine and the delta during bad times.
4)Instance Efficiency Percentages (Target 100%): This section talks about how close are the vital ratios like buffer cache hit, library cache hit,parses etc.These can be taken as indicators ,but should not be a cause of worry if they are low.As the ratios cold be low or high based in database activities, and not due to real performance problem.Hence these are not stand alone statistics, should be read for a high level view .
5)Shared Pool Statistics: This summarizes changes to the shared pool during the snapshot
6)Top 5 Timed Events :This is the section which is most relevant for analysis.This section shows what % of database time was the wait event seen for.Till 9i, this was the way to backtrack what was the total database time for the report , as there was no Database time column in 9i.
7)RAC Statistics :This part is seen only incase of cluster instance.This provides important indication on the average time take for block transfer, block receiving , messages ., which can point to performance problems in the Cluster instead of database.
8)Wait Class : This Depicts which wait class was the area of contention and where we need to focus.Was that network, concurrency, cluster, i/o Application, configuration etc.
9)Wait Events Statistics Section: This section shows a breakdown of the main wait events in the
database including foreground and background database wait events as well as time model, operating
system, service, and wait classes statistics.
10)Wait Events: This AWR report section provides more detailed wait event information for foreground
user processes which includes Top 5 wait events and many other wait events that occurred during
the snapshot interval.
11)Background Wait Events: This section is relevant to the background process wait events.
12)Time Model Statistics: Time mode statistics report how database-processing time is spent. This
section contains detailed timing information on particular components participating in database
processing.This gives information about background process timing also which is not included in database time.
13)Operating System Statistics: This section is important from OS server contention point of view.This section shows the main external resources including I/O, CPU, memory, and network usage.
14)Service Statistics: The service statistics section gives information services and their load in terms of CPU seconds, i/o seconds, number of buffer reads etc.
15)SQL Section: This section displays top SQL, ordered by important SQL execution metrics.
a)SQL Ordered by Elapsed Time: Includes SQL statements that took significant execution
time during processing.
b)SQL Ordered by CPU Time: Includes SQL statements that consumed significant CPU time
during its processing.
c)SQL Ordered by Gets: These SQLs performed a high number of logical reads while
d)SQL Ordered by Reads: These SQLs performed a high number of physical disk reads while
e)SQL Ordered by Parse Calls: These SQLs experienced a high number of reparsing operations.
f)SQL Ordered by Sharable Memory: Includes SQL statements cursors which consumed a large
amount of SGA shared pool memory.
g)SQL Ordered by Version Count: These SQLs have a large number of versions in shared pool
for some reason.
16)Instance Activity Stats: This section contains statistical information describing how the database
operated during the snapshot period.
17)I/O Section: This section shows the all important I/O activity.This provides time it took to make 1 i/o say Av Rd(ms), and i/o per second say Av Rd/s.This should be compared to the baseline to see if the rate of i/o has always been like this or there is a diversion now.
18)Advisory Section: This section show details of the advisories for the buffer, shared pool, PGA and
19)Buffer Wait Statistics: This important section shows buffer cache waits statistics.
20)Enqueue Activity: This important section shows how enqueue operates in the database. Enqueues are
special internal structures which provide concurrent access to various database resources.
21)Undo Segment Summary: This section gives a summary about how undo segments are used by the database.
Undo Segment Stats: This section shows detailed history information about undo segment activity.
22)Latch Activity: This section shows details about latch statistics. Latches are a lightweight
serialization mechanism that is used to single-thread access to internal Oracle structures.The latch should be checked by its sleeps.The sleepiest Latch is the latch that is under contention , and not the latch with high requests.Hence run through the sleep breakdown part of this section to arrive at the latch under highest contention.
23)Segment Section: This portion is important to make a guess in which segment and which segment type the contention could be.Tally this with the top 5 wait events.
Segments by Logical Reads: Includes top segments which experienced high number of
Segments by Physical Reads: Includes top segments which experienced high number of disk
Segments by Buffer Busy Waits: These segments have the largest number of buffer waits
caused by their data blocks.
Segments by Row Lock Waits: Includes segments that had a large number of row locks on
Segments by ITL Waits: Includes segments that had a large contention for Interested
Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage
parameter of the table.
24)Dictionary Cache Stats: This section exposes details about how the data dictionary cache is
25)Library Cache Activity: Includes library cache statistics which are needed in case you see library cache in top 5 wait events.You might want to see if the reload/invalidations are causing the contention or there is some other issue with library cache.
26)SGA Memory Summary:This would tell us the difference in the respective pools at the start and end of report.This could be an indicator of setting minimum value for each, when sga)target is being used..
27)init.ora Parameters: This section shows the original init.ora parameters for the instance during
the snapshot period.
There would be more Sections in case of RAC setups to provide details.