RE: Question - any parameter or hint to FORCE Physical Read (bypass buffer cache)

For my test, I'm doing a simple select count(last_column_name) from tableA - it has a FTS and then a SORT AGGREGATE. I would assume the SORT wouldn't cause the reads to come from cache but perhaps I'm wrong.

The Parallel Hint does do sequential reads on subsequent executions but I wonder if I'll get comparable results from node to node as that will use additional cpu cycles - but won't hurt to try.

Chris

From: Alex Fatkulin [mailto:afatkulin_at_gmail.com] Sent: Monday, April 22, 2013 3:57 PM
To: Taylor Christopher - Nashville
Cc: tanel_at_tanelpoder.com; ORACLE-L
Subject: Re: Question - any parameter or hint to FORCE Physical Read (bypass buffer cache)

Working with "_serial_direct_read"=true is harder because if direct path reads are not happening you will be left guessing whether it's the operation in the plan which doesn't support it or some of the variables used to determine whether serial direct path reads should happen or not are not adding up.

What you can do is:

1. Run your full table scan in parallel, PQ slaves will do the FTS via direct path reads since there is no in-memory PQ in 10.2
2. Allocate a small keep/recycle pool (I assume you don't want to set the default cache too low for the same reasons you don't want to flush it) and put the table in there.

On Mon, Apr 22, 2013 at 4:49 PM, <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> wrote: Forgot a piece - Yes I knew about the FULL requirements on table/index scans and the query is using a simple full tablescan.

Chris

From: Taylor Christopher - Nashville
Sent: Monday, April 22, 2013 3:48 PM
To: 'Tanel Poder'; Alex Fatkulin
Cc: ORACLE-L
Subject: RE: Question - any parameter or hint to FORCE Physical Read (bypass buffer cache)

The amount of IOs per execution I haven't checked. I should/will check that.

The plans for the query I'm interested in are really simple and are the same on all 3 nodes.

Chris

--

Alex Fatkulin,
http://afatkulin.blogspot.com

Enkitec,
http://www.enkitec.com

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 22 2013 - 23:10:37 CEST