Oracle Database Discussions

DB version: 11.2.0.4

Platform : Oracle Linux 6.5

In my DB, a gather stats job with parallelism 5 like below is running fine. From OEM, I confirmed that the below gather stats job is running with 5 slave processes.

exec dbms_stats.gather_table_stats(  -

       ownname => 'PTMS', -

       tabname => 'ORDER_DETAIL', -

       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -

       cascade => TRUE,  -

       method_opt => 'FOR ALL COLUMNS SIZE AUTO', -

       degree => 5);

order_detail is a table with 400 million records. I wanted to get a row count of this table. So, I tried to use parallelism as shown below.

But, the slave processes were not created. The SELECT query was running only with parallelism 1. I cofirmed this from real time data from OEM and gv$px_session view.

SQL> show parameter parallel_max_servers

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

parallel_max_servers                 integer     3200

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 5;

Session altered.

SQL> ALTER SESSION enable parallel query;

Session altered.

SQL> select /*+ PARALLEL(5) */ count(*) from ptms.order_detail;

--- Also tried

select /*+ PARALLEL(ptms.order_detail,5) */ count(*) from ptms.order_detail;

--- Also tried 

ALTER SESSION ENABLE PARALLEL DML;

--- The below setting shouldn't be the cause. Right ? ALTER SESSION command should take precedence over this setting. Right ?

SQL> select table_name, DEGREE from dba_tables where owner='PTMS' and table_name= 'ORDER_DETAIL';

TABLE_NAME                         DEGREE

------------------------------  ----------------------------------------

ORDER_DETAIL                          1