Please read my paper.Quote:
Originally Posted by dgcampos
http://oracleact.com/papers/oracle_parallel_dml.html
Printable View
Please read my paper.Quote:
Originally Posted by dgcampos
http://oracleact.com/papers/oracle_parallel_dml.html
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 tableCode:SQL> SELECT count(*) FROM myobjects;
COUNT(*)
----------
10427392
Elapsed: 00:00:13.06
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"Code:SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'MYOBJECTS';
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
INDX_MYOB OBJECT_NAME
Elapsed: 00:00:00.01
Code:SQL> alter session enable parallel dml;
Session altered.
SQL>
SQL> alter session set db_file_multiblock_read_count=128;
Session altered.
SQL> set timin on
SQL>
SQL>
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.Quote:
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,
Quote:
Originally Posted by davey23uk
To TABREAZ,
You need to increase the INITRANS value for the index.
See below:
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;
Session altered.
Elapsed: 00:00:00.00
11:10:40 SQL> alter session force parallel dml parallel 8 ;
Session altered.
Elapsed: 00:00:00.00
11:11:25 SQL> update /*+ full(t1) parallel(t1,8) */ t1
11:11:34 2 set object_name = lower(object_name);
1855968 rows updated.
Elapsed: 00:04:54.27
Thanks Tamil,
Its working fine now. Thanks a lot
So whats the danger plsssssssssssssss :confused:Quote:
Originally Posted by Bore
Why is TAMIL doing it
tamil isnt 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