Performance - Revision history

Stuart: /* Gather dictionary and fixed objects statistics */

2018-10-24T13:13:55Z

‎Gather dictionary and fixed objects statistics

2018-10-24T13:13:40Z

‎Gather statistics on fixed objects and dictionary

2018-10-24T13:12:03Z

‎Gather database stats

2018-09-04T14:04:32Z

‎Show long-running SQL queries / statements

2018-07-17T08:00:55Z

‎Show current used undo blocks for ongoing transactions

2018-07-17T08:00:21Z

‎Number of undo blocks and records accessed by a transaction

2018-05-27T10:21:00Z

‎Enable Automatic Memory Management (AMM)

2018-05-23T19:07:40Z

‎How much archive has been generated per day

2018-05-23T19:07:18Z

‎How much redo was generated / How many archivelog switches have occurred per hour over the past week?

2017-06-22T09:58:58Z

‎Show long-running SQL queries / statements

@@ Line 100: / Line 100: @@
 How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects ([https://support.oracle.com/epmos/faces/DocumentDisplay?id=457926.1 Doc ID 457926.1])
 <pre>
-exec dbms_stats.gather_dictionary_stats
+exec dbms_stats.gather_dictionary_stats;
 </pre>
 <pre>
-exec dbms_stats.gather_fixed_objects_stats
+exec dbms_stats.gather_fixed_objects_stats;
 </pre>

@@ Line 97: / Line 97: @@
 EOSQL
 </pre>
-===Gather statistics on fixed objects and dictionary===
+===Gather dictionary and fixed objects statistics===
 How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects ([https://support.oracle.com/epmos/faces/DocumentDisplay?id=457926.1 Doc ID 457926.1])
 <pre>

@@ Line 96: / Line 96: @@
 exec dbms_stats.gather_database_stats( estimate_percent=>dbms_stats.auto_sample_size, degree => 8 );
 EOSQL
 </pre>

@@ Line 7: / Line 7: @@
 * [[Tuning]]
 * [http://juliandyke.com/Presentations/Presentations.php#AdvancedDiagnosticsRevisited juliandyke.com - Advanced Diagnostics (including oradebug 10046 10053 etc...)]
 ===Show long-running SQL queries / statements===
 Long running means more than 10 minutes if last_call_et > 600 (adjust as necessary!)

@@ Line 423: / Line 423: @@
 <pre>
 select vs.sid
 ,      vs.username
 ,      vt.used_urec
 from   v$session     vs
 ,      v$transaction vt
-where  vs.saddr = vt.ses_addr
+where  vs.taddr = vt.addr
-order  by vt.used_ublk desc
+order  by 5 desc, 6 desc
 </pre>
 ===Pinpoint which sessions are using lots of undo===
 <pre>

@@ Line 455: / Line 455: @@
 where  vs.sid = vi.sid
 order  by 5 desc
 ,      1, 2, 3, 4;
 </pre>

@@ Line 184: / Line 184: @@
 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 />
 ===SGA tuning===

@@ Line 324: / Line 324: @@
 ,      sum_arch_del.deleted_mb
 ,      sum_arch.generated_mb - sum_arch_del.deleted_mb remaining_mb
-FROM   (
+from   (
         select to_char (completion_time, 'dd-mm-yyyy') day
         ,      sum (round ( (blocks * block_size) / (1024 * 1024), 2)) generated_mb

@@ Line 314: / Line 314: @@
 where  rownum < 8
 order  by 1
+/
 </pre>