Re: (LONG POST) autoextend size vs. extent size in LMT

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 17 Jun 2002 09:21:57 +0100
Message-ID: <3d0d9c26$0$231$ed9e5944@reading.news.pipex.net>

Norman

I'd expect that one of the system processes (smon?) would be the session that issued 'ALTER DATABASE DATAFILE RESIZE....' in the event of autoextend perhaps you were only tracing your session. Howards point about the resize being recorded in the alert.log is well taken, unfortunately this seems to have disappeared in 9.2. NB I have left in the new syntax for datafile creation that fooled me first of all as I thought others might learn from it.

Log of session

SQL> CONN NIALL
Connected.
SQL> CREATE TABLESPACE TSTEST
2 DATAFILE 'C:\TSTEST.DBF' SIZE 2048K AUTOEXTEND ON NEXT 2K MAXSIZE 3M 3 EXTENT MANAGEMENT LOCAL UNIFORM EXTENT SIZE 64K; EXTENT MANAGEMENT LOCAL UNIFORM EXTENT SIZE 64K

ERROR at line 3:
ORA-25146: EXTENT MANAGEMENT option already specified

SQL> ED
Wrote file afiedt.buf

1 CREATE TABLESPACE TSTEST
2 DATAFILE 'C:\TSTEST.DBF' SIZE 2048K AUTOEXTEND ON NEXT 2K MAXSIZE 3M 3* UNIFORM SIZE 64K
SQL> / Tablespace created.

SQL> CREATE TABLE TSTEST(COL1 VARCHAR(2000)) 2 TABLESPACE TSTEST
3 STORAGE(INITIAL 1984K NEXT 64K); Table created.

SQL> SELECT COUNT(*) FROM DBA_EXTENTS
2 WHERE SEGMENT_NAME='TSTEST'; COUNT(*)

SQL> ALTER TABLE TSTEST ALLOCATE EXTENT; Table altered.

SQL> SELECT COUNT(*) FROM DBA_EXTENTS
2 WHERE SEGMENT_NAME='TSTEST'; COUNT(*)

SQL> SELECT BYTES/1024 SIZE_IN_K FROM V$DATAFILE WHERE NAME='C:\TSTEST.DBF'; SIZE_IN_K

2112

SQL> SPOO OFF nb I next connect as sysdba and drop the tablespace and shutdown. No other activity here.

Alert log extract

Mon Jun 17 09:10:49 2002
CREATE TABLESPACE TSTEST
DATAFILE 'C:\TSTEST.DBF' SIZE 2048K AUTOEXTEND ON NEXT 2K MAXSIZE 3M UNIFORM SIZE 64K
Mon Jun 17 09:10:50 2002
Completed: CREATE TABLESPACE TSTEST
DATAFILE 'C:\TSTEST.DBF'
Mon Jun 17 09:14:38 2002
ALTER TABLESPACE TSTEST OFFLINE
Mon Jun 17 09:14:39 2002
Completed: ALTER TABLESPACE TSTEST OFFLINE Mon Jun 17 09:14:44 2002
DROP TABLESPACE TSTEST
Completed: DROP TABLESPACE TSTEST
Shutting down instance: further logons disabled Shutting down instance (normal)
License high water mark = 3
All dispatchers and shared servers shutdown Mon Jun 17 09:15:07 2002
ALTER DATABASE CLOSE NORMAL
Mon Jun 17 09:15:07 2002
SMON: disabling tx recovery
SMON: disabling cache recovery
Mon Jun 17 09:15:07 2002
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active Thread 1 closed at log sequence 53
Successful close of redo thread 1.
Mon Jun 17 09:15:08 2002
Completed: ALTER DATABASE CLOSE NORMAL
Mon Jun 17 09:15:08 2002
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA7023A6819_at_lnewton.leeds.lfs.co.uk...
> Ok I tried it out on 8.1.7.3.0 and didn't find much I'm afraid !
>
> I created a very small tablespace 2 Meg in size, uniform extents 64K and
> autoextending in 2KB chunks up to a max size of 3 MB. (Silly or what?)
> I then created a table in it, with initial size set to 1984KB which was
> enough to create 31 extents of 64KB and NOT (yet) extend the datafile.
> The datafile is at this point 2056 KB in size.
>
> Next up I turned on event 10046 at level 12 - to get everything.
>
> alter table test add extent was next and this added an extent of 64KB.
> The datafile has now extended up to 2129 KB which is exactly 64KB bigger
> - so one uniform extent has been added.
>
>
> Checking the trace file shows not a lot (hopefully the following will
> not have wrapped, if so, I can supply the raw and tkproff'd files)
>
> Cheers,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> =====================
> PARSING IN CURSOR #1 len=33 dep=0 uid=5 oct=15 lid=5 tim=3612218650
> hv=1384354826 ad='9c7de130'
> alter table test allocate extent
> END OF STMT
> PARSE #1:c=0,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=3612218650
> BINDS #1:
> =====================
> PARSING IN CURSOR #2 len=36 dep=1 uid=0 oct=3 lid=0 tim=3612218650
> hv=1705880752 ad='9ce752b0'
> select file# from file$ where ts#=:1
> END OF STMT
> PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3612218650
> BINDS #2:
> bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e5b30 bln=22 avl=02 flg=05
> value=10
> EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3612218650
> FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=3612218650
> FETCH #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3612218650
> STAT #2 id=1 cnt=1 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID
> FILE$ '
> STAT #2 id=2 cnt=2 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
> WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1
> WAIT #1: nam='control file sequential read' ela= 0 p1=1 p2=1 p3=1
> WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=15 p3=1
> WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=19 p3=1
> WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
> WAIT #1: nam='db file sequential read' ela= 0 p1=11 p2=1 p3=1
> WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=15 p3=1
> WAIT #1: nam='file identify' ela= 0 p1=0 p2=0 p3=0
> WAIT #1: nam='file identify' ela= 0 p1=0 p2=0 p3=0
> WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
> WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1
> WAIT #1: nam='control file sequential read' ela= 0 p1=1 p2=1 p3=1
> WAIT #1: nam='db file sequential read' ela= 0 p1=11 p2=1 p3=1
> WAIT #1: nam='control file sequential read' ela= 0 p1=0 p2=15 p3=1
> WAIT #1: nam='db file single write' ela= 0 p1=11 p2=1 p3=1
> WAIT #1: nam='rdbms ipc reply' ela= 0 p1=3 p2=21474836 p3=0
> =====================
> PARSING IN CURSOR #2 len=103 dep=1 uid=0 oct=6 lid=0 tim=3612218653
> hv=3936714171 ad='9cea64f0'
> update tsq$ set
> blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8 where
> ts#=:1 and user#=:2
> END OF STMT
> PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3612218653
> BINDS #2:
> bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e75e0 bln=24 avl=03 flg=05
> value=256
> bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e75b0 bln=24 avl=01 flg=05
> value=0
> bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eaba0 bln=24 avl=01 flg=05
> value=0
> bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eab78 bln=24 avl=01 flg=05
> value=0
> bind 4: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eab50 bln=24 avl=01 flg=05
> value=0
> bind 5: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eab20 bln=24 avl=01 flg=05
> value=0
> bind 6: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e7630 bln=22 avl=02 flg=05
> value=10
> bind 7: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e7608 bln=24 avl=02 flg=05
> value=5
> EXEC #2:c=0,e=0,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,tim=3612218654
> STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE TSQ$ '
> STAT #2 id=2 cnt=2 pid=1 pos=1 obj=57 op='TABLE ACCESS CLUSTER TSQ$ '
> STAT #2 id=3 cnt=2 pid=2 pos=1 obj=11 op='INDEX UNIQUE SCAN '
> XCTEND rlbk=0, rd_only=1
> =====================
> PARSING IN CURSOR #2 len=532 dep=1 uid=0 oct=6 lid=0 tim=3612218654
> hv=336053072 ad='9ce4f3b0'
> update tab$ set
> ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,nu
> ll,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,
> flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16
> ,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,
> samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),insta
> nces=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigf
> lag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare3=:35
> where obj#=:1
> END OF STMT
> PARSE #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3612218655
> BINDS #2:
> bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e5b18 bln=24 avl=02 flg=05
> value=10
> bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eb228 bln=24 avl=02 flg=05
> value=11
> bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eb200 bln=24 avl=02 flg=05
> value=9
> bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eb1d8 bln=24 avl=01 flg=05
> value=0
> bind 4: (No oacdef for this bind)
> bind 5: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eb1b0 bln=24 avl=01 flg=05
> value=0
> bind 6: (No oacdef for this bind)
> bind 7: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eb180 bln=24 avl=02 flg=05
> value=1
> bind 8: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e7630 bln=24 avl=02 flg=05
> value=1
> bind 9: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e7608 bln=24 avl=01 flg=05
> value=0
> bind 10: (No oacdef for this bind)
> bind 11: dty=1 mxl=32(32) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=32 offset=0
> bfp=400e75d8 bln=32 avl=32 flg=05
> value="--------------------------------"
> bind 12: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e75b0 bln=24 avl=02 flg=05
> value=1
> bind 13: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eaba0 bln=24 avl=02 flg=05
> value=10
> bind 14: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eab78 bln=24 avl=02 flg=05
> value=40
> bind 15: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eab50 bln=24 avl=02 flg=05
> value=1
> bind 16: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400eab20 bln=24 avl=03 flg=05
> value=255
> bind 17: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f0320 bln=24 avl=00 flg=05
> bind 18: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f02f8 bln=24 avl=00 flg=05
> bind 19: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f02d0 bln=24 avl=00 flg=05
> bind 20: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f02a8 bln=24 avl=00 flg=05
> bind 21: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f0280 bln=24 avl=00 flg=05
> bind 22: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f0258 bln=24 avl=00 flg=05
> bind 23: dty=12 mxl=07(00) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=8 offset=0
> bfp=400f0240 bln=08 avl=00 flg=05
> bind 24: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f0218 bln=24 avl=00 flg=05
> bind 25: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f1368 bln=24 avl=02 flg=05
> value=1
> bind 26: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f1340 bln=24 avl=01 flg=05
> value=0
> bind 27: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f1318 bln=24 avl=02 flg=05
> value=1
> bind 28: (No oacdef for this bind)
> bind 29: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f12f0 bln=24 avl=02 flg=05
> value=1
> bind 30: (No oacdef for this bind)
> bind 31: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f12c8 bln=24 avl=04 flg=05
> value=16796
> bind 32: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f13b8 bln=24 avl=00 flg=05
> bind 33: dty=2 mxl=22(00) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f1390 bln=24 avl=00 flg=05
> bind 34: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f1298 bln=24 avl=01 flg=05
> value=0
> bind 35: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f36d8 bln=24 avl=03 flg=05
> value=736
> bind 36: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f36b0 bln=24 avl=01 flg=05
> value=0
> bind 37: (No oacdef for this bind)
> bind 38: dty=1 mxl=32(00) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=32 offset=0
> bfp=400f3680 bln=32 avl=00 flg=05
> bind 39: dty=2 mxl=22(01) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=24 offset=0
> bfp=400f3658 bln=24 avl=01 flg=05
> value=0
> bind 40: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e5b48 bln=22 avl=04 flg=05
> value=16796
> EXEC #2:c=3,e=2,p=0,cr=5,cu=3,mis=0,r=1,dep=1,og=4,tim=3612218657
> STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE TAB$ '
> STAT #2 id=2 cnt=2 pid=1 pos=1 obj=4 op='TABLE ACCESS CLUSTER TAB$ '
> STAT #2 id=3 cnt=2 pid=2 pos=1 obj=3 op='INDEX UNIQUE SCAN '
> =====================
> PARSING IN CURSOR #2 len=319 dep=1 uid=0 oct=6 lid=0 tim=3612218662
> hv=114083071 ad='9ce9f268'
> update obj$ set
> obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,fla
> gs=:14,oid$=:15,spare1=:16 where owner#=:1 and name=:2 and namespace=:3
> and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5
> or linkname is null and :5 is null)and(subname=:12 or subname is null
> and :12 is null)
> END OF STMT
> PARSE #2:c=1,e=5,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=3612218662
> BINDS #2:
> bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f3528 bln=24 avl=04 flg=05
> value=16796
> bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f3500 bln=24 avl=02 flg=05
> value=2
> bind 2: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=8 offset=0
> bfp=400e75b0 bln=08 avl=07 flg=05
> value="6/13/2002 11:32:54"
> bind 3: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=8 offset=0
> bfp=400f01f8 bln=08 avl=07 flg=05
> value="6/13/2002 11:37:44"
> bind 4: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=8 offset=0
> bfp=400f34e8 bln=08 avl=07 flg=05
> value="6/13/2002 11:32:54"
> bind 5: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f34c0 bln=24 avl=02 flg=05
> value=1
> bind 6: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f3468 bln=24 avl=04 flg=05
> value=16796
> bind 7: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f3440 bln=24 avl=01 flg=05
> value=0
> bind 8: dty=23 mxl=32(00) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=32 offset=0
> bfp=400f3410 bln=32 avl=00 flg=05
> bind 9: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400f13b8 bln=24 avl=02 flg=05
> value=6
> bind 10: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e5b48 bln=22 avl=02 flg=05
> value=5
> bind 11: dty=1 mxl=32(04) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=32 offset=0
> bfp=400e5b18 bln=32 avl=04 flg=05
> value="TEST"
> bind 12: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1
> size=24 offset=0
> bfp=400e7630 bln=24 avl=02 flg=05
> value=1
> bind 13: dty=1 mxl=32(00) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=32 offset=0
> bfp=400e7600 bln=32 avl=00 flg=05
> bind 14: (No oacdef for this bind)
> bind 15: dty=1 mxl=32(00) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=32 offset=0
> bfp=400e75d0 bln=32 avl=00 flg=05
> bind 16: (No oacdef for this bind)
> bind 17: dty=1 mxl=32(00) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1
> size=32 offset=0
> bfp=400f3490 bln=32 avl=00 flg=05
> bind 18: (No oacdef for this bind)
> EXEC #2:c=1,e=1,p=0,cr=2,cu=1,mis=0,r=1,dep=1,og=4,tim=3612218663
> STAT #2 id=1 cnt=1 pid=0 pos=0 obj=0 op='UPDATE OBJ$ '
> STAT #2 id=2 cnt=2 pid=1 pos=1 obj=34 op='INDEX RANGE SCAN '
> EXEC #1:c=8,e=13,p=0,cr=15,cu=26,mis=0,r=0,dep=0,og=4,tim=3612218663
> WAIT #1: nam='log file sync' ela= 0 p1=295 p2=0 p3=0
> WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> WAIT #1: nam='SQL*Net message from client' ela= 872 p1=1413697536 p2=1
> p3=0
> =====================
>
>
> Which tkprof's down to this :
>
> ************************************************************************
> ********
>
> alter table test allocate extent
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.02 0 0 0
> 0
> Execute 1 0.03 0.04 0 2 21
> 0
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 0.03 0.06 0 2 21
> 0
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 5
> ************************************************************************
> ********
>
> select file#
> from
> file$ where ts#=:1
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 2 0.00 0.00 0 2 0
> 1
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4 0.00 0.00 0 2 0
> 1
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: SYS (recursive depth: 1)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 TABLE ACCESS BY INDEX ROWID FILE$
> 2 INDEX RANGE SCAN (object id 39)
>
> ************************************************************************
> ********
>
> update tsq$ set
> blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8
> where
> ts#=:1 and user#=:2
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 4 1
> 1
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 0.00 0.00 0 4 1
> 1
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: SYS (recursive depth: 1)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 UPDATE TSQ$
> 2 TABLE ACCESS CLUSTER TSQ$
> 2 INDEX UNIQUE SCAN (object id 11)
>
> ************************************************************************
> ********
>
> update tab$ set
> ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=
>
> decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:
> 9),
>
> audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15
> ,
> rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,
>
> analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1
> ,
>
> null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,
>
> flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=
> :34,
> spare3=:35
> where
> obj#=:1
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.01 0 0 0
> 0
> Execute 1 0.03 0.02 0 5 3
> 1
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 0.03 0.03 0 5 3
> 1
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: SYS (recursive depth: 1)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 UPDATE TAB$
> 2 TABLE ACCESS CLUSTER TAB$
> 2 INDEX UNIQUE SCAN (object id 3)
>
> ************************************************************************
> ********
>
> update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,
> dataobj#=:13,flags=:14,oid$=:15,spare1=:16
> where
> owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or
> remoteowner is
> null and :4 is null)and(linkname=:5 or linkname is null and :5 is
> null)
> and(subname=:12 or subname is null and :12 is null)
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.01 0.05 0 0 0
> 0
> Execute 1 0.01 0.01 0 2 1
> 1
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 0.02 0.06 0 2 1
> 1
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: SYS (recursive depth: 1)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 UPDATE OBJ$
> 2 INDEX RANGE SCAN (object id 34)
>
> ************************************************************************
> ********
>
Received on Mon Jun 17 2002 - 03:21:57 CDT