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;

  1. Leave a comment

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: