本文轉自作者：

在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 group by degree;

DEGREE LENGTH(DEGREE)
-------------------- --------------
DEFAULT 10
1 10

SQL>select instances,length(instances) from dba_tables 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'

並行查詢並行度Degree與instances 設定

相關文章