Transcript Slide 1
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.
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 …
# 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.
# 2: Use a Diagram to Map Complex Joins Join conditions Table 3 Table 1 Table 4 Table 2 Table 5 Table 6
# 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.
# 4: Confirm Parallel Processing is Working as you Intended
Are slaves doing multi-block or single-block reads?
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
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
# 5: Save “.sql” Files in Notepad With “All Files” Option Thanks, Vasu!
# 6: Get Sample of Bind Variables
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
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]
# 7: Use ASH to Isolate Problem in a Specific Time Period
AWR is an
aggregate
and gives summary.
ASH is great for nailing specific, brief issue.
Drawback: 10 minute runtime on big db.
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
# 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.
# 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.
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
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 |
# 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: 1.
2.
You turn-on
outline capture
.
You run the sql in question. 3.
4.
5.
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.
A Difficulty with Stored Outlines
1.
2.
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.
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 Set Ol_name = << this table has the hints Decode(ol_name, One, Two, Two, One)
*Thanks to Simon Leung for this tip!