Checking parallel query execution in Oracle

To check the parameters configuration in the init.ora parameter file:

SQL> show parameter parallel

To check for tables and indexes created with the parallel option:

FOR TABLES:

select owner,table_name,degree,instances from dba_tables where
(trim (degree)not in ( ’1′,’0′) or trim(instances) not in ( ’1′,’0′) )
and owner not in (‘SYSTEM’,’SYS’,’OUTLN’,’DBSNMP’,’OPS$ORACLE’)
/

FOR INDEXES:

select owner,index_name,degree,instances from dba_indexes
where (trim (degree)not in ( ’1′,’0′) or trim(instances) not in ( ’1′,’0′) )
and owner not in (‘SYSTEM’,’SYS’,’OUTLN’,’DBSNMP’,’OPS$ORACLE’)
/

And to check for parallel operations:

select name, value from v$sysstat where name like ‘Parallel%’

Please note that if you are running on a RAC configuration, then you should check the parameters and the parallel operations SQLs on all the nodes since those are at the instance level. The table/indexes for that schema need to be checked only once since those are shared across the instances.

 

Post Tagged with

Leave a Reply