Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <cary.millsap@xxxxxxxxxx>, "bill thater" <shrekdba@xxxxxxxxx>
- Date: Wed, 10 May 2006 00:25:43 +0800
> 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.
Other related posts:
