delete statement in oracle enterprise stage

[rgandra]

Hi,

I am using delete statement in oracle enterprise stage and write method is "delete" but the performance is very low i.e 50 rows per second.

my scenario is

dataset--->oracle enterprise stage


my delete statement is

delete from table where col1=ORCHESTRATE.col1

Can anybody help me.

Thanks,
rgandra

[kcbland]

Sure.

1. Look at the load on the server/nodes. Do you have full resources?
2. Look at the queries running against the table. Anybody doing stuff?
3. Look at the indexes. Are there a lot?
4. Look at the indexes. Are they partitioned and are they global or local?
5. Look at your table. Is it partitioned?
6. Look at your table. If it's partitioned, are your rows for deletion sitting in the same partition or are they scattered?
7. If your rows for deletion are in the same partition, are you supplying the partition key column in the delete statement?
8. Are you deleting using a column that's indexed?
9. Are you deleting using a wildcard delete, meaning the database is having to find matching rows, 1:n?

I couldn't even begin to help you without having all of these questions answered. Usually deletes are slow because the data is scattered around a large partitioned table with a bunch of indexes and the partition key is not supplied or you're hitting so many partitions that it doesn't matter.

[kcbland]

If I was to experiment, I would create a table that matches your dataset and run that dataset into the table. Enable parallel on that work table, alter your session enable parallel dml.

Then, check the explain plan on deleting from your big table where the matching key is in your work table. Something like:

Code: Select all

delete from mybigtable where mybigtable.keyvalue in (select keyvalue from my worktable)

Check the explain plan and see what happens. Hopefully, you'll see that most of the work happens inside a single partition. If not, I bet you'll find that this delete my just run a little fast. By bulk loading the deletes (or updates) into a work table and then letting the database parallel delete (or merge) the rows, you can get a pretty fast turnaround.

[yatin.mittal]

Hi

I think you can use data partiones and collector for this.
This may enhance your performance.
These are best suited for Multi - server systems but
this may help you in single server also.

With regards,
yatin Mittal

Hi,

I am using delete statement in oracle enterprise stage and write method is "delete" but the performance is very low i.e 50 rows per second.

my scenario is

dataset--->oracle enterprise stage


my delete statement is

delete from table where col1=ORCHESTRATE.col1

Can anybody help me.

Thanks,
rgandra

[ray.wurlod]

Please explain how a data collector is supposed to help execute the SQL in an Oracle Enterprise stage? The whole rationale of the Enterprise stage types is to operate in parallel with partitioned tables. I believe that the advice you have given is at best misleading, but more probably totally incorrect.

[rgandra]

[quote="kcbland"]Sure.

1. Look at the load on the server/nodes. Do you have full resources?
2. Look at the queries running against the table. Anybody doing stuff?
3. Look at the indexes. Are there a lot?
4. Look at the indexes. Are they partitioned and are they global or local?
5. Look at your table. Is it partitioned?
6. Look at your table. If it's partitioned, are your rows for deletion sitting in the same partition or are they scattered?
7. If your rows for deletion are in the same partition, are you supplying the partition key column in the delete statement?
8. Are you deleting using a column that's indexed?
9. Are you deleting using a wildcard delete, meaning the database is having to find matching rows, 1:n?

I couldn't even begin to help you without having all of these questions answered. Usually deletes are slow because the data is scattered around a large partitioned table with a bunch of indexes and the partition key is not supplied or you're hitting so many partitions that it doesn't matter.[/quote]

Answers for the questios
1.parllel job with 4 nodes.yes, i do have full resources
2.I am the only one who is using that table
3,8.I have only one index and i am using the same index in where clause
4,5,6,7.No partition on table
9. No wild card deletion

can you please tell me to improve performance

Thanks,
rgandra

[rgandra]

My tables are in different data base so i can not write the following delete statement And there are no partitions on that


[quote="kcbland"]If I was to experiment, I would create a table that matches your dataset and run that dataset into the table. Enable parallel on that work table, alter your session enable parallel dml.

Then, check the explain plan on deleting from your big table where the matching key is in your work table. Something like:

[code]delete from mybigtable where mybigtable.keyvalue in (select keyvalue from my worktable)[/code]

Check the explain plan and see what happens. Hopefully, you'll see that most of the work happens inside a single partition. If not, I bet you'll find that this delete my just run a little fast. By bulk loading the deletes (or updates) into a work table and then letting the database parallel delete (or merge) the rows, you can get a pretty fast turnaround.[/quote]

[kcbland]

Okay, so it's the easiest table/index design possible. Unless you can check the explain plan, we're pretty much shooting ducks at night. Is the size of your row very large, meaning that you have enormous rollback usage? What is your commit rate?

If I were your DBA, I would do the whole work table, delete query, explain plan route because it's the only way to see what the database is trying to do. Also, it gives you a frame of reference for expected performance.

If you just ran 10K rows, is performance any better?