Running simple query on LARGE table (slow performance) - Urgent Help

[mghafoori]

Hello Forum,
I am running into performance problem trying to update a large table (440 Million records). I have never worked on such a bigh table before. I tried running the following update statement and it did not finish in 48 hours and therefore i killed it:

update /*+ index( r idx_hcpcs) */ cms.rev_cntr r
set (r.si, r.apc) =
(select x.si, x.apc
from cms.xwalk02 x
where r.hcpcs_cd = x.cpt);

There was no way on knowing how fast was this thin running and therefore i decided to write a little script to time the process and commit every 100,000 records. Here is what i ran next:

DECLARE

CURSOR cur_all IS
SELECT rowid, hcpcs_cd
FROM cms.rev_cntr;

CURSOR cur_xwalk (p_hcpcs_cd_in In xwalk02.cpt%TYPE) IS
SELECT si, apc
FROM cms.xwalk02
WHERE cpt = p_hcpcs_cd_in;

lv_counter NUMBER;
lv_si xwalk02.si%TYPE;
lv_apc xwalk02.apc%TYPE;

BEGIN

lv_counter := 0;

execute immediate 'truncate table cms.log_commit';

FOR cur_all_rec IN cur_all LOOP

-- check if hcpcs_cd is NOT NULL...if NOT NULL then process

IF cur_all_rec.hcpcs_cd IS NOT NULL THEN

FOR cur_xwalk_rec IN cur_xwalk(cur_all_rec.hcpcs_cd)
LOOP
lv_si := cur_xwalk_rec.si;
lv_apc := cur_xwalk_rec.apc;
END LOOP;

UPDATE cms.rev_cntr
SET si = lv_si,
apc = lv_apc
WHERE rowid = cur_all_rec.rowid;


-- commit every 100000
IF lv_counter = 100000 THEN
insert into cms.log_commit values 'Rev_Cntr',100000,sysdate);
COMMIT;
lv_counter := 0;
ELSE lv_counter := lv_counter +1;
END IF;
END IF; -- end of NULL check FOR hcpcs_cd

END LOOP;

COMMIT; -- issued at end to commit records < 10000

END;

I ran the same script on our test DB with 400,000 records and it ran in 3 minutes, but on production, it has taken 7 hors to do 700,000 records. It will be done in about 15 days. Here are some facts and information about this process and the database:

1. We are running Oracle 8i on Unix Box(E450) with dual processor and 2 Gig RAM.
2. There are 12 disks with total of 360 Gig space.
3. The data is being &quot;Mirrored&quot; meaning the same data is being duplicated on 2 disks.
4. There is a bitmap index on rev_cntr.hcpcs_cd column with 14000 distinct hcpcs_cd values
5. The xwalk table is small(12000 records) and it has index on both columns.
6. We are gettinh high I/O and only 2% CPU utilization
7. PCTFree on Rev_Cntr is 10 and PCTUsed is 40

Here are my questions:
1. Can Oracle and this Unix box handle such a volume
2. Is the bitmap index on rev_cntr table appropriate or should i use regular index
3. What other suggestions do you have to speed up this process.
4. would it be better to break up the table into 4 or 5 smaller tables.

I appreciate any help you can offer. I am sure there are many experts here that can help me. Thanks for your time.

Mason
email:

 

[Turkbear]

Hi,
A bitmap index is for LOW cardinality, 14000 is not low..Use a regular index...

Then analyse all the tables so the CBO has up to date stats,
and try it without the hint...( use a subset to test, if possible, to save time)

More later..

 

[fmorel]

It seems that you have too much IO ...

Are you using enough Cache ?
What are your DB_BLOCK_SIZE and DB_BLOCK_BUFFERS ?
Is your SORT_AREA_SIZE large enough?

 

[mghafoori]

My db_block_size is 8192 and db_block_buffers is 5500 and sort_area_size is 20,971,520

 

[fmorel]

You should increase your db_block_buffers. Your DB is using only 44 MB of cache memory and you said that you have 2GB RAM on this server.
Try to do the test after setting it to 60000 ... (you have to restart your database).

What is the value for DB_FILE_MULTIBLOCK_READ_COUNT ?
Did you try to use a PARALLEL hint for your query?


You may try to use STATSPACK to see what happens during this query. And please tell us!

Regards

 

[mghafoori]

Sorry, My Db_Block_buffer is 55,000 not 5,500 and the db_file_multiblock_read_count is 8. What is STATSPACK?

 

[fmorel]

Statspack is a package (version 8i) provided by oracle.
It can gather statistics and produce performance reports that can help you for your database and sql tuning.

It can be found in rdbms\admin directory

To install it

SQL> connect / as sysdba
SQL> @spcreate

Then you connect
SQL> connect perfstat/perfstat

You make a &quot;snapshot&quot; of the database statistics
SQL> execute statspack.snap;

Then you launch your long query. Wait for a while and stop it.
Then you make another snapshot
SQL> execute statspack.snap;

And then you produce the report using spreport
SQL> connect perfstat/perfstat
SQL> @spreport
(spreport is in rdbms\admin too)
--> spreport compares your 2 snapshots and gives you some interesting information.

To get more relevant information you may put timed_statistics=TRUE in your init.ora before you use statspack.

 

[sem]

You should drop bitmap index, because it's really huge and quite unuseful. Or at least you should delete a hint: you're updating ALL the table, so accessing it via index is not very helpfull, unless you're ordering results (se notes later).

As for your script, why don't you place &quot;hcpcs_cd IS NOT NULL&quot; condition into cursor definition? SQL works faster than PL/SQL. Are xwalk02 and xwalk the names of the same table? Does it have an index on cpt?

The query on xwalk is relatively simple, but it's executed 440M times, so it will be helpfull to create a procedure with some cacheing features:
on first call store si, apc, cp as packaged variables and on consecutive calls compare passed cp value with stored one to prevent unnecessary calls to xwalk table. It will be extremely helpfull if the rows from rev_cntr will be grouped by hcpcs_cd. You may also commit on changing hcpcs_cd value.

 

[bjoern6689]

1) Drop the hint.
2) create an index on cms.xwalk02 containing:
cpt, si and apc in that order.
3) Accept updating 440 mill rows takes time.

 

[fmorel]

bjoern6689,
Why do you want to add si and apc in that index ?

The query:
update cms.rev_cntr r
set (r.si, r.apc) =
(select x.si, x.apc
from cms.xwalk02 x
where r.hcpcs_cd = x.cpt);

With &quot;si&quot; and &quot;apc&quot;, the index will be bigger than if there is only &quot;cpt&quot;, and so it should be slower..?
mghafoori, did you try to put table xwalk02 in cache?

alter table xwalk02 cache;

 

[bjoern6689]

including all 3 columns means that it's only nessesary to access the index and not the data-tabel. With only 14000 rows in xwalk02 the index will still be relatively small.

I agree that if possible put the new index in cache.

 

[sem]

Even with bitmap index you may try to speed up your query by looping through xwalk, not rev_cntr:

for f in (select * from xwalk) loop
UPDATE cms.rev_cntr
SET si = f.si,
apc = f.apc
WHERE hcpcs_cd=f.cpt ;

insert into log_table ...

commit;
end loop;