Optimising Oracle (Performance by Design) – Day 1

1

From 21
February till 23 February the OakTable seminar Optimising Oracle (Performance
by Design) by Jonathan Lewis was held. The seminar had been organised by Lex de
Haan, who recently died, and his wife, Juliette Nuijten, from Naturaljoin (http://www.naturaljoin.nl). Despite his
death the seminar continued, just as Lex wanted. Naturaljoin will continue to
organise seminars. There were 82 attendants, an all-time high record for Naturaljoin.

 

The guest
speaker was Jonathan Lewis, an independent consultant who has written a few
Oracle books, including Cost-based Oracle fundamentals (part one in a volume of
three). Jonathan is a very good speaker with lots of humour and details. This
was a big plus since not all the stuff was always that interesting for all the
people. His motto is: don’t believe it because it is written in the book; just
learn how to discover, because Oracle keeps changing (even with patch
releases).


Day 1 was
about:

  • Parsing and Optimising costs
  • UNDO costs
  • REDO costs
  • Latching Issues

 

Parsing and Optimising
costs

This part
was about:

  • discover how expensive SQL is
  • learn what code suits Oracle
  • understand the ‘quick fixes’

 

To discover
how expensive SQL is:

  • create an object (you might use
    dbms_random to create random data)
  • bounce the database (or execute
    alter system flush shared_pool)
  • alter session set sql_trace
    true
  • execute a simple statement
  • check the trace file (using
    tkprof for instance)

 

New
features like monitoring index usage, dynamic sampling of statistics, fine
grained access control (also known as virtual private database) have an impact
on the recursive SQL, so be careful with these features.

 

To see the
details of how the optimizer evaluates and selects a path this command may be
used:

alter session set events ‘10053 trace name context
forever, level 1’;

 

Coding
strategies:

  • Literal strings throughout
  • needed for Decision Support
    Systems (data warehouse)
  • non-scalable for OLTP
  • Bind variables (nearly)
    everywhere
  • Can still end up ‘soft’
    parsing
  • Parse once and hold
  • Harder to code safely

 

Parse once
and hold is done automatically by PL/SQL for cursors. For dynamic SQL
(dbms_sql) or ODBC/JDBC it means: parse or describe the statement only once and
keep the cursor.

Undo costs

This
session was about:

  • understand how undo works
  • understand what undo does
  • be able to monitor the costs of
    undo
  • investigate options for
    minimising undo

 

As a
general rule, Oracle tries to avoid destroying information for as long as possible,
so you can’t make a change to a data block until you have a safe copy of the
original data – and you need some space to make that copy. This is were undo
segments (previously known as rollback segments) come into play.

 

Undo is
needed for rollback and read consistency. Oracle now offers automatic undo for
easier management of undo segments.

 

The command

 

alter system dump undo header { segment name };

 

can be used
to dump the contents of an undo header.

 

The
dictionary tables v$transaction, v$undostat, dbs_undo_extents are useful for
monitoring undo costs.

 

Rollback is
a very expensive operation, so minimising the number of rollbacks will reduce
the costs of undo. No change updates like

 

update t1 set c1 = c1, c2 = c2, c3 = c3

 

are expensive
too.

Redo costs

Why redo:

  • Smarter I/O
  • Recoverability

 

What does
it cost:

  • Redo imaging
  • Serialisation
  • Archiving

 

Redo
logging:

  • a stream of all changes to
    blocks
  • may contain uncommitted changes
  • must be written on a commit
  • if it’s not in the log file, it
    did not happen (create table/index… nologging)
  • not re-read in normal
    processing

 

Effects of
commit:

  • a ‘commit record’ must be
    created
  • redo in  the log buffer is written to disc
  • the session ‘must’ wait for lgwr
    to return
  • other processes may keep
    writing to the buffer
  • other processes may commit and
    then wait
  • lgwr writes all the pending
    commits

 

The costs
for updating 17 rows in a PL/SQL loop:

 

 

Commit at
the end

Commit
every row

redo size

5440

10220

redo
entries

18

34

redo
wastage

16

4660

redo
blocks

11

30

 

How to
avoid redo costs:

  • use ‘proper’ temporary tables
  • do not rollback or delete on
    temporary tables but use commit/truncate, or make sure the table is created
    with on commit delete rows
  • use NOLOGGING for create
    table/index
  • look at options for using insert
    /*+ append */
  • use direct loads with
    SQL*Loader

 

As far as
archiving and backup is concerned: the Oracle backup and recovery tool RMAN
knows about all the Oracle internals.

 

Latching issues

Oracle uses
latches to protect memory (for instance several users trying to read/update a
linked list). Historically, Oracle handled the problem though exclusive
latches. Oracle 9i introduced the shared read latch.

 

Demonstration
code:

 

create table kill_cpu (n integer, primary key (n))

organization index as

select rownum n from all_objects

where rownum <= 23;

 

select count(*) from kill_cpu

connect by n > prior n

start with n = 1;

 

This simple
query takes one minute on an average NT machine (HP Omnibook 700 MHz).

 

The view
v$session_event shows as that there is the event ‘latch free’ is responsible
and from v$latch we find that the latch ‘cache buffer chains’ is responsible.

 

Options to
improve on pinning databuffers: set the arraysize larger (SQL*Plus).

 

Again:
parse once improves on latching issues.

 

The example
is a select from a table for all the numbers from 1 to 10000. There are four
variants:

  1. execute immediate using literal
    text
  2. execute immediate using bind
    variables
  3. parse once using dbms_sql
    (dynamic)
  4. using standard PL/SQL (static)

 

These are
the results:

 

 

Literal

Bind

Dynamic

Static

Parse CPU
Time

17.40

0.66

0

0

Parse
count

10001

10001

2

1

Hard
parses

10000

2

2

1

Soft
parses

1

9999

0

0

CPU Time

21.55

3.74

2.86

1.06

Recursive
calls

10000

10000

30003

10000

 

Things to
avoid:

  • large triggers with SQL statements,
    because they are parsed over and over again.
  • ref cursors do not use the
    pl/sql cursor cache.

 

Share.

About Author

Oracle Consultant at AMIS

1 Comment

  1. Can you please share the material,I am from india ,it is unforunate that,we have no seminars by lewis here in india