1 / 18

DBA LESSONS LEARNED

DBA LESSONS LEARNED. Ten Lessons I Have Learned* *most the hard way. 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.

lynde
Download Presentation

DBA LESSONS LEARNED

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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!

More Related