One does not simply create table with 300 columns

There was quite an interesting post on Oracle OTN, which is worth of this blog post and meme.

Problem was defined as follows:

I have a table which has 300 columns and keeps records 60,00,000 at this time. Records are increasing day by day. When I run a simple query like:

select * from table where store_no = 17

this query takes 30 to 60 seconds for fetching the data against this query which is to much time, while I'll use this table with more filters. Can anyone help me that how i can increase data fetching performance, i have also used a primary key indexing.

If you check supplied DDL against given SQL, you'll likely come to two points

1. There is no index on column store_no and so Oracle will have to do full table scan
2. ... 300 columns!?

My suggestion was to reconsider the data model. 300 columns are pretty rough and columns like IT_CLERK_ID2. IT_CLERK_ID3, ... , IT_CLERK_ID5 tend to be suspicious.

Reason for my suggestion was that Oracle will have to split rows in this table in two pieces because they have more than 255 columns. Interestingly enough, the split will be done from the end of the row. So first piece of the row will have first 45 columns and second piece will contain 255 columns. What should come to your mind now is that order of columns in such table is very important and you should place columns you are going to select as close as possible to the start of the row.

Result of this split will be extra CPU usage when selecting rows from second piece. Secondary result might come in form of row chaining between database blocks which will introduce additional single block reads to walk your rows.

Now let's have a little test. I've created two tables where all columns are VARCHAR2 with value 'X' except first and last column which is NUMBER with value 1. First table has 250 columns and second has 300.

I've traced four following SQL statements:

SELECT SUM(c1) from table_250;
SELECT SUM(c250) from table_250;
SELECT SUM(c1) from table_300;
SELECT SUM(c300) from table_300;

Let's check important parts of trace files:

SELECT SUM(c1) from table_250 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.02 1 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 1.27 10.58 71553 142903 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 1.27 10.60 71554 142904 0 1 SELECT SUM(c250) from table_250 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.59 326 113 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 2.06 11.30 71554 142903 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 2.08 11.90 71880 143016 0 1 SELECT SUM(c1) from table_300 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 1 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 1.60 16.12 90933 181767 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 1.60 16.13 90934 181768 0 1 SELECT SUM(c300) from table_300 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.24 148 82 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 6.92 18.01 90934 2181767 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 6.94 18.26 91082 2181849 0 1

As you can see, sum of column c300 is quite CPU heavy. There are also other consequences of row chaining with connection to table full scan and buffer cache ... which is not as good as you can guess by now.

I would encourage you to check blog post by Jonathan Lewis which covers buffer cache and other important things.

So remember folks ... one does not simply create 300 columns table. You have to have very good reason for that and you have to think about order of columns and consequences.

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/

I'm speaker at DOAG 2015

Facepalm is comming to DOAG 2015! :)

I'm happy to announce that I'll be presenting "Think simple and spare yourself a facepalm" session at developer track at DOAG 2015. Looking forward to meet you there!

Parallel facepalm

If you are used to parallel execution in Oracle, you’ve definitely read through their documentation on the matter ones or twice (http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#i1006712) … I did. And in that in mind I was bashing my head against this simple query:

UPDATE /*+ parallel(d) */ my_table d SET d.flag = 'A';

Table is partitioned by range with 38 partitions. So there is nice opportunity to run this DML in parallel from read to write. So I’ve set up the environment …

ALTER SESSION ENABLE PARALLEL DML;

Checked execution plan and …

----------------------------------------------- | Id  | Operation             | Name          | ----------------------------------------------- |   0 | UPDATE STATEMENT      |               | |   1 |  UPDATE               | PBVADAVKY_VZP | |   2 |   PX COORDINATOR      |               | |   3 |    PX SEND QC (RANDOM)| :TQ10000      | |   4 |     PX BLOCK ITERATOR |               | |   5 |      TABLE ACCESS FULL| MY_TABLE     | -----------------------------------------------

Ok, how about forcing it.

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;

----------------------------------------------- | Id  | Operation             | Name          | ----------------------------------------------- |   0 | UPDATE STATEMENT      |               | |   1 |  UPDATE               | PBVADAVKY_VZP | |   2 |   PX COORDINATOR      |               | |   3 |    PX SEND QC (RANDOM)| :TQ10000      | |   4 |     PX BLOCK ITERATOR |               | |   5 |      TABLE ACCESS FULL| MY_TABLE     | -----------------------------------------------

Ok …. We’ll …. After 1-2 hours …. Long story short:

SELECT trigger_name, trigger_type from DBA_TRIGGERS WHERE table_name='MY_TABLE';

TRIGGER_NAME                   TRIGGER_TYPE ------------------------------ ---------------- MY_TABLE_UPD              BEFORE EACH ROW MY_TABLE_DEL              BEFORE EACH ROW MY_TABLE_INS              BEFORE EACH ROW

So remember folks, you should remember your data model ... and If you don’t …. have a proper look first (http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#BEICCEJJ).

ORA-00600: internal error code [kkmupsViewDestFro_4]

After an upgrade to Oracle 11.2.0.4 (from 11.2.0.3), one of our batch jobs in test environment starting crushing on following error:

SQL Error: ORA-00600: internal error code [kkmupsViewDestFro_4], [0], [8024169], [], [], [], [], [], [], [], [], []
00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
               exceptions. This indicates that a process has encountered an
               exceptional condition.
*Action:   Report as a bug - the first argument is the internal error number

SQL had nothing special in it:

MERGE INTO TABLE1 a USING
   (SELECT d.*
   FROM TABLE2 d,
            TABLE3 t
    WHERE d.cola   = t.cola
    AND d.colb   = t.colb
   ) b ON (a.col1 = b.col1 AND a.col2 = b.col2)
WHEN MATCHED THEN
  UPDATE
  SET ….
  WHERE a.SCN <= b.SCN
WHEN NOT MATCHED THEN
  INSERT
    (
      …
    )
    VALUES
    (
      …
    )

I searched through Oracle My Support and found article ORA-600 [kkmupsViewDestFro_4] During Merge Statement (Doc ID 1181833.1): 

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=534519724153934&id=1181833.1&_adf.ctrl-state=b2we448s9_695

One of the solution was to upgrade to Oracle 11.2.0.2 … Hmmmm … well, that didn’t work out :)

So I tried next one:

alter session set "_optimizer_join_elimination_enabled"=false;

Nope … and next one:

alter session set "_fix_control"="7679164:OFF";

Nope … and next one … recode the MERGE statement, for example add ROWNUM pseudo column:

MERGE INTO TABLE1 a USING
   (SELECT d.*, ROWNUM
   FROM TABLE2 d,
            TABLE3 t
    WHERE d.cola   = t.cola
    AND d.colb   = t.colb
   ) b ON (a.col1 = b.col1 AND a.col2 = b.col2)
WHEN MATCHED THEN
  UPDATE
  SET ….
  WHERE a.SCN <= b.SCN
WHEN NOT MATCHED THEN
  INSERT
    (
      …
    )
    VALUES
    (
      …
    )

And voilà … it’s working!

I still don’t understand why we got the error in 1st place … should have been fixed in 11.2.0.2

How to estimate space needed for archive logs

We are back in LinkedIn and this time in Oracle senior DBA group discussion. The question is:

How to calculate necessary space on File System for put in archive log mode an database oracle 11g, currently is not in archive log mode, thanks

Space needed for your archive log location(s) can be estimated by following script, which gets average redo log file size (which should be same for all files BTW) and basically multiplies that by number of log file switches per day:

SELECT log_hist.*,
ROUND(log_hist.num_of_log_swithes * log_file.avg_log_size / 1024 / 1024) avg_mb
FROM
(SELECT TO_CHAR(first_time,'DD.MM.YYYY') DAY,
COUNT(1) num_of_log_swithes
FROM v$log_history
GROUP BY TO_CHAR(first_time,'DD.MM.YYYY')
ORDER BY DAY DESC
) log_hist,
(SELECT AVG(bytes) avg_log_size FROM v$log) log_file;

Archive log files usually go into same location where your “hot” backups go, like FRA. Space needed there depends on your backup strategy and retention policy. If you are deleting archive log files after each successful full or incremental backup, space needed should be count of days between backups plus some good reserve. If you are backing archive log files from archive log destination to some other device, than you should estimate needed space based on that.

ORA-12008: error in materialized view refresh path

This was just one of those days, when strange things happen. Consider this:

You have a materialized view with left outer join:

CREATE MATERIALIZED VIEW table_mw
      REFRESH FAST ON COMMIT
      WITH ROWID
      AS
SELECT
      v.ROWID v_rid,
      t.ROWID t_rid,
      s.ROWID s_rid,
      v.*,
      s.dfr,
      s.dfr
FROM
      table_v v,
      table_t t,
      table_s s
WHERE v.ref_id = t.id (+)
      AND v.ref_id = s.id (+)
/

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID
/

Now, let’s keep aside that not only you have to comply with documented rules for view to be fast refreshable (http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1006674), there is also one undocumented restriction: ANSI-joins are not supported.

With that solved, we did a simple UPDATE of one row in table TABLE_V. Everything looked fine, until we wanted to commit. Commit returned following error stack:

ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist

Cause:  Table SNAP$_<mview_name> reads rows from the view MVIEW$_<mview_name>, which is a view on the master table (the master may be at a remote site). Any error in this path will cause this error at refresh time. For fast refreshes, the table <master_owner>.MLOG$_<master> is also referenced.

Action: Examine the other messages on the stack to find the problem. See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>, <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink> still exist.

I checked alert log and trace files to find nothing of use there. After some experimentation I found out that for some reason, Oracle cannot cope with timestamp-based materialized view logs. Implementation of commit SCN-based materialized view logs solved the problem.

So in the end materialized view logs looked like this:

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID, COMMIT SCN
/

Seems like one of those problems you solve, but don’t know why they came in first place.

PS: There is also one important think you should not forget or you'll get same generic error on COMMIT of one of the source tables. The thing is that if you materialized view is in different schema, don't forget to also GRANT SELECT on materialized view logs (MLOG$, RUPD$).

How to get IOPS from AWR and Real-time

Recently an interesting question came up in Oracle Database Performance Tuning forum on LinkedIn.

The question was:

How to calculate the IOPS from AWR Report. ?

With follow up question after my first SQL:

Is there any way to calculate IOPS in real time?
What views we should use to calculate IOPS real time.
And can we use gv$ASM_IOSTAT or gv$sysmetric.

I did little Google check and linked nice blog post by G.Makino about how to get needed information from AWR Report in EM: https://gmakino.wordpress.com/2012/11/08/how-to-identify-iops-in-awr-reports .

So I figured it would be nice to share some SQL in LinkedIn forum and here.

Get IOPS from AWR:

SELECT metric_name,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(MIN(minval / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(MIN(minval),1)) END) min,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(MAX(maxval / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(MAX(maxval),1)) END) max,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(AVG(average / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(AVG(average),1)) END) avg
FROM dba_hist_sysmetric_summary
WHERE metric_name
  IN ('Physical Read Total IO Requests Per Sec',
  'Physical Write Total IO Requests Per Sec',
  'Physical Read Total Bytes Per Sec',
  'Physical Write Total Bytes Per Sec')
GROUP BY metric_name
ORDER BY metric_name;

METRIC_NAME                                                      MIN                                         MAX                                         AVG ---------------------------------------------------------------- ------------------------------------------- ------------------------------------------- ------------------------------------------- Physical Read Total Bytes Per Sec                                0 KB                                        83453.8 KB                                  415.9 KB Physical Read Total IO Requests Per Sec                          0                                           881.9                                       13.3 Physical Write Total Bytes Per Sec                               0 KB                                        8014.9 KB                                   55 KB Physical Write Total IO Requests Per Sec                         0                                           901.1                                       3.8

Get real time IOPS (It’s good to point out, that there is no such view as gv$ASM_IOSTAT). There is view gv$asm_disk_stat, but its stats are cumulative. For real time sample we have to use gv$sysmetric (v$sysmetric):

SELECT inst_id,
  intsize_csec / 100 "Interval (Secs)",
  metric_name "Metric",
  (
  CASE
    WHEN metric_name LIKE '%Bytes%'
    THEN TO_CHAR(ROUND(AVG(value / 1024 ),1))
      || 'KB'
    ELSE TO_CHAR(ROUND(AVG(value),1))
  END) "Value"
FROM gv$sysmetric
WHERE metric_name IN ('Physical Read Total IO Requests Per Sec', 'Physical Write Total IO Requests Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec')
GROUP BY inst_id,
  intsize_csec,
  metric_name
ORDER BY inst_id,
  intsize_csec,
  metric_name;

   INST_ID Interval (Secs) Metric                                                           Value ---------- --------------- ---------------------------------------------------------------- ------------------------------------------          1           60.77 Physical Read Total Bytes Per Sec                                354.6KB          1           60.77 Physical Read Total IO Requests Per Sec                          16.4          1           60.77 Physical Write Total Bytes Per Sec                               27.8KB          1           60.77 Physical Write Total IO Requests Per Sec                         2.7          2            59.6 Physical Read Total Bytes Per Sec                                2460KB          2            59.6 Physical Read Total IO Requests Per Sec                          32.8          2            59.6 Physical Write Total Bytes Per Sec                               67.6KB          2            59.6 Physical Write Total IO Requests Per Sec                         3.4

You should be aware of fact that samples 60 seconds +/- on each node of RAC. So they are not exactly the same, but difference is not of a big deal.

Think simple and spare yourself a facepalm

On one of my training sessions, I was presented with a simple query which looked like this:

SELECT company,
            COUNT(*)
FROM invoices
WHERE can_access( company ) = 1
GROUP BY company;

Execution plan with statistics was a follows:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                   |      2 |        |       |   264 (100)|          |       |       |   2587 |00:00:02.84 |    2402 |       |       |          | |   1 |  VIEW                   |                   |      2 |   1733 | 36393 |   264  (24)| 00:00:04 |       |       |   2587 |00:00:02.84 |    2402 |       |       |          | |   2 |   HASH GROUP BY         |                   |      2 |   1733 | 15597 |   264  (24)| 00:00:04 |       |       |   2587 |00:00:02.84 |    2402 |    17M|  3187K|     1/0/0| |   3 |    PARTITION RANGE ALL  |                   |      2 |   2900 | 26100 |   263  (24)| 00:00:04 |     1 |    41 |    586K|00:00:03.00 |    2402 |       |       |          | |*  4 |     INDEX FAST FULL SCAN| INVOICES          |     82 |   2900 | 26100 |   263  (24)| 00:00:04 |     1 |    41 |    586K|00:00:02.74 |    2402 |       |       |          | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$2 / from$_subquery$_001@SEL$1    2 - SEL$2    4 - SEL$2 / INVOICES@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    4 - filter("CAN_ACESS"(TO_NUMBER("COMPANY"))=1)

Function can_access was containing a single SQL and some simple PL/SQL code. Purpose of this function was to filter records based on privileges of current user.

First, I told them, that I don’t like the usage of PL/SQL function at all in this kind of situation. It’s a CPU burner on SQL and PL/SQL context switches in first place, and second, it’s hiding some important information from Oracle optimizer (selectivity for example).

Response was that its legacy stuff and that they have to deal with it somehow … ouch. The problem was, that they knew that function could be ran on grouped result set (limiting calls by great deal), but they was unable to force Oracle to do so.

So I tried usual shenanigans with parentheses, no_merge, no_query_transformation and great deal of begging. But it was to no use. Execution plan looked always the same. In the end I used the following trick to do the job:

SELECT * FROM
            (SELECT /*+ no_merge */
                        company,
                        COUNT(*)
            FROM invoices
            GROUP BY company)
WHERE (SELECT can_access( company ) FROM DUAL) = 1;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |                   |      1 |        |       |  2758 (100)|          |       |       |   2537 |00:00:00.17 |    1201 |       |       |          | |*  1 |  FILTER                  |                   |      1 |        |       |            |          |       |       |   2537 |00:00:00.17 |    1201 |       |       |          | |   2 |   VIEW                   |                   |      1 |   2536 | 55792 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |   3 |    HASH GROUP BY         |                   |      1 |   2536 | 22824 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.15 |    1201 |    17M|  3187K|     1/0/0| |   4 |     PARTITION RANGE ALL  |                   |      1 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.21 |    1201 |       |       |          | |   5 |      INDEX FAST FULL SCAN| INVOICES          |     41 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.10 |    1201 |       |       |          | |   6 |   FAST DUAL              |                   |   2537 |      1 |       |     2   (0)| 00:00:01 |       |       |   2537 |00:00:00.01 |       0 |       |       |          | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2    5 - SEL$2 / INVOICES@SEL$2    6 - SEL$3 / DUAL@SEL$3 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(=1)

Half processed buffers, nice time, great! After that I left home. Next day I’m telling my success story to a colleague of mine and he is like

“... Umm ... why don’t you use HAVING?”

 So final solution should have looked like this:

SELECT company,
            COUNT(*)
FROM invoices
GROUP BY company
HAVING can_access(company) = 1;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                   |      1 |        |       |   221 (100)|          |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |*  1 |  FILTER                 |                   |      1 |        |       |            |          |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |   2 |   HASH GROUP BY         |                   |      1 |     26 |   234 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.14 |    1201 |    17M|  3187K|     1/0/0| |   3 |    PARTITION RANGE ALL  |                   |      1 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.21 |    1201 |       |       |          | |   4 |     INDEX FAST FULL SCAN| INVOICES          |     41 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.10 |    1201 |       |       |          | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    4 - SEL$1 / INVOICES@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("CAN_ACCESS"(TO_NUMBER("COMPANY"))=1)

So remember folks … don’t try to solve stuff, when you are tired after a long day and think simple. You will spare yourself some facepalm.

ORA-23313: object group is not mastered at

DISCLAIMER:

Solution presented below is a last resort solution. Official Oracle stand is that you should NEVER ever make any change in data dictionary. Always double check and make notes and backups. Use on your own risk.

One of our customers made a copy of production database (11.2.0.4) to test environment. Administrator also made all necessary changes to environment and database. When I wanted to drop our master replication group, I’ve got following error:

begin DBMS_REPCAT.DROP_MASTER_REPGROUP('MYREPGROUP'); commit; end; Error report: ORA-23313: object group "PUBLIC"."MYREPGROUP" is not mastered at MYDB.MYNEWDOMAIN.COM ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2870 ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2662 ORA-06512: at "SYS.DBMS_REPCAT", line 635 ORA-06512: at line 2 23313. 00000 -  "object group \"%s\".\"%s\" is not mastered at %s"

I queried dba_db_links and everything looked fine. So I checked database domain and global name:

SELECT * FROM GLOBAL_NAME; GLOBAL_NAME           ---------------------- MYDB.MYNEWDOMAIN.COM show parameter domain; NAME        TYPE        VALUE           ----------- ----------- ----------------- db_domain   string      mynewdomain.com

Changes he made also (unfortunately) included change of database domain and global name.

Usual solution would be to revert changes by:

alter system set db_domain='mydomain.com' scope=spfile;
alter database rename global_name to MYDB.mydomain.com;

Then bounce database, drop master replication group, change database domain and global name back and bounce database.

Unfortunately I was not able to implement this solution since database bounce was not possible because of running tests.

In the end I had to do following change in data dictionary to be able to drop master replication group:

alter table system.REPCAT$_REPSCHEMA disable constraint REPCAT$_REPSCHEMA_DEST;
update system.REPCAT$_REPSCHEMA set dblink='MYDB.MYNEWDOMAIN.COM' where sname='MYREPGROUP';
update SYSTEM.DEF$_DESTINATION set dblink='MYDB.MYNEWDOMAIN.COM' where dblink='MYDB.MYDOMAIN.COM';
alter table system.REPCAT$_REPSCHEMA enable constraint REPCAT$_REPSCHEMA_DEST;

I encourage you to study impacted tables very carefully and make backup copy before making any change. Triple check any change you are about to make. Don’t forget about remote sites which you should try to handle before dropping master site. Principle is the same, but usually change in database link is sufficient.

Presented change was done with knowledge that we WILL drop whole replication group and recreate it from scratch. We were not concerned with any delayed transactions.

Locks and Locking

If you missed Oracle Locks and Locking Virtual Session, you can still check out the presentation:



I'll probably stick another free virtual session to start of June. I would like to hear from you what you would like to see. Looking forward to your suggestions in comments bellow ...

Oracle is ignoring my DOP

Some time ago I came over interesting problem with parallel execution in Oracle Database 11g Release 2 (11.2.0.4 PSU 5) which I think is worth sharing.

One of the programmers came to me claiming, that Oracle is totally ignoring his parallel degree which he set with parallel hint. Query looked in principle like this (please keep in mind that query is specifically tailored for problem to manifest):

SELECT
            sel.item_id,
            COUNT(*)
FROM
            (SELECT
                       /*+ full(line) parallel(line, 2) */
                       DISTINCT item.item_id,
                       item.order_date
            FROM order_line line,
                      order_item item
            WHERE line.line_id = item.line_id
                       AND line.order_date = item.order_date
                       AND line.order_date BETWEEN '01012014' AND '31122014'
            ) sel,
            order_item_detail detail
WHERE detail.order_date BETWEEN '01012014' AND '31122014'
           AND detail.order_date = sel.order_date
           AND detail.item_id = sel.item_id
GROUP BY sel.item_id;

All tables and ranged partitioned by quarter of year on DATE column order_date. All indexes are local with no compression.

So I ran it and checked parallel query overview:
Username     QC/Slave Slave Set  SID  QC SID  Requested DOP Actual DOP ------------ -------- ---------- ---- ------- ------------- ----------  - p000      (Slave)  1          364  435                 4          4  - p001      (Slave)  1          433  435                 4          4  - p002      (Slave)  1          189  435                 4          4  - p003      (Slave)  1          249  435                 4          4  - p008      (Slave)  2          129  435                 4          4  - p009      (Slave)  2          188  435                 4          4  - p010      (Slave)  2          248  435                 4          4  - p011      (Slave)  2          304  435                 4          4 MYDBA        QC                  435  435  

As you can see, he was requesting DOP 2, but our parallel query overview claims he requested DOP 4. Even if that was true, how is it that we see 8 parallel slaves?

You can see something called Slave Set in our query witch has value 1 and 2. This means that Oracle has created two slave sets for processing of our query. This is because Oracle identified two operations which can be done at same time. Basically slave set 1 is producing data for slaves in set 2 which are performing our aggregation operation. Each set is respecting requested DOP, but together, they go double the DOP originally requested.

So this is why DOP 8 and not 4. Now let's try to find out why DOP 4 when we wanted 2. Let's check execution plan first:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                                       | Name              | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   | Max-Tmp | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                                |                   |        |       |       | 87984 (100)|          |       |       |        |      |            |       |       |          |         | |   1 |  PX COORDINATOR                                 |                   |        |       |       |            |          |       |       |        |      |            |       |       |          |         | |   2 |   PX SEND QC (RANDOM)                           | :TQ10006          |  57770 |  1692K|       | 87984   (1)| 00:20:32 |       |       |  Q1,06 | P->S | QC (RAND)  |       |       |          |         | |   3 |    HASH GROUP BY                                |                   |  57770 |  1692K|  2288K| 87984   (1)| 00:20:32 |       |       |  Q1,06 | PCWP |            |  5747K|  2487K|          |         | |   4 |     PX RECEIVE                                  |                   |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,06 | PCWP |            |       |       |          |         | |   5 |      PX SEND HASH                               | :TQ10005          |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,05 | P->P | HASH       |       |       |          |         | |*  6 |       HASH JOIN BUFFERED                        |                   |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,05 | PCWP |            |    32M|  7375K|    12/0/0|    6144 | |   7 |        PX RECEIVE                               |                   |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,05 | PCWP |            |       |       |          |         | |   8 |         PX SEND HASH                            | :TQ10003          |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,03 | P->P | HASH       |       |       |          |         | |   9 |          VIEW                                   |                   |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |       |       |          |         | |  10 |           HASH UNIQUE                           |                   |    132K|  4525K|  9104K| 45982   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |    32M|  6786K|     8/0/0|         | |  11 |            PX RECEIVE                           |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |       |       |          |         | |  12 |             PX SEND HASH                        | :TQ10001          |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | P->P | HASH       |       |       |          |         | |* 13 |              FILTER                             |                   |        |       |       |            |          |       |       |  Q1,01 | PCWC |            |       |       |          |         | |  14 |               NESTED LOOPS                      |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | PCWP |            |       |       |          |         | |  15 |                NESTED LOOPS                     |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | PCWP |            |       |       |          |         | |  16 |                 PX BLOCK ITERATOR               |                   |  29124 |   398K|       |   930   (1)| 00:00:14 |   KEY |   KEY |  Q1,01 | PCWC |            |       |       |          |         | |* 17 |                  TABLE ACCESS FULL              | ITEM_LINE         |  29124 |   398K|       |   930   (1)| 00:00:14 |   KEY |   KEY |  Q1,01 | PCWP |            |       |       |          |         | |  18 |                 PARTITION RANGE AND             |                   |      1 |       |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |       |       |          |         | |* 19 |                  INDEX RANGE SCAN               | ITEM_ORDER_IX2    |      1 |       |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |       |       |          |         | |  20 |                TABLE ACCESS BY LOCAL INDEX ROWID| ITEM_ORDER        |      7 |   147 |       |     3   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |       |       |          |         | |  21 |        PX RECEIVE                               |                   |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,05 | PCWP |            |       |       |          |         | |  22 |         PX SEND HASH                            | :TQ10004          |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,04 | P->P | HASH       |       |       |          |         | |  23 |          VIEW                                   | index$_join$_005  |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |* 24 |           HASH JOIN                             |                   |        |       |       |            |          |       |       |  Q1,04 | PCWP |            |    14M|  3248K|    12/0/0|         | |  25 |            PX RECEIVE                           |                   |    778K|    11M|       |  6406   (2)| 00:01:30 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |  26 |             PX SEND HASH                        | :TQ10002          |    778K|    11M|       |  6406   (2)| 00:01:30 |       |       |  Q1,02 | P->P | HASH       |       |       |          |         | |  27 |              PX BLOCK ITERATOR                  |                   |    778K|    11M|       |  6406   (2)| 00:01:30 |   KEY |   KEY |  Q1,02 | PCWC |            |       |       |          |         | |* 28 |               INDEX FAST FULL SCAN              | ITEM_ORDR_DET_IX2 |    778K|    11M|       |  6406   (2)| 00:01:30 |   KEY |   KEY |  Q1,02 | PCWP |            |       |       |          |         | |  29 |            BUFFER SORT                          |                   |        |       |       |            |          |       |       |  Q1,04 | PCWC |            |    12M|  1246K|     8/0/0|         | |  30 |             PX RECEIVE                          |                   |    778K|    11M|       | 29363   (1)| 00:06:52 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |  31 |              PX SEND HASH                       | :TQ10000          |    778K|    11M|       | 29363   (1)| 00:06:52 |       |       |        | S->P | HASH       |       |       |          |         | |  32 |               PARTITION RANGE ITERATOR          |                   |    778K|    11M|       | 29363   (1)| 00:06:52 |   KEY |   KEY |        |      |            |       |       |          |         | |* 33 |                INDEX FAST FULL SCAN             | ITEM_ORDR_DET_IX1 |    778K|    11M|       | 29363   (1)| 00:06:52 |   KEY |   KEY |        |      |            |       |       |          |         | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Well, nothing special there for a first sight, but DOP has to run up from somewhere. Let's try to limit it in our session by:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;

And run our query:
Username   QC/Slave Slave Set  SID   QC SID  Requested DOP Actual DOP ---------- -------- ---------- ----- ------- ------------- ----------  - p000    (Slave)  1          360   435                 3          3  - p001    (Slave)  1          436   435                 3          3  - p002    (Slave)  1          189   435                 3          3  - p003    (Slave)  2          249   435                 3          3  - p004    (Slave)  2          307   435                 3          3  - p005    (Slave)  2          365   435                 3          3 MYDBA      QC                  435   435                            

As we can see our DOP is 3 now. This must be an application of rule where if there is no DOP set, Oracle will use default DOP. We are limiting DOP of tables by our hint and this should be inherited to all tables, which are part of parallel query. From execution plan, we can see that Oracle is also performing parallel execution on some indexes with index fast full scan. Normally if Oracle sets to run parallel on indexes also, he'll use DOP which is set for the query. But it seems that he ignored our DOP from tables and overridden it with default value from parallel scan of indexes, since we did not specifically set it. Let's test our query with following hints then:

SELECT /*+ parallel_index(detail, item_ordr_det_ix1, 2) parallel_index(detail, item_ordr_det_ix2, 2) */
            sel.item_id,
            COUNT(*)
FROM
            (SELECT
                       /*+ full(line) parallel(line, 2) */
                       DISTINCT item.item_id,
                       item.order_date
            FROM order_line line,
                      order_item item
            WHERE line.line_id = item.line_id
                       AND line.order_date = item.order_date
                       AND line.order_date BETWEEN '01012014' AND '31122014'
            ) sel,
            order_item_detail detail
WHERE detail.order_date BETWEEN '01012014' AND '31122014'
           AND detail.order_date = sel.order_date
           AND detail.item_id = sel.item_id
GROUP BY sel.item_id;

Username  QC/Slave Slave Set  SID  QC SID  Requested DOP Actual DOP --------- -------- ---------- ---- ------- ------------- ----------  - p000   (Slave)  1          360  435                 2          2  - p001   (Slave)  1          436  435                 2          2  - p002   (Slave)  2          189  435                 2          2  - p003   (Slave)  2          249  435                 2          2 MYDBA     QC                  435  435                            

Now that's a nice BUG. Oracle has really overridden our DOP with default DOP from index parallel scans in index join.

This behavior is very hard to reproduce (I was not able to do so with my own data) and I have seen it only twice in very particular setup, so you might never run into it. But it's good to be prepared.

Character Set Migration

Just yesterday, someone on LinkedIn has created discussion with following question:

Hello,

I want to convert Character Set of database from UTF8 to US7ASCII. (Degrade)

I have used CSSCAN utility, which says very less data loss.(Few MB's)

Please suggest best possible way for this migration. This is to be performed on Dev DB.

My response was following:

Other guys might be more helpful, since I’ve never had a "luck" of changing character set.

You haven't specified your database version (you always should), so I'll point you to Oracle Database Documentation for 10g Release 2, which seems to be quite specific on the subject:
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch11charsetmig.htm#CEGDHJFF

Please google doc for your version.

My guts tell me that if DB is quite small, I would go for full Export and Import.

Michal

PS: Don't forget to backup you DB first

So I figured it would be good idea to actually try it by myself. I used full database export and import I thought would be a good solution (all is done on database version 11.2.0.4):

dbca - create database TESTDB - Character set: AL32UTF8, National Character Set: AL16UTF16

$ sqlplus / as sysdba

SQL> create user michal identified by michal;
SQL> grant dba to michal;
SQL> grant DATAPUMP_EXP_FULL_DATABASE to michal;
SQL> create directory DB_EXPORT as '/oradata/dump';
SQL> grant read,write on directory DB_EXPORT to michal; 

$ sqlplus michal/michal

SQL> create table my_test_table
(
 name varchar2(1024), 
 nname nvarchar2(1024)
)
tablespace users; 

SQL> insert into my_test_table values ('Michal Šimoník','Michal Šimoník');
SQL> insert into my_test_table values ('včelí úly','včelí úly');
SQL> insert into my_test_table values ('žvatlám šišlám','žvatlám šišlám');
SQL> commit;

$ sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/csminst.sql 
$ csscan full=y tochar=US7ASCII process=4
user: sys/Manager as sysdba 

$ view scan.err 

.... User : MICHAL Table : MY_TEST_TABLE Column: NAME Type : VARCHAR2(1024) Number of Exceptions : 3 Max Post Conversion Data Size: 14 ROWID Exception Type Size Cell Data(first 30 bytes) ------------------ ------------------ ----- ------------------------------ AAAVU3AAEAAAACsAAA lossy conversion Michal Šimoník AAAVU3AAEAAAACsAAB lossy conversion vcelí úly AAAVU3AAEAAAACsAAC lossy conversion žvatlám šišlám ------------------ ------------------ ----- ------------------------------

$ expdp michal/michal full=Y directory=DB_EXPORT dumpfile=testdb.dmp logfile=testdb.log

dbca - drop database TESTDB
dbca - create database TESTDB - Character set: US7ASCII, National Character Set: AL16UTF16

$ sqlplus / as sysdba

SQL> create user michal identified by michal;
SQL> grant dba to michal;
SQL> grant DATAPUMP_IMP_FULL_DATABASE to michal;
SQL> create directory DB_EXPORT as '/oradata/dump';
SQL> grant read,write on directory DB_EXPORT to michal;

$ impdp michal/michal full=Y directory=DB_EXPORT dumpfile=testdb.dmp logfile=testdb_imp.log
$ sqlplus michal/michal

SQL> select * from my_test_table;

NAME               NNAME ------------------ ---------------- Michal Simonik     Michal Šimoník   vceli uly          včelí úly        zvatlam sislam     žvatlám šišlám  

Results seemed as expected. But still, it's a small test. I would expect difficulties with things like advanced queuing, replications, etc.

Note:
As pointed out in the discussion, I would also encourage you to read through Oracle Document Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET in 8i, 9i , 10g and 11g (Doc ID 1283764.1) on Oracle Support.

ORA-00439: feature not enabled: Real Application Clusters

Just a quick heads up based on my recent experience.

If you are upgrading your RAC database to Oracle 11g Release 2 on Unix system (hp-ux 11.31 in my case), you might want to check if feature Real Application Clusters is enabled. Easiest way to do so is to run following select from any single database which is running from same ORACLE_HOME:

select value from v$option where parameter='Real Application Clusters';

If value is FALSE, your RAC database will not come up. Follow RAC Survival Kit: Rac On / Rac Off - Relinking the RAC Option (Doc ID 211177.1):

Login as the Oracle software owner and shutdown all database instances on all nodes in the cluster.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_on

If this step did not fail with fatal errors then proceed to next step

make -f ins_rdbms.mk ioracle

Oracle Database Architecture Virtual Session

If you missed Oracle Database Architecture Virtual Session, you can still check out the presentation:

Database Upgrade Assistant - Unexpected error occured

Yesterday I was asked to do a test upgrade from Oracle database version 11.2.0.3 to 11.2.0.4 on one of our databases. "Ok" I said, that will be easy.

Our development environment consists of two servers with grid infrastructure. Two databases are in RAC and rest are single instance databases. I wanted to pass whole grid thing and just upgrade one single database for test.

So I downloaded Oracle software and PSU from Oracle Support. Installed it all in separate ORACLE_HOME and fired dbua ...

I really love this kind of errors. I checked the trace.log file to found nothing of use there. Usual lifesaver Mr. Google did provide some help thou. I found one guy with, what seemed to be, similar problem in thread https://community.oracle.com/thread/2571929. Unfortunately solution suggested was as follows:

Oracle Support said to perform the upgrade manually.  DBUA is confused because we have grid infrastructure installed on the node to be upgraded, but the DB to be upgraded on the node is non-rac.

How persuade dbua that there is no grid infrastructure? Luckily for me in desperate move I looked in trace.log again and found this:

[main] [ 2015-03-11 21:17:17.798 CET ] [UnixSystem.isCRSConfigured:2597] configFile=/var/opt/oracle/ocr.loc
[main] [ 2015-03-11 21:17:17.799 CET ] [Utils.getPropertyValue:302] propName=ocrconfig_loc propValue=+OCR
[main] [ 2015-03-11 21:17:17.799 CET ] [UnixSystem.isCRSConfigured:2604] ocrconfig_loc=+OCR
[main] [ 2015-03-11 21:17:17.800 CET ] [Utils.getPropertyValue:302] propName=local_only propValue=false

The solution was to edit /var/opt/oracle/ocr.loc file and change parameter local_only=false to true. Restart dbua and start database upgrade and then change parameter local_only to false again.

Next time, I'll read log file more carefully ....