我另一个问题是oracle是怎么把大的shared pool请求break into small chunks的,因为我用
SQL> set serveroutput on size 100000
SQL> execute SYS.DBMS_SHARED_POOL.SIZES(100)
SIZE(K) KEPT NAME
------- ------ ---------------------------------------------------------------
3777SELECT * FROM "SYS"."_ALL_REPRESOLUTION"
(C00000020D8356C0,72834749(CURSOR)
3774.SELECT * FROM "SYS"."_ALL_REPRESOLUTION"(CURSOR)
.............................
看到SELECT * FROM "SYS"."_ALL_REPRESOLUTION"(CURSOR)居然占了3M多,通过
ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 4';
看到
$ grep -i ALL_REPRESOLUTION odsdb_ora_8028.trc
SELECT "A1"."SNAME","A1"."ONAME","A1"."CONFLICT_TYPE","A1"."REFERENCE_NAME","A1"."SEQUENCE_NO","A1"."METHOD_NAME","A1"."FUNCTION_NAME","A1"."PRIORITY_GROUP","A1"."RESOLUTION_COMMENT" FROM "SYS"."_ALL_REPRESOLUTION" "A1" WHERE "A1"."SNAME"=:SNAME AND "A1"."ONAME"=:ONAME
name=SYS._ALL_REPRESOLUTION
name=SYS.ALL_REPRESOLUTION
name=SELECT * FROM "SYS"."_ALL_REPRESOLUTION"
而SELECT * FROM "SYS"."_ALL_REPRESOLUTION"的内容在trace也只看到如下信息,
BUCKET 111467:
LIBRARY OBJECT HANDLE: handle=c00000020d8356c0
name=SELECT * FROM "SYS"."_ALL_REPRESOLUTION"
hash=2b69b36b timestamp=06-10-2006 02:06:42
namespace=CRSR flags=RON/TIM/PN0/SML/[12010000]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=2
lwt=c00000020d8356f0[c00000020d8356f0,c00000020d8356f0] ltm=c00000020d835700[c00000020d835700,c00000020d83570
0]
pwt=c00000020d835720[c00000020d835720,c00000020d835720] ptm=c00000020d8357b0[c00000020d8357b0,c00000020d8357b
0]
ref=c00000020d8356d0[c00000020d8356d0, c00000020d8356d0] lnd=c00000020d8357c8[c00000020d8357c8,c00000020d8357
c8]
LIBRARY OBJECT: object=c000000219cf3838
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child#table reference handle
------ -------- --------- --------
0 c000000211e87850 c000000215ab7340 c00000020d835570
LIBRARY OBJECT HANDLE: handle=c00000020dd80840
name=/* OracleOEM */ ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LOCK_TIME UNLIMIT
ED PASSWORD_GRACE_TIME UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_VERIFY_FUN
CTION NULL
hash=5d2db36b timestamp=04-27-2006 08:42:10
namespace=CRSR flags=RON/TIM/PN0/MED/[50010000]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=2
lwt=c00000020dd80870[c00000020dd80870,c00000020dd80870] ltm=c00000020dd80880[c00000020dd80880,c00000020dd8088
0]
pwt=c00000020dd808a0[c00000020dd808a0,c00000020dd808a0] ptm=c00000020dd80930[c00000020dd80930,c00000020dd8093
0]
ref=c00000020dd80850[c00000020dd80850, c00000020dd80850] lnd=c00000020dd80948[c00000020dd80948,c00000020dd809
48]
LIBRARY OBJECT: object=c000000211e9d4c8
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child#table reference handle
------ -------- --------- --------
0 c000000211e3a9e8 c000000211e3ad20 c0000002093208d0
BUCKET 111467 total object count=2
实在看不出oracle是否把大的请求给分成小块请求,也看不出为啥这个cursor这么大?
|