SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Execute procedures parallel

m.abohsinJan 22 2010 — edited Jan 22 2010

Hi everyone,

I have a procedure which processes 80 million records, performing several data manipulation statements, the details are not important. What is important is that I want to divide the 80 million records into 4 groups of 20 million records. So I have a procedure called sp_data_manipulation(). I want to create a new procedure as follows:
create or replace procedure sp_data_manipulation as
begin
for i in (select id from masters_tab)
loop
exec sp_data_manipulation_p1();
exec sp_data_manipulation_p2();
exec sp_data_manipulation_p3();
exec sp_data_manipulation_p4();
end loop;
end;
/

All 4 procedures being called are identical, but each processes a different set of records. If procedure is executed in that way, it executes sequentially proc 1,2,3 then 4, which ofcourse is just the same as executing one procedure for all records. I want to execute them in parallel, so that all four procedures are executed at once. One suggestion I have done, but I don't like is:
nohup sqlplus user/password@db @proc1.sql &
nohup sqlplus user/password@db @proc2.sql &
nohup sqlplus user/password@db @proc3.sql &
nohup sqlplus user/password@db @proc4.sql &

where each proc.sql contains 'EXEC SP_DATA_MANIPULATION()', so all 4 are run in parallel, but I need to run this from operating system and not from inside Oracle.

Another solution, which did not convince me, is creating four different Scheduled Jobs, each calling one of the procedures, then they can all fire at same time, but I want to do it from within the PL/SQL code itself. If anyone can help it would be appreciated.

Thanks in advance.

jeneesh

So the total records is 1800. The output count is 600..

Full Table Scan is not a bad thing here..

You can force index usage by INDEX hint and check what is the plan , cost and details.. (Just see the details)

You will probably understand the difference then..

Full scans are NOT EVIL

BEDE

Try using the hint:

SELECT /*+ index(t  btmp_typeid) */ tran_id, city_id, valid_records 
 FROM transaction_details t
 WHERE type_id=101

Still, if there are lots of transactions on that table, it is not good to have bitmap indexes. Bitmap indexes are to be used on tables where you have few transactions, generally in database warehouses.
Moreover, are you sure you have Oracle Enterprise database? If it is not Enterprise, then it does not create bitmap indexes, but only tree indexes will be created even when you specify bitmap.

BEDE

Moreover, bitmap indexes are generally used in database warehouses for partitioned tables and are also local indexes, so that a local index may be rebuilt on a given partition, not on the whole table which will be time consuming.
And, if you do not use hints, the optimizer will take the latest DB statistics available to make an execution plan for your query. If you just created the index and have not run statistics, then the index may not have been analyzed.
And, if you have few rows in a table, then a fullscan may run faster than an index range scan. Even having the statistics up to date, the optimizer may decide a fullscan will run faster.

Manik

BEDE wrote:
Moreover, bitmap indexes are generally used in database warehouses for partitioned tables and are also local indexes,

I guess this is incorrect statment..BITMAP indexes are created and are most appropriate for columns having low distinct values.
I would not recommend index hint here because optimizer knows the best way to generate explain plan..

May be OP has to gather stats on his table after creating index. But yes if the table is small then the optimizer thinks better to do FTS sometimes.

Cheers,
Manik.

jeneesh

Manik wrote:

BEDE wrote:
Moreover, bitmap indexes are generally used in database warehouses for partitioned tables and are also local indexes,

I guess this is incorrect statment..BITMAP indexes are created and are most appropriate for columns having low distinct values.

Normally BITMAP indexes are not much advisable for OLTP systems.. It will cause a lot of locking issues - For example, multiple rows may get locked during a single row UPDATE.

BITMAP are mainly used in DW systems where the DMLs will be in batch and wont be much frequent..

890271

Thanks BEDE,

using the hint query i got this plan,

Plan
SELECT STATEMENT ALL_ROWS*Cost: 149* Bytes: 8,876 Cardinality: 634
3 TABLE ACCESS BY INDEX ROWID TABLE TRANSACTION_DETAILS Cost: 149 Bytes: 8,876 Cardinality: 634
2 BITMAP CONVERSION TO ROWIDS
1 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) BTMAP

Now the cost is increased from 29 to 149. why... it is supposed to be lesser than 29.. am i correct or wrong?

890271

thanks jeneesh,

posted the plan above for the query with index hint. Kindly explain me why its greater now.

kindly help me.

Edited by: 887268 on Apr 3, 2013 11:58 PM

Paul Horth

887268 wrote:
Thanks BEDE,

using the hint query i got this plan,

Plan
SELECT STATEMENT ALL_ROWS*Cost: 149* Bytes: 8,876 Cardinality: 634
3 TABLE ACCESS BY INDEX ROWID TABLE TRANSACTION_DETAILS Cost: 149 Bytes: 8,876 Cardinality: 634
2 BITMAP CONVERSION TO ROWIDS
1 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) BTMAP

Now the cost is increased from 29 to 149. why... it is supposed to be lesser than 29.. am i correct or wrong?

The cost is greater because index access is worse than a full table scan in this instance. Do not use the hint. Let Oracle decide.

Please read what other have written. Full table scans are not evil. Often index access can be worse.

jeneesh

887268 wrote:

Now the cost is increased from 29 to 149. why... it is supposed to be lesser than 29.. am i correct or wrong?

You are not correct ..

Think of a Simple example:

I have a small table - 100 rows . In the database all these rows are residing in a single data block (Assume)

And there is an index on COL1. Suppose the index also is in another single block.

Now, if you have query

select *
from table
where  col1 = something;

Suppose this will return 20 rows.

Now if ORACLE is going for a Full Table scan => How many blocks of data it has to read ? Answer is 1 coz, the whole data is residing in a single block.

If oracle is going for an INDEX SCAN, it has to read 2 blocks of data - Firstly the INDEX Block to find out the ROWIDs, and then the TABLE BLOCK to find the rows...

So, which is better? Which is of less cost?

bencol

..BITMAP indexes are created and are most appropriate for columns having low distinct values.

Agree with you, but I would go futher and say that bitmap indexes are most appropriate where they can be combined. If there are low distinct values, then a single one is not going to provide much advantage over a full table scan. I have found them useful in data warehouses as you can control the writes, minimising the contention, and there can be many more distinct queries, so deciding a good selection of btree indexes can be difficult. They are also useful for aggregations, if you are grouping on a bitmap indexed column.

Ben

34MCA2K2

jeneesh wrote:

Normally BITMAP indexes are not much advisable for OLTP systems.. It will cause a lot of locking issues - For example, multiple rows may get locked during a single row UPDATE.

I am sorry for being ignorant, can you please cite any scenario of locking due to bitmap indices? A link can be useful as well.

Regards,

unknown-7404

>
total number of rows in the table = 1800 ;
distinct value of type_ids are 101,102,103

so i created a bit map index on it.

CREATE BITMAP INDEX btmp_typeid ON transaction_details
(type_id)
LOGGING
NOPARALLEL;

after creating the index, the explain plan shows the same. why it goes for full table scan?.
>
Why would Oracle use that index to retrieve 1700 rows of an 1800 row table? That doesn't make any sense.

As you already showed Oracle will use the plan with the lowest cost and that is the full table scan.

unknown-7404

>
I am sorry for being ignorant, can you please cite any scenario of locking due to bitmap indices? A link can be useful as well.
>
See my full reply in this thread
2399392
>
ETL is affected because DML operations (INSERT/UPDATE/DELETE) on tables with bitmapped indexes can have serious performance issues due to the serialization involved. Updating a single bit-mapped column value (e.g. from 'M' to 'F' for gender) requires both bitmapped index blocks to be locked until the update is complete. A bitmap index stored ROWID ranges (min rowid - max rowid) than can span many, many records. The entire 'range' of rowids is locked in order to change just one value.

To change from 'M' the 'M' rowid range for that one row is locked and the ROWID must be removed from the range byt clearing the bit. To change to 'F' the 'F' rowid id range needs to be found, locked and the bit set that corresponds to that rowid. No other rows with rowids in the range can be changed since this is a serial operation. If the range includes 1000 rows and they all need changed it takes 1000 serial operations.

1 person found this helpful

unknown-7404

>
BITMAP indexes are created and are most appropriate for columns having low distinct values.
>
That MYTH has been busted for many years now by many people.

See Richard Foote's series of articles about this myth and many others regarding indexes.
http://richardfoote.wordpress.com/2010/02/18/myth-bitmap-indexes-with-high-distinct-columns-blow-out/
>
One of the great myths in Oracle is that bitmap indexes are only suitable and should only be used with columns that have so-called low cardinality (few distinct) values
. . .

user648773

did you try a normal index? the bitmap is usefull for safe space when you have few values (is not your problem becuase the table is small), and because you can use more than one (with bitmap you can acces by the table by more than one index, with normal optimezer have to choose one, this is correct for you because you have one condition)

did you collect statistics on the table?

Manik

rp0428 wrote:
>
BITMAP indexes are created and are most appropriate for columns having low distinct values.
>
That MYTH has been busted for many years now by many people.

See Richard Foote's series of articles about this myth and many others regarding indexes.
http://richardfoote.wordpress.com/2010/02/18/myth-bitmap-indexes-with-high-distinct-columns-blow-out/
>
One of the great myths in Oracle is that bitmap indexes are only suitable and should only be used with columns that have so-called low cardinality (few distinct) values
. . .
>

USED in DW is fine.. I am not arguing that..

But learnt something from that link.. Thanks RP. This completely changed my view about BITMAP indexes.
I like the last sentance there :

The next time you unfortunately read that bitmap indexes should only be used with very “few” distinct values and would be “huge” otherwise, well hopefully you’ll appreciate that’s simply not correct.

;)

I should have read thoroughly the documentation where it says :

http://docs.oracle.com/cd/B28359_01/server.111/b28313/indexes.htm

A gender column, which has only two distinct values (male and female), is optimal for a bitmap index. However, data warehouse administrators also build bitmap indexes on columns with higher cardinalities.

Thanks for sharing!!!

Cheers,
Manik.

1 person found this helpful

jeneesh

34MCA2K2 wrote:
I am sorry for being ignorant, can you please cite any scenario of locking due to bitmap indices? A link can be useful as well.

Sorry for the late response. rp0428's post is clear enough for you to understand the issue.

You could also go through this discussion at asktom : Bitmap indexes and locking

1 person found this helpful

890271

Thank you all for sharing your ideas. im little bit confused with this concepts . i will so through with the links shared.

Thanks again for all.

Edited by: 887268 on Apr 6, 2013 1:39 AM

1 - 18

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 19 2010

Added on Jan 22 2010

#pl, #procedure, #sql

1 comment

2,361 views

SQL & PL/SQL

Execute procedures parallel

Comments

Post Details