Internal 2008-04-09

并行查询并行度Degree与instances 设置

eygle 2008-04-09 10:20

在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_tables
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

Degree和Instances实际上记录了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-

历史上的今天

2016-04-09 回顾学习展望：读书会分享我的Oracle数据库历程
2013-04-09 Oracle 2012年数据库市场份额48.3 - 再居第一
2012-04-09 数据安全防范提升需从今日始 - 浅析数据安全
2009-04-09 使用DATAPUMP导致ORA-00600 17020错误
2009-04-09 Oracle如何维护SMON_SCN_TIME表？
2006-04-09 What's Mean "internal dummy connection"?
2006-04-09 关于自动PGA管理的进一步探讨
2006-04-09 经典重现

By eygle 2008-04-09 10:20 评论 (0)

eygle

前Oracle ACE Director，云和恩墨创始人。技术为骨，历史为脉，人文为魂。

返回首页 →