Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Wed, 10 May 2006 00:25:43 +0800
Message-ID: <09d101c67385$3a668640$3f121eac@porgand>

> 10200 is especially intrusive, because it emits a line of text to the
> trace file for every buffer cache access that takes place.

Btw, you can use event 10812 in conjuction with KST tracing to get less intrusive consistent get tracing into X$TRACE buffers.

X$TRACE example:

SQL> select pid from v$process where addr =

  2     (select paddr from v$session where sid =
  3         (select sid from v$mystat where rownum = 1));

       PID
----------
        15

1 row selected.

SQL>
SQL> alter tracing enable "10812:1:15"; -- event#:level:PID

Tracing altered.

SQL>
SQL> select data from x$trace where event=10812;

no rows selected

SQL>

SQL> select to_char(dbms_utility.make_data_block_address(
  2                              dbms_rowid.rowid_relative_fno(rowid),
  3                              dbms_rowid.rowid_block_number(rowid)),
  4                 'XXXXXXXX') HEXDBA

  5 from t;

HEXDBA

---

   40EDFA 1 row selected.

SQL>
SQL> select data from x$trace where event=10812;

DATA

---

0x000000000040EDFA 0x0000000000000000 0x00000000000B5FE2
0x0000000000000000 0x0000000000000000 0x0000000000000000
0x0000000000000000 0x0000000000000000 0x0000000000000000
0x000000000040EDFA 0x0000000000000000 0x0000000000000000

Or alternatively you could just enable in-memory buffering for the regular ksdwrt() tracing with:

alter system set events 'immediate trace name trace_buffer_on level 1048576';

That should reduce the number of system calls made, instead writing every row to OS a write call is made once buffer is full or the flush is requested manually.

Cheers,
Tanel.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 09 2006 - 11:25:43 CDT