Re: Environment for Parallel Query?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 28 Mar 2002 23:06:08 -0600
Message-ID: <uadsru760.fsf@rcn.com>

On Thu, 28 Mar 2002, Martin.Haltmayer_at_d2mail.de wrote:
> First, get rid of the index hint. It is not used anyway according to
> the execution plan.

Arghh!!! That was left there by the previous developer. I got the plan I wanted and didn't worry about the hint. It could bite me later though. Thanks. I'll get rid of it.

> 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.

I do this. I set it to 100M though. The DBA doesn't want anymore than this. We have 4G of ram on the box with 4 cpus, but 5 instances of Oracle running. :-(

> Third, increase db_file_multiblock_read_count to 10000

Hm... Let me try this one out. db_file_multiblock_read_count was 64. Let me try it on a count(*). (I would assume that this parameter would help with these as well, or must I bounce the instance? The db_file_multiblock_read_count parameter can be altered at the session level)

        SQL> select count(*) from testload.oov;

          COUNT(*)
        ----------
          92691435

        Elapsed: 00:02:161.43
        SQL> alter session set DB_FILE_MULTIBLOCK_READ_COUNT = 100000;

        Session altered.

        Elapsed: 00:00:00.60
        SQL> select count(*) from testload.oov;

          COUNT(*)
        ----------
          92691435

        Elapsed: 00:02:163.46

Hm... No change there.

I'll try the an insert similar to the one I posted earlier, but one which completes.

    QUERY_PLAN PARR PART CARDINALITY COST

• ---- ---- ----------- ---------- INSERT STATEMENT N N 7 3179905 HASH JOIN N N 7 3179905 TABLE ACCESS FULL PERIODS_TBL N N 2 1 HASH JOIN N N 34181 3179903 TABLE ACCESS FULL PERIODS_TBL N N 2 1 HASH JOIN N N 170902645 3179000 TABLE ACCESS FULL PERIODS_TBL N N 2 1 HASH JOIN N N 8.5451E+11 385412 TABLE ACCESS FULL LOCATION_HIERARCHY_TBL N N 1239 1 HASH JOIN N N 6.8968E+10 12632 TABLE ACCESS FULL VOLUME_GRPS N N 1372260 180 TABLE ACCESS FULL MERCHANDISE_HIERARCHY_TBL N N 5025868 2713

SQL> insert into tmp_volume_grps_load(

               [...]
		)
	select /*+ ordered use_hash(t4) use_hash(t5) use_hash(t6)  */
               [...]
	from   	 testload.volume_grps		t1
		,merchandise_hierarchy_tbl 	t2
		,location_hierarchy_tbl		t3
		,periods_tbl			t4 -- period_id
		,periods_tbl			t5 -- from_period_id
		,periods_tbl			t6 -- to_period_id
        where   [...]

506452 rows created.

Elapsed: 00:25:1539.73

Hm... Now, here's the execution with smaller DB_FILE_MULTIBLOCK_READ_COUNT alter session set DB_FILE_MULTIBLOCK_READ_COUNT = 128;

Session altered.

Elapsed: 00:00:00.40
SQL> truncate table tmp_volume_grps_load;

Table truncated.

Elapsed: 00:00:37.35
SQL> insert into tmp_volume_grps_load(

                 [...]
		)
	select /*+ ordered use_hash(t4) use_hash(t5) use_hash(t6)  */
               [...]
	from   	 testload.volume_grps		t1
		,merchandise_hierarchy_tbl 	t2
		,location_hierarchy_tbl		t3
		,periods_tbl			t4 -- period_id
		,periods_tbl			t5 -- from_period_id
		,periods_tbl			t6 -- to_period_id
        where   [...]

506452 rows created.

Elapsed: 00:22:1360.26

1539 - 1360 = 179 seconds.

This doesn't seem all the much different.

Am I not doing everything I need to take advantage of this parameter?

> 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.

The documentation on this parameter says that this affects backup, restore, direct path read and write functions. Is a full scan considered a direct path read?

> Fourth, use (forcedirectio,noatime) mounts for your filesystems that
> contain database parts

I don't have any control over this.

> (check v$controlfile, v$logfile, dba_data_files and dba_temp_files)

I don't understand how to get at the mounting information from these tables.

> 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.

On a FTS, do I need buffering? Don't I need the fastest throughput from disk to memory?

> The noatime will avoid touching the inode for update of access time
> when just reading from the datafile.

Hm... Is this analogous to setting a tablespace read-only and therefore Oracle doesn't need to "check" if there are any dirty buffers?

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

I'll give this one a try.

> What gives?

I don't know. Just seems that the instance is pretty overloaded and was never thought about as a place to try any kind of performance tuning/analysis.

Thanks.

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

Received on Thu Mar 28 2002 - 23:06:08 CST