Create Histogram without having to gather Table Stats

There was a question on forums : "Is there a way to ONLY generate histograms? not touching the rest of the statistics that are not histograms".

If you use DBMS_STATS.GATHER_TABLE_STATS( ... method_opt='FOR COLUMNS x SIZE n'); Table Stats are also gathered and updated.  Can this be avoided ?

Here's a test case :

00:57:48 SQL> create table ST_TEST as select object_id as id, owner as col_1, object_name as col_2
00:57:56   2  from dba_objects;

Table created.

00:58:05 SQL> exec dbms_stats.gather_table_stats('','ST_TEST');

PL/SQL procedure successfully completed.

00:58:13 SQL> select num_rows, last_analyzed from user_tables where table_name = 'ST_TEST';

  NUM_ROWS LAST_ANALYZED
---------- ------------------
     76713 26-APR-12 00:58:13

00:58:24 SQL> delete ST_TEST where col_2 like 'DBA_%';     

1650 rows deleted.

00:58:48 SQL> commit;

Commit complete.

00:58:52 SQL> !sleep 30

00:59:30 SQL> exec dbms_stats.gather_table_stats('','ST_TEST',method_opt=>'FOR COLUMNS COL_1 SIZE 250');

PL/SQL procedure successfully completed.

01:00:12 SQL> select num_rows, last_analyzed from user_tables where table_name = 'ST_TEST';

  NUM_ROWS LAST_ANALYZED
---------- ------------------
     75063 26-APR-12 01:00:12

01:00:20 SQL>

The DBMS_STATS call with METHOD_OPT also updated Table level statistics.  Can you avoid having to gather Table level statistics ?  What about column statistics and histograms on other columns in the table ?  Are they recomputed and updated as well ?  Can you avoid the CPU overheads for these actions ?

.
.
.

AIOUG Sangam '12 -- CFP

The Call for Papers for the AIOUG Sangam '12 is out.  This year the focus/themes will be on
Performance Tuning, Optimization and Upgrades.

.
.
.

When is an ArchiveLog created ?

Here's something about the V$ARCHIVED_LOG view.

I start with a few archivelogs today :

21:01:01 SQL> get Latest_ArchiveLogs
  1  select sequence#, substr(name,1,96),creator, to_char(first_time,'DD-MON HH24:MI'), to_char(completion_time,'DD-MON HH24:MI')
  2  from v$archived_log
  3  where first_time > sysdate-1
  4* order by 1
21:01:07 SQL> /

 SEQUENCE#
----------
SUBSTR(NAME,1,96)
--------------------------------------------------------------------------------------------------------------------------------
CREATOR TO_CHAR(FIRST_TIME,'D TO_CHAR(COMPLETION_TI
------- --------------------- ---------------------
         6
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_6_7rh975jt_.arc
ARCH    12-APR 20:51          12-APR 21:00

         7
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_7_7rh97rjs_.arc
ARCH    12-APR 21:00          12-APR 21:00

         8
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_8_7rh97vv7_.arc
ARCH    12-APR 21:00          12-APR 21:00

         9
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_9_7rh97vwp_.arc
ARCH    12-APR 21:00          12-APR 21:00


21:01:09 SQL> 

Next, I move and re-register them :

[oracle@localhost 2012_04_12]$ pwd
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12
[oracle@localhost 2012_04_12]$ ls -ltr
total 11072
-rw-rw---- 1 oracle oracle 8797696 Apr 12 20:51 o1_mf_1_5_7rh8orgp_.arc
-rw-rw---- 1 oracle oracle 2498560 Apr 12 21:00 o1_mf_1_6_7rh975jt_.arc
-rw-rw---- 1 oracle oracle   11264 Apr 12 21:00 o1_mf_1_7_7rh97rjs_.arc
-rw-rw---- 1 oracle oracle    1024 Apr 12 21:00 o1_mf_1_9_7rh97vwp_.arc
-rw-rw---- 1 oracle oracle    4096 Apr 12 21:00 o1_mf_1_8_7rh97vv7_.arc
[oracle@localhost 2012_04_12]$ rm *1_5_*
[oracle@localhost 2012_04_12]$ pwd
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12
[oracle@localhost 2012_04_12]$ ls -l
total 2464
-rw-rw---- 1 oracle oracle 2498560 Apr 12 21:00 o1_mf_1_6_7rh975jt_.arc
-rw-rw---- 1 oracle oracle   11264 Apr 12 21:00 o1_mf_1_7_7rh97rjs_.arc
-rw-rw---- 1 oracle oracle    4096 Apr 12 21:00 o1_mf_1_8_7rh97vv7_.arc
-rw-rw---- 1 oracle oracle    1024 Apr 12 21:00 o1_mf_1_9_7rh97vwp_.arc
[oracle@localhost 2012_04_12]$ mkdir /tmp/ARCHLOGS
[oracle@localhost 2012_04_12]$ mv *arc /tmp/ARCHLOGS/
[oracle@localhost 2012_04_12]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 12 21:03:15 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
validation failed for archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_5_7rh8orgp_.arc RECID=15 STAMP=780439880
validation failed for archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_6_7rh975jt_.arc RECID=16 STAMP=780440437
validation failed for archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_7_7rh97rjs_.arc RECID=17 STAMP=780440456
validation failed for archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_8_7rh97vv7_.arc RECID=19 STAMP=780440459
validation failed for archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_9_7rh97vwp_.arc RECID=18 STAMP=780440459
Crosschecked 5 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
15      1    5       X 04-APR-12
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_5_7rh8orgp_.arc

16      1    6       X 12-APR-12
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_6_7rh975jt_.arc

17      1    7       X 12-APR-12
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_7_7rh97rjs_.arc

19      1    8       X 12-APR-12
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_8_7rh97vv7_.arc

18      1    9       X 12-APR-12
        Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_9_7rh97vwp_.arc


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_5_7rh8orgp_.arc RECID=15 STAMP=780439880
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_6_7rh975jt_.arc RECID=16 STAMP=780440437
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_7_7rh97rjs_.arc RECID=17 STAMP=780440456
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_8_7rh97vv7_.arc RECID=19 STAMP=780440459
deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_04_12/o1_mf_1_9_7rh97vwp_.arc RECID=18 STAMP=780440459
Deleted 5 EXPIRED objects


RMAN> 
RMAN> exit


Recovery Manager complete.
[oracle@localhost 2012_04_12]$ sleep 120
[oracle@localhost 2012_04_12]$ 
[oracle@localhost 2012_04_12]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 12 21:06:18 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> catalog start with '/tmp/ARCHLOGS';

using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/ARCHLOGS

List of Files Unknown to the Database
=====================================
File Name: /tmp/ARCHLOGS/o1_mf_1_9_7rh97vwp_.arc
File Name: /tmp/ARCHLOGS/o1_mf_1_6_7rh975jt_.arc
File Name: /tmp/ARCHLOGS/o1_mf_1_7_7rh97rjs_.arc
File Name: /tmp/ARCHLOGS/o1_mf_1_8_7rh97vv7_.arc

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/ARCHLOGS/o1_mf_1_9_7rh97vwp_.arc
File Name: /tmp/ARCHLOGS/o1_mf_1_6_7rh975jt_.arc
File Name: /tmp/ARCHLOGS/o1_mf_1_7_7rh97rjs_.arc
File Name: /tmp/ARCHLOGS/o1_mf_1_8_7rh97vv7_.arc

RMAN> 

I have relocated and re-cataloged the files. Now, I query them again in V$ARCHIVED_LOG :

21:08:32 SQL> l
  1  select sequence#, substr(name,1,96),creator, to_char(first_time,'DD-MON HH24:MI'), to_char(completion_time,'DD-MON HH24:MI')
  2  from v$archived_log
  3  where first_time > sysdate-1
  4  and name is not null
  5* order by 1
21:08:33 SQL> /

 SEQUENCE#
----------
SUBSTR(NAME,1,96)
--------------------------------------------------------------------------------------------------------------------------------
CREATOR TO_CHAR(FIRST_TIME,'D TO_CHAR(COMPLETION_TI
------- --------------------- ---------------------
         6
/tmp/ARCHLOGS/o1_mf_1_6_7rh975jt_.arc
RMAN    12-APR 20:51          12-APR 21:06

         7
/tmp/ARCHLOGS/o1_mf_1_7_7rh97rjs_.arc
RMAN    12-APR 21:00          12-APR 21:06

         8
/tmp/ARCHLOGS/o1_mf_1_8_7rh97vv7_.arc
RMAN    12-APR 21:00          12-APR 21:06

         9
/tmp/ARCHLOGS/o1_mf_1_9_7rh97vwp_.arc
RMAN    12-APR 21:00          12-APR 21:06


21:08:33 SQL> 
21:09:23 SQL> !ls -l /tmp/ARCHLOGS
total 2464
-rw-rw---- 1 oracle oracle 2498560 Apr 12 21:00 o1_mf_1_6_7rh975jt_.arc
-rw-rw---- 1 oracle oracle   11264 Apr 12 21:00 o1_mf_1_7_7rh97rjs_.arc
-rw-rw---- 1 oracle oracle    4096 Apr 12 21:00 o1_mf_1_8_7rh97vv7_.arc
-rw-rw---- 1 oracle oracle    1024 Apr 12 21:00 o1_mf_1_9_7rh97vwp_.arc

21:09:26 SQL> 

What has changed ? The COMPLETION_TIME has changed from 21:00 to 21:06. Apparently, this is NOT read from the ArchiveLog file. It is not read from the OS timestamp either.
 Today's question :  How does Oracle set the COMPLETION_TIME that we see in V$ARCHIVED_LOG ?  What are the implications of the change we see here ?  (Think about Standby Databases and Cloning).

.
.
.