Re: Environment for Parallel Query?

First, get rid of the index hint. It is not used anyway according to the execution plan.

Second, increase hash_area_size to <available RAM>/<no of used processes> with "alter session ..." because you are obviously using hash joins according to your execution plan.

Third, increase db_file_multiblock_read_count to 10000 and check the used value
with "show parameter db_file_multiblock_read_count". Assume the session reports
128. Set db_file_direct_io_count to the same value and bounce the instance.

Fourth, use forcedirectio mounts for your filesystems that contain database parts (check v$controlfile, v$logfile, dba_data_files and dba_temp_files) for two reasons: it will reduce the number of file system buffers (so you have more RAM left), and it will leave the caching to Oracle which is more efficient than unix' file buffering.

Check with perfstat what is the most important wait event. Check with perfstat the hit ratios.

What gives?

Martin

Galen Boyer wrote:
>
> 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.
Received on Thu Mar 28 2002 - 10:28:32 CST