Create table as select /*+ parallel/ from

[himridul]

Hi ,

Following is a create table statement :

CREATE TABLE TEMP
PARALLEL 64 NOLOGGING
PARTITION BY HASH (EMP_ID)
PARTITIONS 64 STORE IN (MRI_D)
AS
SELECT /*+ PARALLEL (emp, 64) PARALLEL(detail,64) USE_HASH(emp,detail) */
emp.emp_id emp_id
,deatil.house_id house_id
,detail.first_name first_name
,detail.last_name last_name
FROM
employee emp
,emp_details detail
WHERE
emp.emp_id = detail.emp_id;

Now , We have 16 processors . And each processor has Degree of parallelism = 4 .

EMPLOYEE table contains 170 millions of data.
EMP_DETAILS table contains 70 millions of data .

I couldn't test this query with 16 processors . I did test the query in 4 processors.

My question is :
Does this query create 16 different thread because we have 16 processors and we are using PARALLEL hint . Because when we are going to create the table using this query , the query is getting hanged . we had to kill this process .

Does anyone have any idea regarding this ?
Or is there any material regarding this? I tried several material in the net , but didn't dind out appropriate one .

Any help plz ......

 

[tkrish]

Hi,

Here are few things u need to try:
* As empl and details tables are very big, u must partition these tables(this will help query performance)
* Try to create indexes (use bitmap indexes) whereever applicable.
* Have you analyzed these tables and computed statistics?
* If you are going for RBO, identify hints that you need to give.
* Last but not the least, how do u know ur query has hanged? Its still active, but taking too much of time to return. R u really sure that theres no activity going on?
* Before running this query, make sure u kill all inactive sessions or active ones that take too much of CPU time (if its a production bbox, then it'll be tough to do this)

from my experience, doing these things helped performance of my queries that were accessing really huge tables...

tkrish.