How to count (*) big table in oracle ?
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
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
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