Oracle Performance Tuning Learning 0622

1. To query for wait events in the database:

   SET Lines 100
   SET pages 10000
   COLUMN wait_class Format A12
   COLUMN Event Format A30
   COLUMN total_waits Format 999999
   COLUMN total_us Format 999999999
   COLUMN pct_time Format 99.99
   COLUMN avg_us Format 999999.99
   SET Echo on

   SELECT Wait_class, event, total_waits as Waits,
   ROUND (time_waited_micro/1000) as Total_ms,
   ROUND (Time_waited_micro * 100/sum (Time_waited_micro) over (),
   2
   ) as Pct_time,
   ROUND ((time_waited_micro/total_waits)/2) as Avg_ms
   From V$system_event
   WHERE wait_class <> ' Idle '
   ORDER by Time_waited_micro DESC;

2. Integrate the time model and the Wait interface:

SELECT event,
       total_waits,
       Round (time_waited_micro/1000000) as Time_waited_secs,
       round (time_waited_ Micro * 100/sum (TIME_WAITED_MICRO) over (), 2) as Pct_time
  from (SELECT event, Total_waits, time_waited_micro          from V$system_event
          WHERE wait_class <> ' Idle '
        UNION
         SELECT stat_name, NULL, VALUE
           from V$sys_time_model
         WHERE stat_name in (' DB CPU ', ' Backup CPU time ')
 order by 3 DESC

SELECT sample_seconds,
       stat_name,
        waits_per_second        waits_per_sec,
        Microseconds_per_second ms_per_sec,
       pct_of_time              pct
  from Opsg_delta_report
 where Microseconds_per_second > 0;
Monitoring the usage of indexes:

With In_plan_objects as
(SELECT DISTINCT object_name from V$sql_plan WHERE object_owner = ' SCOTT ')
SELECT table_name,
Index_name,
Case
When object_name was NULL then
' NO '
ELSE
' YES '
END as In_cached_plan
From User_indexes
Left OUTER JOIN in_plan_objects
On (index_name = object_name);

4. Identify the SQL statements that benefit from the binding variables:

With Force_matches as
(SELECT force_matching_signature,
COUNT (*) matches,
MAX (sql_id | | child_number) Max_sql_child,
Dense_rank () over (ORDER by COUNT (*) DESC)
Ranking
From V$sql
WHERE force_matching_signature <> 0
and Parsing_schema_name <> ' SYS '
GROUP by Force_matching_signature
Having COUNT (*) > 5)
SELECT sql_id, matches, parsing_schema_name schema, Sql_text
From V$sql JOIN force_matches
On (sql_id | | child_number = max_sql_child)
WHERE ranking <= 10
ORDER by matches DESC;

Oracle Performance Tuning Learning 0622