本文主要是介绍分区表建partition index 和global index 中parallel 的影响，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
select /*+ parallel (8)*/ distinct COL from XX
COL
1
2
3
4
5
6
--- 6个分区有数据
with ps as
(select inst_id, sid, serial#, qcsid, qcserial#
from GV$px_session
where qcserial# is not null)
Select PS.QCSID as parent_session_sid, PS.QCSERIAL# as parent_session_serial, PS.inst_id as parent_session_instid, s.INST_ID, rawtohex(s.SADDR) as SADDR, s.SID, s.SERIAL#, s.AUDSID, rawtohex(s.PADDR) as PADDR, s.USER#, s.USERNAME, s.COMMAND, s.OWNERID, s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.SCHEMA#, s.SCHEMANAME, s.OSUSER, s.PROCESS, s.MACHINE, s.PORT, s.TERMINAL, UPPER(s.PROGRAM) PROGRAM, s.TYPE, s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.SQL_EXEC_START, s.SQL_EXEC_ID, s.PREV_SQL_ADDR, s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PREV_EXEC_START, s.PREV_EXEC_ID, s.PLSQL_ENTRY_OBJECT_ID, s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE, s.MODULE_HASH, substr(s.ACTION, 1, 64) as ACTION, s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#, s.TOP_LEVEL_CALL#, s.LOGON_TIME, s.LAST_CALL_ET, s.PDML_ENABLED, s.FAILOVER_TYPE, s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS, s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION, s.CLIENT_IDENTIFIER, s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.FINAL_BLOCKING_SESSION_STATUS, s.FINAL_BLOCKING_INSTANCE, s.FINAL_BLOCKING_SESSION, s.SEQ#, s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW, s.P3TEXT, s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME, s.SECONDS_IN_WAIT, s.STATE, s.WAIT_TIME_MICRO, s.TIME_REMAINING_MICRO, s.TIME_SINCE_LAST_WAIT_MICRO, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS, s.SQL_TRACE_BINDS, s.SQL_TRACE_PLAN_STATS, s.SESSION_EDITION_ID, s.CREATOR_ADDR, s.CREATOR_SERIAL#, s.ECID, s.SQL_TRANSLATION_PROFILE_ID, s.PGA_TUNABLE_MEM, s.SHARD_DDL_STATUS, s.CON_ID, s.EXTERNAL_NAME, s.PLSQL_DEBUGGER_CONNECTED
FROM GV$SESSION S, PS
WHERE ( (s.USERNAME is not null) /*and (NVL(s.osuser,'x') <> 'SYSTEM') */ and (s.type <> 'BACKGROUND') )
and PS.SID (+) = S.SID AND PS.SERIAL# (+) = S.SERIAL# and PS.INST_ID (+) = S.INST_ID
and s.STATUS='ACTIVE'
order by PROGRAM, OWNERID
create index on local parallel 16
----local index 的有sql_id的进程 只有6个，因为只有6个分区有数据
3574379718 651wn7bahtd66
3574379718 651wn7bahtd66
3574379718 651wn7bahtd66
3574379718 651wn7bahtd66
3574379718 651wn7bahtd66
3574379718 651wn7bahtd66
0
0
0
0
0
0
0
0
0
0
session P1:----这个分区数据多
100 Table Scan: 52706673 out of 52706673 Blocks done 28/04/2024 2:41:57 AM 952 0 0 1
35.53 Sort Output: : 1926101 out of 5420360 Blocks done 28/04/2024 2:57:51 AM 378 0 686 1
Table scan的 blocks 不准，大了52706673>5420360
session P2:这个分区数据少
100 Sort Output: : 1763487 out of 1763487 Blocks done 28/04/2024 2:47:39 AM 264 0 0 1
----local index [3:29:54 AM] Done in 1491.538 seconds ----时间差很大
----global index [3:09:54 AM] Done in 491.538 seconds
with ps as
(select inst_id, sid, serial#, qcsid, qcserial#
from GV$px_session
where qcserial# is not null)
Select PS.QCSID as parent_session_sid, PS.QCSERIAL# as parent_session_serial, PS.inst_id as parent_session_instid, s.INST_ID, rawtohex(s.SADDR) as SADDR, s.SID, s.SERIAL#, s.AUDSID, rawtohex(s.PADDR) as PADDR, s.USER#, s.USERNAME, s.COMMAND, s.OWNERID, s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.SCHEMA#, s.SCHEMANAME, s.OSUSER, s.PROCESS, s.MACHINE, s.PORT, s.TERMINAL, UPPER(s.PROGRAM) PROGRAM, s.TYPE, s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.SQL_EXEC_START, s.SQL_EXEC_ID, s.PREV_SQL_ADDR, s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PREV_EXEC_START, s.PREV_EXEC_ID, s.PLSQL_ENTRY_OBJECT_ID, s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE, s.MODULE_HASH, substr(s.ACTION, 1, 64) as ACTION, s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#, s.TOP_LEVEL_CALL#, s.LOGON_TIME, s.LAST_CALL_ET, s.PDML_ENABLED, s.FAILOVER_TYPE, s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS, s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION, s.CLIENT_IDENTIFIER, s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.FINAL_BLOCKING_SESSION_STATUS, s.FINAL_BLOCKING_INSTANCE, s.FINAL_BLOCKING_SESSION, s.SEQ#, s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW, s.P3TEXT, s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME, s.SECONDS_IN_WAIT, s.STATE, s.WAIT_TIME_MICRO, s.TIME_REMAINING_MICRO, s.TIME_SINCE_LAST_WAIT_MICRO, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS, s.SQL_TRACE_BINDS, s.SQL_TRACE_PLAN_STATS, s.SESSION_EDITION_ID, s.CREATOR_ADDR, s.CREATOR_SERIAL#, s.ECID, s.SQL_TRANSLATION_PROFILE_ID, s.PGA_TUNABLE_MEM, s.SHARD_DDL_STATUS, s.CON_ID, s.EXTERNAL_NAME, s.PLSQL_DEBUGGER_CONNECTED
FROM GV$SESSION S, PS
WHERE ( (s.USERNAME is not null) /*and (NVL(s.osuser,'x') <> 'SYSTEM') */ and (s.type <> 'BACKGROUND') )
and PS.SID (+) = S.SID AND PS.SERIAL# (+) = S.SERIAL# and PS.INST_ID (+) = S.INST_ID
and s.STATUS='ACTIVE'
and PS.QCSID=1121
create index on parallel 16---global index
启动了32个，16个scan 16 个sort！！！
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
0000000558BEFCB8 1403667790
32 session 16 for scan and 16 for sort!!!
-----------------------------partition index 加快并行
create index on local unusable;---------------unusable;
select 'alter index rebuild partition '|| di.partition_name||' parallel 16;' from dba_ind_partitions di where di.index_name=' ‘
这篇关于分区表建partition index 和global index 中parallel 的影响的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
