Null is Not Null – Filter

While checking something got through below old OTN question, so wanted to verify on optimizer behaviour

https://community.oracle.com/thread/2406366

TEST@XCONTEST>create table test as select RPAD(TO_CHAR(ROWNUM),3,’X’) a,rownum b from dual CONNECT BY LEVEL<=1000; Table created. TEST@XCONTEST>create index test_idx on test (a,b);

Index created.

TEST@XCONTEST>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>’TEST’,CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

PL/SQL procedure successfully completed.

Check for rows with a=”

TEST@XCONTEST>select * from test where a=”;

no rows selected

TEST@XCONTEST>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
 ----------------------------------------------------------------------------------------------------------------------------------
 SQL_ID 1f8m76xwz6mpv, child number 0
 -------------------------------------
 select * from test where a=''

Plan hash value: 766971379

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 1 (100)| |
 |* 1 | FILTER | | | | | |
 | 2 | TABLE ACCESS FULL| TEST | 1000 | 8000 | 2 (0)| 00:00:01 |
 ---------------------------------------------------------------------------

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

1 - filter(NULL IS NOT NULL)

Optimizer will add filter as null is not null when sub plan tree is false, the sub plan tree will never be executed, that is the reason the cost of parent operation is 0 when the child has 2.

What was the reason for not picking the index and the plan to have the specific filter?

A ZERO length varchar2 is treated as NULL (Thanks to Tom Kyte), so in above query we are searching for null value and nothing equates to null,so the child operation at 2 doesn’t need to be executed at all. Index wasn’t used here as the column a isn’t defined as not null.

TEST@XCONTEST>alter table test modify a not null;

Table altered.

TEST@XCONTEST>select * from test where a=”;

no rows selected

TEST@XCONTEST>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
 ----------------------------------------------------------------------------------------------------
 SQL_ID bzgnr9bg1z2ps, child number 0
 -------------------------------------
 select * from test where a=''

Plan hash value: 1731493281

-----------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 -----------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 1 (100)| |
 |* 1 | FILTER | | | | | |
 | 2 | INDEX FULL SCAN| TEST_IDX | 1000 | 8000 | 1 (0)| 00:00:01 |
 -----------------------------------------------------------------------------

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

1 - filter(NULL IS NOT NULL)
 19 rows selected.

DBMS_METADATA_DDL and Interval partitions

I am writing this post after a OTN question on DBMS_METADATA not showing the DDL after interval partitions are created and Jonathan lewis reason for this behavior.

create table test3
 (
 id number,
 created_dt date
 )
 partition by range (created_dt)
 interval (numtoyminterval(1, 'month'))
 (
 partition p_feb2017 values less than (to_date('2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')) ,
 partition p_mar2017 values less than (to_date('2017-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
 ) ;
 SQL> select partition_name,partition_position,high_value from user_tab_partitions where table_name ='TEST3' order by partition_position;

PARTITION_NAME PARTITION_POSITION HIGH_VALUE
 -------------------- ------------------ --------------------------------------------------------------------------------
 P_FEB2017 1 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 P_MAR2017 2 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Check the DBMS_METADATA output for the table.

SQL> select dbms_metadata.get_ddl('TABLE','TEST3','RAKESH') from dual;
 DBMS_METADATA.GET_DDL('TABLE','TEST3','RAKESH')
 --------------------------------------------------------------------------

CREATE TABLE "RAKESH"."TEST3"
 ( "ID" NUMBER,
 "CREATED_DT" DATE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS"
 PARTITION BY RANGE ("CREATED_DT") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  (PARTITION "P_FEB2017" VALUES LESS THAN (TO_DATE(' 2017-03-01 00:00:00', 'SYYY
 Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 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 "USERS" ,
 PARTITION "P_MAR2017" VALUES LESS THAN (TO_DATE(' 2017-04-01 00:00:00', 'SYYYY
 -MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 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 "USERS" )

Insert new data into the table.

SQL> insert into test3 values (1,to_date('8-MAY-2017', 'DD-MON-YYYY'));

1 row created.

SQL> commit;

Commit complete.

Interval partition created.

SQL> select partition_name,partition_position,high_value from user_tab_partitions where table_name ='TEST3' order by partition_position;

PARTITION_NAME PARTITION_POSITION HIGH_VALUE
 -------------------- ------------------ --------------------------------------------------------------------------------
 P_FEB2017 1 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 N')

P_MAR2017 2 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 N')

SYS_P911 3 TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  N')

But the DDL from DBMS_METADATA still shows the partitions that were created when the table first created, doesn’t include the new partition created for MAY.

CREATE TABLE "RAKESH"."TEST3"
 ( "ID" NUMBER,
 "CREATED_DT" DATE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS"
 PARTITION BY RANGE ("CREATED_DT") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
 (PARTITION "P_FEB2017" VALUES LESS THAN (TO_DATE(' 2017-03-01 00:00:00', 'SYYY
 Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 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 "USERS" ,
 PARTITION "P_MAR2017" VALUES LESS THAN (TO_DATE(' 2017-04-01 00:00:00', 'SYYYY
 -MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
 PCT FREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" )

Thanks to Jonathan Lewis , who put a comment on rationale behind DBMS_METADATA not showing the interval partition. I am quoting his words.

“There is a simple rationale for this behaviour.

Imagine the only out-of-range data you’ve added is for December – Oracle will add the december partition but will not add the partitions for april through to november.
If dbms_metadata.get_ddl now created the DDL to create partitions with the three boundaries that existed the code would create three RANGE partitions, which means the december partition would cover april to december and act as the lower bound for the interval partitions, and you wouldn’t get (e.g.) a dedicated june partition created if you then inserted some data for June”

 

 

Oracle 12c Internal Users

DBA_USERS in oracle 12c included a new column ORACLE_MAINTAINED which will show if the user is oracle maintained or No.

SQL> desc dba_users
Name Null? Type
———————————————– ——–
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)

SQL> select username,oracle_maintained,account_status from dba_users where oracle_maintained=’Y’ ;

USERNAME O ACCOUNT_STATUS
——————– – ——————————–
GSMCATUSER Y EXPIRED & LOCKED
SYSBACKUP Y EXPIRED & LOCKED
OUTLN Y OPEN
SYSDG Y EXPIRED & LOCKED
DBSNMP Y OPEN
ORACLE_OCM Y EXPIRED & LOCKED
SYSKM Y EXPIRED & LOCKED
XS$NULL Y EXPIRED & LOCKED
GSMUSER Y EXPIRED & LOCKED
AUDSYS Y EXPIRED & LOCKED
SYS Y OPEN
SYSTEM Y OPEN
DIP Y EXPIRED & LOCKED
ANONYMOUS Y LOCKED
XDB Y EXPIRED & LOCKED
APPQOSSYS Y EXPIRED & LOCKED
GSMADMIN_INTERNAL Y EXPIRED & LOCKED

17 rows selected.

Automatic Memory Management in Oracle 11g

Below to post is to verify my understanding on how shared memory is managed in AMM. Thanks to Tanel poder Post on AMM internals.

Current setting for MEMORY_TARGET parameters in DB, not all 50GB allocated.

SQL> sho parameter memory

NAME TYPE VALUE
 ------------------------------------ -------------------------------- -----
 hi_shared_memory_address integer 0
 memory_max_target big integer 50G
 memory_target big integer 50G
 shared_memory_address integer 0

SGA parameters set in DB

SQL> sho parameter sga

NAME TYPE VALUE
 -------------------------
 lock_sga boolean FALSE
 pre_page_sga boolean FALSE
 sga_max_size big integer 50G
 sga_target big integer 20G

In AMM , oracle manages shared memory as files in shared memory filesystem (/dev/shm)

cd /dev/shm

$ ls -ltr *TEST* |more
 -rw-r----- 1 oracle dba 134217728 Apr 26 18:42 ora_TEST_131235851_0
 -rw-r----- 1 oracle dba 134217728 Apr 26 18:42 ora_TEST_131203082_369
 Each files is of size 128M

ls -ltr *TEST* | awk '$5==134217728' |wc -l
 170

170*128M=21.25 GB

21.25GB shared memory currently allocated to database TEST,Initially it will allocate (verify in /dev/shm for the files) the memory set for sga_target , if not set will be set based on memory_target value.

Other files with 0 bytes are (MEMORY_TARGET- SGA_TARGET) ,these are memory files mapped to server process (size 0 bytes), but its not used, if space needed for pga , these files are freed and memory allocated to pga.

$ ls -ltr *TEST* | awk '$5==0' |more
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_230
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_229
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_228
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_227
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_226
 -rw-r----- 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_225

No of empty files which can freed whenever memory needs to be allocated to pga.

$ ls -ltr *TEST* | awk '$5==0' |wc -l
 231

231*128M=28.8GB  ( MEMORY_TARGET-Current SGA Size)

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
 -------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- --------- ------------
 SGA Target 2.2683E+10 2.2012E+10 2.2683E+10 2.1475E+10 5 GROW DEFERRED 02-MAY-16 134217728
 
SQL> select to_char(2.2683E+10)/1024/1024/1024 from dual;

TO_CHAR(22683000000)/1024/1024/1024
 -----------------------------------
 21.12519

sga_target current size is 21Gb which matches to the size of all shared memory files created for the DB in /dev/shm.
If /dev/shm not defined or properly configured, then setting AMM at instance will throw error message ORA-00845: MEMORY_TARGET not supported on this system.

ASMM can be configured without /dev/shm.

Summary:

During instance startup , oracle will check if /dev/shm has free space left for the value specified in Memory_max_target(though not all is initialized/allocated) otherwise we get ORA-00845.

If space is sufficient then the virtual address space (in /dev/shm) is mapped to the value of memory_target, but only value sga_target + small for pga is allocated (check /dev/shm files with non zero bytes size) is initialized, rest of the files are mapped but will have 0 bytes. Due to this we can see /dev/shm has more free space than (total tmps – memory_target) value.

If instance started (by the value in memory_target) initially ,later if sga_target grows and no space left on /dev/shm to accommodate until memory_target , then the instance terminates and have core dump file with below exceptions.

Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x1EDA76000] [PC:0x6080DD4, __intel_memset()+2660] [flags: 0x0, count: 1] 

 

 

 

Ref: http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/

 

Convert Non-CDB to PDB

1) Shut down the non CDB database and startup in read only mode

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 905969768 bytes
Database Buffers 155189248 bytes
Redo Buffers 9650176 bytes
Database mounted.
Database opened.

2) Describe the Non-CDB using the DBMS_PDB.DESCRIBE procedure. This procedure creates an XML file in the same way that the unplug operation does for a PDB.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => ‘/apps/oracle/home/TEST12c.xml’);
END;
/ 2 3 4 5

PL/SQL procedure successfully completed.

3) Shutdown the non-CDB database

SHUTDOWN IMMEDIATE;SQL>
Database closed.
Database dismounted.
ORACLE instance shut down.

4) Need to check the compatibility of Non-CDB to be converted to Pluggable DB So, connect to the container DB (CDB) and run the following

SQL>
SET SERVEROUTPUT ON
SQL> SQL> DECLARE
2 compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
3 pdb_descr_file => ‘/apps/oracle/home/TEST12c.xml’,
4 pdb_name => ‘TESTPDB2’)
5 WHEN TRUE THEN ‘YES’
6 ELSE ‘NO’
END;
7 8 BEGIN
9 DBMS_OUTPUT.PUT_LINE(compatible);
10 END;
/ 11
YES

PL/SQL procedure successfully completed.

5) Create Pluggable database from the XML file, here we are using the same datafiles that were there earlier for the Non CDB DB.

SQL> CREATE PLUGGABLE DATABASE TESTPDB2 using ‘/apps/oracle/home/TEST12c.xml’ nocopy tempfile reuse;

Pluggable database created.

6) Check the status of the created PDB

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

CON_ID NAME OPEN_MODE
———- —————————— ———-
2 PDB$SEED READ ONLY
3 TESTPDB READ WRITE
4 TESTPDB2 MOUNTED
SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;

PDB_NAME DBID CON_ID STATUS
——————– ———- ———- ———
TESTPDB 977767011 3 NORMAL
PDB$SEED 4077181153 2 NORMAL
TESTPDB2 2198019070 4 NEW
SQL> alter session set container=TESTPDB2;

Session altered.

SQL> show con_name;

CON_NAME
——————————
TESTPDB2

7) Execute the script to update metadata

SQL> !echo $ORACLE_HOME
/apps/oracle/product/12.1.0

SQL> @?/rdbms/admin/noncdb_to_pdb.sql

SQL>
SQL>
SQL> WHENEVER SQLERROR CONTINUE;

8) Open the new PDB database

SQL> alter session set container=TESTPDB2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> alter session SET CONTAINER =CDB$ROOT;

Session altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TESTPDB READ WRITE
TESTPDB2 READ WRITE

3 rows selected.

SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;

PDB_NAME DBID CON_ID STATUS
——————– ———- ———- ———
TESTPDB 977767011 3 NORMAL
PDB$SEED 4077181153 2 NORMAL
TESTPDB2 2198019070 4 NORMAL

3 rows selected.