Home » RDBMS Server » Performance Tuning » HASH cluster building very Slow (Oracle 10g R2)
icon8.gif  HASH cluster building very Slow [message #441699] Tue, 02 February 2010 14:26 Go to next message
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
Junior Member
Hi all,

I have table of 400 million rows, average row length 75 bytes
i am trying to recreate that table using hash clustered table.
Tablespace block size is 8kb.

--Create Hash cluster

create cluster events_hash_cluster (
cr_code number
)
single table hashkeys 1000000
size 8000

--Column cr_code has 500 000 distinct values
--There are 400 000 000 rows in table

--For table creation i excute the following statement

CREATE TABLE cr_events_hash
(
CR_ID NUMBER,
CR_CODE NUMBER,
CR_MNEMO VARCHAR2(30 Byte),
CR_EVT_CODE NUMBER,
CR_EVT_METH VARCHAR2(30 Byte),
CR_EVT_PL_CD NUMBER,
CR_EVT_PL_IR NUMBER(4),
CR_EVT_TRNS DATE,
CR_EVT_TRNS_CODE NUMBER,
CR_EVT_TRNS_AMT NUMBER(13,2),
CR_EVT_AMOUNT NUMBER(17,6),
CR_EVT_PARAM VARCHAR2 (1 Byte),
CR_EVT_DT DATE,
CR_EVT_USER VARCHAR2 (30 Byte),
CR_EVT_SYS_DT DATE)
CLUSTER events_hash_cluster(cr_code);

--In the end i try to insert rows into clustered table
INSERT INTO credit_events_hash
SELECT CR_ID,
CR_CODE,
CR_MNEMO,
CR_EVT_CODE,
CR_EVT_METH,
CR_EVT_PL_CD,
CR_EVT_PL_IR,
CR_EVT_TRNS,
CR_EVT_TRNS_CODE,
CR_EVT_TRNS_AMT,
CR_EVT_AMOUNT,
CR_EVT_PARAM,
CR_EVT_DT,
CR_EVT_USER,
CR_EVT_SYS_DT)
FROM s_cr_evnt;

--It takes 48hours already and still only 8% done

Is there a better way to fill has clustered table?
May be i should use CTAS on s_cr_evnt table before inserting to clustered table?

[Updated on: Tue, 02 February 2010 15:14]

Report message to a moderator

Re: HASH cluster building very Slow [message #441722 is a reply to message #441699] Tue, 02 February 2010 21:36 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
See this once....
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4536286321836#1986881900346458655
Hope you are not performing on production environment!
For more reference
http://forums.oracle.com/forums/message.jspa?messageID=2502386#2502386

http://www.psoug.org/reference/clusters.html


sriram Smile

[Updated on: Tue, 02 February 2010 21:42]

Report message to a moderator

Re: HASH cluster building very Slow [message #441766 is a reply to message #441722] Wed, 03 February 2010 01:00 Go to previous messageGo to next message
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
Junior Member
ramoradba Smile of course it is testing enviroment.

the information on hash clusters i have found is very theoretical.
and the is not much info about them on net.

I had to broke the procces of filling that cluster as it would take me about a month! for 400 million row table.

As you can see i set the size for hash cluster - 8000 which is equal to the block size.
Does it 100% means that in the block there will never be several
different hashed column values in the same block (i assume i set the haskeys number high enough)

[Updated on: Wed, 03 February 2010 01:03]

Report message to a moderator

Re: HASH cluster building very Slow [message #441793 is a reply to message #441766] Wed, 03 February 2010 02:31 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Have you refered the links posted?

sriram Smile
Re: HASH cluster building very Slow [message #441800 is a reply to message #441793] Wed, 03 February 2010 03:01 Go to previous messageGo to next message
John Watson
Messages: 6581
Registered: January 2010
Location: Global Village
Senior Member
I've done a fair bit of work with clusters, and would welcome more information, too. I can't simulate your problem yet (ran out of disc space) but I do have a couple of suggestions. First, hash clusters can be very good for concurrent inserts: no buffer busy waits. You can't use parallelism, but you could divide up the insert into several (32? 64? just guessing) smaller transactions and try again. Partitioning the source table accordingly might help. Second, how often are you log switching? I would usually create huge logfile members for such a big operation (or even be naughty, and set _disable_logging=true). Third, you are going to need an undo tablespace the size of Jupiter. So again, would it make sense to break it up into smaller transactions?
Incidentally, what are the wait events for the session? And can you post an AWR report covering the insert?
Please keep this topic updated, it is an important subject.
Re: HASH cluster building very Slow [message #441804 is a reply to message #441766] Wed, 03 February 2010 03:31 Go to previous messageGo to next message
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
Junior Member
Yes i reffered the links you posted thanks.
I already had read some of them before.

I will try to switch off archive logs, and redo logging.
also as "John Watson" suggested will try to cut the source table into pieces.

[Updated on: Wed, 03 February 2010 04:00]

Report message to a moderator

Re: HASH cluster building very Slow [message #442160 is a reply to message #441804] Fri, 05 February 2010 01:15 Go to previous messageGo to next message
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
Junior Member
Finally i managed to fill the clustered table in about 9 hours.

--The steps i made:
--Devide table into 100 almost egual pieces
--and esure column's "credit_evt_credit_code" values exists only in one partition

CREATE TABLE s_credit_events_hash_part
NOLOGGING
PARTITION BY HASH (credit_evt_credit_code)
PARTITIONS 100 STORE IN (kredit_events_idx)
PARALLEL (DEGREE 20) AS
SELECT /*+parallel(i 20)*/i.* FROM s_cr_evnt i

--Filling the Clustered table
DECLARE
V_NAME VARCHAR2(30);
BEGIN
FOR i IN 51..150 LOOP
V_NAME := 'SYS_P'||TO_CHAR(i);
EXECUTE IMMEDIATE 'INSERT INTO credit_events_hash '||'(SELECT * FROM s_credit_events_hash_part PARTITION ('||V_NAME||'))';
COMMIT;
END LOOP;
END;
--Executed in 31623.8 seconds. Comparing to IOT building on the same table it took 4 times more time.

Now i am on testing the performance.

Interestingly i could not specify "NOLOGGING" hint nor in Cluster creation nor in Clustered table creation.
Does anyone knows how to switch off redo logs in this situation?
I think it could speed the creation process.
Or maybe by building hash cluster it takes NOLLOGING hint value from the tablespace in which th cluster is built?


Re: HASH cluster building very Slow [message #442178 is a reply to message #441699] Fri, 05 February 2010 03:05 Go to previous message
John Watson
Messages: 6581
Registered: January 2010
Location: Global Village
Senior Member
Hi - an excellent result.
Do you have time to test loading the data concurrently? Instead of looping through your partitions, launch several sessions and do several partitions at once? That is why I have used hash clusters: to do concurrent inserts from many sessions without serializing on buffer busy wait events.
And can you possibly post AWR reports for the the load you've just done? And also if you try doing it in concurrent sessions?

With regard to logging, I would never recommend the underscore parameter I mentioned before, it was actually rather unprofessional of me to suggest it (though it certainly works). I do wonder if a direct load is technically possible for a cluster, it might be that the load must go through the SGA because of the nature of the cluster structure. Though syntactically, these do work:
insert into cr_events_hash select * from S_CR_EVNT nologging;
insert /*+append*/ into cr_events_hash select * from S_CR_EVNT nologging;

I haven't yet checked whether redo is in fact generated.

Previous Topic: Performance issue in Oracle 10g using Bulk Collect
Next Topic: Long run time of query with Order by clause
Goto Forum:
  


Current Time: Sat Dec 10 20:35:15 CST 2016

Total time taken to generate the page: 0.24364 seconds