Monday, January 13, 2014

Oracle 12c. Accessing tables in PDB from CDB or another PDB.

If you are reading this, you should know that Oracle 12c  introduced the new big feature – Pluggable Data Bases. Finally Oracle has made the thing that was available in other RDBMSs for years – now you can have multiple databases run by one instance. This is available from very beginning in RDBMSs such as Sybase, DB2, MSSQL, MySQL… you name it.

In these RDBMSs a user being currently in one database can access tables in another database. For example, in MSSQL you can be in DB01 and select data from DB02 (given that you have permissions):

use DB01
select * from DB02.dbo.ATABLE


It is not that simple in Oracle 12c! You cannot specify PDB before schema. You have to switch or to connect to a PDB to access data in that.

ALTER SESSION SET CONTAINER=PDB02;
select * from ASCHEMA.ATABLE;


Or

CONNECT ASCHEMA@PDB02
select * from ATABLE;


But what if you need to select data from a PDB while you are in CDB or in another PDB?
Oracle documentation states: “The common user cannot query tables or views in a PDB.” 1
“Wait a second! How to query/join data from different PDBs for reports or comparisons?” you may ask.
Relax, it is still possible. The answer is – DATABASE LINK.

To make it possible you should first modify tnsnames.ora file on database server, in Oracle Home of the instance.

Add there TNS entries for PDBs you would like to access.

pdb01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb01)
    )
  )


Second, create DB link.

connect system/password
create public database link PDB01 using 'PDB01';
select * from ASCHEMA.ATABLE@PDB01;


Done!

In this case the DB link was created in CDB, so common users can access data in PDB01.
If you need to allow a local user to access another PDB you should create DB link in the PDB of the local user.

You can play with public or user’s DB links to build security you need, main idea is to access PDBs via DB links.

References:
1.           Oracle® Database Administrator's Guide 12c Release 1 (12.1) , Chapter 40 Administering a CDB with SQL*Plus http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN13616 )

Thursday, December 19, 2013

Poor export performance of SAP database and how to speed it up.

During an export (regular exp not datapump) of SAP database with CONSISTENT=YES I've found that performance was suboptimal. There was 100% CPU consumption, and even empty tables were exported one per 2-3 sec!
The export took so long time that finally caused it crash with "Snapshoot too old".

Investigation showed that the exp called "SYS"."DBA_CAPTURE_PREPARED_TABLES" view for each table. And, you may be know, there can be 80-90 thousands of tables in SAP schema.
In my DB there were 3 SAP schemas so total number of tables was quite big.

I've got an execution plan of the offensive query that exp called:

SELECT NVL(MAX(CO.SCN), 0) FROM ALL_CAPTURE_PREPARED_TABLES CO WHERE CO.TABLE_OWNER = :B2 AND CO.TABLE_NAME = :B1;

And the plan shows full scan of SYS.CDEF$ table.

ALL_CAPTURE_PREPARED_TABLES calls DBA_CAPTURE_PREPARED_TABLES.
The query of DBA_CAPTURE_PREPARED_TABLES view in my Oracle 10g is:

 select u.name, o.name, co.ignore_scn, co.timestamp,
       decode(bitand(cd.flags, 1), 1,
              decode(bitand(co.flags, 1), 1, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 2), 2,
              decode(bitand(co.flags, 2), 2, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 4), 4,
              decode(bitand(co.flags, 4), 4, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 8), 8,
              decode(bitand(co.flags, 8), 8, 'IMPLICIT', 'EXPLICIT'), 'NO')
  from obj$ o, user$ u, streams$_prepare_object co,
       (select obj#, sum(DECODE(type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)) flags
          from sys.cdef$ group by obj#) cd

  where o.obj# = co.obj# and o.owner# = u.user# and co.obj# = cd.obj#(+);


Please note highlighted text. The combination of aggregating inline view cd and its left outer join caused Oracle to full scan whole CDEF$ table, aggregate it to calculate "FLAGS" for each table and then hash join it to just one row from streams$_prepare_object to get cd.FLAGS.
Not good.

To speed up the query I tried all legitimate ways - gathered stats, set performance hints like FIRST_ROWS(1) PUSH_PRED(cd) USE_NL_WITH_INDEX(cd I_CDEF2) and so on.
But nothing convinced the optimizer to push OBJ#= predicate into the cd inline view.

So I've just ended up with total rewrite the view's query as:

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_CAPTURE_PREPARED_TABLES" ("TABLE_OWNER", "TABLE_NAME", "SCN", "TIMESTAMP", "SUPPLEMENTAL_LOG_DATA_PK", "SUPPLEMENTAL_LOG_DATA_UI", "SUPPLEMENTAL_LOG_DATA_FK", "SUPPLEMENTAL_LOG_DATA_ALL") AS
  /* made by MM to speedup export */

  select
       u.name, o.name, co.ignore_scn, co.timestamp,
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 1), 1,
              decode(bitand(co.flags, 1), 1, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 2), 2,
              decode(bitand(co.flags, 2), 2, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 4), 4,
              decode(bitand(co.flags, 4), 4, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 8), 8,
              decode(bitand(co.flags, 8), 8, 'IMPLICIT', 'EXPLICIT'), 'NO')
  from streams$_prepare_object co
  join obj$ o on o.obj# = co.obj# 
  join user$ u on o.owner# = u.user# 
  left outer join sys.cdef$ cd on co.obj# = cd.obj#
  group by u.name, o.name, co.ignore_scn, co.timestamp, co.flags;


that produces same results, but 5000 times faster.
I know, it is not good to modify dictionary objects. But what else to do when you need the thing done fast?
Export of empty tables become lightning fast, and full export has been finally taken.
If you are going to do this trick too, do not forget to restore the original view's SQL, that is provided by Oracle.

Saturday, February 23, 2013

Setup Subversion 1.7 into JDeveloper 11R2

I do not know for what reason Oracle JDeveloper R2 still does not support Subversion 1.7, but I've managed to make it to support it. I've done it n Linux, but you can apply it for Windows too.
So.
That is what I have:
- Ubuntu 11.20 Linux 3.5.0-24-generic SMP x86_64
- JDeveloper Studio Edition 11.1.2.3.0

1. Check what Subversion is installed in JDeveloper.
Start JDeveloper and open menu Tools -> Preferences, select Versioning/Subversion: see
SVNKit/1.3.5 Client installed. JNA disabled.

2. Close JDeveloper.

3. This step is optional. Install linsvn-java package if you think you will need some fancy ways to login to Subversion using system security.

sudo apt-get install libsvn-java

4. Download SVNKit Standalone Version 1.7 from http://svnkit.com/download.php
cd /download/svn/
wget http://www.svnkit.com/org.tmatesoft.svn_1.7.8.standalone.zip
unzip  org.tmatesoft.svn_1.7.8.standalone.zip


/download/svn/svnkit-1.7.8 directory is created

5. Download Subclipse 1.8 from http://subclipse.tigris.org
cd /download/subclipse/
wget http://subclipse.tigris.org/files/documents/906/49260/site-1.8.18.zip
unzip site-1.8.18.zip 

later we will need only files from /download/subclipse/plugins

5. Go to JDeveloper extension directory (my JDev is installed in /opt/oracle/) and do following:
cd /opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/extensions
mv oracle.jdeveloper.subversion oracle.jdeveloper.subversion-1.3
mkdir oracle.jdeveloper.subversion-1.7
ln -s oracle.jdeveloper.subversion-1.7 oracle.jdeveloper.subversion

6. Copy required SVNKit files here and link them as "versionless" names
cd oracle.jdeveloper.subversion
cp /download/svn/svnkit-1.7.8/lib/antlr-runtime-3.4.jar .
cp /download/svn/svnkit-1.7.8/lib/svnkit-1.7.8.jar .
cp /download/svn/svnkit-1.7.8/lib/sqljet-1.1.6.jar .
cp /download/svn/svnkit-1.7.8/lib/sequence-library-1.0.2.jar .
cp /download/svn/svnkit-1.7.8/svnkit-javahl16-1.7.8.jar .
cp /download/svn/svnkit-1.7.8/trilead-ssh2-1.0.0-build215.jar .

ln -s antlr-runtime-3.4.jar antlr-runtime.jar
ln -s svnkit-1.7.8.jar svnkit.jar
ln -s sqljet-1.1.6.jar sqljet.jar
ln -s sequence-library-1.0.2.jar sequence-library.jar
ln -s svnkit-javahl16-1.7.8.jar svnkit-javahl.jar
ln -s trilead-ssh2-1.0.0-build215.jar trilead.jar


7. Copy required Subclipse files here and link them as "versionless" names

cp /download/subclipse/plugins/net.java.dev.jna_3.4.0.t20120117_1605.jar .
cp /download/subclipse/plugins/org.tigris.subversion.clientadapter_1.8.3.jar .

cp /download/subclipse/plugins/org.tigris.subversion.clientadapter.javahl_1.7.8.1.jar .
cp /download/subclipse/plugins /org.tigris.subversion.clientadapter.svnkit_1.7.8.1.jar . 

ln -s net.java.dev.jna_3.4.0.t20120117_1605.jar jna.jar
ln -s org.tigris.subversion.clientadapter_1.8.3.jar svnClientAdapter.jar 
ln -s org.tigris.subversion.clientadapter.javahl_1.7.8.1.jar svnjavahl.jar
ln -s org.tigris.subversion.clientadapter.svnkit_1.7.8.1.jar casvnkit.jar

5. Go to JDeveloper jdev/bin directory and open jdev.conf file in your favorite text editor:
cd /opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/bin
gedit jdev.conf

6. Add following to the end
#
# Needed for Subversion 1.7
#
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/antlr-runtime.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnkit.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/casvnkit.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/sqljet.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnClientAdapter.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnjavahl.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/trilead.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnkit-javahl.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/sequence-library.jar

# If you want to use JNA
#AddNativeCodePath /usr/lib/x86_64-linux-gnu/jni/libsvnjavahl-1.so
#AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/jna.jar

# for debug
#AddVMOption -Djava.util.logging.config.file=../extensions/oracle.jdeveloper.subversion/svnkit-logging.conf
#AddVMOption -verbose


7. Check what Subversion is now installed in JDeveloper.
Start JDeveloper and open menu Tools -> Preferences, select Versioning/Subversion: see
SVNKit/1.7.8 Client installed. JNA Disabled.

Done!
Now you can connect to your Subversion 1.7 repository and use Versioning Navigator, commit files in Application Navigator etc.


Some additional notes!

I had issues when I wanted to add a project or an application to Versioning (using JDK 1.6 (i386) and 1.7 (amd64) ). SVNKit returned exception
 java.lang.NullPointerException at org.tmatesoft.svn.core.internal.wc.SVNClassLoader.loadProperties(SVNClassLoader.java:193)

I've filed the bug to SVNKit issues tracker http://issues.tmatesoft.com/issue/SVNKIT-352 .
However if you do not want to wait for official update that includes the fix, you can fix it yourself.
1. unzip sources
cd /download/svn/svnkit-1.7.8-nojna/src
mkdir svnkit
cd svnkit
unzip ../svnkit-1.7.8-sources.jar 

2. unzip classes
cd /opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/extensions/oracle.jdeveloper.subversion
mkdir svnkit
cd svnkit
unzip ../svnkit.jar

3. copy source to classes
cd org/tmatesoft/svn/core/internal/wc/ 
cp /download/svn/svnkit-1.7.8-nojna/src/svnkit/org/tmatesoft/svn/core/internal/wc/SVNClassLoader.java .


4. modify code to fix the bug
Open for edit SVNClassLoader.java
go to line 193

resourceStream = SVNClassLoader.class.getClassLoader().getResourceAsStream(svnkitPropertiesResource);
delete it

insert here the following

ClassLoader cl = SVNClassLoader.class.getClassLoader();
if (cl == null) cl = ClassLoader.getSystemClassLoader();
resourceStream = cl.getResourceAsStream(svnkitPropertiesResource);

save, exit from editor


5. compile the file
/opt/oracle/jdk1.7.0_15-amd64/bin/javac -g:source,lines,vars -source 1.5 -target 1.5 -cp /opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/extensions/oracle.jdeveloper.subversion/sequence-library.jar:/opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/extensions/oracle.jdeveloper.subversion/svnkit.jar SVNClassLoader.java

6. modify jdev.conf to use svnkit dir instead of svnkit.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnkit

If you need that SVNKit to generate a detailed output create file
../extensions/oracle.jdeveloper.subversion/svnkit-logging.conf and populate it with something like
svnkit.level=FINEST
svnkit-network.level=FINEST
svnkit-fsfs.level=FINEST
svnkit-wc.level=FINEST
svnkit-cli.level=FINEST


----------
Note. By some reason History in JDeveloper does not work with SVN. Though you can see change log in Version Navigator or other ways.











Tuesday, February 19, 2013

Setup of Alsa on EMU0404 PCI

If you have no idea what is Alsa, Jack and emu10k1 you can stop reading right here.

I've built a new PC based on ASUS mobo P8Z77-M and installed Ubuntu Studio 12.10 on it. Also I've plugged emu0404 PCI (not PCIe) sound card into it. Though the card is supported by Soundblaster/Emu only for Windows, there are Alsa drivers for Linux too. I've successfully installed alsa-base and other alsa packages that include emu10k1 driver and firmware for it. However there were some issues.

Issue #1. Initially output to emu was broken. aplay repeated 1 sec fragment of sound many times. The issue is appeared to be in poor interrupts distribution by BIOS, or by inability this card to share an interrupt with other devices, in my case it was USB host controller. I could not plug the card into another slot, because there is only one legacy PCI slot on mobo. I tried to boot with kernel parameters acpi=off noapic and other acpi and pci options. Sometimes it worked, but often not.
The issue has been fixed by updating BIOS to 1806.

Issue #2. Playback to emu is perfect but there is no input capture. I've turned on all switches in alsamixer, maxed up all shaders. Still no capture. Somehow I've googled this thread: https://ardour.org/node/4061 . I started to set all alsamixer settings according to what linuxdsp user has recommended. And Ta-Daa! Sound got captured into Alsa. The important thing that was not described anywhere is to set internal routing in emu0404.
Specifically, in alsamixer on Capture tab set [DSP 0] to [0202 ADC Left] and [DSP 1] to [0202 ADC Right] accordingly.

Monday, December 17, 2012

PIOB started

I was looking for a tool to measure I/O from Oracle to disks. The only tools were available ORION and SLOB. These are very good tools and they do the job, however these have some limitations.
First of all they are OS dependable. Even SLOB that is mostly implemented in PL/SQL. It also uses Bash and one binary component for inter-process communication. It also uses AWR that should be licensed additionally.
I've decided I'll create something similar but without these limitations.
Couple of days of work and it is done.
It is named PIOB - PL/SQL Input/Output Benchmark (tool).

It generates different kinds of I/O using Oracle database means and measures it.

As it said, the PIOBs idea is same as SLOB (Silly Little Oracle Benchmark) - PIOB benchmarks disk I/O using Oracle.
You can use PIOB for similar tasks - tuning of disk subsystem on a machine where you have your Oracle.

How it works

   When you call PIOB_TOOL.RUN* it will create several Oracle Scheduler jobs for
   workers that you specified with *_WORKERS parameter(s).
   These jobs will simultaneously execute workers.
   Oracle I/O metrics will be saved just before workers start generating
   actual I/O and right after that.
   When you run PIOB_TOOL.REPORT the saved metrics will be used to calculate
   more commonly used metrics: IO/s for short random I/O and MB/s for long sequential I/O.

It can create any combination of 5 types of workload:
- Short Read - random reads by one DB block - index access.

- Long Read - random reads by multiple DB blocks - full table scans.
- Light Write - modification of one column in random table row: index access, one block modification, little redo.
- Heavy Write - modification of all columns in random range of table 256 rows: index range scan, multiple block modification, large redo.
- Redo Write - modification of all column in one table row: index range scan, same one block modification, large redo.

Other features of PIOB are:
 - it is OS and platform independent because it is implemented purely in
   Oracle SQL*Plus, SQL and PL/SQL;
 - it does not use Oracle AWR (AWR requires additional licensing);
 - it takes snapshots of only relevant performance metrics;
 - it is instrumented for monitoring its progress;
 - SEED table is tuned to have 1 row per 8K block for more precise calculation
   of random I/O;
 - it uses RECYCLE buffer of minimal size to reduce caching (can be turned off);
 - it keeps some configuration parameters in its repository;
 - its work can be limited by amount of work cycles or by timer;
 - it can be stopped anytime.

Of course it is free, GPLv3.

You can download it from PIOB SF project page https://sourceforge.net/projects/piob/?source=directory

Enjoy!

Wednesday, November 07, 2012

How to use Cygwin svn and TortoiseSVN both, or fixing " Unable to open an ra_local session to URL" svn error

I have SVN repo located in C:/SVNrepo. And I have a working copy created with TortoiseSVN.
Sometimes I need to access my machine remotely with just command line. I use Cygwin SSH for this.
Sometimes in Cygwin bash session I need to use svn command, for example to update or commit my project working copy.
Well. It did not work. :(
The cause is that Subversion stores path to repository in working copy directory .svn. Inside this svn repo path is set by a tool that created the working copy. If the working copy was created by TortoiseSVN, the repo path will be DOS/Windows path - C:/SVNrepo. Or whatever path you have, important is that it is in DOS/Windows convention, it starts with a drive letter followed by colon.
Cygwin in general can honor such pathes. However its svn utility cannot.

For example it will fail if you issue:

$ svn ls file:///C:/SVNrepo
svn: E180001: Unable to connect to a repository at URL 'file:///C:/SVNrepo'
svn: E180001: Unable to open an ra_local session to URL
svn: E180001: Unable to open repository 'file:///C:/SVNrepo'


Cygwin's svn honors only Cygwin's path convention - /cygdrive/c/SVNrepo.

$ svn ls file:///cygdrive/c/SVNrepo
 project1/
 project2/

As you can see, the repo URL has three slashes in prefix - file:///
First two belong to the prefix itself - file://. The last slash means filesystem root.
It means that when ra_local library parses the URL 'file:///C:/SVNrepo' it will take out "file://" part, and try to find '/C:/SVNrepo' directory. And, of course, it fails! - there is no such path in my system.
Despite there is C:/SVNrepo, there is no /C:/SVNrepo.

What to do?!

To create a link!

ln -s /cygdrive/c /C:

check it is created

ls -l /
...
lrwxrwxrwx   1 mark           None     11 Nov  7 14:25 C: -> /cygdrive/c
...


ensure svn likes it

$ svn ls file:///C:/SVNrepo
 project1/
 project2/

It does!
It works now!





Thursday, June 07, 2012

Refreshing multiple summary tables in one DML

In data warehouses summary (or aggregation) tables sometimes are used to speed up some expensive aggregations.

For example there are several summary tables that are computed from one fact table across different dimensions. After the fact table is added by a new portion of data all these summary tables have to be refreshed. Usually it is done one-by-one summary table, using multiple INSERT...SELECT statements. It is OK if the fact table is not big. If the table is huge, it can be too expensive to go through it again and again for each summary table.
In Oracle you can do it in one step!

There are:
- INSERT ALL statement that allows to insert into multiple tables. These tables can be different.
- Couple of multi-aggregation GROUP BY options such as ROLLUP,  GROUPING SET, CUBE to aggregate across multiple or all dimension combinations.

This can be used to refresh all summary tables at once.

Example:

1. Create "fact" table

create table dba_segm as
select owner, tablespace_name, segment_type, segment_name, bytes
from dba_segments;

2. Create empty summary tables for all dimension combinations (though you do not have to have all combinations, you can have just some of them, the method is still applicable. You can also use GROUP BY GROUPING SET in such cases):

create table dba_segm_sum_by_o as
select owner, bytes from dba_segm where 1=0;

create table dba_segm_sum_by_s as
select tablespace_name, bytes from dba_segm where 1=0;

create table dba_segm_sum_by_t as
select segment_type, bytes from dba_segm where 1=0;

create table dba_segm_sum_by_ot as
select owner,segment_type, bytes from dba_segm where 1=0;

create table dba_segm_sum_by_os as
select owner,tablespace_name, bytes from dba_segm where 1=0;

create table dba_segm_sum_by_st as
select tablespace_name,segment_type, bytes from dba_segm where 1=0;

create table dba_segm_sum_by_ost as
select owner,tablespace_name,segment_type, bytes from dba_segm where 1=0;




3. Finally populate all summary tables at once:

INSERT ALL
  when (owner is not null and tablespace_name is not null and segment_type is not null)
   then into dba_segm_sum_by_ost(owner,tablespace_name, segment_type, bytes)
  when (owner is not null and tablespace_name is not null and segment_type is null)
   then into dba_segm_sum_by_os(owner,tablespace_name,bytes) values (owner,tablespace_name,bytes)
  when (owner is not null and tablespace_name is null and segment_type is not null)
   then into dba_segm_sum_by_ot(owner,segment_type, bytes) values (owner,segment_type, bytes)
  when (owner is null and tablespace_name is not null and segment_type is not null)
   then into dba_segm_sum_by_st(tablespace_name, segment_type, bytes) values (tablespace_name, segment_type, bytes)
  when (owner is not null and tablespace_name is null and segment_type is null)
   then into dba_segm_sum_by_o(owner,bytes) values (owner,bytes)
  when (owner is null and tablespace_name is not null and segment_type is null)
   then into dba_segm_sum_by_s(tablespace_name, bytes) values (tablespace_name, bytes)
  when (owner is null and tablespace_name is null and segment_type is not null)
   then into dba_segm_sum_by_t(segment_type, bytes) values (segment_type, bytes)
select owner, tablespace_name, segment_type, sum(bytes) bytes
from dba_segm
GROUP BY CUBE (owner,tablespace_name, segment_type);

Done!

If you look into execution plan of this statement, you will find that table DBA_SEGM has been accessed once.