Environment for Parallel Query?

Oracle 8.1.7.2.

I'm working as a developer and I've become the defacto performance tuning guy, so I'm having a boatload of fun. Anyways, I'm working on a loading proc which I would like to use parallel query to speed it up, but everytime I run it in parallel, it slows down. That says to me that the environment must not be tuned to allow for PQ. ("Its just a development environment" is the stock answer from our DBA's, and I'm just a contractor, so I don't want to rock many boats, but I would like to give a clear picture to them as to why PQ isn't working and what we should do to get our environment tuned for it.

Here are the parallel parameter settings.

parallel_automatic_tuning					 FALSE
parallel_execution_message_size 				 2148
parallel_max_servers						 5
parallel_min_servers						 0
recovery_parallelism						 0
fast_start_parallel_rollback					 LOW
parallel_adaptive_multi_user					 FALSE
parallel_threads_per_cpu					 2
optimizer_percent_parallel					 0
parallel_min_percent						 0
cpu_count							 4

So, we have 4 cpus, many gigabytes of disk space and 4Gig of Ram on the OS. I would think PQ could be made to scream with this kind of horsepower.

The size of the loading table being accessed is 3.5G.

So, one thing that just plain doesn't make sense to me is the

parallel_max_servers.  We have 4 cpus each running 2
parallel_threads_per_cpu.  So, we should have at minimum,
parallel_max_servers = 8?  I would think this should be bumped to 100?

My suggestion to them for starters was shut down, turn parallel_automatic_tuning = TRUE and let Oracle tell us what it thinks it should be.

Some more to look at is the configuration of the disks.

SQL> select file_name, TABLESPACE_NAME from dba_data_files order by file_name;

FILE_NAME TABLESPACE_NAME

-------------------------------------------------- ------------------------------


/dbu09/oradata/a4pd/temp01_02.dbf		   TEMP

/dbu11/oradata/a4pd/users_01.dbf		   USERS

/dbu14/oradata/a4pd/data01_04.dbf		   DATA_01

/dbu16/oradata/a4pd/data01_02_03.dbf		   DATA_02

/dbu34/oradata/a4pd/sample02.dbf		   SAMPLE

/dbu35/oradata/a4pd/data_02_02.dbf		   DATA_02

/dbu36/oradata/a4pd/sample03.dbf		   SAMPLE

/dbu37/oradata/a4pd/rbs02.dbf			   RBS

/dbu39/oradata/a4pd/data_01_03.dbf		   DATA_01

/dbu42/oradata/a4pd/index_01_02.dbf		   INDEX_01

/dbu43/oradata/a4pd/a4pd_system01.dbf		   SYSTEM

/dbu43/oradata/a4pd/data_01_02.dbf		   DATA_01

/dbu44/oradata/a4pd/data_02.dbf 		   DATA_02

/dbu44/oradata/a4pd/index_01.dbf		   INDEX_01

/dbu44/oradata/a4pd/rbs01.dbf			   RBS

/dbu45/oradata/a4pd/data_01.dbf 		   DATA_01

/dbu45/oradata/a4pd/temp01.dbf			   TEMP

/dbu46/oradata/a4pd/sample01.dbf		   SAMPLE

I know, there are lots of things wrong already. System is contending with DATA_O1. RBS is contending with INDEX_01.

FROM OUR UNIX ADMIN (I intermixed the mapping to tablespaces, the

                     lines without the `>')

    > All volumes are Raid 0+1 slices. Each line below denotes a set of     > spindles:

    > - dbu09           dbu11
        TEMP            USERS

    > - dbu14           dbu16
        DATA_01         DATA_02

    > - dbu34           dbu35   dbu36   dbu37   dbu39
        SAMPLE          DATA_02 SAMPLE  RBS     DATA_01

    > - dbu42           dbu43   dbu44   dbu45   dbu46
        INDEX_01        SYSTEM  DATA_02 DATA_01 SAMPLE

    > Note further, that dbu09 through dbu16 share a common set of SCSI     > controllers. Similarly for dbu34 through dbu46.

The tables I'm joining (Hash) are found on SAMPLE and DATA_01.

With Raid 0+1 slices, wouldn't parallel make the disks jump all over the place? CPU1 is asked to scan a portion of SAMPLE, CPU2 is asked to scan a portion of DATA_01 ..., So, CPU1 gets a set of rowids to get, it retrieves some data, but CPU2 now takes its turn and its set of rowids are found on the same disk set as CPU1's so the disk head hops over and finds some blocks then CPU1 comes back ...

The actual query that I'm trying to speed up is,

	    INSERT INTO tmp_size_profiles_load
	    (
	        LOCATION_ID,
	        MERCHANDISE_ID,
	        CLIENT_MERCH_ATTRS, -- should be here PROD_TYPE / BRAND_NBR
	        SIZE_SCALE, --> SIZE_RANGE
	        SIZE_GROUP, --> SIZE_GROUP
	        SIZE_NAME,
	        SIZE_WEIGHT
	    )
	    SELECT /*+ index(t3 MH_CLID_LD) */
	        t2.location_id,
	        t3.merchandise_id,
	        DECODE(t1.brand_nbr, null,
	               trim(t1.prod_type), trim(t1.prod_type) || '/' || t1.brand_nbr),
	        trim(t1.size_range),
	        trim(t1.size_group),
	        t1.sz,
	        t1.sls_qty
	    FROM testload.size_profile t1,
	         location_hierarchy_tbl t2,
	         merchandise_hierarchy_tbl t3
	    WHERE  ((t2.client_load_id = TO_CHAR(t1.str_nbr))
	        AND (t2.level_desc = 'STORE'))
	        AND ((t3.client_load_id = TO_CHAR(t1.subdivision))
	        AND (t3.level_desc = 'SUBDIVISION'))

QUERY_PLAN						     
------------------------------------------------------------ 
  INSERT STATEMENT					     
    HASH JOIN						     
      TABLE ACCESS FULL MERCHANDISE_HIERARCHY_TBL	     
      HASH JOIN 					     
	TABLE ACCESS FULL LOCATION_HIERARCHY_TBL	     
	TABLE ACCESS FULL SIZE_PROFILE			     

t1 is on SAMPLE
t2 and t3 are on DATA_01.

Your thoughts, comments, further things to check, jokes, ... are all welcome.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.