Yes there are many executions on this two query: 2ym6hhaq30r73 - around 11 millions executions per hour SELECT type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL (lists, 65535), NVL (GROUPS, 65535), cachehint, hwmincr, NVL (spare1, 0), NVL (scanhint, 0) FROM seg$ WHERE ts# = :1 AND file# = :2 AND block# = :3 And top 4 segment logical reads: Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM I_FILE#_BLOCK# INDEX 34,485,856 19.60 SYS SYSTEM I_OBJ2 INDEX 17,154,800 9.75 SYS SYSTEM SEG$ TABLE 16,886,000 9.60 SYS SYSTEM I_OBJ1 INDEX 12,866,416 7.31 Thanks! -- LSC On Mon, Dec 21, 2009 at 10:17 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote: > On Mon, Dec 21, 2009 at 11:34 AM, Randolf Geist > <info@xxxxxxxxxxxxxxxxxxxxx> wrote: > > Still it is interesting that using the explicit partition pruning syntax > changes the outcome of your test case. > > Using partition extended syntax reduces the locks and metadata > required, so the issue seems to point in that direction, not extent > allocation, etc. If it were the latter, it would reproduce in both > cases. > > There should be some dictionary query on a $ table (like SEG$, TSQ$, > etc) that shows up, I would think, in the ASH/AWR report. Finding > that SQL might make it more apparent what the issue may be. > > -- > Regards, > Greg Rahn > http://structureddata.org >