« 百度 城管 与 GFW | Blog首页 | 数据字典表之：DBA_TABLES »
并行查询并行度Degree与instances 设置
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2008/04/parallel_degree_instances.html
在DBA_TABLES字典表中有一个degree字段，这个字段代表并行查询在数据表上的并行度，在RAC环境中，这个参数还和实例有关。
以下生活文档中对于 DEGREE 和 INSTANCES 参数的说明：
DEGREE VARCHAR2(10) Number of threads per instance for scanning the table
INSTANCES VARCHAR2(10) Number of instances across which the table is to be scanned
但是注意，当你使用类似如下查询时，你可能无法获得返回值：
SQL> select table_name from dba_tables where degree='1' or degree='DEFAULT';
no rows selected
我们看一下Degree以及instances的记录方式：
SQL> select degree,length(degree) from dba_tablesDegree和Instances实际上记录了10个字符，左端用空格补齐。
2 group by degree;
DEGREE LENGTH(DEGREE)
-------------------- --------------
DEFAULT 10
1 10
SQL>select instances,length(instances) from dba_tables
2 group by instances;
INSTANCES LENGTH(INSTANCES)
-------------------- -----------------
DEFAULT 10
1 10
0 10
在 dba_tables 的创建语句中，我们可以找到根本原因，以下是这两个字段的定义来源：
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
以上信息来自Oracle10gR2数据库：
SQL> select table_name,owner from dba_tables where degree=' DEFAULT' or instances=' DEFAULT';
TABLE_NAME OWNER
------------------------------ ------------------------------
TEST_EXT2 SYS
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
以下收录dba_tables视图的创建语句：
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-
历史上的今天...
>> 2006-04-09文章:
------
这篇 【并行查询并行度Degree与instances 设置】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key
By eygle on 2008-04-09 10:20 | Comments (0) | Posted to Internal | Edit |Pageviews:
搜索本站:
