-
Originally Posted by dgcampos
tamilselvan :
can you be more specific about parallel DML? what exaclty do you have in mind?
regards,
daniel
Please read my paper.
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
Code:
SQL> SELECT count(*) FROM myobjects;
COUNT(*)
----------
10427392
Elapsed: 00:00:13.06
I'm trying to run Parallel DML on that table, I have following index on that table
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
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> 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
-
Originally Posted by tamilselvan
Ray,
PL/SQL Block always runs slower than a single update.
For a simple update, do it in a single statement. If possible, use parallel DML.
Yes Tamil, I do agree with you.
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
no you cannot do that hope you reliase how dangerous it is too even if you could
-
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
-
Originally Posted by Bore
So, to summarize the thread,
So whats the danger plsssssssssssssss
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|