if 2 cpu then we can put no of parallel process 2 X 4 = 8 select /*+ parallel (ttable, 8) */ count(*) from &table_name ttable ; select /*+ parallel (ttable, 8) */ count(*) from anuj.user_site ttable ; COUNT(*) ---------- 280973975 Elapsed: 00:02:10.96
set linesize 200 time on timing on define table_name='XXX.XXX' ----- owner.table col human_readable for a15 select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count, dbms_xplan.format_number(count(*)) human_readable from &table_name ttable ;col human_readable for a15 col TABLE_NAME for a25 select TABLE_NAME,NUM_ROWS,dbms_xplan.format_number(NUM_ROWS) human_readable,LAST_ANALYZED from dba_tables where TABLE_NAME='&T_NAME'define d='.' define o='SYS' define t='EMP' set linesize 200 time on timing on define table_name='SYS.EMP' ----- owner.table col human_readable for a15 col PARTITIONED for a15 select * from (select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count, dbms_xplan.format_number(count(*)) human_readable from &o&d&t ttable) , (select LAST_ANALYZED,NUM_ROWS,PARTITIONED from dba_tables where table_name='&t' and owner='&o' )b ;set linesize 200 time on timing on define table_name='ANUJ.LOG' ----- owner.table col human_readable for a15 col min_TIMESTAMP for a28 col max_TIMESTAMP for a28 select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count ,dbms_xplan.format_number(count(*)) human_readable ,min(TIMESTAMP) min_TIMESTAMP , max(TIMESTAMP) max_TIMESTAMP from &table_name ttable ;set linesize 200 pagesize 300 time on timing on define table_name='ANUJ.LOG' ----- owner.table col human_readable for a15 col min_TIMESTAMP for a28 col max_TIMESTAMP for a28 col dday for 9999 col days for 999999 prompt ***************** &table_name ******************select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count ,dbms_xplan.format_number(count(*)) human_readable ,min(trunc(TIMESTAMP)) min_TIMESTAMP , max(trunc(TIMESTAMP)) max_TIMESTAMP --,max(TIMESTAMP)- min(TIMESTAMP) diff , max(trunc(TIMESTAMP)) - min (trunc(TIMESTAMP)) days from &table_name ttable ; prompt ***************** &table_name ******************set linesize 200 time on timing on define table_name='ANUJ.LOG' ----- owner.table col human_readable for a15 col min_TIMESTAMP for a28 col max_TIMESTAMP for a28 def 1="TIMESTAMP'2025-10-08 00:00:00'" def 2="TIMESTAMP'2025-10-09 00:00:00'" select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count ,dbms_xplan.format_number(count(*)) human_readable ,min(TIMESTAMP) min_TIMESTAMP , max(TIMESTAMP) max_TIMESTAMP from &table_name ttable WHERE 1=1 and TIMESTAMP between &1 and &2 ;set linesize 200 time on timing on define table_name='ANUJ.LOG' ----- owner.table col human_readable for a15 col min_TIMESTAMP for a28 col max_TIMESTAMP for a28 def 1="TIMESTAMP'2025-10-09 00:00:00'" def 2="TIMESTAMP'2025-10-09 12:00:00'" select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count ,dbms_xplan.format_number(count(*)) human_readable ,min(TIMESTAMP) min_TIMESTAMP , max(TIMESTAMP) max_TIMESTAMP from &table_name ttable WHERE 1=1 and TIMESTAMP between &1 and &2 ;WHERE 1=1 and TIMESTAMP between to_timestamp('09-OCT-25 12.00.00.271000000 PM','DD-MON-RR HH.MI.SS.FF PM') and to_timestamp('09-OCT-25 11.59.00.271000000 PM','DD-MON-RR HH.MI.SS.FF PM') / WHERE 1=1 and TIMESTAMP between to_timestamp('09-OCT-25 01.00.00.271000000 AM','DD-MON-RR HH.MI.SS.FF AM') and to_timestamp('09-OCT-25 12.00.00.271000000 PM','DD-MON-RR HH.MI.SS.FF PM') /====set time on timing on def 1="TIMESTAMP'2025-10-06 00:00:00'" def 2="TIMESTAMP'2025-10-07 00:00:00'" DELETE /*+ ENABLE_PARALLEL_DML PARALLEL(8) */ FROM anuj.LOG WHERE 1=1 and TIMESTAMP between &1 and &2 ;set linesize 200 time on timing on define table_name='ANUJ.LOG' ----- owner.table col human_readable for a15 col min_TIMESTAMP for a28 col max_TIMESTAMP for a28 col day for 6 select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count ,dbms_xplan.format_number(count(*)) human_readable ,min(TIMESTAMP) min_TIMESTAMP , max(TIMESTAMP) max_TIMESTAMP , EXTRACT(DAY from (max(TIMESTAMP) - min(TIMESTAMP)) ) day from &table_name ttable ;set linesize 200 pagesize 300 time on timing on define table_name='XXX.XXX' ----- owner.table col human_readable for a15 col min_TIMESTAMP for a28 col max_TIMESTAMP for a28 col dday for 9999 col days for 999999 select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count ,dbms_xplan.format_number(count(*)) human_readable ,min(trunc(TIMESTAMP)) min_TIMESTAMP , max(trunc(TIMESTAMP)) max_TIMESTAMP --,max(TIMESTAMP)- min(TIMESTAMP) diff , max(trunc(TIMESTAMP)) - min (trunc(TIMESTAMP)) days from &table_name ttable ;
cpu info
col STAT_NAME for a30
col VALUE for a15
SELECT STAT_NAME, TO_CHAR (VALUE) AS VALUE, COMMENTS FROM v$osstat
WHERE stat_name IN ('NUM_CPUS', 'NUM_CPU_CORES', 'NUM_CPU_SOCKETS')
UNION
SELECT STAT_NAME, trunc(VALUE / 1024 / 1024 / 1024) || ' GB', COMMENTS FROM v$osstat
WHERE stat_name IN ('PHYSICAL_MEMORY_BYTES');
====
col param_name format a40 heading "Parameter Name"
col param_value format a38 heading "Parameter Value" word_wrap
col isdefault format a7 heading "Default|Value"
col isses_modifiable format a10 heading "Session|Modifiable"
col issys_modifiable format a10 heading "System|Modifiable"
col ismod heading "Is|Modified"
set linesize 500
col DISPLAY_VALUE for a20
col DEFAULT_VALUE for a20
col VALUE for a20
col UPDATE_COMMENT for a20
col DESCRIPTION for a20
col NAME for a35
select *
from v$parameter
where name in ('cpu_count', 'parallel_threads_per_cpu', 'parallel_degree_policy', 'parallel_degree_limit');
Default Session System
NUM NAME TYPE VALUE DISPLAY_VALUE DEFAULT_VALUE Value Modifiable Modifiable ISPDB ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION UPDATE_COMMENT HASH CON_ID
---------- ----------------------------------- ---------- -------------------- -------------------- -------------------- ------- ---------- ---------- ----- ----- ---------- ----- ----- ----- -------------------- -------------------- ---------- ----------
111 cpu_count 3 16 16 0 TRUE FALSE IMMEDIATE TRUE TRUE FALSE FALSE FALSE FALSE number of CPUs for t 1095434542 3
his instance
3392 parallel_degree_policy 2 AUTO AUTO MANUAL FALSE TRUE IMMEDIATE TRUE TRUE FALSE FALSE FALSE FALSE policy used to compu 1979359697 3
te the degree of par
allelism (MANUAL/LIM
ITED/AUTO/ADAPTIVE)
3403 parallel_threads_per_cpu 3 2 2 2 TRUE FALSE IMMEDIATE FALSE TRUE FALSE FALSE FALSE FALSE number of parallel e 1020981983 3
xecution threads per
CPU
3776 parallel_degree_limit 2 CPU CPU CPU TRUE TRUE IMMEDIATE TRUE TRUE FALSE FALSE FALSE FALSE limit placed on degr 3004872640 3
ee of parallelism
===
Parallel_index Hint Is Not Honored (Doc ID 2294720.1)
he index is not partitioned.
So, you cannot use the parallel_index hint.
3 comments:
SYS@PRD> !cat /proc/cpuinfo|grep processor|wc -l
24
lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 2
NUMA node(s): 2
for partition count
select 'select count(*) from ' ||OWNER||'.'||TABLE_NAME|| ' partition ('||PARTITION_NAME||');' from dba_tab_statistics where OWNER='&Owner' and table_name='&Table_Name';
Post a Comment