Re: Surprising parameters for direct path read in 100046 trace
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: chris@xxxxxxxxxxxxxxxxxxxxx
- Date: Wed, 08 Mar 2006 01:19:29 -0500
On 03/07/2006 05:14:35 AM, Chris Dunscombe wrote:
> Oracle version 9.2.0.4 64 bit on Solaris. Filenum# 202 is an Oracle temp file.
> The SQL was in the middle of a sort merge at the time.
Chris, Oracle uses direct path reads only to read LOB segments or temporary
files.
If anything else is read using direct path, your application is RMAN. Why is
Oracle
re-reading it? Have you dumped those blocks? I bet they are the header blocks,
re-read and updated for "maintenance reasons". Of course, another recourse
would be
to search bug lists for 9.2.0.5, 9.2.0.6 and 9.2.0.7 versions for any bugs
regarding
sort performance.
You can dump temporary file by using the following command:
1 alter system dump tempfile
2 '/oradata/10g/oracle/10G/datafile/o1_mf_temp_202xcsr1_.tmp'
3* block min 0 block max 300
SQL> /
System altered.
SQL>
You can also do it like this:
1 alter system dump tempfile 1
2* block min 0 block max 300
SQL> /
System altered.
SQL>
The output looks a bit like this:
/oracle/product/10g/admin/10G/udump/10g_ora_7440.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
ORACLE_HOME = /oracle/product/10g
System name: Linux
Node name: medo.noip.com
Release: 2.6.15.4
Version: #1 Tue Feb 21 20:34:25 EST 2006
Machine: i686
Instance name: 10G
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 7440, image: oracle10G@xxxxxxxxxxxxx
*** 2006-03-08 01:11:48.166
*** ACTION NAME:() 2006-03-08 01:11:48.166
*** MODULE NAME:(SQL*Plus) 2006-03-08 01:11:48.166
*** SERVICE NAME:(ORACLE) 2006-03-08 01:11:48.166
*** SESSION ID:(58.7) 2006-03-08 01:11:48.166
Start dump data block from file
/oradata/10g/oracle/10G/datafile/o1_mf_temp_202xcsr1_.tmp minblk 0 maxblk 300
V10 STYLE FILE HEADER: Compatibility Vsn = 169869824=0xa200200 Db
ID=3289174460=0xc40cd1bc, Db Name='10G' Activation ID=0=0x0 Control
Seq=31=0x1f, File size=2560=0xa00
File Number=1, Blksiz=8192, File Type=6 TEMP FILE Dump all the blocks in
range: buffer tsn: 3 rdba: 0x00400002 (1/2)
scn: 0x0000.00048d60 seq: 0x00 flg: 0x0c tail: 0x8d601d00 frmt: 0x02 chkval:
0x48d6 type: 0x1d=KTFB Bitmapped File Space Header Hex dump of block: st=0,
typ_found=1 Dump of memory from 0x0CCBA000 to 0x0CCBC000
CCBA000 0000A21D 00400002 00048D60 0C000000 [......@.`.......]
CCBA010 000048D6 00000001 00000080 00000A00 [.H..............]
CCBA020 00000009 00000050 003FFFFE 00000007 [....P.....?.....]
CCBA030 00000988 00000010 00000003 00000000 [................]
CCBA040 00000000 00000000 00000000 00010000 [................]
CCBA050 00000789 00000080 00000000 00000000 [................]
CCBA060 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
Dump will tell you what kind of blocks you are looking at and may by, but just
may be, you will be able to guess why is it re-reading the same two blocks
gazillion
times. Mysterious are the ways of Oracle.
--
Mladen Gogala
http://www.mgogala.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
