oracle查询表的并行度,并行查询并行度Degree与instances 设置

CREATE OR REPLACE VIEW dba_tables (owner,

table_name,

tablespace_name,

cluster_name,

iot_name,

status,

pct_free,

pct_used,

ini_trans,

max_trans,

initial_extent,

next_extent,

min_extents,

max_extents,

pct_increase,

FREELISTS,

freelist_groups,

LOGGING,

backed_up,

num_rows,

blocks,

empty_blocks,

avg_space,

chain_cnt,

avg_row_len,

avg_space_freelist_blocks,

num_freelist_blocks,

DEGREE,

INSTANCES,

CACHE,

table_lock,

sample_size,

last_analyzed,

partitioned,

iot_type,

TEMPORARY,

secondary,

NESTED,

BUFFER_POOL,

row_movement,

global_stats,

user_stats,

DURATION,

skip_corrupt,

MONITORING,

cluster_owner,

dependencies,

compression,

dropped

)

AS

SELECT u.NAME, o.NAME,

DECODE (BITAND (t.property, 2151678048), 0, ts.NAME, NULL),

DECODE (BITAND (t.property, 1024), 0, NULL, co.NAME),

DECODE ((BITAND (t.property, 512) + BITAND (t.flags, 536870912)),

0, NULL,

co.NAME

),

DECODE (BITAND (t.trigflag, 1073741824),

1073741824, 'UNUSABLE',

'VALID'

),

DECODE (BITAND (t.property, 32 + 64),

0, MOD (t.pctfree$, 100),

64, 0,

NULL

),

DECODE (BITAND (ts.flags, 32),

32, TO_NUMBER (NULL),

DECODE (BITAND (t.property, 32 + 64),

0, t.pctused$,

64, 0,

NULL

)

),

DECODE (BITAND (t.property, 32), 0, t.INITRANS, NULL),

DECODE (BITAND (t.property, 32), 0, t.MAXTRANS, NULL),

s.iniexts * ts.BLOCKSIZE,

DECODE (BITAND (ts.flags, 3),

1, TO_NUMBER (NULL),

s.extsize * ts.BLOCKSIZE

),

s.minexts, s.maxexts,

DECODE (BITAND (ts.flags, 3), 1, TO_NUMBER (NULL), s.extpct),

DECODE (BITAND (ts.flags, 32),

32, TO_NUMBER (NULL),

DECODE (BITAND (o.flags, 2),

2, 1,

DECODE (s.lists, 0, 1, s.lists)

)

),

DECODE (BITAND (ts.flags, 32),

32, TO_NUMBER (NULL),

DECODE (BITAND (o.flags, 2),

2, 1,

DECODE (s.GROUPS, 0, 1, s.GROUPS)

)

),

DECODE (BITAND (t.property, 32 + 64),

0, DECODE (BITAND (t.flags, 32), 0, 'YES', 'NO'),

NULL

),

DECODE (BITAND (t.flags, 1), 0, 'Y', 1, 'N', '?'), t.rowcnt,

DECODE (BITAND (t.property, 64), 0, t.blkcnt, NULL),

DECODE (BITAND (t.property, 64), 0, t.empcnt, NULL), t.avgspc,

t.chncnt, t.avgrln, t.avgspc_flb,

DECODE (BITAND (t.property, 64), 0, t.flbcnt, NULL),

LPAD (DECODE (t.DEGREE, 32767, 'DEFAULT', NVL (t.DEGREE, 1)), 10),

LPAD (DECODE (t.INSTANCES, 32767, 'DEFAULT', NVL (t.INSTANCES, 1)),

10

),

LPAD (DECODE (BITAND (t.flags, 8), 8, 'Y', 'N'), 5),

DECODE (BITAND (t.flags, 6), 0, 'ENABLED', 'DISABLED'),

t.samplesize, t.analyzetime,

DECODE (BITAND (t.property, 32), 32, 'YES', 'NO'),

DECODE (BITAND (t.property, 64),

64, 'IOT',

DECODE (BITAND (t.property, 512),

512, 'IOT_OVERFLOW',

DECODE (BITAND (t.flags, 536870912),

536870912, 'IOT_MAPPING',

NULL

)

)

),

DECODE (BITAND (o.flags, 2), 0, 'N', 2, 'Y', 'N'),

DECODE (BITAND (o.flags, 16), 0, 'N', 16, 'Y', 'N'),

DECODE (BITAND (t.property, 8192),

8192, 'YES',

DECODE (BITAND (t.property, 1), 0, 'NO', 'YES')

),

DECODE (BITAND (o.flags, 2),

2, 'DEFAULT',

DECODE (s.cachehint,

0, 'DEFAULT',

1, 'KEEP',

2, 'RECYCLE',

NULL

)

),

DECODE (BITAND (t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),

DECODE (BITAND (t.flags, 512), 0, 'NO', 'YES'),

DECODE (BITAND (t.flags, 256), 0, 'NO', 'YES'),

DECODE (BITAND (o.flags, 2),

0, NULL,

DECODE (BITAND (t.property, 8388608),

8388608, 'SYS$SESSION',

'SYS$TRANSACTION'

)

),

DECODE (BITAND (t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),

DECODE (BITAND (o.flags, 2),

2, 'NO',

DECODE (BITAND (t.property, 2147483648),

2147483648, 'NO',

DECODE (ksppcv.ksppstvl, 'TRUE', 'YES', 'NO')

)

),

DECODE (BITAND (t.property, 1024), 0, NULL, cu.NAME),

DECODE (BITAND (t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),

DECODE (BITAND (t.property, 32),

32, NULL,

DECODE (BITAND (s.spare1, 2048),

2048, 'ENABLED',

'DISABLED'

)

),

DECODE (BITAND (o.flags, 128), 128, 'YES', 'NO')

FROM SYS.user$ u,

SYS.ts$ ts,

SYS.seg$ s,

SYS.obj$ co,

SYS.tab$ t,

SYS.obj$ o,

SYS.obj$ cx,

SYS.user$ cu,

x$ksppcv ksppcv,

x$ksppi ksppi

WHERE o.owner# = u.user#

AND o.obj# = t.obj#

AND BITAND (t.property, 1) = 0

AND BITAND (o.flags, 128) = 0

AND t.bobj# = co.obj#(+)

AND t.ts# = ts.ts#

AND t.file# = s.file#(+)

AND t.block# = s.block#(+)

AND t.ts# = s.ts#(+)

AND t.dataobj# = cx.obj#(+)

AND cx.owner# = cu.user#(+)

AND ksppi.indx = ksppcv.indx

AND ksppi.ksppinm = '_dml_monitoring_enabled'

-The End-

历史上的今天...

>>

2016-04-09文章:

2013-04-09文章:

2012-04-09文章:

2009-04-09文章:

2006-04-09文章: