28 August, 2013

"Sins" of Software Deployment

Here's an article on The Seven Deadly Sins of Software Deployment.
.
.
.

No comments:

20 August, 2013

Gather Statistics Enhancements in 12c -- 3

Here's how an enhancement to the DBMS_STATS package can generate reports :


SQL> exec dbms_stats.gather_schema_stats('HEMANT');

PL/SQL procedure successfully completed.

SQL> declare
  2  mystatsreport clob;
  3  begin
  4  mystatsreport := dbms_stats.report_stats_operations(
  5  since=>SYSTIMESTAMP-1,
  6  until=>SYSTIMESTAMP,
  7  detail_level=>'TYPICAL',
  8  format=>'TEXT');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> print mystatsreport

MYSTATSREPORT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

SQL> set long 100000
SQL> print mystatsreport

MYSTATSREPORT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| Operation Id | Operation      | Target | Start Time
    | End Time      | Status    | Total Tasks | Successful
 Tasks | Failed Tasks | Active Tasks  |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 623        | gather_schema_stats | HEMANT | 20-AUG-13 11.11.02.927041 PM +08
:00 | 20-AUG-13 11.11.06.505036 PM +08:00 | COMPLETED | 3     | 3
       | 0       | 0       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------


SQL> 
SQL> variable mystatrep2 clob;
SQL> set long 1000000
SQL> begin
  2  :mystatrep2 := dbms_stats.report_stats_operations(
  3  since=>SYSTIMESTAMP-16,
  4  until=>SYSTIMESTAMP-1,
  5  detail_level=>'TYPICAL',
  6  format=>'TEXT');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print mystatrep2
MYSTATREP2
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| Operation Id | Operation        | Target | Start Time
  | End Time      | Status    | Total Tasks | Successfu
l Tasks | Failed Tasks | Active Tasks |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 603        | purge_stats        |        | 11-AUG-13 12.40.53.9264
33 AM  | 11-AUG-13 12.40.54.321760 AM    | COMPLETED | 0      | 0
 | 0        | 0       |
|        |         |        | +08:00
  | +08:00      |        |      |
 |        |       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------
| 583        | gather_database_stats (auto) | AUTO   | 11-AUG-13 12.35.42.5560
98 AM  | 11-AUG-13 12.40.53.926137 AM    | COMPLETED | 806      | 802
 | 4        | 0       |
|        |         |        | +08:00
  | +08:00      |        |      |
 |        |       |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------


SQL>

I can generate reports of Gather_Stats executions.
Note : It doesn't report the"automatic" Gather_Stats that I demonstrated on 06-Aug (for a CTAS) and on 11-Aug (for a Direct Path INSERT into an empty table).

.
.
.

No comments:

14 August, 2013

Common Mistakes Java Developers make when writing SQL

No comments:

11 August, 2013

Gather Statistics Enhancements in 12c -- 2


Building on my previous example, I demonstrate how the IAS (INSERT AS SELECT)  also includes a Gather Table Stats.


00:35:55 SQL> create table obj_list_2 tablespace hemant       
00:36:21   2  as select * from obj_list where 1=2;

Table created.

00:36:29 SQL> select count(*) from obj_list_2;

  COUNT(*)
----------
  0

00:36:40 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:36:53   2  from user_tables 
00:36:58   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:37:03 SQL>

The table got built with 0 (zero) rows.Now, let's populate the table.


00:37:03 SQL> insert into obj_list_2
00:38:42   2  select * from obj_list
00:38:46   3  where owner = 'SYS';

41818 rows created.

00:38:54 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:39:06   2  from user_tables
00:39:08   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:39:11 SQL> commit;

Commit complete.

00:39:39 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:39:52   2  from user_tables
00:39:56   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:39:59 SQL>

A selective insert does not update the statistics. What if we do a full IAS ?


00:39:59 SQL> insert into obj_list_2
00:41:32   2  select * from obj_list;

91465 rows created.

00:41:39 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:41:47   2  from user_tables
00:41:50   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:41:51 SQL> commit;

Commit complete.

00:41:55 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:42:04   2  from user_tables
00:42:07   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:42:09 SQL>

So, a simple IAS will not update statistics. What will cause an IAS to update statistics ?
Let's begin again.


00:43:47 SQL> truncate table obj_list_2;

Table truncated.

00:51:16 SQL> insert /*+ APPEND */ into obj_list_2
00:51:26   2  select * from obj_list;

91465 rows created.

00:51:34 SQL> commit;

Commit complete.

00:51:37 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:51:45   2  from user_tables
00:51:48   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:51

00:51:51 SQL>

Aaha ! We have it now. A Direct Path INSERT into an empty table. This is what updates statistics when an IAS is done.

Notes :
1.  I do not expect DELETE operations to update statistics.
2.  Both the CTAS and IAS do not update Index Statistics.

Let's see the latter case.


00:51:51 SQL> truncate table obj_list_2;

Table truncated.

00:54:35 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:54:50   2  from user_tables
00:54:53   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:51

00:54:55 SQL> create index obj_list_2_ndx tablespace hemant
00:55:22   2  on obj_list_2 (owner);
create index obj_list_2_ndx tablespace hemant
                            *
ERROR at line 1:
ORA-00969: missing ON keyword


00:55:40 SQL> create index obj_list_2_ndx on obj_list_2 (owner) tablespace hemant;

Index created.

00:56:12 SQL> select index_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:56:27   2  from user_indexes
00:56:31   3  order by 1;

INDEX_NAME
--------------------------------------------------------------------------------
  NUM_ROWS TO_CHAR(LAST
---------- ------------
OBJ_LIST_2_NDX
  0 11-AUG 00:56


00:56:34 SQL> col index_name format a30
00:56:40 SQL> 
00:57:00 SQL> insert /*+ APPEND */ into obj_list_2 
00:57:15   2  select * from obj_list;

91465 rows created.

00:57:22 SQL> commit;

Commit complete.

00:57:24 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:57:39   2  from user_tables
00:57:42   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:57

00:57:45 SQL> select index_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:57:59   2  from user_indexes
00:58:03   3  order by 1;

INDEX_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST_2_NDX    0 11-AUG 00:56

00:58:07 SQL>

So, Table Statistics did get updated at the next Direct Path Insert (see the update time as 00:57) but Index statistics did not get updated.
.
.
.
 
1 comment:

06 August, 2013

Gather Statistics Enhancements in 12c -- 1

12c has introduced some enhancements in the gathering and reporting of optimizer statistics.

A CTAS automatically includes statistics on the table (although it may not include column histograms)


SQL> create table obj_list tablespace hemant as select * from dba_objects;


Table created.

SQL> SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
  2  from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
  NUM_ROWS TO_CHAR(LAST
---------- ------------
OBJ_LIST
     91465 06-AUG 22:57


SQL> select count(*) from obj_list;

  COUNT(*)
----------
     91465

SQL>

When the table was created with rows as a CTAS, Oracle automatically gathered statistics on the table.  Column NDVs were also computed, although no histogram was created --- which is the correct behaviour because, at this point without any queries against the table, Oracle doesn't know what histograms are required.


 
SQL> select column_name, num_distinct, histogram
  2  from user_tab_col_statistics
  3  where table_name = 'OBJ_LIST'
  4  /

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    32 NONE
OBJECT_NAME                           53360 NONE
SUBOBJECT_NAME                          284 NONE
OBJECT_ID                             91465 NONE
DATA_OBJECT_ID                         7914 NONE
OBJECT_TYPE                              46 NONE
CREATED                                1034 NONE
LAST_DDL_TIME                          1132 NONE
TIMESTAMP                              1161 NONE
STATUS                                    1 NONE
TEMPORARY                                 2 NONE
GENERATED                                 2 NONE
SECONDARY                                 2 NONE
NAMESPACE                                24 NONE
EDITION_NAME                              0 NONE
SHARING                                   3 NONE
EDITIONABLE                               2 NONE
ORACLE_MAINTAINED                         2 NONE

18 rows selected.

SQL>

So we see NUM_DISTINCT being populated. And it was very fast !
.
.
.
No comments:

05 August, 2013

12c New Features to "Watch Out For"

I think that these 12c New Features are such that their behaviour may create surprises.  Be ready to catch unexpected behaviour.

1.2.4.1 Adaptive Query Optimization
1.2.4.7 Dynamic Statistics
1.2.4.11 Improved Automatic Degree of Parallelism
1.3.3.1 Automatic Data Optimization (ADO)
1.3.3.5 Row-Level Compression Tiering
1.3.3.6 Segment-Level Compression Tiering
1.3.3.7 In-Database Archiving
1.5.4.1 Oracle ASM Disk Scrubbing
1.5.6.5 Real-Time Apply is Default Setting for Data Guard
1.6.1.4 Resource Manager Runaway Query Management
1.6.1.5 Spot ADDM
1.6.2.4 Database Replay Workload Scale-Up and Characterization
1.7.3.1 Oracle Flex Cluster
1.8.3.1 Multi-Process Multi-Threaded Oracle
1.9.2.11 Resource Role Default Privileges

The important documents to read are :
1.  New Features Guide
2.  SQL Tuning Guide
3.  Database Testing Guide
4.  Administrator's Guide section on Managing a Multitenant Environment

.
.
.


No comments:

03 August, 2013

Re-CATALOGing BackupPieces ??

What happens if you re-CATALOG BackupPieces ?  Is it possible to duplicate entries.

This forum thread :  https://forums.oracle.com/thread/2562839  implies so in 11.1.0.7

This is in 11.2.0.2 :

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/app/oracle/flash_
                                                 recovery_area
db_recovery_file_dest_size           big integer 3852M
recovery_parallelism                 integer     0
SQL> 

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       168.59M    DISK        00:00:02     29-JUL-13      
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20130729T082500
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp

  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    621     14089456   03-JUN-13 14098001   16-JUN-13
  1    622     14098001   16-JUN-13 14098016   16-JUN-13
  1    623     14098016   16-JUN-13 14121393   13-JUL-13
  1    624     14121393   13-JUL-13 14149486   13-JUL-13
  1    625     14149486   13-JUL-13 14172383   16-JUL-13
  1    626     14172383   16-JUL-13 14201524   27-JUL-13
  1    627     14201524   27-JUL-13 14226575   28-JUL-13
  1    628     14226575   28-JUL-13 14248805   28-JUL-13
  1    629     14248805   28-JUL-13 14271107   29-JUL-13
  1    630     14271107   29-JUL-13 14271656   29-JUL-13
  1    631     14271656   29-JUL-13 14271770   29-JUL-13
  1    632     14271770   29-JUL-13 14271813   29-JUL-13
  1    633     14271813   29-JUL-13 14271822   29-JUL-13
  1    634     14271822   29-JUL-13 14271905   29-JUL-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    2.13G      DISK        00:01:58     29-JUL-13      
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20130729T082504
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_nnndf_TAG20130729T082504_8zf2bj83_.bkp
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/system01.dbf
  2       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/users01.dbf
  5       Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  11      Full 14271579   29-JUL-13 /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    9.36M      DISK        00:00:03     29-JUL-13      
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20130729T082504
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_ncsnf_TAG20130729T082504_8zf2gk3r_.bkp
  SPFILE Included: Modification time: 29-JUL-13
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 14283558     Ckp time: 29-JUL-13

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11      334.75M    DISK        00:00:05     29-JUL-13      
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20130729T082717
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082717_8zf2govj_.bkp

  List of Archived Logs in backup set 11
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    635     14271905   29-JUL-13 14273457   29-JUL-13
  1    636     14273457   29-JUL-13 14275520   29-JUL-13
  1    637     14275520   29-JUL-13 14276886   29-JUL-13
  1    638     14276886   29-JUL-13 14278245   29-JUL-13
  1    639     14278245   29-JUL-13 14279601   29-JUL-13
  1    640     14279601   29-JUL-13 14281402   29-JUL-13
  1    641     14281402   29-JUL-13 14283542   29-JUL-13
  1    642     14283542   29-JUL-13 14283558   29-JUL-13
  1    643     14283558   29-JUL-13 14283570   29-JUL-13

RMAN>

Here we see that Backups created on 29-Julyl, comprising of BackupSets 8 (ArchiveLogs), 9 (DatabaseFiles), 10 (SPFILE and Controlfile) and 11 (ArchiveLogs) are currently present in the FRA (/home/oracle/app/oracle/flash_recovery_area.

Next, I run a CATALOG command :

RMAN> catalog start with '/home/oracle/app/oracle/flash_recovery_area';

searching for all files that match the pattern /home/oracle/app/oracle/flash_recovery_area
no files found to be unknown to the database

RMAN> 
RMAN> delete backup tag 'TAG20130729T082500';

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
8       8       1   1   AVAILABLE   DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_07_29/o1_mf_annnn_TAG20130729T082500_8zf2bdkc_.bkp

Do you really want to delete the above objects (enter YES or NO)? NO

RMAN>

Oracle finds them to be "known" to the database and refuses to re-CATALOG them.  Of course, if I were to move or copy them to a different location, Oracle would CATALOG them as they would have a different "name" (the directory path being different).

Is there any situation where the backup pieces in the same location would appear duplicated in a LIST BACKUP listing ?  That they would have been re-CATALOGed ?

.
.
.


1 comment:
Subscribe to: Posts (Atom)