db_multiblock_read_count – to be or not to be?

Sometimes you wanna to use things with default options…and sometimes the default is not good enough.

We all know that db_file_multiblock_read_count is bumped by default on startup, usually you get 128 blocks per read. Why?

From Oracle documentation, this value is high, but CBO will not favor full tables scans.

“Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.”

Okay. But Why? From a 10053 trace within a session, you can see that the CBO will compute db_file_multiblock_read_count always as “8”…even if you see 128 in spfile.

CBO you always use 8, if you dont set db_file_multiblock_read_count explicitly. How to know if you set db_file_multiblock_read_count as 128? Use this:

SELECT nvl(value,’*NULL*’) AS value
FROM v$spparameter
WHERE name = ‘db_file_multiblock_read_count’;

If this return *NULL*, you have not set db_file_multiblock_read_count, Oracle is showing you 128, but CBO uses 8. Sad no?

You all read the documentation and know the effects of bumping db_file_multiblock_read_count high. Full table scans will happen more often and as a result, in a OLTP system, usually you will get a call from your boss (hehe)

But there is a way to set correctly the db_file_multiblock_read_count…by testing…for example, take a look on the block below. Its silly, I know, but it can give you a magic value for this parameter.

set serveroutput on
DECLARE
l_count PLS_INTEGER;
l_time PLS_INTEGER;
l_starting_time PLS_INTEGER;
l_ending_time PLS_INTEGER;
BEGIN
dbms_output.put_line(‘dbfmbrc seconds’);
FOR l_dbfmbrc IN 1..64
LOOP
EXECUTE IMMEDIATE ‘ALTER SESSION SET db_file_multiblock_read_count=’||l_dbfmbrc;
EXECUTE IMMEDIATE ‘ALTER system flush buffer_cache’;
EXECUTE IMMEDIATE ‘ALTER session disable parallel dml’;
l_starting_time := dbms_utility.get_time();
SELECT /*+ full(t) */ count(*) INTO l_count FROM big_table t;
l_ending_time := dbms_utility.get_time();
l_time := round((l_ending_time-l_starting_time)/100);
dbms_output.put_line(l_dbfmbrc||’ ‘||l_time);
END LOOP;
END;
/

(Thanks to Chris Antognini, you’re great)

With this block (be carefull, it will take 60-90 minutes depending of the big table’s size, as a advice, use a 5GB table), you will have values, plot a graph and see the behavior. Dont choose a high value for OLTP.

See you around!

Advertisements
This entry was posted in Performance, Troubleshooting and tagged , , . Bookmark the permalink.

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