Parallel SQL statements were a part runs in serial are candidates of tuning.
Oracle 9i introduced the view V$SQL_PLAN where we can find the execution plans for all SQL statement in the library cache. We can use this view to find parallel running SQL statements which are candidates for tunning.
We use the fact that the distribution method
PARALLEL_FROM_SERIAL (in the explain plan we see S->P) is an indicator that some parts of the SQL statement runs in serial. This is often caused by different settings of the parallel degree or that we force a query to run in parallel with only one parallel hint.
Oracle 9i introduced the view V$SQL_PLAN where we can find the execution plans for all SQL statement in the library cache. We can use this view to find parallel running SQL statements which are candidates for tunning.
We use the fact that the distribution method
PARALLEL_FROM_SERIAL (in the explain plan we see S->P) is an indicator that some parts of the SQL statement runs in serial. This is often caused by different settings of the parallel degree or that we force a query to run in parallel with only one parallel hint.
The query:
- SQL>select p.hash_value ,t.sql_text from v$sqltext t, v$sql_plan p where p.other_tag In ('PARALLEL_FROM_SERIAL' ) and t.HASH_VALUE = p.HASH_VALUE and t.ADDRESS = p.ADDRESS order by t.HASH_VALUE , t.ADDRESS , t.PIECE;
Finds all 'bad' running parallel SQL statements in the library cache where a part runs in serial.
This query will give HASH_VALUE and SQL_TEXT which is candidate for tuning.
Then with the help of Execution plan we can check the degree setting
What exactly we see in execution plan?
The table which is used in query(candidate for tuning) has a parallel degree of 1 and so we scan this table in serial. In the IN-OUT column of PLAN_TABLE we find the value s->P (PARALLEL_FROM_SERIAL) the script looks for statement with this condition.
To investigate the degree setting on the tables and indexes we run below mention two query
This query will give HASH_VALUE and SQL_TEXT which is candidate for tuning.
Then with the help of Execution plan we can check the degree setting
What exactly we see in execution plan?
The table which is used in query(candidate for tuning) has a parallel degree of 1 and so we scan this table in serial. In the IN-OUT column of PLAN_TABLE we find the value s->P (PARALLEL_FROM_SERIAL) the script looks for statement with this condition.
To investigate the degree setting on the tables and indexes we run below mention two query
- SQL> select table_name , degree from user_tables where table_name in ('
tablename’, ' tablename');
- SQL> select index_name , degree from user_indexes where table_name in ('
tablename’, 'tablename ' );
Here tablename is name of table used in query which is candidate for tuning.
How to fix this problem?
The only problem is of degree 1 of the table We change the degree of table to 4
- SQL> alter table tablename
parallel 4;
And suppose if problem is of degree only then after altering table we have fixed this issue and in the column IN-OUT of new execution plan we can also see in parallel which give in the most cases a performance benefits. We can also see that we have lower costs.
Summary
[1] Find parallel running queries where some parts run inserial.
[2] Check all tables and indexes if the have the correct parallel degree.
[3] If you use parallel hints consider to use parallel degree on object ( table/index ) level or use more parallel hints that all tables/indexes can access in parallel.
[2] Check all tables and indexes if the have the correct parallel degree.
[3] If you use parallel hints consider to use parallel degree on object ( table/index ) level or use more parallel hints that all tables/indexes can access in parallel.
