Useful Oracle Youtube videos

This will be a periodically updated list of useful Oracle Youtube videos.
(dates are the dates I added the videos to this list)

17-Mar-13   Jonathan Lewis -- Pessimist Exadata

17-Mar-13   Connor McDonald -- RAC Upgrade Nightmare

19-Mar-13  Cary Millsap (with Kerry Osborne)  --  My Perspective on Exadata

24-Mar-13  Tim Gorman  --  Data Warehousing

13-Apr-13  Tom Kyte  --  What are we still doing wrong ?

03-Jun-13  How to Download and Install Virtual Box

16-Jun-13  Gwen (Chen) Shapira  --  Hadoop and Oracle Database

27-Jun-13  Yuri Velikanov  --  How to become an Expert -- Part 1  (also see the next part - Part 2)

28-Jun-13  Tom Kyte on 12c

18-Jul-13  Tom Kyte on the Top 12 Features of 12c

21-Jul-13  Kellyn Pot'vin on Plugging In Oracle Database 12c Databases

01-Jan-14  Christo Kutrovsky on Oracle, Memory and Linux

04-Jan-14  Kellyn Pot'vin on Performance Tuning with ASH and AWR Data

21-Oct-14  Jonathan Lewis -- Selectivity

21-Oct-14  Christian Antognini -- Indexes

03-Jan-15  Jonathan Lewis  -- Parallel Execution Plans

03-Jan-15  Kyle Haily -- Visual SQL Tuning

06-Jun-15  Toon Koppelaars  -- Putting Application Logic into the Database (versus in the Application Server -- Java ?)

12-Oct-15  Ric Van Dyke on Histograms

26-Oct-15  Kerry Osborne  on 12c Adaptive Optimization

29-Oct-15 Jonathan Lewis -- Calculating Selectivity

.
.

Segment Size of a Partition (11.2.0.2 and above)

In 11.2.0.2, Oracle introduced a change to the default size of Partition Segment.
According to Support Note "Initial Extent Size of a Partition Changed To 8MB From 64KB After Upgrade to 11.2.0.2 [ID 1295484.1]",  the INITIAL Extent of a Partition is now 8MB.  Furthermore, Deferred Segment Creation also applies to Partitions.

Thus, when you initially create a Partitioned Table, (and DEFERRED_SEGMENT_CREATION is left at the default value of TRUE), the Partitions are segmentless.  As soon as you populate a Partition, it is created as a Segment with an INITIAL of 8MB and NEXT of 1MB.

Here is a simple demo :

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 9 16:45:49 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: hemant/hemant
ERROR:
ORA-28002: the password will expire within 7 days



Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> 
SQL> create tablespace PARTITION_TEST ;      

Tablespace created.

SQL> 
SQL> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name = 'PARTITION_TEST'
  5  /

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

SQL> 
SQL> l
  1  create table a_partitioned_table
  2  (id   number not null,
  3  data_column varchar2(20)
  4  )
  5  partition by range (id)
  6  (
  7  partition P_ID_100 values less than ('101') tablespace PARTITION_TEST,
  8  partition P_ID_200 values less than ('201') tablespace PARTITION_TEST,
  9  partition P_MAX values less than (MAXVALUE) tablespace PARTITION_TEST
 10* )
SQL> /

Table created.

SQL> 
SQL> set pages600
SQL> set long 32767
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 

Note how the two Partitions are created with "SEGMENT CREATION DEFERRED" and there is no Storage allocated to them yet. Let's look for the Segments and Extents.

SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

no rows selected

SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

no rows selected

SQL> select table_name, partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
A_PARTITIONED_TABLE            P_MAX
A_PARTITIONED_TABLE            P_ID_200
A_PARTITIONED_TABLE            P_ID_100

SQL> 

So, although the three Partitions exist, no Segments and Extents yet exist. Now, let's populate one Partition -- just inserting a single row will suffice
.

SQL> insert into a_partitioned_table values (150,'HundredFifty');

1 row created.

SQL> commit;

Commit complete.

SQL> 

Now, let's look for the Segment and Extent. Note that Partition P_ID_200 would have the row with ID=150.

SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                     BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                             1024       8192


SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                                0          6        128       1024


SQL> 

So, Partition P_ID_200 now has an 8MB Segment with an Initial Extent of 8MB. Let's view the DDL for the Table.

SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 

Note how Partitions P_ID_100 and P_MAX are unchanged but Partition P_ID_200 now has SEGMENT CREATION IMMEDIATE and STORAGE (INITIAL 8M NEXT 1M) !

What if I were to split the Partitions to create new Partitions ?

SQL> alter table A_PARTITIONED_TABLE split partition P_ID_100 at ('51')
  2  into (partition P_ID_50, partition P_ID_100);

Table altered.

SQL> alter table A_PARTITIONED_TABLE split partition P_ID_200 at ('151')
  2  into (partition P_ID_150, partition P_ID_200);

Table altered.

SQL> 
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_50"  VALUES LESS THAN ('51') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_150"  VALUES LESS THAN ('151') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 
SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                     BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                             1024       8192

A_PARTITIONED_TABLE
P_ID_150                             1024       8192


SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_150                                0          6        128       1024

A_PARTITIONED_TABLE
P_ID_200                                0          6       1152       1024


SQL> 

So, only the two Partitions that have been created out of P_ID_200 have been materialized as Segments and Extents while the two Partitions split out of P_ID_100 are still Segment-less (without Storage allocated).

So remember : the default behaviour of Segment creation for Partitions has been changed since 11.2.0.2
Having said that, the note that I mentinoed at the beginning does document how this default behaviour can be overridden.
You need this information when you are creating a table with, say, 125 partitions but have only 1 to a few thousand rows in each partition and have to explain why the table is taking up 1GB of space.
Also note that once a Partition has had an 8MB extent allocated to it, any new Partitions that you create out of splitting it will also hae 8MB extents allocated --even if they are empty !

.
.
.

Short-Circuiting the COST

The Oracle Optimizer can be smart when evaluating predicates in a query.

If it finds a predicate that causes a "short-circuit" --- one that prevents rows from being returned by the query --- it can evaluate the COST of the query to 0 (zero).  Normally, we would never expect a COST to zero ---  even where we know zero rows will be returned by the query, Oracle may have to undertake some I/O  (a Full Table Scan or an Index Range Scan in the simplest cases) and some CPU cycles to verify the resulting blocks for the expected result.

Here I show a simple example of a short-circuit.

First, I build a Table and an Index, with statistics :

SQL> create table my_table
  2  as select * from dba_objects;

Table created.

SQL> select count(*) from my_table;

  COUNT(*)
----------
     76609

SQL> create index my_table_ndx on my_table(owner);

Index created.

SQL> exec dbms_stats.gather_table_stats('','MY_TABLE',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> 
SQL> select num_rows, sample_size
  2  from user_tab_statistics
  3  where table_name = 'MY_TABLE';

  NUM_ROWS SAMPLE_SIZE
---------- -----------
     76609       76609

SQL> select num_distinct
  2  from user_tab_col_statistics
  3  where table_name = 'MY_TABLE'
  4  and column_name = 'OWNER';

NUM_DISTINCT
------------
          44

SQL> select leaf_blocks, distinct_keys, num_rows, sample_size
  2  from user_ind_statistics
  3  where table_name = 'MY_TABLE'
  4  and index_name = 'MY_TABLE_NDX';

LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS SAMPLE_SIZE
----------- ------------- ---------- -----------
        183            44      76609       76609

SQL> 

So we know that all the statistics (Row Count in the table and number of Distinct values in the Index) are consistent. I then run a simple query :

SQL> col object_name format a30
SQL> col object_type format a18
SQL> set autotrace on
SQL> select object_name, object_type
  2  from my_table
  3  where owner = 'HEMANT'
  4  and object_name like 'S%'
  5  order by 1,2;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
SOURCE_PK                      INDEX
SOURCE_PK1                     INDEX
SOURCE_TABLE                   TABLE


Execution Plan
----------------------------------------------------------
Plan hash value: 1587485563

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    24 |   984 |    53   (2)| 00:00:01 |
|   1 |  SORT ORDER BY               |              |    24 |   984 |    53   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| MY_TABLE     |    24 |   984 |    52   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | MY_TABLE_NDX |  1741 |       |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME" LIKE 'S%')
   3 - access("OWNER"='HEMANT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        579  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> 

Oracle has estimated that it will fetch 1741 rowids from the index in the plan step id=3 and then fetch 1,741 rows and also apply a filter for object_name in plan step id=2 to reduce the row count to 24.
The actual resulting row count is 3.  (The 24 row count is an estimate)
The estimated cost of the Index Range Scan is 5, the estimated cost of the Table Access (1741 rowids) and Filter is 47 (52-5) and the estimated cost of the Sort (for 24 rows) is 1 (53-52), resulting in a total cost of 53.
Oracle read a total of 5 blocks (consistent gets).

What happens if I add an "AND 1=2" predicate (which is always FALSE) ?

SQL> select object_name, object_type
  2  from my_table
  3  where owner = 'HEMANT'
  4  and object_name like 'S%'
  5  and 1=2
  6  order by 1,2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 422461895

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    41 |     0   (0)|          |
|   1 |  SORT ORDER BY                |              |     1 |    41 |            |          |
|*  2 |   FILTER                      |              |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MY_TABLE     |    24 |   984 |    52   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MY_TABLE_NDX |  1741 |       |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NULL IS NOT NULL)
   3 - filter("OBJECT_NAME" LIKE 'S%')
   4 - access("OWNER"='HEMANT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        356  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 

The query returns no rows (i.e. zero rows).
But notice the "filter (NULL IS NOT NULL)".  This is the short-circuit that Oracle adds.  This will always evaluate to FALSE.  This is Oracle's way of translating the "1=2" condition that I added to the query.
Although there is a supposed COST of 5 for the 1,741 rowids from the Index Range Scan and a supposed COST of 47 (52-5) for the Table Access of the 1,741 rowids and filtering to 24 rows for "OBJECT_NAME LIKE 'S%'), Oracle then proceeds to add the NULL IS NOT NULL FILTER before the SORT ORDER BY.  This converts the COST to 0.
We can see that Oracle*did not do any I/O* because the Statistics show 0 block gets (consistent gets).
Let me say that again : Oracle did NOT do any I/O inspite of the supposed Index Range Scan and Table Access by Index Rowid.

.
.
.