This is an old revision of the document!
Table of Contents
Performance
Tanel Poder's session snapper
Show long-running SQL queries / statements
Long running means more than 10 minutes if last_call_et > 600 (adjust as necessary!)
0@@
Is stats gathering enabled?
1@@
Check when statistics were last gathered
2@@
Gather more accurate statistics
default is 2
3@@
Check for stale statistics
4@@
or
5@@
Gather database stats
Some helpful stuff on statistics gathering here
6@@
Gather dictionary and fixed objects statistics
How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects (Doc ID 457926.1)
7@@
8@@
Gather schema statistics
9@@
Gather table statistics
10@@
Show any events set in database
11@@
Top memory consumers in AIX
12@@
Show all Oracle hidden parameters
13@@
How much memory is being used by processes (PGA memory)?
14@@
Enable Automatic Memory Management (AMM)
AMM is enabled by setting one or both of the following memory parameters:
15@@
With these memory parameters set, AMM is enabled.<br /> Memory will now be allocated automatically to where it is needed.<br />
16@@
If sga_target and/or pga_aggregate_target are set, these will be treated as minimum values.<br /> If only one of sga_target or pga_aggregate_target is set, the other will be set to (memory_target - the value set).<br /> <br />
Disable AMM
17@@
Enable ASMM
18@@
Reboot database and verify that we have switched from AMM to ASMM
19@@
–> AMM disabled
20@@
–> ASMM enabled !
SGA tuning
Problem with 12c SGA growing too high. Database keeps crashing due to memory issues. Started at 2G, then to 4G and now at 5G.<br /> Raised SR with Oracle. This is the response
21@@
or another one from an 11g incident…
22@@
But.. From 1323708.1 …
23@@
How much redo was generated / How many archivelog switches have occurred per hour over the past week?
24@@
How much archive has been generated per day
Also how much has RMAN deleted
25@@
Top session activity
Enter READS, EXECS or CPU to order session activity by that column
26@@
Show current used undo blocks for ongoing transactions
27@@
Pinpoint which sessions are using lots of undo
28@@
How many blocks have been changed by sessions?
High values indicate a session generating lots of redo<br /> Use this query to check for programs generating lots of redo when these programs activate more than one transaction.
29@@
Top SQL
Shows the SQL statements that have caused the most disk reads per execution since the instance was last started
30@@
Top ten SQL statements with the greatest aggregate elapsed time
31@@
See the explain plan of these SQL's by feeding the sql_id and child_no into this cracker!
32@@
Top waits
Displays a list of the events currently being waited on by active sessions.<br /> The meaning of the wait_time and seconds_in_wait columns varies depending on their values follows:<br />
- wait_time - A non-zero value represents the session’s last wait time, while a zero value indicates that the session is currently waiting.<br />
- seconds_in_wait - When the wait_time is zero, the seconds_in_wait value represents the seconds spent in the current wait condition.<br />
When the wait_time is greater than zero, the seconds_in_wait value represents the seconds since the start of the last wait,<br /> and (seconds_in_wait - wait_time / 100) is the active seconds since the last wait ended.
33@@
Session wait history
Once a session of interest has been identified, we can display the history of events associated with that session
34@@
System waits
35@@
Oracle Log File Sync Wait Event
Reference: logicalread.solarwinds.com<br /> Snippet: <blockquote> When a user session waits on the 'log file sync' event, it is actually waiting for the LGWR process to write the log buffer to the redo log file and<br /> return confirmation/control back to it. If the total wait time is significant, review the average wait time. If the average wait time is low but the<br /> number of waits is high, reduce the number of commits by batching (or committing after 'n') rows.
If slow I/O, investigate the following:
# Reduce contention on existing disks.<br /> # Put log files on faster disks.<br /> # Put alternate redo logs on different disks to minimise the effect archive processes (log files switches).<br /> # Review application design, use NOLOGGING operations where appropriate, and avoid changing more data than required.<br />
If wait times are still significant, review each component of the 'log file sync' and tune separately. </blockquote>
EXPLAIN PLAN
Usage (old school)
If no access to the system plan_table, create your own (also to keep the plans longer than a session), run $ORACLE_HOME/rdbms/admin/utlxplan.sql
36@@
See the results
37@@
Usage (new school)
1. Explain plan for the most recent SQL statement executed in the session
38@@
2. Execute an explain plan command on a SELECT statement
39@@
3. Explain plan on a previously executed statement<br /> Find the SQL_ID<br />
40@@
Get the explain_plan
41@@
Virtual Indexes
An index created to see if the optimiser would use it without actually having to build it
42@@
Now re-run the explain plan and see the difference.
Statspack
If you are using Standard Edition, you cannot use the Grid utilities or to debug performance issues. You need to use the “old” method.<br /> http://www.akadia.com/services/ora_statspack_survival_guide.html<br /> Reproduced here in case the page disappears…
43@@