parallel query & physical reads direct

After an intermezzo in Munich that left me with no time for posting, I am now in Bucharest (Romania) for an 11g Data Warehouse Administration course. The students are all Oracle Internals, which is almost a guarantee for fun and a nice atmosphere. The most difficult thing about this course was to get into the Oracle building in Bucharest at all – the security staff is very sharp here…

To get technical: Did you know that if you are doing a parallel query, it is processed with direct reads under circumvention of the Database Buffer Cache? Here is how you can investigate this:

sqlplus sh/sh
SQL> select /*+ parallel (sales,2) */ sum(amount_sold) from sales;
SQL> select name,sum(value)
     from v$sesstat
     natural join v$statname
     natural join v$session
     where username='SH'
     and name like '%physical reads%'
     group by name;
About these ads

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,745 other followers

%d bloggers like this: