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!