2015 BGOUG Autumn Conference

Few photos from my visit at 2015 BGOUG Autumn Conference

Oracle hint ignore_row_on_dupkey_index - part 2

Last time we’ve seen that there is something really sneaky going on when we use hint ignore_row_on_dupkey_index. We have several clues for that:

1. 1.85 seconds vs 1:27.96 with hint. That’s about 50x slower.
2. 133 369 logical reads vs 1 156 645. That’s almost 9x more.

Another thing you would definitely find strange is the difference in sizes of trace files: 22 KB vs 35 MB … that’s quite huge.

So let’s open the larger one and see if we can spot anything strange:

… *** 2015-11-19 12:08:07.979 *** SESSION ID:(95.53) 2015-11-19 12:08:07.979 *** CLIENT ID:() 2015-11-19 12:08:07.979 *** SERVICE NAME:(SYS$USERS) 2015-11-19 12:08:07.979 *** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2015-11-19 12:08:07.979 *** ACTION NAME:() 2015-11-19 12:08:07.979 WAIT #140056955617216: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1447931287979703 *** 2015-11-19 12:08:18.806 WAIT #140056955617216: nam='SQL*Net message from client' ela= 10826864 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1447931298806851 CLOSE #140056955617216:c=0,e=11,dep=0,type=1,tim=1447931298806957 ===================== PARSING IN CURSOR #140056955763032 len=113 dep=0 uid=0 oct=2 lid=0 tim=1447931298808784 hv=688533685 ad='b89b3418' sqlid='5v0vfdcnhnc5p' insert /*+ rule ignore_row_on_dupkey_index(table1, table1_pk) */ into mydbdba.table1 select * from mydbdba.table2 END OF STMT PARSE #140056955763032:c=2000,e=1770,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=3,plh=1708808563,tim=1447931298808778 ===================== PARSING IN CURSOR #140056955737968 len=131 dep=1 uid=0 oct=3 lid=0 tim=1447931298809899 hv=1635361899 ad='b8e8eb30' sqlid='2jfqzrxhrm93b' select /*+ rule */ c.name, u.name from con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and c.owner# = u.user# END OF STMT PARSE #140056955737968:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298809895 EXEC #140056955737968:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298810084 FETCH #140056955737968:c=1000,e=180,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=1447931298810294 STAT #140056955737968 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=8 pr=0 pw=0 time=204 us)' STAT #140056955737968 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=6 pr=0 pw=0 time=154 us)' STAT #140056955737968 id=3 cnt=1 pid=2 pos=1 obj=31 op='TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=102 us)' STAT #140056955737968 id=4 cnt=1 pid=3 pos=1 obj=56 op='INDEX RANGE SCAN I_CDEF4 (cr=2 pr=0 pw=0 time=63 us)' STAT #140056955737968 id=5 cnt=1 pid=2 pos=2 obj=28 op='TABLE ACCESS BY INDEX ROWID CON$ (cr=3 pr=0 pw=0 time=44 us)' STAT #140056955737968 id=6 cnt=1 pid=5 pos=1 obj=52 op='INDEX UNIQUE SCAN I_CON2 (cr=2 pr=0 pw=0 time=28 us)' STAT #140056955737968 id=7 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=34 us)' STAT #140056955737968 id=8 cnt=1 pid=7 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=12 us)' CLOSE #140056955737968:c=0,e=105,dep=1,type=1,tim=1447931298810435 PARSE #140056955737968:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298810877 EXEC #140056955737968:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298810985 FETCH #140056955737968:c=0,e=73,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=1447931298811098 CLOSE #140056955737968:c=0,e=23,dep=1,type=3,tim=1447931298811162 PARSE #140056955737968:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298811475 EXEC #140056955737968:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298811601 FETCH #140056955737968:c=0,e=93,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=1447931298811722 CLOSE #140056955737968:c=0,e=12,dep=1,type=3,tim=1447931298811766 PARSE #140056955737968:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298812046 EXEC #140056955737968:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298812137 FETCH #140056955737968:c=0,e=80,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=1447931298812278 CLOSE #140056955737968:c=0,e=12,dep=1,type=3,tim=1447931298812321 PARSE #140056955737968:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298812625 EXEC #140056955737968:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298812720 FETCH #140056955737968:c=0,e=72,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=1447931298812819 CLOSE #140056955737968:c=0,e=11,dep=1,type=3,tim=1447931298812861 PARSE #140056955737968:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298813149 EXEC #140056955737968:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298813268 FETCH #140056955737968:c=0,e=82,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=1447931298813379 CLOSE #140056955737968:c=0,e=12,dep=1,type=3,tim=1447931298813422 PARSE #140056955737968:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298813774 EXEC #140056955737968:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=1027684349,tim=1447931298813868 FETCH #140056955737968:c=0,e=71,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=1447931298813966 CLOSE #140056955737968:c=0,e=11,dep=1,type=3,tim=1447931298814008 …

It’s cursor #140056955737968 craziness! It’s getting called again and again and ….

Ok, let’s have a look how many times it’s actually called

SQL ID: 2jfqzrxhrm93b Plan Hash: 1027684349 select /*+ rule */ c.name, u.name from  con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and   c.owner# = u.user# call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse    99001      2.63       2.72          0          0          0           0 Execute  99001      5.44       4.02          0          0          0           0 Fetch    99001     11.73       8.65          0     792008          0       99001 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total   297003     19.81      15.40          0     792008          0       99001 Misses in library cache during parse: 0 Optimizer mode: RULE Parsing user id: SYS   (recursive depth: 1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  NESTED LOOPS  (cr=8 pr=0 pw=0 time=204 us)          1          1          1   NESTED LOOPS  (cr=6 pr=0 pw=0 time=154 us)          1          1          1    TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=102 us)          1          1          1     INDEX RANGE SCAN I_CDEF4 (cr=2 pr=0 pw=0 time=63 us)(object id 56)          1          1          1    TABLE ACCESS BY INDEX ROWID CON$ (cr=3 pr=0 pw=0 time=44 us)          1          1          1     INDEX UNIQUE SCAN I_CON2 (cr=2 pr=0 pw=0 time=28 us)(object id 52)          1          1          1   TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=34 us)<          1          1          1    INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=12 us)(object id 11) ********************************************************************************

Remember how many rows which table had? Let me remind you

• table1 with 100 000 rows 
• table2 with 199 001 rows from which 99 001 have same primary key value like rows in table1

So this SQL is called EXACLY as many times as there are matching keys (duplicates)!

What this query seems to do is to select names of owners and names of constraints enabled for table1. Why would you do that and why for EVERY failed row is really beyond me …

Oracle hint ignore_row_on_dupkey_index - part 1

At this year’s DOAG Conference, I had a session called “Think simple and space yourself a facepalm”. In one of examples, we were discussing usage of MERGE in particular situation. We came with a good solution using set operation (MINUS). After that I suggested another idea which would simplify given SQL even more. It involved usage of hint ignore_row_on_dupkey_index.

For those of you who are not familiar with this hint, you are basically telling Oracle:” Hey Oracle! I’ll fire this INSERT and I want you to ignore any rows, which will fail on ORA-0001”. So in principle, if you have a unique index to check against, you don’t need to check whether particular row adheres to your unique constraint before you try to insert it.

Example of usage looks like this (table1_pk is unique index):

insert /*+ ignore_row_on_dupkey_index(table1, table1_pk) */ into table1 select * from table2;

Now there are certain properties you have to be aware of, when using this hint.

1. If you have on target table BEFORE INSERT FOR EACH ROW trigger, it will (of course) fire for ALL rows. So for example if you are logging DML operations that way, you’ll log inserts of rows, which will not appear in table
2. At the end of the session I’ve got a question if sql%rowcount counts rows processed or only those rows, which are inserted in table. I haven’t really tested that, so now I did. It counts only rows which are inserted, so it works fine.

Now we have got to the point why I’m writing this blog post in first place. There is one last thing I’ve forgot to mention on that slide – It’s elegant but very slow.

Let me show you very simple example.

I have two tables:

• table1 with 100 000 rows 
• table2 with 199 001 rows from which 99 001 have same primary key value like rows in table1

Now let’s run a simple test:

SQL> set timing on;
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.08

SQL> insert into table1 select * from table2 t2 where not exists (select null from table1 t1 where t1.id = t2.id);
100000 rows created.
Elapsed: 00:00:01.85

SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.18

SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.82

SQL> insert /*+ ignore_row_on_dupkey_index(table1, table1_pk) */ into table1 select * from table2;
100000 rows created.
Elapsed: 00:01:27.96

SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.08

You heard me saying that “time is just a hint” that we should always compare exact measures, like logical reads or memory. But this is simply too obvious. Anyway, just to be sure, let’s trace it:

insert into table1 select * from table2 t2 where not exists<br />   (select null from table1 t1 where t1.id = t2.id) call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.03       0.03        986        139          1           0 Execute      1      2.40       2.40        733     128970       4399      100000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      2.44       2.44       1719     129109       4400      100000 insert /*+ rule ignore_row_on_dupkey_index(table1, table1_pk) */ into table1 select * from table2 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1     44.76      87.52          1      99881    1056764      100000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2     44.76      87.52          1      99881    1056764      100000

1 056 764 current reads? Something smells really bad here. Let’s find out in next part, what it is ...

Oracle hint ignore_row_on_dupkey_index - part 2

Virtual classes schedule for 1st half of 2016

Dates of virtual classes for 1st half of 2016 are up! You can register at www.michalsimonik.com

PL/SQL Profiling in Amazon AWS

In my online classes, I do prefer, when students have a chance to test in action what they have learned. My class databases run in Amazon AWS cloud using “license included” model (Oracle 12c SE One).

This solution does have advantages and also some disadvantages:

• Limitation by license – Oracle SE One (so no bitmap indexes, no partitioning, no result cache, etc. :( )
• Limitation in administration
• Technical support limitation (my case only, ‘cos I do not pay for support)

I wanted my students to be able to try at least part of process for PL/SQL Profiling. This has few challenges:

• You cannot directly access file system to create directories and files
• You cannot connect as sysdba or create objects in sys schema
• You cannot ask technical support to do that for you, ‘cos you don’t pay for the support

So let’s get through steps to get at least some profiling done.

Directory

First, you need a directory where you are able to generate your profile files. But you can’t access your file system to create one (plus to grant appropriate privileges to oracle). Luckily for us, Amazon creates his databases with defined directory DATA_PUMP_DIR where you have read/write privilege. You can check its contents by using following SQL:

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'));

So all you need to do is to grant required privileges to database user who is going to run profiling:

grant execute on DBMS_HPROF to user;
grant read, write on directory DATA_PUMP_DIR to user;

Tables

Now, under normal circumstances, you would probably log in as sysdba and run script dbmshptab.sql from $ORACLE_HOME/rdbms/admin directory …. which you can’t do.

Fortunately, DBMS_HPROF does use object names without schema, so it can be used in line with following instructions:

The tables and sequence can be created in the schema for each user who wants to gather profiler data. Alternately these tables can be created in a central schema. In the latter case the user creating   these objects is responsible for granting appropriate privileges (insert, update on the tables and select on the sequence) to all users who want to store data in the tables. Appropriate synonyms must also be created so the tables are visible from other user schemas.

So basically, you can copy/paste script and use it. I ran it in main database master schema named oracle (you define it when you are creating instance in AWS). And after that I used following commands:

create or replace public synonym dbmshp_runs for oracle.dbmshp_runs;
create or replace public synonym dbmshp_function_info for oracle.dbmshp_function_info;
create or replace public synonym dbmshp_parent_child_info for oracle.dbmshp_parent_child_info;
create or replace public synonym dbmshp_runnumber for oracle.dbmshp_runnumber;

grant select, insert, update, delete on oracle.dbmshp_runs to user;
grant select, insert, update, delete on oracle.dbmshp_function_info to user;
grant select, insert, update, delete on oracle.dbmshp_parent_child_info to user;
grant select on oracle.dbmshp_runnumber to user;

Profiling

Now we can start profiling as usual:

exec DBMS_HPROF.START_PROFILING(' DATA_PUMP_DIR', 'prof.txt')
…
exec DBMS_HPROF.STOP_PROFILING;

To view contents of file, you can use following SQL:

select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR',' prof.txt '));

Ok. We can run analyze now …

SET SERVEROUTPUT ON;
DECLARE
 v_runid INTEGER;
BEGIN
 v_runid := DBMS_HPROF.ANALYZE( location => 'DATA_PUMP_DIR',
                                                      filename => 'prof.txt');
 DBMS_OUTPUT.PUT_LINE('RUNID: '||v_runid);
END;

/

… and select the contents of tables DBMSHP_RUNS, DBMSHP_FUNCTION_INFO, etc.

Unfortunately this is as far as you can go in these conditions and setup. You cannot run plshprof go create HTML report. I’m kind of baffled that Oracle did not provide database version of this utility.

I'm Amazon AWS noob, so feel free to correct me if I'm wrong. :)

Database duplication

There is a decent share of blogs and articles about database duplication but I thought its worth to give my share too. Recently I did quite simple database copy for testing purposes and I ran into a few bumps so I thought it might be interesting.

Goal was, as usual, to create a duplicate database with different SID. In my case the problem was little more complex since source database was RAC and we wanted the new one to be also. As of now only way to do that is to copy you database as single and then convert it to RAC. But we will not go into that today.

So, let’s start:

Create PFILE (initnewSID.ora) for your auxiliary database

Important parameter are:

db_name – Axiliary database SID
db_block_size – Database block size
db_create_file_dest – File destination for OMF
db_file_name_convert – Conversion of location for files
log_file_name_convert – Conversion of location for redlogs

Mine looked like this:

db_name=newSID
db_block_size=8192
db_create_file_dest=+DATA3
db_file_name_convert=(+DATA,+DATA3)
log_file_name_convert=(+DATA,+DATA3)
compatible='11.2.0.3'

You can get RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 11.2.0.3.0 incompatible with ORACLE version 11.2.0.0.0 error, if you are using compatible parameter in your target database. Then you’ll have to add it into your auxiliary PFILE

Create password file

$ orapwd file=orapwnewSID password=MyPassworrd entries=20

Start auxiliary database in nomount

$ sqlplus / as sysdba
SQL> startup nomount

Start target database in mount

$ sqlplus / as sysdba
SQL> startup mount;

Now, before you move forward, I do suggest you edit tnsnames.ora and listener.ora to register your auxiliary database

Example:

cd /opt/oracle/product/11.2.0/dbhome_4/network/admin
vi tnsnames.ora

newSID =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
  (CONNECT_DATA =
  (SERVER = DEDICATED)
   (SERVICE_NAME = newSID.mydomain.com)
  )
 )

vi listener.ora

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = newSID.mydomain.com)
   (SID_NAME = SCVON)
   (ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_4)
  )
 )

$ lsnrctl reload

Run rman and connect to both target and auxiliary database

$ rman nocatalog
RMAN> connect target sys@oldSID
RMAN> connect auxiliary sys@newSID

Start duplication

duplicate target database to newSID from active database;

If you get error like ORA-01103: database name 'oldSID' in control file is not 'newSID', when starting new database, just check your PFILE, if it did not get messed up by rman. If so, just correct the values in PFILE and try to restart your new database.

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Just a quick post today, recently I was extending advanced replications with table containing varray. Creation of remote materialized view failed with

ORA-22804: remote operations not permitted on object tables or user-defined type columns

I found Domagoj’s blog post very helpful and it did the trick. So if you have same problem, I would suggest to you to go and check it out: https://blog.dsl-platform.com/query-user-defined-types-over-database-link/