Please read my paper.
Originally Posted by dgcampos
Dear Tamil, While trying example provided in the link given by you I got the following problem
I'm trying to run Parallel DML on that table, I have following index on that table
SQL> SELECT count(*) FROM myobjects;
Here is the code for Parallel DML, I'm running only one session which is current. I dont know how comes I got an error saying "DEADLOCK DETECTED"
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'MYOBJECTS';
SQL> alter session enable parallel dml;
SQL> alter session set db_file_multiblock_read_count=128;
SQL> set timin on
SQL> UPDATE /*+ full(myobjects) parallel(myobjects, 8)*/ myobjects
2 SET object_name = upper(object_name);
update /*+ full(myobjects) parallel(myobjects, 8)*/
ERROR at line 1:
ORA-12801: error signaled in parallel query server P015
ORA-00060: deadlock detected while waiting for resource
Yes Tamil, I do agree with you.
Originally Posted by tamilselvan
In fact I myself have posted before,
long time ago about using PARALLEL DML
also here in this forum.
PL/SQL Block always runs slower than a single update.-
- yes, it was actually a sort of a test to the poster. Davey, actually posted contesting the use of pl/sql to update before my post.
It'a trick that will work for small table
but not in huge one...heheh..my bad.
So, to summarize the thread,
Originally Posted by davey23uk
You need to increase the INITRANS value for the index.
create index t1_idx on t1(object_name)
pctfree 30 initrans 9
tablespace large_01_idx nologging parallel 8
11:10:32 SQL> alter session enable parallel dml;
11:10:40 SQL> alter session force parallel dml parallel 8 ;
11:11:25 SQL> update /*+ full(t1) parallel(t1,8) */ t1
11:11:34 2 set object_name = lower(object_name);
1855968 rows updated.
Its working fine now. Thanks a lot
So whats the danger plsssssssssssssss
Originally Posted by Bore
Why is TAMIL doing it
There are some occasions you need to update a big table.
For example, a ETL process may need to clean up the source data in a staging table in a DW system before loading into FACT table.
It's nothing wrong using parallel DML as long as you achieve the goal in a shorter time.
If you are not using the feature, then what's the purpose of it?
1) When we talk about danger we mean update with no redo generation
2) There is no danger since that's not possible
3) If you do not generate redo and the database fail and you bring the file from backup, you'll loose your table
Click Here to Expand Forum to Full Width