DBA LESSONS LEARNED

1. DBA LESSONS LEARNED Ten Lessons I Have Learned* *most the hard way

2. Why Share “Tips?”It’s in your own Best Interest! • There’s tons of smart people here. Learning from them is better than Oracle classes—it’s supremely practical. • It’s hard for anyone to be an expert at more than 1 thing. So watch for people who have a better way at doing something and copy them! • Many of my scripts are from someone else. One came from a beginner. So without further ado …

3. # 1: “Active” Sessions is What Matters • Many projects mistakenly dwell on potential users, or users “connected.” “We will have 10,000 concurrent users.” • It’s more important to count users who actually query db simultaneously. • Hint: Only a few % of users are really active (in the db) at any time.

4. # 2: Use a Diagram to Map Complex Joins Table 2 Table 1 Join conditions Table 3 Table 4 Table 5 Table 6

5. # 3: Performance Issues Require Specific Solutions • Let the problem lead you to the answer—don’t presuppose a solution. • Focusing on a database-wide silver bullet is a losing strategy 99.9% of the time. • Biggest distraction: “Let’s increase SGA.” • In 5,000 perf problems, increasing SGA memory was the fix 0 times. • Slow SAN was root cause 3 times.

6. # 4: Confirm Parallel Processing is Working as you Intended Are slaves doing multi-block or single-block reads?

7. For Reference:The Parallel Slave Script (part 1) column child_wait format a30 column parent_wait format a30 column server_name format a4 heading 'Name' column x_status format a10 heading 'Status' column schemaname format a10 heading 'Schema' column x_sid format 9990 heading 'Sid' column x_pid format 9990 heading 'Pid' column p_sid format 9990 heading 'Parent' column program format a12 break on p_sid skip 1 set linesize 200

8. For Reference:The Parallel Slave Script (part 2) select x.server_name , x.pid as x_pid , x.sid as x_sid, w2.sid as p_sid , v.osuser , v.schemaname , program , w1.event as child_wait, w2.event as parent_wait from v$px_process x , v$lock l, v$session v , v$session_wait w1 , v$session_wait w2 where x.sid <> l.sid(+) and to_number (substr(x.server_name,2)) = l.id2(+) and x.sid = w1.sid(+) and l.sid = w2.sid(+) and x.sid = v.sid(+) and nvl(l.type,'PS') = 'PS' and x.status not like 'AVAIL%' and w2.event not like 'SQL*Net%‘ order by 1,2

9. # 5: Save “.sql” Files in Notepad With “All Files” Option Thanks, Vasu!

10. # 6: Get Sample of Bind Variables Select INST_ID, c.name||'/'||c.value_string bind_var from GV$sqlarea a, dba_users b, v$sql_bind_capture c where b.user_id=a.parsing_user_id and b.username != 'SYS‘ and c.address=a.address and a.sql_id = [enter] • We often need to know typical values the user sets for a bind variable. • Useful for testing a performance solution • For historical, use DBA_HIST_SQLBIND

11. # 7: Use ASH to Isolate Problem in a Specific Time Period With P1 As (Select /*+Parallel(a 6) */ Distinct Sample_time, Session_id, Sql_text, Event, Instance_number, Blocking_session From Dba_hist_active_sess_history A, V$sqltext B Where A.Sql_id = B.Sql_id AND Sample_time Like '30-SEP-09 10.52%AM' And Piece = 0 ) Select Instance_number Ins,session_id, SAMPLE_TIME, Sql_text, Event, Blocking_session From P1 Order By 3 • AWR is an aggregate and gives summary. • ASH is great for nailing specific, brief issue. • Drawback: 10 minute runtime on big db.

12. # 8: The Leading Table is Key to Fast Joins • Start a join with table getting biggest proportional reduction in result set. • Often not the smallest table. • Use hint, /*+ LEADING (alias) */ • Sometimes use ORDERED if other tables need to follow certain order. • Use a diagram to decide on join order.

13. # 9: Use Sql*Plus Autotrace for Quick Stats • Gives you a quick overview of exec plan and statistics count. • Set Autotrace On [or Traceonly]; • Caveat—the result set is still sent to your client—just not displayed. • If you’re timing, be careful you’re not measuring time for network xfer.

14. Autotrace Sample Output (p1) Statistics --------------------------------------------- 0 recursive calls 0 db block gets 10136 consistent gets 0 physical reads 0 redo size 226 bytes sent via SQL*Net to client 239 bytes received via SQL*Net from client

15. Autotrace Sample Output (p2) --------------------------------------------------------------- | Id | Operation | Name | Rows | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | SORT AGGREGATE | | 1 | | 2 | VIEW | DBA_OBJECTS | 75218 | | 3 | UNION-ALL | | | |* 4 | FILTER | | | |* 5 | HASH JOIN | | 83015 | | 6 | TABLE ACCESS FULL | USER$ | 60 | |* 7 | TABLE ACCESS FULL | OBJ$ | 83015 | |* 8 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | |* 9 | INDEX UNIQUE SCAN | I_IND1 | 1 |

16. # 10: Tricky Way to Use Stored Outlines A stored outline preserves an execution plan. It’s most often used in testing. Here’s how it works: • You turn-on outline capture. • You run the sql in question. • Oracle watches how the sql runs & figures out what sql hints ensure the present exec plan. • When that exact sql is run in the future, Oracle applies those sql hints to keep same exec plan. • The hints are stored in 3 outline tables.

17. A Difficulty with Stored Outlines • What if you want a different exec plan to happen when you run a certain sql? • How can Oracle do this, because Stored Outlines preserve an existing execution plan? • The scheme: • Use sql hint to create the exec plan you would like to occur; • Trick Oracle to use the new exec plan even when we don’t supply the sql hint.

18. Lesson # 10 detail* • Turn on stored outline gathering. • Run Sql. Then run 2nd sql with hint added. • We now have 2 stored outlines: No hint >> Oracle uses Outline 1 (bad plan) With hint >> Oracle uses Outline 2 (good plan) • Reverse hints so that Oracle will apply Outline 2 when it sees the sql without the hint. • Update Outln.Ol$hints << this table has the hints Set Ol_name = Decode(ol_name, One, Two, Two, One) *Thanks to Simon Leung for this tip!