MULTISET experiments

Recently I was asked to make some investigation about the PL/SQL procedure running slowly. PL/SQL procedure does not contain any SQL or complicate math operation but for some reasons have unexpected delays in processing. After small investigation I localize the problematic statement as containing the join of two nested tables – “multiset union”.

I was not able to explain it and crate test case to get to the bottom of this issue.

CREATE OR REPLACE PACKAGE epani.multicast_pkg
  IS
     TYPE t_number_nt IS TABLE OF NUMBER; -- test nested table type
       v_new_number  t_number_nt; --second nested table
       v_number t_number_nt; --first nested table
     PROCEDURE init (num_init1 number
                                  , num_init2 number); -- procedure that populate first nested table
     PROCEDURE add_number_old; -- procedure that join nested table in old fashion
     PROCEDURE add_number_new; -- procedure that join nested tables with MULTISET
END;
/

CREATE OR REPLACE PACKAGE BODY epani.multicast_pkg
 IS
    PROCEDURE  init (num_init1 number  -- number of elements in the first table
                                  , num_init2 number) -- number of elements in the second table
    is
    begin
        SELECT num BULK COLLECT INTO v_number   FROM (
                              SELECT  LEVEL num  FROM DUAL CONNECT BY LEVEL <= num_init1);
        SELECT num BULK COLLECT INTO v_new_number  FROM (
                              SELECT  LEVEL num  FROM DUAL CONNECT BY LEVEL <= num_init2);
    end;

    PROCEDURE  add_number_old 
     IS
     BEGIN
          v_number.EXTEND(v_new_number.COUNT); -- allocate nested table 
        FOR i IN v_new_number.FIRST .. v_new_number.LAST
        LOOP
          v_number(v_number.LAST) := v_new_number(i);
        END LOOP;
     END add_number_old;

    PROCEDURE  add_number_new
     IS
     BEGIN
       v_number:=v_number multiset union v_new_number;
     END add_number_new;
END;
/

I have prepared few test cases
First : We join the small table to the big one

---We initialise the first nested table by significant number of rows and second by small number
SQL> exec EPANI.multicast_pkg.init(356000,5);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.33
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
-- the procedure does not really takes so long
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.41
-- the procedure takes almost ½ of time to initially generate the table

Second: we join two relatively small tables but do it repeatedly 1000 times to get the meaningful timing

---We initialise the nested tables by small number of rows
SQL> exec EPANI.multicast_pkg.init(1000,100);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
-- We run pl/sql block using old fashion method
SQL> begin
  for i in 1..1000 LOOP
    EPANI.multicast_pkg.add_number_old;
  END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
-- We run pl/sql block using new method
SQL> begin
  for i in 1..1000 LOOP
     EPANI.multicast_pkg.add_number_new;
  END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:04:29.75

Third: We join two big tables

---We initialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(100000, 100000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.97
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
-- the procedure does not  takes measurable time
---We reinitialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(100000, 100000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.11
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.28
-- the procedure takes almost extra 50% above the old method

Forth: We join big tables to the small one

---We initialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(5, 356000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.08
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
-- the procedure does takes measurable time
---We reinitialise the nested tables in the same fashion
SQL> exec EPANI.multicast_pkg.init(5, 356000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.27
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.07
-- the procedure takes almost extra 50% above the old method

We have proved that for all cases we got better performance on old fashion method in all cases, but why. What stands behind this degradation? The answer can be found if we rewrite our procedures in slightly different way.

CREATE OR REPLACE PACKAGE BODY epani.multicast_pkg
 IS
    PROCEDURE  init (num_init1 number  -- number of elements in the first table
                                  , num_init2 number) -- number of elements in the second table
    is
    begin
        SELECT num BULK COLLECT INTO v_number   FROM (
                              SELECT  LEVEL num  FROM DUAL CONNECT BY LEVEL <= num_init1);
        SELECT num BULK COLLECT INTO v_new_number  FROM (
                              SELECT  LEVEL num  FROM DUAL CONNECT BY LEVEL <= num_init2);
    end;

    PROCEDURE  add_number_old 
     IS
	v_result t_number_nt:= t_number_nt(); --resulting nested table
     BEGIN
        v_result.EXTEND(v_number.COUNT); -- allocate nested table 
        FOR i IN v_number.FIRST .. v_number.LAST
        LOOP
          v_result(v_number.LAST) := v_number(i);
        END LOOP;
        v_result.EXTEND(v_new_number.COUNT); -- allocate nested table 
        FOR i IN v_new_number.FIRST .. v_new_number.LAST
        LOOP
          v_result(v_number.LAST) := v_new_number(i);
        END LOOP;
     END add_number_old;

    PROCEDURE  add_number_new
     IS
	v_result t_number_nt:= t_number_nt(); --resulting nested table
     BEGIN
       v_result:=v_number multiset union v_new_number;
     END add_number_new;
END;
/

And repeat our tests

First : We join the small table to the big one

---We initialise the first nested table by significant number of rows and second by small number
SQL> exec EPANI.multicast_pkg.init(356000,5);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.16
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.93
-- the procedure does takes significantly longer
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.64
-- faster by new method

Second: we join two relatively small tables but do it repeatedly 1000 times to get the meaningful timing

---We initialise the nested tables by small number of rows
SQL> exec EPANI.multicast_pkg.init(1000,100);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
-- We run pl/sql block using old fashion method
SQL> begin
  for i in 1..1000 LOOP
    EPANI.multicast_pkg.add_number_old;
  END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.30
-- We run pl/sql block using new method
SQL> begin
  for i in 1..1000 LOOP
     EPANI.multicast_pkg.add_number_new;
  END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.96
-- Faster by new method

Third: We join two big tables

---We initialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(100000, 100000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.56
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.48
-- the procedure does not  takes measurable time
---We reinitialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(100000, 100000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.08
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.40
-- slightly faster by new method

Forth: We join big tables to the small one

---We initialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(5, 356000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.31
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.58
-- the procedure does takes measurable time
---We reinitialise the nested tables in the same fashion
SQL> exec EPANI.multicast_pkg.init(5, 356000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.38
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.50
-- slightly faster by new method

It looks like that for new set of test MULTICAST shows better performance than the handmade one. The answer is lighting in a scope of functionality. When we add one collection to the already existing the old one works perfectly but when we create the new collection as join of two old one and later assign it to the one of them the MULTICAST shows better efficiency. Multicast is able to resolve the vide scope of tasks. The developers should clearly see the difference between operating two collections or three collections and choose the appropriate method based on application requirements.

Partition pruning using non-prefixed local indexes

This small post is from nice and frighten world of partitions, specifically the case of partition pruning. If you are using partitions in your application there is fair chance that your database is quite big and performance demanding. If you are not working in classic Data Warehousing manner you have to care about DML statement performance. One of the basic rules of DML performance is less indexes you have and less index columns in existing indexes then higher is the DML performance. Thus one of the architectural DBA targets is to reduce the number of indexed columns by just most critical. Another axiom statement is that smaller indexes is easier to support and manage then big one, thus local indexes usually more preferable. The conclusion from two previous axiom is that probably most indexes on partitioned tables in your application is non-prefixed local indexes. Now we came to the topic of this post how oracle optimizer deal with such indexes and what we can do with it.

Lets create list base partitioned table transactions

Column Name	Comment
SERNO	Unique identifier
PARTITIONKEY	Partition Key
SGENERAL	Some indexed Field

And create two indexes TRANSACTIONS_PK (SERNO, PARTITIONKEY) and local partitioned non-prefixed index TRANSACTIONSI (SGENERAL).

Let’s start from the most common statement

EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM transactions
 WHERE PARTITIONKEY='JUL2012' AND SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————-
Plan hash value: 2338610280

Id	Operation	Name	Rows	Bytes	Cost	Time	Pstart	Pstop
0	SELECT STATEMENT		5	55	69978 (1)	00:14:00
1	HASH GROUP BY		5	55	69978 (1)	00:14:00
2	PARTITION LIST SINGLE		5340K	56M	69727 (1)	00:13:57	KEY	KEY
*3	TABLE ACCESS FULL	TRANSACTIONS	5340K	56M	69727 (1)	00:13:57	19	19

Predicate Information (identified by operation id):
—————————————————
3 – filter(“STGENERAL” IS NOT NULL)

Does it look like non-optimal? Optimizer has chosen to do FULL SCAN even if it has nice looking index TRANSACTIONSI. The problem is that partition pruning works only for prefixed indexes, that means you have to pay for support extra column in your index even if you do not need it because you clearly can get the partition name based from the identical rules on a table.

But luckily we have extended syntaxes. Uses it we can manually define the partition that should be used. In our case the partition name is equal to the partition key. Getting the partition name is very simple for list partitions but can be received in all other cases too.

EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM TRANSACTIONS PARTITION ("JUL2012")
 WHERE SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————-
Plan hash value: 198159339

Id	Operation	Name	Rows	Bytes	Cost	Time	Pstart	Pstop
0	SELECT STATEMENT		4	20	10 (20)	00:00:01
1	SORT GROUP BY NOSORT		4	20	10 (20)	00:00:01
2	PARTITION LIST SINGLE		8890	44450	8 (0)	00:00:01	KEY	KEY
*3	INDEX FULL SCAN	TRANSACTIONSI	8890	44450	8 (0)	00:00:01	19	19

Predicate Information (identified by operation id):

—————————————————
3 – filter(“SGENERAL” IS NOT NULL)

This plan looks much better. According to optimizer estimations we can save 14 minutes on it. But there is even more attractive syntaxes for those who do not want to spend their time calculating the partition name

 EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM TRANSACTIONS PARTITION FOR ('JUL2012')
 WHERE SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————
Plan hash value: 198159339

Id	Operation	Name	Rows	Bytes	Cost	Time	Pstart	Pstop
0	SELECT STATEMENT		4	20	10 (20)	00:00:01
1	SORT GROUP BY NOSORT		4	20	10 (20)	00:00:01
2	PARTITION LIST SINGLE		8890	44450	8 (0)	00:00:01	KEY	KEY
*3	INDEX FULL SCAN	TRANSACTIONSI	8890	44450	8 (0)	00:00:01	19	19

Predicate Information (identified by operation id):
—————————————————
3 – filter(“SGENERAL” IS NOT NULL)

The only problem with last syntax is that it is supported only from 11.2 version. The syntax was developed to support interval partitions but can be handy for all other types.

Does it looks that with new syntax we came to the kingdom of wealth and prosperity, where we can easily avoid prefixes on local indexes and still use the effective partition pruning? But it is not. The hidden rock is that the partition keys in PARTITION FOR clause could not be defined throw the variables like in WHERE clause. Below there are examples of such attempt

Firstly traditional approach

 DECLARE
 v_part char(7):='JUL2012';
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS
 WHERE PARTITIONKEY=v_part AND SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /
 Elapsed: 00:12:42.05

It works but does not looks like very performance effective. Let’s go and try 10g approach in PL/SQL

DECLARE
 v_part varchar2(10):="JUL2012";
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS PARTITION (v_part)
 WHERE SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /

ERROR at line 2:
 ORA-06550: line 2, column 25:
 PLS-00201: identifier "JUL2012" must be declared
 ORA-06550: line 2, column 10:
 PL/SQL: Item ignored

It looks like kind of expected behavior. You could not use table name as variable, why you should be able to use partition name. Personally I put a lot of hope on the last test with PARTITIONKEY reference

DECLARE<span style="color: #000000;">
v_part char(7):='JUL2012';
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS
 PARTITION FOR (v_part)
 WHERE SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /

ERROR at line 1:
 ORA-14763: Unable to resolve FOR VALUES clause to a partition number
 ORA-06512: at line 4

It was the first time I see the error thus have a look into the documentation

ORA-14763: Unable to resolve FOR VALUES clause to a partition number

Cause: Could not determine the partition corresponding to the FOR VALUES clause.

Action: Remove bind variables and dependencies on session parameters from the values specified in the FOR VALUES clause.

Ha-Ha-Ha nothing change we still could not pass partition key values into query.

Summarizing oracle offer the mechanism that allows to use partition pruning on non-prefixed local indexes but have not support the syntaxes in pl/sql using binds. The usage of this syntaxes have sense only in case of big queries (e.g. reports) running on a table, when gains from effective execution plan prevail over the loses of dynamic sql.

Application Code Level script

With patches and fixes delivered to different sites and applying to different testing, verification and production regions, there is a need for a simple way to compare database objects in the application schema without having database link between different databases. This can be challenging as the PL/SQL code is usually encrypted and database segments have different storage attributes. Here I show the method to achieve this comparison.
The reason why I think it would be interesting to public is that the problem is very common and up to day. There is no common white paper that helps to solve it. The only note that pretends to cover the topic is 781719.1 But it speaks only about PL/SQL objects that are not enough in a real world.
The attached script use MD5 hashing algorithm to get hash value associated to the version of database object. The hashing CLOB can be get from DBMS_METADATA package or from DBA_SOURCE view for PL/SQL objects. The principals are very simple but there are few points that have been raised during development.
1. The tables, indexes and e.t.c. with different storage configuration should generate the same hash. This issue has been solved by DBMS_METADATA.SET_TRANSFORM_PARAM
2. The tables with different order of columns and constraints have to provide the same hash too. The solution was to use Simple XML format of DBMS_METADATA output and sorting the columns and constraints alphabetically using XML functions.
3. The nested and index organise service tables are included into DBA_OBJECTS as TABLES but DBMS_METADATA include their description into the master objects but failed on servant items. My solution was to use DBA_TABLES view to segregate only high level objects.
4. Java objects has no option to generate description in XML format thus we generate it in general DDL format.
5. Role description should include grants that have been granted to the role. The DBMS_METADA does not include grants into generated description thus again the XML functions was used to get proper CLOB

The following privileges required for the user who run the script

-- GRANT CREATE PROEDURE
-- GRANT EXECUTE ON DBMS_CRYPTO
-- GRANT EXECUTE ON DBMS_METADATE
-- GRANT SELECT ON DBA_SOURCE
-- GRANT SELECT ON DBA_SYS_PRIVS
-- GRANT SELECT ON DBA_TAB_PRIVS
-- GRANT SELECT ON DBA_ROLE_PRIVS

The following script take one parameter – the application schema name and provide as output list of database objects with corresponding hash values.

create or replace function verify_source(p_source_type in varchar2,
p_source_name in varchar2,
p_source_owner in varchar2) return varchar2 AUTHID CURRENT_USER as
code_source clob;
md5hash varchar2(32);
v_h NUMBER; -- handle returned by OPEN
v_th NUMBER; -- handle returned by ADD_TRANSFORM
begin
IF p_source_type in ('VIEW','ROLE','TABLE','INDEX'
,'MATERIALIZED_VIEW','MATERIALIZED_VIEW_LOG','SEQUENCE','SYNONYM') THEN
v_h := DBMS_METADATA.OPEN(p_source_type);
if p_source_type not in ('ROLE') THEN
DBMS_METADATA.SET_FILTER(v_h,'SCHEMA',p_source_owner);
END IF;
DBMS_METADATA.SET_FILTER(v_h,'NAME',p_source_name);
v_th := DBMS_METADATA.ADD_TRANSFORM(v_h,'SXML');
if p_source_type not in ('VIEW','ROLE') THEN
DBMS_METADATA.SET_TRANSFORM_PARAM (v_th,'SEGMENT_ATTRIBUTES',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM (v_th,'STORAGE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM (v_th,'TABLESPACE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM (v_th,'PARTITIONING',FALSE);
END IF;
code_source := DBMS_METADATA.FETCH_CLOB(v_h);
IF p_source_type in ('TABLE') THEN
-- get rid off sorting misconfigurations
SELECT UPDATEXML(
UPDATEXML(SRC,'/TABLE/RELATIONAL_TABLE/COL_LIST',
XMLELEMENT("COL_LIST", (
XMLQuery (
'for $i in /TABLE/RELATIONAL_TABLE/COL_LIST/COL_LIST_ITEM
order by $i/NAME
return $i'
passing by value SRC
RETURNING CONTENT
)
) )
) ,'/TABLE/RELATIONAL_TABLE/FOREIGN_KEY_CONSTRAINT_LIST',
XMLELEMENT("FOREIGN_KEY_CONSTRAINT_LIST", (
XMLQuery (
'for $i in
/TABLE/RELATIONAL_TABLE/FOREIGN_KEY_CONSTRAINT_LIST/FOREIGN_KEY_CONSTRAINT_LIST_ITEM
order by $i/NAME
return $i'
passing by value SRC
RETURNING CONTENT
)
) )
).getClobVal() INTO code_source
FROM ( SELECT XMLQuery(
'declare function local:removeNS($e as element()) as element()
{
element { QName("", local-name($e)) }
{
for $i in $e/node()|$e/attribute::*
return typeswitch ($i)
case element() return local:removeNS($i)
default return $i
}
}; (::)
local:removeNS($SRC/child::*)'
passing XMLType(code_source) as "SRC"
returning content
) SRC
FROM dual) INITSRC;
END IF;
DBMS_METADATA.CLOSE(v_h);
ELSIF p_source_type in ('PROCEDURE','FUNCTION','TYPE','TYPE BODY','TRIGGER','PACKAGE','PACKAGE BODY')
THEN
code_source := '';
for source_record in (select text from dba_source where owner = upper(p_source_owner) and name =
upper(p_source_name) and type = upper(p_source_type) order by line)
loop
code_source := code_source||source_record.text;
end loop;
ELSIF p_source_type in ('JAVA_CLASS','JAVA_SOURCE') THEN
code_source := DBMS_METADATA.GET_DDL(p_source_type,p_source_name,upper(p_source_owner));
ELSIF p_source_type in ('ROLE') THEN
SELECT
INSERTCHILDXMLAFTER(SRC,'/ROLE','TYPE[1]',
(SELECT
XMLAgg(
XMLELEMENT("privs",
XMLFOREST(OWNER, TABLE_NAME, PRIVILEGE)))
FROM
(SELECT DISTINCT *
FROM (
SELECT OWNER, TABLE_NAME, PRIVILEGE
FROM dba_tab_privs
WHERE GRANTEE IN
(SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
START WITH GRANTEE=p_source_name
CONNECT BY NOCYCLE PRIOR GRANTED_ROLE=GRANTEE
UNION ALL
SELECT p_source_name FROM DUAL)
UNION ALL
SELECT NULL OWNER, PRIVILEGE TABLE_NAME, NULL PRIVILEGE
FROM dba_sys_privs
WHERE GRANTEE IN (SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
START WITH GRANTEE=p_source_name
CONNECT BY NOCYCLE PRIOR GRANTED_ROLE=GRANTEE
UNION ALL
SELECT p_source_name FROM DUAL))
ORDER BY 1,2,3))).getClobVal() INTO code_source
FROM ( SELECT XMLQuery(
'declare function local:removeNS($e as element()) as element()
{
element { QName("", local-name($e)) }
{
for $i in $e/node()|$e/attribute::*
return typeswitch ($i)
case element() return local:removeNS($i)
default return $i
}
}; (::)
local:removeNS($SRC/child::*)'
passing XMLType(code_source) as "SRC"
returning content
) SRC
FROM dual) INITSRC;
END IF;

md5hash := rawtohex(dbms_crypto.hash(typ => dbms_crypto.HASH_MD5,
src => code_source));
return md5hash;
exception
WHEN OTHERS THEN
return p_source_type||p_source_name;
end;
/
show error

Spool code_level_checksum.lst
set pagesize 5000
set heading off
set echo off
set feedback off

col OBJECT_TYPE FORMAT a30
col OBJECT_NAME FORMAT a30
col HASHVAL FORMAT a35

prompt ###################################
prompt # TABLES #
prompt ###################################

SELECT 'TABLE' OBJECT_TYPE, TABLE_NAME OBJECT_NAME , verify_source('TABLE',TABLE_NAME, OWNER)
HASHVAL
FROM DBA_TABLES WHERE OWNER='&1'
AND IOT_NAME IS NULL
order by 1,2
/

prompt ###################################
prompt # INDEXES #
prompt ###################################

SELECT 'INDEX' OBJECT_TYPE, INDEX_NAME OBJECT_NAME , verify_source('INDEX',INDEX_NAME, OWNER)
HASHVAL
FROM DBA_INDEXES WHERE OWNER='&1'
order by 1,2
/

prompt ###################################
prompt # ROLES #
prompt ###################################

SELECT 'ROLE' OBJECT_TYPE, ROLE OBJECT_NAME , verify_source('ROLE',ROLE, 'TCTDBS') HASHVAL
FROM DBA_ROLES
order by 1,2
/

prompt ###################################
prompt # JAVA SOURCES #
prompt ###################################

select OBJECT_TYPE, OBJECT_NAME, verify_source('JAVA_SOURCE',OBJECT_NAME, OWNER) HASHVAL FROM
dba_objects
WHERE OWNER='&1' and OBJECT_TYPE IN ('JAVA SOURCE')
order by 1,2
/

prompt ###################################
prompt # JAVA CLASSES #
prompt ###################################

select 'JAVA_CLASS' OBJECT_TYPE, NAME OBJECT_NAME, verify_source('JAVA_CLASS',NAME, OWNER) HASHVAL
FROM dba_java_classes
WHERE OWNER='&1'
order by 1,2
/

prompt ###################################
prompt # MISC #
prompt ###################################

select OBJECT_TYPE, OBJECT_NAME, verify_source(OBJECT_TYPE,OBJECT_NAME, OWNER) HASHVAL FROM
dba_objects
WHERE OWNER='&1' and OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TYPE','TYPE
BODY','TRIGGER','VIEW','MATERIALIZED_VIEW','MATERIALIZED_VIEW_LOG','SEQUENCE','SYNONYM','PACKAGE','PACKAG
E BODY')
order by 1,2
/

spool off;

drop function verify_source
/


Hope you will find it usefull.

The output from this script can be compared as simple flat file using any favorite comparison tool, e.g. WinMerge.

TO_CHAR or NOT TO_CHAR

Another post where I probably show trivial things. This issue is very easy to diagnose and fix but quite complicate to prevent.
I’ll start from my personal statistics of performance analyses during development. From my point of view there are 5 levels of SQL tuning that are using in code development. I try to present percent of queries that tuned by each of them.

N	Method	% of statement tested
1	No test. Rely on optimizer and experience.	50
2	Execute on developer database.	25
3	Execute on a database with real size data.	15
4	Execute on database with real size data and analyse the execution plan.	10
5	Execute on database with real size data and analyse the execution plan and predicates.	0.0000001

It shows that usually we usually start intensively tune queries only when it does not work on a real production or preproduction stage. It have sense but some hidden rocks can and should be diagnosed during using the first level of tuning. Based on my own experience the own expertise can be easily overestimated. Before I start this post I query some my friends and no one was aware this oracle trick, thus I believe that it would be useful for general audience too.
Will you check the predicates if the access method is expected and use the correct index. Probably not but oracle loves to make surprises. In our case it was surprise with concatenated indexes and implicit conversion.
I’ll try to show the case using the example

--Create sample table
CREATE TABLE eterpani.TESTTABLE
   (    COL1 NUMBER(10,0) DEFAULT (0) NOT NULL ENABLE,
        ID NUMBER(10,0) NOT NULL ENABLE,
        PARTITIONKEY CHAR(1) NOT NULL ENABLE,
        COL2 NUMBER(10,0),
        COL3 VARCHAR2(6 CHAR) NOT NULL ENABLE,
        COL4 DATE NOT NULL ENABLE,
        COL5 VARCHAR2(3 CHAR) NOT NULL ENABLE,
        AMOUNT NUMBER(16,3) DEFAULT (0) NOT NULL ENABLE,
         CONSTRAINT TESTTABLE_PK PRIMARY KEY (ID,PARTITIONKEY) USING INDEX )
/
--Put concatenated index on it where col2 column is in the middle
CREATE INDEX eterpani.TESTTABLE_IDX1 ON eterpani.TESTTABLE 
   (PARTITIONKEY,  COL4, COL5, COL2, COL3)
/
--Populate table by some data
INSERT INTO eterpani.TESTTABLE
SELECT COL1, ID, PARTITIONKEY, COL2, COL3, COL4,COL5,AMOUNT FROM dual
   MODEL DIMENSION by (65 i)
   MEASURES (0 COL1,
             0 ID,
             CAST(' ' as CHAR(1)) PARTITIONKEY,
             0 COL2,
             CAST(' ' as CHAR(25)) COL3,
             sysdate COL4,
             CAST(' ' as CHAR(3)) COL5,
             0 AMOUNT)
     (col1[for i from 1 to 100000 increment 1] = ROUND(DBMS_RANDOM.VALUE(100,150)),
      ID[for i from 1 to 100000 increment 1] = cv(i),
      PARTITIONKEY[for i from 1 to 100000 increment 1] = CHR(65+MOD(cv(i),26)),
      col2[for i from 1 to 100000 increment 1] = ROUND(DBMS_RANDOM.VALUE(10,12)),
      col3[for i from 1 to 100000 increment 1] = PARTITIONKEY[cv(i)]||DBMS_RANDOM.STRING('U',5),
      col4[for i from 1 to 100000 increment 1] = TRUNC(SYSDATE)+ROUND(DBMS_RANDOM.VALUE(-5,5)),
      col5[for i from 1 to 100000 increment 1] = TO_CHAR(ROUND(DBMS_RANDOM.VALUE(10,999))),
      AMOUNT[for i from 1 to 100000 increment 1] = ROUND(DBMS_RANDOM.VALUE(100,1000)))
/
COMMIT
/

The preparation steps are over. Lets run the query.

Set autotrace on
SELECT PARTITIONKEY, COL4, COL3, COL2, COL5, SUM(AMOUNT) SUMAMOUNT
FROM eterpani.TESTTABLE
WHERE PARTITIONKEY = 'C' AND COL3='COWRTE'
     AND COL4 = TRUNC(SYSDATE) AND COL2 = 11
     AND COL5 = 901 AND COL1=131
GROUP BY PARTITIONKEY,  COL4, COL5, COL2, COL3;

Let’s look on execution plan

Execution Plan
----------------------------------------------------------
Plan hash value: 3717891987

-------------------------------------------------------------------------------------------
|Id| Operation                   | Name           | Rows  | Bytes | Cost (%CPU)|  Time    |
-------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT             |                |   1   |  59   |  4   (0)   | 00:00:01 |
| 1| SORT GROUP BY NOSORT        |                |   1   |  59   |  4   (0)   | 00:00:01 |
|*2|  TABLE ACCESS BY INDEX ROWID| TESTTABLE      |   1   |  59   |  4   (0)   | 00:00:01 |
|*3|   INDEX RANGE SCAN          | TESTTABLE_IDX1 |   1   |       |  4   (0)   | 00:00:01 |

If someone had showed me this execution plan few months ago I would have said that it is the good one without any doubts. But let me make a small changes in a query – put explicit conversion to COL5 predicate.

Set autotrace on
SELECT PARTITIONKEY, MAX(COL1), COL4, COL3, COL2, COL5, SUM(AMOUNT) SUMAMOUNT
FROM eterpani.TESTTABLE
WHERE PARTITIONKEY = 'C' AND COL3='COWRTE'
     AND COL4 = TRUNC(SYSDATE)  AND COL2 = 11
     AND COL5 = TO_CHAR(901) AND COL1=131
GROUP BY PARTITIONKEY,  COL4, COL5, COL2, COL3;

As result we have the same execution plan. Even the “Plan hash value” is the same. At this stage you can say no difference no reasons to bother. But difference exists. Firstly pay attention to the bottom part of the autotrace output

Without TO_CHAR	Statistics ———————————————————- 0 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 675 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
With TO_CHAR	Statistics ———————————————————- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 746 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed

Does the triple number of read blocks confuse you? The answer on this confusion is in the middle part of autotrace output

Without TO_CHAR	Predicate Information (identified by operation id): ————————————————— 2 – filter(“COL1″=131) 3 – access(“PARTITIONKEY”=’C’ AND “COL4″=TRUNC(SYSDATE@!) AND “COL2″=11 AND “COL3″=’COWRTE’) filter(“COL3″=’COWRTE’ AND “COL2″=11 AND TO_NUMBER(“COL5″)=901)
With TO_CHAR	Predicate Information (identified by operation id): ————————————————— 2 – filter(“COL1″=131) 3 – access(“PARTITIONKEY”=’C’ AND “COL4″=TRUNC(SYSDATE@!) AND “COL5″=’901′ AND “COL2″=11 AND “COL3″=’COWRTE’)

As you understand the most selective predicate in this query is “COL3=’COW131’”. This predicate use indexed column and mentioned in corresponding section thus everything should be easy and strait forward. But there is one small difference. Oracle implicitly convert all values in COL5 to number. Why Oracle is doing it gods know. But it prevent usage of COL5 in access path. Even without explicit conversion we still use the index but read more blocks.
I have tried to describe this difference in reading on a picture

All index blocks with the same PARTITIONKEY and COL4 values would be reviewed in a process of searching the relevant records. It is just index blocks reading but it is additional read operation that can dramatically increase the time query depending on cardinality of COL5. Moreover we have extra CPU waste. Instead of one conversion operation from constant to char we convert each single COL5 value to number. Believe me it is not we all expected on our databases.
There are two conclusions on above information:
firstly – be careful when looking on execution plan, check not only plan but predicates and other bits and pieces
Secondly – implicit thing have tendency to work unpredictably. Convert explicitly where you can.
Usefull doc: ORA-01722: Invalid Number; Implicit Conversion Not Working Correctly [ID 743595.1]

Fake lack of TimeZones in DBMS_JOBs

Reading about others mistakes is great fun, but everyone believes that he will not make such stupid mistakes. My personal attitude is a bit different – If someone has done such mistake, it can be repeated by another man. We are living in the world without unique things either good or bad.
Today I’ll speak about DBMS_JOBs and TIMEZONEs. The issue that I got was looking very odd thus I want to present it hear.
I have discovered it when I run the dbms_jobs. The job successfully posted into DBA_JOBS but launch postponed for one hour in comparison to the time form NEXT_DATE. Moreover this problem appeared to be only on one node.
To reproduce the case I have chosen the very simple job:

declare
    job_id pls_integer;
  begin
    sys.dbms_job.submit(job =>  job_id,
                         what => 'begin null; end;', -- fake job pl/sql block
                         next_date => sysdate, -- start immediately
                         interval => 'sysdate+1', -- next start next day
                         instance => 1); -- RAC instance that I am going to use
    commit;
end;
/

I have run the same queries for the first and for the second nodes. As result of these scripts I got the following output (the query can be launched on any RAC node). To make it more clear I have add “SYSDATE” column to the DBMS_JOBS view.

> select JOB, WHAT, BROKEN, LAST_DATE, NEXT_DATE, INSTANCE, SYSDATE from DBA_JOBS;
JOB  WHAT                 B LAST_DATE            NEXT_DATE              INSTANCE SYSDATE
---- -------------------- - -------------------- ---------------------- -------- --------------------
   1 begin null; end;     N                      26 Jul 10:13:18               1 26 Jul 10:21:36
   2 begin null; end;     N 26 Jul 10:18:14      27 Jul 10:18:14               2 26 Jul 10:21:36

The job on the first node does not run even when sysdate is after next_date. All general checks like job_queue_processes and broken jobs do not explain the situation.
After one hour when I starring on the screen the content of DBA_JOBS changed to

> select JOB, WHAT, BROKEN, LAST_DATE, NEXT_DATE, INSTANCE, SYSDATE from DBA_JOBS;
JOB  WHAT                 B LAST_DATE            NEXT_DATE              INSTANCE SYSDATE
---- -------------------- - -------------------- ---------------------- -------- --------------------
   1 begin null; end;     N 26 Jul 10:13:20      27 Jul 10:13:18               1 26 Jul 11:15:23
   2 begin null; end;     N 26 Jul 10:18:14      27 Jul 10:18:14               2 26 Jul 11:15:23

Aha! The CJQ process run as expected but just believes that the time is different from what I see. According to documentation DBMS_JOB uses the “date” data type to store the start date/time and does not store/use time zone related information. JOBS who are scheduled are always executed when sysdate => next execution time. But this information contradicts with data that I see.
I struggle with this puzzle until find out that in UNIX each process have separate time zone. And can be easily managed by environment variable TZ.

> export TZ=Europe/London
>  echo $TZ
Europe/London
> date
Tue Jul 26 12:12:25 GMT+01:00 2011
> export TZ=GMT-1:30
>  echo $TZ
GMT-1:30
> date
Tue Jul 26 12:42:04 GMT 2011

Back to oracle side of the process. What is sysdate – sysdate is oracle function that simply queries the OS time of the day. The result looks like independent of any oracle time zone information but
the OS TZ environment variable influences the time that the OS will pass on to Oracle. The process time zone is defined at process start time.
Thus my first attempt to sort it out was to restart coordinator CJQ process.

>alter system job_queue_processes=0 scope=memory sid='db1';
System altered.
-- wait for a minute
>alter system job_queue_processes=100 scope=memory sid='db1';
System altered.
> host ps -ef | grep cjq
  oracle 31653974        1   0 12:46:59      -  0:00 ora_cjq0_db1

The CJQ process was restarted without any gains. It looks like the oracle core respawns it with the same TZ as the PMON. But after restarting the instance from UNIX session with the right TZ environment variable the issue has been fixed. The conclusion is common – be careful and life would be easier.

Useful Note: DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained. [ID 467722.1]
Troubleshooting DBMS_SCHEDULER and DBMS_JOB [ID 783357.1]

FGAC and new partition features

It is better to start with minor note then to wait for long without any. Thus my first post in this blog would be about very specific problem.

I have spent some time investigating it before get to the bottom of this miscommunication between SQL statement improving and Fine-Grain Access Control (FGAC). Thus I believe it can be interesting for audience too. Below is the test case that allows to easily reproduce the issue.

Create partitioned table with non-standard partition names.

CREATE TABLE TESTONFGAC
( ID NUMBER(5,0) NOT NULL ENABLE,
PartitionKey NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,
COMENT VARCHAR2(255)
) TABLESPACE "USERS"
PARTITION BY RANGE (PartitionKey)
(PARTITION "1" VALUES LESS THAN (1) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" ,
PARTITION "2" VALUES LESS THAN (2) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" ,
PARTITION "3" VALUES LESS THAN (3) SEGMENT CREATION IMMEDIATE TABLESPACE "USERS" )
/

Create FGAC policy function. For our case it does not matter how complicate would it be, thus create just dummy one.

CREATE OR REPLACE FUNCTION TEST_FGA (object_schema IN VARCHAR2, object_name VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN '1=1';
END;
/

Create the policy on the newly created table using the function

exec DBMS_RLS.ADD_POLICY ( 'ETERPANI', 'TESTONFGAC', 'test_privacy', 'ETERPANI', 'TEST_FGA');

Everything before was just preparations. Now we are running the relatively new (since Oracle 10g) syntaxes.

DELETE FROM ETERPANI.TESTONFGAC PARTITION ("3") WHERE ID=3;

This reasonable simple statement fails with error “ORA-28113: policy predicate has error”. The further diagnostic can be found on oracle trace file.

*** 2011-06-25 01:23:27.188
-------------------------------------------------------------
Error information for ORA-28113:
Logon user : ETERPANI
Table/View : ETERPANI.TESTONFGAC
Policy name : TEST_PRIVACY
Policy function: ETERPANI.TEST_FGA
RLS view :
SELECT "ID","PARTITIONKEY","COMENT" FROM "ETERPANI"."TESTONFGAC" PARTITION (3) "TESTONFGAC" WHERE (1=1)
ORA-14108: illegal partition-extended table name syntax
-------------------------------------------------------------

As we see double quotes around partition name magically disappears.
In case of STANDARD partition naming (e.g. FIRST , SECOND e.t.c) it does not matter but if you choose lowercase naming or names starting from numbers you can get the ORA-28113.

Fancy automatic database schema generation tools frequently create objects in lower-case in this case it can be fatal.

The expected conclusion “little wit in the head makes much work for the feet” – keep your solution standard and simple (if you can).