DBMS_STATS And Direct Path Operations

Continuing my theme of (almost pointless) testing that DBMS_STATS does as you would hope, this post proves the answer to the question below that was posed at my work place.

Do direct path loads count as modifications for DBMS_STATS or do they bypass it?

My initial instinct was “of course it catches them” but I stopped myself. I’ve never actually seen it so it would be improper to say it out loud. Better dash home and test it (“dash” = 1 month later in this case).

This test is in from Database 11.2 and has been verified on 10.2 also. It has also been tested and tweaked thanks to Surachart’s input – see the first comment on the post. I thought I’d unearthed an interesting difference between 10g and 11g – it turns out I’d only discovered that ARCHIVELOG is not the same as NOARCHIVELOG – doh!

Create a test table, populate it and check that the modifications are caught.

DROP TABLE tab1;
CREATE TABLE tab1
(	col1 NUMBER
,	col2 VARCHAR2(1));

INSERT INTO tab1
SELECT	ROWNUM
,	'Y'
FROM	dual
CONNECT BY ROWNUM < 1000;
COMMIT;

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

column table_name format a10
select table_name,INSERTS,UPDATES,DELETES 
from USER_TAB_MODIFICATIONS
where table_name = 'TAB1';

TABLE_NAME    INSERTS    UPDATES    DELETES
---------- ---------- ---------- ----------
TAB1              999          0          0

All good. Now let’s try a direct path load.

ALTER TABLE tab1 NOLOGGING;

INSERT /*+ APPEND */ INTO tab1
SELECT	ROWNUM
,	'Y'
FROM	dual
CONNECT BY ROWNUM < 1000;
COMMIT;

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

column table_name format a10
select table_name,INSERTS,UPDATES,DELETES 
from USER_TAB_MODIFICATIONS
where table_name = 'TAB1';

TABLE_NAME    INSERTS    UPDATES    DELETES
---------- ---------- ---------- ----------
TAB1             1998          0          0

Looking good but the self doubter in me is asking:

How can you prove that was a direct path load?

Like this hopefully.

sqlplus /

ALTER TABLE tab1 LOGGING;

INSERT INTO tab1
SELECT	ROWNUM
,	'Y'
FROM	dual
CONNECT BY ROWNUM < 1000;
COMMIT;

@mystat
Enter value for namefilter: redo size

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             19712

And…

sqlplus /

ALTER TABLE tab1 NOLOGGING;

INSERT /*+ APPEND */ INTO tab1
SELECT	ROWNUM
,	'Y'
FROM	dual
CONNECT BY ROWNUM < 1000;
COMMIT;

@mystat
Enter value for snamefilter: redo size

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size for direct writes                                              52
redo size                                                              4500

All good, but…

What about DDL?

…shouts my annoying inner self.

TRUNCATE TABLE tab1;

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

column table_name format a10
select table_name,INSERTS,UPDATES,DELETES 
from USER_TAB_MODIFICATIONS
where table_name = 'TAB1';

TABLE_NAME    INSERTS    UPDATES    DELETES
---------- ---------- ---------- ----------
TAB1             3996          0       3996

OK, so both my annoying inner voice and I am happy that direct writes are indeed caught by DBMS_STATS. I know this is as expected but heck, sometimes it’s good to see it in black and white.

About these ads

3 thoughts on “DBMS_STATS And Direct Path Operations

  1. Oracle 11gR2 ? But my database differ (on database archivelog mode):
    SQL> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
    PL/SQL Release 11.2.0.2.0 – Production
    CORE 11.2.0.2.0 Production
    TNS for Linux: Version 11.2.0.2.0 – Production
    NLSRTL Version 11.2.0.2.0 – Production

    SQL> @new

    OLD_VALUE
    ———-
    0

    SQL> insert into t_log select * from all_objects;

    88090 rows created.

    SQL> @diff
    old 1: select (value – &OLD_VALUE) OLD_VALUE
    new 1: select (value – 0) OLD_VALUE

    OLD_VALUE
    ———-
    10354540

    SQL> @new

    OLD_VALUE
    ———-
    0

    SQL> insert /*+ APPEND */ into t_log select * from all_objects;

    88090 rows created.

    SQL> @diff
    old 1: select (value – &OLD_VALUE) OLD_VALUE
    new 1: select (value – 0) OLD_VALUE

    OLD_VALUE
    ———-
    10585740

    THENNNNN alter table … nologging:

    SQL> alter table t_log nologging;

    Table altered.

    SQL> @new

    OLD_VALUE
    ———-
    0

    SQL> insert /*+ APPEND */ into t_log select * from all_objects;

    88090 rows created.

    SQL> @diff
    old 1: select (value – &OLD_VALUE) OLD_VALUE
    new 1: select (value – 0) OLD_VALUE

    OLD_VALUE
    ———-
    104208

    • Thank you for correcting me Surachart. The reason I saw differences between 10g and 11g was my 11g database was in NOARCHIVELOG mode.

      I have corrected the post now and put a comment near the top pointing this out.

      So thanks again for taking the time to read the post and double check my findings – much appreciated :-)

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