Re: Surprising parameters for direct path read in 100046 trace

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Wed, 08 Mar 2006 01:19:29 -0500
Message-Id: <1141798769l.6853l.2l@medo.noip.com>

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_at_medo.noip.com

• 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

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

Received on Wed Mar 08 2006 - 00:19:29 CST