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