PL/SQL Hierarchical Profiler Oracle Database 11G release 2 (11.2)

Posted on by
2

With 11g release 1 Oracle has introduced new package DBMS_HPROF. It’s very useful for DBAs and developers to analyze execution of PL/SQL code. It enables to gather, store and organize a lot of information about executed code.

Informations gathered by this package are supported by Oracle sqldeveloper which is another great news :).

The PL/SQL hierarchical profiler features:

  • easy to install and use
  • collect execution times for SQL and PL/SQL code
  • keeps results in database tables
  • Provides subprogram-level execution summary information, such as:
    • Number of calls to the subprogram
    • Time spent in the subprogram itself
    • Time spent in the subprogram itself and in its descendent’s subprograms
    • Detailed parent-children information, for example:
      • All callers of a given subprogram (parents)
      • All subprograms that a given subprogram called (children)
      • How much time was spent in subprogram x when called from y
      • How many calls to subprogram x were from y

Continue reading →

Dynamic SQL changes in PL/SQL 11G – Oracle Database 11G release 2 (11.2)

Posted on by
Reply

New enhancements are added in 11g for dynamic SQL in PL/SQL :

  • SQL statements that are larger than 32 KB are now allowed in native dynamic SQL
  • CLOB can be used for DBMS_SQL.PARSE
  • DBMS_SQL cursor can be converted to REF CURSOR and vice versa
  • Mored data types(including object types, collections) are supported in DBMS_SQL

Continue reading →

Install Oracle 12C Release 1 (12.1) on Fedora 20

Posted on by
15

This article presents how to install Oracle 12C(12.1.0.1) Release 1 on Fedora 20.

Read following article how-to install Fedora 20 Linux:Install Fedora 20 (for comfort set 2G memory for your virtual machine before proceeding with Oracle software installation).

Check following article for latest release Fedora 21: Install Oracle 12C Release 1 (12.1) on Fedora 21

Software

Software for 12CR1 is available on OTN or edelivery

Database software

linuxamd64_12c_database_1of2.zip 
linuxamd64_12c_database_2of2.zip

Continue reading →

Install Oracle 11G Release 2 (11.2) on Fedora 20

Posted on by
12

This article presents how to install Oracle 11G on Fedora 20.

Read following article how to install Fedora 20 Linux: Install Fedora 20 (for comfort set 2G memory for your virtual machine before proceeding with Oracle software installation).

Installation software is available on OTN version 11.2.0.1 or metalink 11.2.0.4. In this installation I’m presenting installation for 11.2.0.4 but for previous version 11.2.0.X it shouldn’t be different.

Check article for new release Install Oracle 12C Release 1 (12.1) on Fedora 20

Oracle software which I verified

release 11.2.0.4

p13390677_112040_Linux-x86-64_1of7.zip 
p13390677_112040_Linux-x86-64_2of7.zip

Continue reading →

New parallel parameters and options – Oracle Database 11G release 2 (11.2)

Posted on by
1

New parameters and parallel behaviour are introduced in Oracle 11g that simplifies management of parallel DOP calculations just before execution of a query in database.

New parameters

PARALLEL_MIN_TIME_THRESHOLD - any number > 0, AUTO(default)
PARALLEL_DEGREE_POLICY - MANUAL(default),LIMITED,AUTO
PARALLEL_DEGREE_LIMIT - any number > 1, CPU(default), IO, AUTO
PARALLEL_FORCE_LOCAL - TRUE,FALSE(default)

Changed parameters:

PARALLEL_IO_CAP_ENABLED  - is deprecated and remapped 
to PARALLEL_DEGREE_LIMIT set to IO
PARALLEL_THREADS_PER_CPU - is now hidden parameter
 _PARALLEL_THREADS_PER_CPU
PARALLEL_AUTOMATIC_TUNING - is deprecated and should not 
be used. It means should have default value FALSE.

Continue reading →

Oracle – generate rows as INSERT statements from table, view using PL/SQL – generate PL/SQL

Posted on by
15

If you are working as developer/DBA probably you are quite often asked to migrate some rows from one database to another – usually people reference to the rows as “metadata” rows. Rows that driving your business !

If you need to prepare such rows for migrations there are a lot of methods to do it

  • just copy the rows using database link from one database to another
  • export/import the rows using tools like expdp/impdp
  • prepare manually scripts with INSERT statements

I would like to focus on the last method. It’s very popular to keep metadata in some files as number of INSERTs for backup/migrations/versioning etc.

I decided to write my own Oracle PL/SQL function to do that. It generates PL/SQL code which can be used to load exported rows later. This function should be used only for small number of rows.

Continue reading →

Install Oracle 12C Release 1 (12.1) on Centos 6 – UDEV disks, NFS disks, kmod-oracleasm disks

Posted on by
1

This article presents installation of Oracle Database 12C(12.1) on Centos 6. Both database and grid software(standalone version) are installed. Grid software will use devices configured using three methods:

  • disk devices configured using UDEV
  • disk devices accessed by NFS
  • kmod-oracleasm library – extra Red Hat 6 rpm to simulate oracleasm lib

In this article there are two separated users:

  • oracle – manage database software and databases
  • grid – manage grid software and asm database.

Read following article how to install Centos 6 Linux: Install Centos 6 64 bit(for comfort set 4G memory and 64G for disk in your virtual machine). Cetnos6_1

Continue reading →

Install Fedora 20

Posted on by
Reply

This article presents how to install Fedora 20. Check installation for next release Fedora 21.

I assume you have already downloaded Fedora 20 64 bit(about 4 G) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for Linux Fedora 64 bit. 1GB ram and 32G for disk is enough plus increase video memory to 128M and turn on acceleration 3D. Rest of options you can keep default.

Fedora20_1

Continue reading →