Oracle Performance - Part 2

Data-Dictonary / Tables / Views and more

• v$ - Views are like "Dynamic Performance Views"
• Not all of them are relevant for tuning
• they are a part of the SYS

Checkout the Oracle ref. books, within the Database Performance Guide and reference book, they describe the most important views

Measuring:

Usually you measure CPU, Elapsed Time, I/O und access at the SGA

Possibilities for analyses:

• statspack
• dv-review / tuning package
• enterprise manager

1. Instance information are retrived from: SELECT * FROM V$SYSSTAT
2. Session information are retrived from: SELECT * FROM V$SESSTAT
3. My sessions information is retrived from: SELECT * FROM V$MYSTAT
4. Events are retrieved from : SELECT * FROM V$EVENT_NAME

Examples:

1. SELECT n.name statsitic, st.VALUE, se.username from v$sesstat st, v$statname n, v$session se WHERE st.statistic#=n.statistic# and st.sid = se.sid and se.username=''your_username'
2. Wait - Statistics -> SELECT * FROM v$waitstat;
3. Show the wait of your actuall session: SELECT sid, event, p1text, p1, p2text, p2, seconds_in_wait sek , state from v$session_wait where sid=YOUR_ID

Possibles values for the value state:

• WAITING - session is waiting
• WAITED UNKNOWN TIME - duration of waiting is unknown (TIMED_STATISTICS)
• WAITED SHORT TIME - last wait was lower then 0.01 sec.
• WAITED KNOWN TIME - duration of last wait

extension for the V$SESSION_WAIT -> V$ACTIVE_SESSION_HISTORY

it display the history of your session, and some new col. are displayed which are making life easier to identify BLOCKING_SESSION, BLOKING_SESSION_SERIAL and some more specific data.

the above described views are a part of the diagnostic pack. before you gone use them, please check your license pack there are some hints! oracle is getting warster then MS, they even deliver everything and install it directly but you're not allowed to use it... so check your lic. package.