Systems Engineering and RDBMS

Archive for February 18th, 2007

Checking parallel query executions in Oracle

Posted by decipherinfosys on February 18, 2007

In a previous blog post ( https://decipherinfosys.wordpress.com/2007/02/17/parallel-execution-plans-for-queries/ ), we had briefly discussed about parallel queries. In this post, we will put together some SQL statements that you can use in Oracle to check the configuration at the parameter level, table and index configuration level and also the SQL query to check for parallel operations via v$sysstat.

To check the parameters configuration in the init<sid>.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.

Posted in Oracle | Leave a Comment »