Follow-up questions so that I can get a better understanding
John Cantu, May 13, 2011 - 4:03 pm UTC
Tom,
"assuming parallel query is enabled in the database setup"
Please tell me how parallel query is enabled in the database setup?
I am trying to figure out how to turn parallel query ON and OFF. I also want to know what the default setting is. I am going to ask you some questions so that I can figure out how the optimizer is suppose to perform "Normally".
If I create a new database and leave all the default values for the initialization parameters, can the optimizer ever perform a parallel query? Note: assume that I will not execute 'alter session enable parallel query' , 'alter table ... parallel' or provide a hint in the query.
Thanks,
May 18, 2011 - 2:50 am UTC
basically by setting the parallel_* parameters - parallel_max_servers for example.
http://docs.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams182.htm#REFRN10158 if you set that to zero, parallel execution would be disabled. The default varies based on other settings.
By default, yes, you'll have parallel query 'enabled' in an enterprise edition database. See, on my demo database:
ops$tkyte%ORA11GR2> show parameter parallel_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 135
ops$tkyte%ORA11GR2> select name||'='||value
2 from v$parameter where isdefault = 'FALSE';
NAME||'='||VALUE
-------------------------------------------------------------------------------
processes=150
sga_target=536870912
control_files=/home/ora11gr2/app/ora11gr2/oradata/ora11gr2/control01.ctl, /home
/ora11gr2/app/ora11gr2/fast_recovery_area/ora11gr2/control02.ctl
db_block_size=8192
compatible=11.2.0.0.0
db_create_file_dest=/home/ora11gr2/app/ora11gr2/oradata/ora11gr2/
db_recovery_file_dest=/home/ora11gr2/app/ora11gr2/fast_recovery_area
db_recovery_file_dest_size=4196401152
db_flashback_retention_target=1440
undo_tablespace=UNDOTBS2
undo_retention=120
remote_login_passwordfile=EXCLUSIVE
db_domain=localdomain
dispatchers=(PROTOCOL=TCP) (SERVICE=ora11gr2XDB)
audit_file_dest=/home/ora11gr2/app/ora11gr2/admin/ora11gr2/adump
optimizer_features_enable=11.2.0.2
audit_trail=DB
db_name=ora11gr2
open_cursors=300
pga_aggregate_target=268435456
diagnostic_dest=/home/ora11gr2/app/ora11gr2
22 rows selected.
I did not set anything "parallel" but parallel is enabled and ready to go.
Parallel query
Daasch, May 15, 2011 - 7:44 pm UTC
Look at your INIT.ORA settings like:
0 PARALLEL_MIN_SERVERS
0 PARALLEL_MAX_SERVERS
0 PARALLEL_AUTOMATIC_TUNING
Original Reason why I asked my simple question
John Cantu, May 18, 2011 - 3:00 pm UTC
Thank you, Tom. I asked that simple question because after reading through the 10G concepts and data warehouse Oracle reference regarding parallelism, and trying things out, Oracle wasn't working the way I expect it to. Sometimes it would parallelize when I didn't want it to parallelize and sometimes it would not parallelize when I thought it should.
If you have time, please answer the following odd situation:
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 4096
parallel_instance_group string
parallel_max_servers integer 200
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
alter table transactions parallel;
I see from the explain plan that parallelism occurs on queries because the 'PX' processes appear.
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | TQ
|IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 8 | 104 | 1219 (1)| | |
| | |
| 1 | PX COORDINATOR | | | | | | |
| | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 8 | 104 | 1219 (1)| | | Q1,0
1 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 8 | 104 | 1219 (1)| | | Q1,0
1 | PCWP | |
| 4 | PX RECEIVE | | 8 | 104 | 1218 (1)| | | Q1,0
1 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 8 | 104 | 1218 (1)| | | Q1,0
0 | P->P | HASH |
| 6 | PX PARTITION RANGE ALL | | 8 | 104 | 1218 (1)| 1 | 398 | Q1,0
0 | PCWC | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TRANX | 8 | 104 | 1218 (1)| 1 | 398 | Q1,0
0 | PCWP | |
| 8 | INDEX RANGE SCAN | TRANSACTIONS_IDX1 | 430 | | 1204 (1)| 1 | 398 | Q1,0
0 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
-----------------------
Next I 'disabled' parallelism with (which is what I thought I needed to do before I asking my question):
alter table detailuser.tranx noparallel;
alter system set parallel_max_servers=0;
After this, I reran the query and as expected, no PX processes showed up in the explain plan.
----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1225 (1)| | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 21 | 273 | 1225 (1)| | |
| 3 | COUNT STOPKEY | | | | | | |
| 4 | VIEW | | 21 | | 1225 (1)| | |
| 5 | SORT ORDER BY STOPKEY | | 21 | 2520 | 1225 (1)| | |
| 6 | HASH JOIN | | 21 | 2520 | 1224 (1)| | |
| 7 | HASH JOIN | | 22 | 2420 | 1221 (1)| | |
| 8 | TABLE ACCESS FULL | TRANSCODES | 362 | 13032 | 4 (0)| | |
| 9 | PARTITION RANGE ALL | | 430 | 31820 | 1217 (1)| 1 | 398 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TRANX | 430 | 31820 | 1217 (1)| 1 | 398 |
| 11 | INDEX RANGE SCAN | TRANSACTIONS_IDX1 | 430 | | 1203 (1)| 1 | 398 |
| 12 | TABLE ACCESS FULL | STATEMENTLAYOUT | 5 | 50 | 2 (0)| | |
--------------------------------------------------------------------------------------------------------------------
Then, I dropped and recreated TRANSACTIONS_IDX1 (This decreased the consistent block reads from 3000 to 1500) and 'enabled' parallelism with:
alter table detailuser.tranx parallel;
alter system set parallel_max_servers=200;
Now, I CAN'T get parallelism to work. (I don't get the 'PX' entries in the explain plan anymore.) Do you have any idea as to why the optimizer is choosing not to parallize anymore? Could a more efficient index cause the optimizer not to choose parallelism anymore?
Can I get it to parallelise by setting: alter session force parallel query parallel; In my case, the answer is no. Still no 'PX' entries.
Please shed some light on this very odd problem.
May 18, 2011 - 11:40 pm UTC
I don't know your complete set of steps here. You say you dropped and recreated an index - I never saw you create an index in the first place. it could be a statistics thing. If you
create tables
create indexes
load tables
run queries
drop indexes
create indexes
run queries
You'll likely get some different plans the second time around because when you create an index on tables with data in them - we compute stats on the indexes, when you create indexes on tables without data - you don't get statistics. I don't know what steps you went through or what your data looks like.
altering a table to be parallel does not mean "all queries will be parallel", it means they are allowed to be.
John Cantu, May 19, 2011 - 5:35 am UTC
Tom,
The steps I took where these:
1) Original scenario:
table 400 partitioned TRANX with TRANSACTION_IDX with millions of records. These objects have existed for many years and statistics are up to date.
Explain plan on query showed optimizer was parallelizing.
2) Dropped and Recreated index, TRANSACTION_IDX, collected statistics with dbms_stats.gather_table_stats(OWNNAME=>'ITOP', TABNAME=>'TRANX', ESTIMATE_PERCENT=>10, CASCADE=>TRUE);
Explain plan on query showed optimizer was not parallelizing anymore. Also, noticed that the number of consistent reads also went down from 3000 to 1500 after the index recreate.
Note: records are never deleted nor updated from the TRANX table.
Summary: Optimizer can make a significant change like decide not to parallelize anymore after dropping and recreating the same index.
Thank you, Tom.
May 19, 2011 - 8:45 am UTC
2) that was a waste of cycles, you didn't need to gather stats on the table and a CREATE INDEX command in 10g and above does a compute statistics while it is running.
summary: yes, the index is potentially very different from the old index. So you should expect plan changes to be completely possible after recreating things from scratch.