_____________________________________________________________________________________ set pages 1000 set lines 200 _____________________________________________________________________________________ EXPLAIN PLAN ============ http://docs.oracle.com/cd/E11882_01/server.112/e41573/ex_plan.htm#PFGRF009 Creating a PLAN_TABLE --------------------- @$ORACLE_HOME/rdbms/admin/utlxplan.sql Running EXPLAIN PLAN -------------------- EXPLAIN PLAN FOR SELECT last_name FROM employees; Displaying PLAN_TABLE Output ---------------------------- _____________________________________________________________________________________ _____________________________________________________________________________________ STATSPACK REPORTS ================= https://docs.oracle.com/cd/B10501_01/server.920/a96533/statspac.htm Scripts are located in $ORACLE_HOME/rdbms/admin/ sppurge.sql Deletion of old reports. EXECUTE statspack.snap (i_snap_level =>5) Create Snapshot spauto.sql Create job for Snapshots spreport.sql Generate Report _____________________________________________________________________________________ _____________________________________________________________________________________ ELAPSED TIME CONSUMERS (TOP 10) =============================== select * from ( select round(elapsed_time/1000000/60/60) "ELPSD_TIME_HRS", elapsed_time/1000000/60 "ELPSD_TIME_MIN", sql_id from v$sql order by 1 desc) where rownum < 11; select sql_text from v$sql where sql_id = 'xxxxxxxxxxxxx'; select sql_text, elapsed_time/1000000/60 "Elapsed Time (in Hours)", elapsed_time/1000000/60/60 "Elapsed Time (in Minutes)" from v$sql where sql_id = 'xxxxxxxxxxxxx'; _____________________________________________________________________________________ _____________________________________________________________________________________ WAITS ===== select owner, segment_name, segment_type, wait_class, event from v$session, dba_extents where p1 = file_id and p2 between block_id and (block_id + blocks -1); col owner for a12 col segment_name for a20 col wait_class for a15 col event for a25 col username for a15 col program for a20 select owner, segment_name, segment_type, wait_class, event, username, program, sid, serial# from v$session, dba_extents where p1 = file_id and p2 between block_id and (block_id + blocks -1); _____________________________________________________________________________________ _____________________________________________________________________________________ AUTOMATIC WORKLOAD REPOSITORY (AWR) =================================== Scripts are located in $ORACLE_HOME/rdbms/admin/ awrrpt.sql Generiert einen HTML- oder Text-Bericht, der Statistiken für eine Reihe von Snapshot IDs anzeigt. awrsqrpt.sql Damit lässt sich die Performance eines bestimmten SQL-Statements in einer Zeitperiode untersuchen. awrddrpt.sql Vergleicht die Performance-Attribute und -Konfiguration zweier auszuwählender Zeitabschnitte. _____________________________________________________________________________________ _____________________________________________________________________________________ CHECK FEATURE USAGE =================== set lines 200 set pages 1000 break on version skip 1 select version, name, detected_usages, currently_used, first_usage_date, last_usage_date from DBA_FEATURE_USAGE_STATISTICS where detected_usages > 0 order by 1, 2; _____________________________________________________________________________________