John Kanagaraj, DB Soft Inc Expert Session

1
John Kanagaraj, DB Soft IncExpert Session 319
(2 hour)
ORACLE DATABASE 10GR2 AN ENLIGHTENED REVISIT
(BEFORE WE GIVE UP AND MOVE TO 11G!)
2
Speaker Qualifications

• John is a Principal Consultant _at_ DB Soft Inc.
• Executive Editor for IOUGs SELECT Journal
• Co-author of Oracle Database 10g Insider
  Solutions
• Technical Editor for various books
• Frequent presenter IOUG/OAUG/OOW/NoCOUG
• Published in SELECT, OAUG Insight, SQL Server
  Magazine and other publications
• Recognized by Oracle Corp as an Oracle ACE

3
SELECT Call for Articles/Reviewers

• The SELECT Journal is IOUGs Technical Quarterly
• Distributed to all IOUG members worldwide
• Yearly Best Practices and Tips booklet
• Submit an article or Review one!
• Contact select_at_ioug.org

4
What this presentation is about

• Oracle Database 10g now in full production mode
• R1 released in 2003 (?), R2 in 2005 (?)
• A little history lesson book, papers
• Lead time between books/initial articles and
  consolidation / better understanding
• Need to revisit and update the first revelation
• Reconsider new features fresh look
• Hidden surprises and little bonuses
• A peek into the future 11g titbits

5
Audience survey

• Using Oracle Database 11g in production?
• Still using Oracle 7 or 8i?
• Migrating to Oracle Database 10g?
• Read the New features manual for every release?
• Used Advisories in 9i?
• Used Active Session History for troubleshooting?
• Read AWR/ADDM/ASH reports?
• Used SQL Profiles to fix SQL issues?

6
Overview of new perf features

• Strictly related to performance related features
• Need to unlearn the old and embrace the new
• AWR The Performance Warehouse
• ASH What happened to the sessions?!?
• ADDM Your inbuilt (and unpaid!) expert
• Tuning advisors Bonus freebies
• In-Memory metrics and Server generated alerts
• Where to look for more details

7
Philosophy behind Oracle DB 10g

• Automation
• Incremental steps in 9i (Advisors, Time)
• Most significant change in Performance management
• Out of the box setups
• GUI hides the complexity (and details!)
• Consistency
• Common/Unified interface
• Stats storage and presentation
• Interpretation

8
Reality check before we proceed

• Licensing changes in Oracle Database 10g
• A tale of two packs
• Oracle Diagnostic Pack
• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitor (ADDM)
• Monitoring and Event notifications and history
• Oracle Tuning Pack
• SQL Access Advisor
• SQL Tuning Advisor and STS
• Object Advisor
• Required even to access Views directly!!!

9
AWR Performance Warehouse

• Performance Data Warehouse for 10g
• Basis for most of the problem detection and
  reporting
• AWR collects, stores performance data
• Direct memory access (MMNL/MMON)
• In-memory component (V/Metric views)
• Persisted in WR tables (SYSAUX)
• 162 tables WRI, WRH, WRM
• Exposed via DBA_HIST_ Views
• Self managing out of the box
• Set retention, frequency, baseline

10
AWR Contents

• Active Session History (ASH)
• High-load SQL statements
• Time model statistics (both System/Session)
• Object usage - access counts for segments
• Snapshots of V and some Metrics
• Host CPU and Memory statistics (VOSSTAT)
• Baseline information
• Management type information
• Snapshot details
• Advisor and other parameters

11
AWR Statspack on Steroids

• Similar to STATSPACK snapshots
• Reportable AWRRPT.SQL
• AWR snapshot automatically analyzed
• Accessible via GUI and API/SQL ()
• High-impact SQL captured differently
• Stores session level info as well
• Read the fine print License required, even
  for SQL access!

12
AWR Baselining and comparison

• Enables performance baselining
• Collection of two or more snapshots
• Stored in _BL tables data not purged
• View using WRM_BASELINE/DBA_HIST_BASELINE
• Reports diff via AWRDDRPT.SQL
• Execution statistics for specific SQL statement
  using AWRSQRPT.SQL

13
ASH Whats up with sessions

• Historical view of active sessions
• Active sessions sampled every second
• Stored in circular memory buffer
• Every 10th sample persisted in AWR
• VACTIVE_SESSION_HISTORY In-memory
• WRH_ACTIVE_SESSION_HISTORY Persisted
• Enables after-the-fact analysis!!!
• Reported via ASHRPT (Not available in 10gR1)
• Slice-and-dice analysis can reveal a lot of
  info

14
ASH Session states exposed!

• On-the-spot analysis
• Retroactive analysis
• From memory buffer (VACTIVE_SESSION_HISTORY)
• From persisted AWR data (WRH_ACTIVE_SESSION_HISTO
  RY connected via SNAP_ID)
• Supports manual drill down from AWR/ADDM
• Tracks High load SQL execution behavior
• Determine Blocking sessions and hot segments
• SESSION_STATE ON CPU or WAITING

15
ADDM Your unpaid Tuning Expert!

• Starting point for most investigations
• Runs after every AWR snapshot
• Determines and records performance issue
• Recommends corrective action
• Generates probable benefit
• Suggest use of other advisors
• Common currency - DB Time (qualitative!)
• Oracle DB 11g new Instance ADDM

16
ADDM Partial check list

• CPU bottlenecks
• Excessive parsing
• Lock contention
• Concurrency
• I/O capacity
• Incorrect sizing of Oracle memory and file
  structures
• High-load SQL, Java and PL/SQL statements
• Poor connection management
• Hot objects
• RAC-specific issues

17
ADDM Findings/Recommendations

• Qualitative rather than Quantitative analysis
• Hardware changes
• Database-configuration changes
• Schema-level changes
• Application changes
• Using other advisors (for example)
• SQL Tuning Advisor / SQL Access Advisor
• Segment Advisor
• Dont stare at the screen Use SQL to summarize
• Details in my 2007 paper

18
Advisors A step beyond

• Builds on 9i advisors
• Buffer cache advisor
• Shared pool advisor
• MTTR (Mean Time To Recover) advisor
• Summary (MVIEW) advisor
• PGA Target Advisor
• New in 10g
• SQL Tuning Advisor
• SQL Access Advisor
• Segment Advisor

19
SQL Tuning Advisor

• Frontend to Automatic Tuning Optimizer
• Extension (reuse) of Optimizer (CBO)
• Performs what-if analysis
• Not restricted by time to optimize
  (_optimizer_max_permutations 2000)
• The following advice is provided
• Gather missing or stale statistics
• Create new indexes
• Restructure SQL statement
• SQL profiles

20
SQL Tuning Advisor

• SQL Profile
• Collects additional information via
  sampling/partial execution techniques
• Verifies and adjusts CBOs estimates at runtime
• Similar in function to Outlines
• Enabled by category test-and-set
• Access/manipulate DBMS_SQLTUNE
• Precedence given to Stored Outlines
• Runs against individual SQL or SQL Tuning Sets
  (STS)

21
Other Advisors

• SQL Access Advisor
• Works alongside SQL Tuning Advisor
• Advice on MV, Indexes, MV logs
• Considers space usage vs performance
• Inputs STS, User-defined, Hypothetical
• Advanced Workload type (RO), Drop unused
  indexes, Filters (Top N, Module)
• Segment Advisor
• Undo Advisor
• Memory Advisor
• Metrics and Server Generated Alerts

22
Avoiding Advisor Pitfalls

• Out-of-the-box thinking (redesign rethink
  approach)
• False positives (check validity for all
  situations e.g. Index non-usage)
• Changing workload or environment (additional
  load, new code, H/W or S/W changes)

23
Features revisited

• STATSPACK Not dead yet!
• New and changed views tons of goodies!
• Event Breakout Greater visibility
• Mutexes Boon or Bane?
• Operating System Statistics all in one place
• Time and Wait Model aids better understanding
• Hidden Surprises watch out!
• Useful AWR sections Segstats and much more
• Extracting Data from AWR Drill down example
• DBMS_XPLAN in-depth view

24
STATSPACK Not dead yet!

• STATSPACK is still available in Oracle DB 10g
• No license required to track performance stats
• Setup/maintenance/reporting same as before
• Default is Level 5, set at Level 7 to get Segment
  stats
• Number of new sections available
• OS Statistics
• DB Time reporting
• Response time Histograms (Event, File and Temp)
• Additional SQL sections in Report
• Comparison to AWR
• Check Metalink Note 394937.1 (incomplete)

25
STATSPACK Host Statistics

• Host CPU (CPUs 32)
• Load Average
• Begin End User System
  Idle WIO WCPU
• ------- ------- ------- -------
  ------- ------- --------
• 0.04 0.03 3.38 1.06
  95.56 3.13
• Instance CPU
• of total CPU for Instance
  3.64
• of busy CPU for Instance
  81.90
• DB time waiting for CPU - Resource Mgr
• Memory Statistics Begin
  End
• ------------
  ------------
• Host Mem (MB) 65,274.7
  65,274.7
• SGA use (MB) 6,144.0
  6,144.0
• PGA use (MB) 2,038.8
  2,233.6
• Host Mem used for SGAPGA 12.5
  12.8
• ----------------------------------------
  ---------------------

26
STATSPACK Host Statistics

• Calculation of Total and Busy CPU (spcpkg.sql)
• of total CPU for Instance' ch45n, 100
  ((dbcpubgcpu)/1000000) / (ttics) pctval
• of busy CPU for Instance' ch45n, 100
  ((dbcpubgcpu)/1000000) / ((btic)/100) pctval
• Available in Time Model (VSYS_TIME_MODEL)
• Discrepancy between estimated and actual CPU
• (Busy_Time Idle_Time) vs. (No.of.secs No. of
  CPUs)
• Usually present when load is high (queue
  depth/wait for CPU)
• Note memory statistics tracked in VOSSTAT
• This section not reported in AWR Report!!!

27
STATSPACK Response Histograms

• Wait Event Histogram DB/Inst MYRAC/MYRAC1
  Snaps begin_snap-end_snap
• -gt Total Waits - units K is 1000, M is 1000000,
  G is 1000000000
• -gt of Waits - column heading lt1s is truly
  lt1024ms, gt1s is truly gt1024ms
• -gt of Waits - value .0 indicates value was
  lt.05, null is truly 0
• -gt Ordered by Event (idle events last)
• Total
  ----------------- of Waits ------------------
• Event Waits lt1ms lt2ms
  lt4ms lt8ms lt16ms lt32ms lt1s gt1s
• -------------------------- ----- ----- -----
  ----- ----- ----- ----- ----- -----
• enq TX - row lock content 7561 4.1 .0
  .0 .1 .1 95.8
• enq WF - contention 7 57.1
  42.9
• enq WL - contention 4 75.0 25.0
• gc buffer busy 1893 74.2 4.5
  3.4 11.5 3.4 1.2 1.7
• gc cr block busy 33K 21.9 8.4
  3.5 5.0 5.0 27.3 28.7
• gc cr block congested 117 3.4
  23.1 53.8 18.8 .9
• gc cr disk read 1626 99.6 .4
  .1
• gc cr failure 1 100.0
• ltsnipgt
• SQLNet break/reset to cli 2276 99.3 .5
  .1 .2

28
STATSPACK Response Histograms

• Response Time Histograms
• Snapped for Event, File and Temp
• VEVENT_HISTOGRAM
• VFILE_HISTOGRAM
• VTEMP_HISTOGRAM
• Helpful in seeing spread, bumps in wait events
• Troubleshooting network response (example later)
• SQLNet break/reset to client - Client traffic
• SQLNet message to client (and to dblink)
  Inter server
• gc cr block congested (Internode traffic) Intra
  instance
• Not present in AWR (neither snapped or reported)

29
STATSPACK New SQL sections

• New SQL reporting sections in Oracle DB 10g SP
• SQL ordered by CPU From CPU_TIME in VSQL
• SQL ordered by Elapsed From ELAPSED_TIME in
  VSQL
• SQL ordered by Cluster Wait Time From new
  CLUSTER_WAIT_TIME in 10g VSQL
• Report compares total time for SQL statements
  captured to total time reported in the DB
• SQL ordered by CPU DB/Inst MYRAC/MYRAC1 Snaps
  begin_snap-end_snap
• -gt Resources reported for PL/SQL code includes
  the resources used by all SQL
• statements called by the code.
• -gt Total DB CPU (s) 1,826
• -gt Captured SQL accounts for 202.4 of Total DB
  CPU
• -gt SQL reported below exceeded 1.0 of Total DB
  CPU
• CPU CPU per
  Elapsd
• Time (s) Executions Exec (s) Total Time
  (s) Buffer Gets
• ---------- ------------ ---------- ------
  ---------- ---------------
• 947.13 437 2.17 51.9
  3503.86 29,619,674
• select q_name, state, delay, expiration, rowid,
  msgid, dequeue

30
New and changed views

• Large number of new as well as changed views
• (Sometimes) Inadequate documentation, but
  powerful uses!
• Classified by
• Services related
• In-memory metrics
• Response Histograms
• Extensions to existing views
• Interesting views
• Common columns

31
Service related views

• Service concept created in Oracle 8.0
• Provides ability to connection to an abstract
  entity
• SERVICE_NAME column in various views
• New views
• VSERVICES Services description (failover and
  load balancing information included)
• VSERVICE_STATS Workload statistics
• VSERVICE_EVENT Events by service

32
Service related views

• SQLgt select name, goal, dtp, aq_ha_notification,
  clb_goal
• 2 from vservices
• NAME GOAL D
  AQ_HA_NOTIFICATION CLB_GOAL
• -------------------- ---------- -
  ------------------ ----------
• APPLSYS.WF_CONTROL NONE N NO
  LONG
• MYRAC1.MYDOM.COM NONE N NO
  LONG
• SYSBACKGROUND NONE N NO
  SHORT
• SYSUSERS NONE N NO
  SHORT
• SQLgt select service_name, stat_name, value
• 2 from vservice_stats
• 3 where stat_name in
• 4 ('cluster wait time','db block changes')
  -- among 28 different stats
• SERVICE_NAME STAT_NAME
  VALUE
• -------------------- -----------------------------
  - ----------------
• SYSUSERS db block changes
  34,618,836
• MYRAC1.MYDOM.COM db block changes
  471,456,178

33
Service related views

• SQLgt select service_name, event, total_waits,
• 2 total_timeouts, time_waited
• 3 from vservice_event
• 4 where event in (
• 5 'latch library cache', 'read by other
  session') -- from 114 events
• SERVICE_NAME EVENT
  TOTAL_WAITS TIMEOUTS TIME_WAITED
• ---------------- ---------------------
  ----------- -------- -----------
• MYRAC1.MYDOM.COM read by other session
  25107023 23985 10848655
• MYRAC1.MYDOM.COM latch library cache
  420975 0 4352231
• SYSBACKGROUND read by other session
  29 0 14
• SYSBACKGROUND latch library cache
  6742 0 104454
• SYSUSERS read by other session
  27368974 435 3657701
• SYSUSERS latch library cache
  144347 0 734451
• Ability to segment and measure workload
• Needs to be setup (becomes complex in RAC
  environments)

34
In-memory metrics

• VMETRIC/VSYSMETRIC (VSYSSTAT) -gt
  VSYSMETRIC_HISTORY
• VEVENTMETRIC (VSYSTEM_EVENT) -gt
  VEVENT_METRIC_HISTORY
• VSYSMETRIC_SUMMARY (Avg/Min/StDev)
• WRH_SYSMETRIC_SUMMARY
• VMETRICNAME / VMETRICGROUP
• Metrics -gt Rate of change of statistics counters
• Alerts on rate -gt Server Generated Alerts
  (changeable via EM)
• Short-lived (3 mins of 15 secs, 1 hr of 60 secs)

35
In-memory metrics

• SQLgt select event, wait_time_milli, wait_count
• 2 from vevent_histogram
• 3 where event like 'SQLNet to client'
• EVENT WAIT_TIME_MILLI
  WAIT_COUNT
• ------------------------------ ---------------
  ----------
• SQLNet message to client 1
  137034612
• SQLNet message to client 2
  19183
• SQLNet message to client 4
  9922
• SQLNet message to client 8
  5446
• SQLNet message to client 16
  2931
• SQLNet message to client 32
  1519
• SQLNet message to client 64
  752
• SQLNet message to client 128
  310
• SQLNet message to client 256
  111
• SQLNet message to client 512
  29
• SQLNet message to client 1024
  1
• SQLNet message to client 2048
  1
• SQLNet more data to client 1
  8582781
• SQLNet more data to client 2
  34556

36
Existing/New views

• Number of existing views expanded
• VSQL, VSESSION
• Information merged from various views
• VSESSION (VSESSION_WAIT and VLOCK)
• New visibility
• VSQL_BIND_CAPTURE
• VSQL_SHARED_CURSOR
• Common Columns
• WAIT_CLASS and related
• WRH Tables exposed via DBA_HIST_ views
• VPROCESS_MEMORY session wise memory
• VSQLSTATS Low overhead access

37
VSESSION view

• Wait information merged from VSESSION_WAIT
• EVENT, P1 P3, SEQ, WAIT_CLASS
• Lock information from VLOCK
• BLOCKING_SESSION, BLOCKING_SESSION_STATUS,
  BLOCKING_INSTANCE
• Tons of new columns
• SQL_ID (Current and Previous)
• SQL_CHILD_NUMBER (Current and Previous)
• PLSQL related information (Entry, Subprogram,
  Object, etc)
• SQL_TRACE columns (binds and waits as well)
• CLIENT_IDENTIFIER (DBMS_MONITOR)
• No need to join to VSESSION_WAIT, VLOCK now

38
VSQL view

• Complete text in SQL_FULLTEXT
• Parallel execution (PX_SERVERS_EXECUTION)
• Different types of wait times
• APPLICATION_WAIT_TIME
• CONCURRENCY_WAIT_TIME
• CLUSTER_WAIT_TIME
• USER_IO_WAIT_TIME
• PLSQL_EXEC_TIME
• JAVA_EXEC_TIME
• Additional details
• PARSING_SCHEMA_NAME
• Optimizer details
• BIND_DATA
• LAST_LOAD_TIME (Docs LAST_ACTIVE_TIME??)

39
VSQL related views

• VSQL_OPTIMIZER_VIEW
• Optimizer parameters for SQL_ID and particular
  child cursor
• VSQL_PLAN
• TIMESTAMP, OTHER_XML, QBLOCK_NAME, etc.
• DBMS_XPLAN exposes details
• VSQL_SHARED_CURSOR
• Why was a cursor not shared?
• USER_BIND_PEEK_MISMATCH column
• Groundwork for 11g bind-awareness/bind-sensitivene
  ss
• VSQL_BIND_CAPTURE
• Use with ASH to get some bind values
• Undocumented ADVANCED parameter to get
  OTHER_XML details

40
Event Breakout

• Event names for blocking type events changed
• latch free now has 29 types (10.2.0.3)
• latch cache buffers chains
• latch library cache
• latch library cache pin
• enqueue has 205 types (10.2.0.3)
• enq TX - row lock contention (Waiting for Row
  lock)
• enq UL contention (Waiting for User generated
  lock)
• enq XR - quiesce database (Waiting for DB
  Quiesce)
• enq ST contention (Space transaction rare
  now with LMT)
• read by other session (BBW with reason code
  130)
• Break out at all levels (session/system- compare
  R1)
• Major change in monitoring / reporting code!!!

41
Mutexes Boon or Bane?

• Low overhead mechanism replacing some latches
• Simulated on certain platforms
• HP-UX uses a CAS (compare-and-swap) simulator
• This itself is a latch quite an overhead
• Track using VMUTEX_SLEEP and VMUTEX_SLEEP_HISTOR
  Y
• _kks_use_mutex_pin FALSE turns this off
• Snapshotted in STATSPACK, not in AWR ?

42
Operating System Statistics

• Set SQLPlus format to get right value! (issue in
  AWR)
• SQLgt select stat_name, value
• 2 from vosstat
• STAT_NAME
  VALUE
• --------------------------------------------------
  -------------- ----------
• NUM_CPUS
  24
• IDLE_TIME
  802033123
• BUSY_TIME
  878091185
• USER_TIME
  489231709
• SYS_TIME
  388859476
• IOWAIT_TIME
  187539358
• AVG_IDLE_TIME
  33398667
• AVG_BUSY_TIME
  36564711
• AVG_USER_TIME
  20362160
• AVG_SYS_TIME
  16180042
• AVG_IOWAIT_TIME
  7794964
• OS_CPU_WAIT_TIME
  2.6867E13
• RSRC_MGR_CPU_WAIT_TIME
  0

43
Time Model

• CPU breakup updated every 3 seconds!!!
• SQLgt select stat_name, value from
  vsys_time_model
• where value gt 0
• STAT_NAME
  VALUE
• ---------------------------------------------
  --------------
• DB time
  893170091346
• DB CPU
  176244910473
• sequence load elapsed time
  10215471781
• parse time elapsed
  4524012412
• hard parse elapsed time
  3657262901
• failed parse elapsed time
  103540062
• hard parse (sharing criteria) elapsed time
  365217641
• hard parse (bind mismatch) elapsed time
  5923514
• repeated bind elapsed time
  14768010
• connection management call elapsed time
  328536127
• PL/SQL execution elapsed time
  5554924592
• PL/SQL compilation elapsed time
  333815896
• background elapsed time
  13782131027
• background cpu time
  4572399582

44
Wait Model

• SQLgt select wait_class, time_waited, total_waits
• from vsystem_wait_class
• WAIT_CLASS TIME_WAITED
  TOTAL_WAITS
• -------------------- ----------------
  ----------------
• Other 1370371162
  10777690366
• Application 11031616
  77468
• Configuration 433075
  3972043
• Administrative 986
  10
• Concurrency 5069513044
  4023651698
• Commit 4187059
  4606985
• Idle 26816217176
  1179884266
• Network 1964510
  152943745
• User I/O 845512795
  3197266600
• System I/O 21813081
  47778673

45
Wait Class

• Types of wait classified by WAIT_CLASS
• SQLgt select wait_class, count() from
  vevent_name
• 2 group by wait_class
• WAIT_CLASS COUNT()
• ----------------- -----
• User I/O 17
• Application 12
• Network 27
• Concurrency 25
• Administrative 46
• Configuration 23
• Scheduler 2
• Cluster 47
• Other 592
• Idle 62
• System I/O 24

46
Hidden surprises

• GROUP BY does not ensure ORDER BY
• New code base avoids sort for GROUP BY operation
• Order was never guaranteed
• Large amount of legacy/custom code needs change
• Investigate _gby_hash_aggregation_enabled
  parameter
• System Statistics
• CPU Cost optional in 9i
• Fully functional (by default) in 10g
• Sample collected when DB installed/upgraded
• Check in SYS.AUX_STATS view
• See Metalink Notes 457228.1, 153761.1, 149560.1
• Collecting System Stats does NOT invalidate
  shared pool

47
Hidden surprises

• Dynamic sampling
• 9i default was 1
• 10g default is 2
• all unanalyzed tables are sampled
• Number of blocks sampled twice default number
  (2x3264)
• Impact on Global Temp tables
• Impact on parse (not affected by plan permutation
  limits)
• Statistics gathering and Table monitoring
• Monitoring ON by default
• Used by Automatic Stats gathering (Dictionary
  stats included)
• Trigger fixed at 10, changeable in 11g
• Stats collection for Fixed table (new but
  optional)

48
Hidden surprises

• DBMS_STATS
• 9i and below Defaults were fixed
• 10g and above Defaults can be changed (boon or
  bane?)
• Many parameters default to decided by Oracle
• Examples
• NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE
• CASCADE DBMS_STATS.AUTO_CASCADE
• ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE
• METHOD_OPT FOR ALL COLUMNS SIZE AUTO
• GRANULARITY AUTO (For partitioned objects only)
• Automatically stores past 31 days
  DBA_TAB_STATS_HISTORY
• DIFF_TABLE_STATS_ function in 10.2.0.4

49
Useful sections AWR Report

• Segment statistics in AWR report
• Same as Top 5 Segments in Level 7 STATSPACK
  reports (9i)
• Segments by Global Cache Buffer Busy (RAC only,
  new section in 10g)
• Segments by CR Blocks Received (RAC only)
• Segments by Current Blocks Received (RAC only)
• New GC Load profile and GC Efficiency percentages
• Global Cache Load Profile
• Per
  Second Per Transaction
• 
  --------------- ---------------
• Global Cache blocks received
  1,375.70 2.90
• Global Cache blocks served
  164.77 0.35
• GCS/GES messages received
  5,165.31 10.88
• GCS/GES messages sent
  8,684.54 18.30
• DBWR Fusion writes
  0.63 0.00
• Estd Interconnect traffic (KB)
  15,028.80
• Global Cache Efficiency Percentages (Target
  localremote 100)
• 
  
• Buffer access - local cache 97.41

50
Extracting data from the AWR

• Complete source in paper
• Adaptable for different types of extracts
• / This cursor fetches details of the current
  snapshot plus the next one using the LEAD
  function. We will use this to
• make sure that there was no DB restart
  in-between /
• cursor snapshot is
• select snap_id, lead(snap_id, 1, 0) OVER (ORDER
  BY snap_id),
• startup_time, lead(startup_time, 1) OVER (ORDER
  BY snap_id),
• to_char(begin_interval_time) begin_interval_time,
• to_char(end_interval_time) end_interval_time
• from sys.dba_hist_snapshot
• where instance_number v_instance_number -- For
  RAC instances
• and dbid (select dbid from vdatabase)

51
Extracting data from the AWR

• -- We don't subtract for certain types such as
  NUM_CPUS, etc.
• cursor osstat is
• select e.instance_number, e.stat_name,
• case
• when e.stat_name in ('NUM_CPU_SOCKETS','NUM_CPUS
  ','NUM_CPU_CORES','LOAD')
• then e.value
• else e.value - b.value
• end stat_value
• from sys.dba_hist_osstat b, sys.dba_hist_osstat e
• where b.stat_id e.stat_id
• and b.snap_id v_begin_snap_id and e.snap_id
  v_end_snap_id
• and b.instance_number e.instance_number
• and b.instance_number v_instance_number

52
Extracting data from the AWR

• v_instance_number 1 -- Send this from
  command line
• open snapshot
• LOOP
• fetch snapshot into v_begin_snap_id,
  v_end_snap_id,
• v_begin_startup_time, v_end_startup_time,
• v_begin_interval_time, v_end_interval_time
  
• exit when snapshotNOTFOUND
• -- Run through only if the startup times for
  both snaps are same!
• -- also, avoid the last line (lead will
  return 0 for end_id)
• if ( v_begin_startup_time
  v_end_startup_time ) and ( v_end_snap_id ! 0 )
  then
• open osstat
• loop
• fetch osstat into v_instance_number,
  v_stat_name, v_value
• exit when osstatNOTFOUND
• dbms_output.put_line(v_end_snap_id ','
• to_char(v_end_interval_time,
  'DD-MON-YY HH24MI')
• ',' v_stat_name ','
  v_value)
• end loop
• close osstat

53
Extracting data from the AWR

• Sample output (portion of output)
• SQLgt _at_awr_osstat 1
• old 38 v_instance_number 1 -- Send this
  from command line
• new 38 v_instance_number 1 -- Send this
  from command line
• 5363,18-FEB-08 0000,NUM_CPUS,24
• 5363,18-FEB-08 0000,IDLE_TIME,5975106
• 5363,18-FEB-08 0000,BUSY_TIME,2664727
• 5363,18-FEB-08 0000,USER_TIME,970933
• 5363,18-FEB-08 0000,SYS_TIME,1693794
• 5363,18-FEB-08 0000,IOWAIT_TIME,531265
• 5363,18-FEB-08 0000,LOAD,.3505859375
• 5363,18-FEB-08 0000,NUM_CPU_SOCKETS,24
• 5363,18-FEB-08 0000,PHYSICAL_MEMORY_BYTES,0
• 5363,18-FEB-08 0000,VM_IN_BYTES,23502864
• 5363,18-FEB-08 0000,VM_OUT_BYTES,0
• --
• 5364,18-FEB-08 0100,NUM_CPUS,24
• 5364,18-FEB-08 0100,IDLE_TIME,6018339
• 5364,18-FEB-08 0100,BUSY_TIME,2684043

54
New features in DBMS_XPLAN

• Excellent alternative for EXPLAIN_PLAN
• Introduced in 9i, expanded in 10g
• DISPLAY_AWR - Plan stored in the AWR
• DISPLAY_CURSOR VSQL cursor cache
• DISPLAY_SQLSET - Plan of a given statement stored
  in a SQL tuning set (STS)
• Simple to use
• select from table(dbms_xplan.display_awr(ltSQL_
  IDgt, options))
• Displays all child cursors if present

55
New features in DBMS_XPLAN

• SQLgt select from table(dbms_xplan.display_cursor
  ('g6jzbgnku8024',NULL,'ADVANCED'))
• PLAN_TABLE_OUTPUT
• --------------------------------------------------
  -----------------------------------------
• SQL_ID g6jzbgnku8024, child number 0
• -------------------------------------
• SELECT R.RESPONSIBILITY_NAME FROM
  FND_RESPONSIBILITY_VL R
• WHERE R.RESPONSIBILITY_ID 1 AND
  R.APPLICATION_ID 1
• Plan hash value 3221072286
• --------------------------------------------------
  --------------------------------------------------
  -----
• Id Operation Name
  Rows Bytes Cost (CPU) Time
  
• --------------------------------------------------
  --------------------------------------------------
  -----
• 0 SELECT STATEMENT
  2 (100)
  
• 1 NESTED LOOPS
  1 51 2 (0)
  000001
• 2 INDEX UNIQUE SCAN
  FND_RESPONSIBILITY_U1 1 10 1
  (0) 000001
• 3 TABLE ACCESS BY INDEX ROWID
  FND_RESPONSIBILITY_TL 1 41 1
  (0) 000001
• 4 INDEX UNIQUE SCAN
  FND_RESPONSIBILITY_TL_U1 1 0
  (0)
• --------------------------------------------------
  --------------------------------------------------
  -----
• Query Block Name / Object Alias (identified by
  operation id)
• --------------------------------------------------
  -----------

56
New features in DBMS_XPLAN

• Peeked Binds (identified by position)
• --------------------------------------
• 1 - 1 (NUMBER) 50357
• 2 - 1 (NUMBER, Primary1)
• Predicate Information (identified by operation
  id)
• --------------------------------------------------
  -
• 2 - access("B"."APPLICATION_ID"1 AND
  "B"."RESPONSIBILITY_ID"1)
• 4 - access("T"."APPLICATION_ID"1 AND
  "T"."RESPONSIBILITY_ID"1 AND
• "T"."LANGUAGE"USERENV('LANG'))
• Column Projection Information (identified by
  operation id)
• --------------------------------------------------
  ---------
• 1 - "T"."RESPONSIBILITY_NAME"VARCHAR2,100

57
Items Learned in this Session

• So there you have it all!
• This was a summary of a few of the useful
  features
• Hopefully, this adds to the vast information out
  there
• Where do you go from here?
• Papers, OTN, Blogs, and.. the Oracle manuals! ?
• What do you plan to use when you get back to the
  office?

58
Questions and feedback

• Questions?
• Contact information ora_apps_dba_y_at_yahoo.com
• Please fill up your evaluation form!
• Session 319
• Oracle DB 10g An enlightened revisit
• Thank you!