26 April, 2012

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 ?

.
.
.

23 April, 2012

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.

.
.
.

13 April, 2012

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).

.
.
.

05 April, 2012

Primary Key name appears to be different

Here is a quick post on a scenario where Oracle attempts to validate a Primary Key (index present) when creating a Materialized View and apparently reports a different name for the constraint !. The Primary Key and Indexis called "TARGET_TABLE" but the ORA-2437 error reports the error as being against "TARGET_TABLE1" -- with a "1" seemingly appended.


SQL> drop table target_table purge;

Table dropped.

SQL> create table target_table (column_1 number not null, column_2 varchar2(30), column_3 varchar2(300));

Table created.

SQL> 
SQL> insert into target_table
  2  select rownum, dbms_random.string('X',8),dbms_random.string('U',30)
  3  from dual connect by level < 101;

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from target_table;

  COUNT(*)
----------
       100

SQL> 
SQL> create index target_table on target_table(column_1);

Index created.

SQL> insert into target_table values (5,'ABC','XYZXZYZ');

1 row created.

SQL> 
SQL> alter table target_table add constraint target_table primary key (column_1) enable novalidate ;

Table altered.

SQL> 
SQL> select index_name from user_indexes where table_name = 'TARGET_TABLE';

INDEX_NAME
------------------------------
TARGET_TABLE

SQL> 
SQL> select constraint_name, index_name
  2  from user_constraints
  3  where table_name = 'TARGET_TABLE'
  4  and constraint_type in ('U','P');

CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------
TARGET_TABLE                   TARGET_TABLE

SQL> 
SQL> create materialized view log on target_table;

Materialized view log created.

SQL> 
SQL> create materialized view tt_mv
  2  refresh fast on demand
  3  as select column_1, column_2
  4  from target_table
  5  /
from target_table
     *
ERROR at line 4:
ORA-02437: cannot validate (HEMANT.TARGET_TABLE1) - primary key violated


SQL> 
SQL> 
SQL> select count(*) from target_table where column_1 = 5;

  COUNT(*)
----------
         2

SQL> 
Today's question : Can you create another test case where a "1" appears to be appended ?
.
.
.

01 April, 2012

TOO_MANY_ROWS and Variable Assignment

UPDATE : Also see Jeffrey Kemp's blog post on the same topic, earlier in 2008.

When you have a cursor that is assigning a return value to a variable, you expect the cursor to return a single row.  If it returns more than one row, the resulting error is a TOO_MANY_ROWS error.  This can be handled as an Exception.
However, this blog post is about what might be happening to the variable that you assign the fetched value to.

In this demo, I use a "CUSTOMER" table and a VALIDATE_CUSTOMER procedure :


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> 
SQL> drop table CUSTOMER purge;

Table dropped.

SQL> 
SQL> create table CUSTOMER
  2  (cust_id  number,  sales_mgr_id number, cust_name varchar2(50), cust_start_date date, status_flag varchar2(1));

Table created.

SQL> 
SQL> insert into CUSTOMER values (1,1,'USA-NY Customer',to_date('01-JAN-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (2,1,'USA-LA Customer',to_date('01-JUL-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (3,2,'GB-LN Customer',to_date('01-SEP-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (4,3,'FR-PR Customer',to_date('01-OCT-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> -- this cust_id 4 will be replacing the current one from 01-JAN-2013
SQL> -- unfortunately, the date entered is 01-JAN-2011
SQL> insert into CUSTOMER values (4,4,'JP-TY Customer',to_date('01-JAN-2011','DD-MON-YYYY'),'N');

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> create or replace procedure VALIDATE_CUSTOMER (in_cust_id in number, out_cust_status out varchar2)
  2  as
  3  -- initialize status
  4  -- U for Unknown
  5  l_cust_flag  varchar2(1) := 'U';
  6  begin
  7  select status_flag into l_cust_flag
  8  from  CUSTOMER
  9  where cust_id = in_cust_id
 10  and cust_start_date <= SYSDATE;
 11  out_cust_status := l_cust_flag;
 12  dbms_output.put_line('Flag : ' || l_cust_flag);
 13  dbms_output.put_line('Cust Status Returned : ' || out_cust_status);
 14  exception
 15  when others then
 16  dbms_output.put_line('Flag : ' || l_cust_flag);
 17  dbms_output.put_line('Exception  !! Cust Status Returned : ' || out_cust_status);
 18  end;
 19  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 
So, I have a table with 2 rows for CUST_ID=4.  How will the procedure handle it ?
I have deliberately added dbms_output.put_line to show the values that the procedure "sees".

I run with three test CUST_IDs  : 1  (existant and valid),   9  (not existant),  4  ("too_many_rows").

SQL> SET SERVEROUTPUT ON
SQL> 
SQL> -- declare a variable to get the return value in sqlplus
SQL> 
SQL> -- a valid customer
SQL> variable cust_status_1 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(1,:cust_status_1);
Flag : Y
Cust Status Returned : Y

PL/SQL procedure successfully completed.

SQL> print :cust_status_1

CUST_STATUS_1
--------------------------------
Y

SQL> 
SQL> -- a non-existent customer
SQL> variable cust_status_9 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(9,:cust_status_9);
Flag : U
Exception  !! Cust Status Returned :

PL/SQL procedure successfully completed.

SQL> print :cust_status_9

CUST_STATUS_9
--------------------------------------------------------------------------------


SQL> 
SQL> -- a cust_id with too_many_rows
SQL> variable cust_status_4 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(4,:cust_status_4);
Flag : Y
Exception  !! Cust Status Returned :

PL/SQL procedure successfully completed.

SQL> print :cust_status_4

CUST_STATUS_4
--------------------------------------------------------------------------------


SQL> 
The "Flag" and returned (output out_cust_status) values are correct for CUST_IDs 1 and 9. (For 9, the Flag is the initialized value of "U" because the cursor did not overwrite it).
However, for CUST_ID 4, although the procedure raises an EXCEPTION and returns no value, the Flag that is present *did* get reset from the initialized value of "U" to "Y" !! Apparently, when a cursor returns a row, it does assign the return value to the designated variable. If the cursor then returns *another* row, it raises an Exception but does not clear the value that was already assigned from the first row.

.
.
.