Labelling Connections In Oracle UCP

Oracle’s Universal Connection Pool (UCP) provides a feature called connection labeling where an application could attach arbitrary name/value pairs to a connection.By associating particular labels with particular connection states, an application can retrieve an already initialized connection from the pool and avoid the time and cost of re-initialization. The initialization varies and could include simple state re-initialization that requires method calls within the application code or database operations that require round trips over the network.

Connection labeling is application-driven and requires the application to implement an callback interface called oracle.ucp.ConnectionLabelingCallback. The callback determines whether or not a connection with a requested label already exists. If no connections exist, the interface allows current connections to be configured as required.

Advertisement

Oracle ROWID and its Uniqueness

ROWID Data Type

Oracle Database uses a ROWID datatype to store the address (rowid) of every row in the database.

■ Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.
■ Logical rowids store the addresses of rows in index-organized tables.

A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway.

Enabling Trace On Oracle Client

Tracing produces a detailed sequence of statements that describe network events as they are run. Tracing an operation enables you to obtain more information about the internal operations of the components of Oracle Net Services than is provided in a log file.

Each Oracle Net Services component produces its own trace file. To enable tracing on the oracle client, the configuration file which needs to be modified is sqlnet.ora. This file can be found in ORACLE_HOME\network\admin directory.

Oracle Net Manager is a component/tool installed while installing Oracle client. It enables you to configure Oracle Net Services for an Oracle home on a local client or server host.

Autogenerated Keys with Oracle JDBC Driver

JDBC 3.0’s autogenerated keys feature provides a way to retrieve values from columns that are part of an index or have a default value assigned. The method Statement.getGeneratedKeys can be called to retrieve the value of such a column. This method returns a ResultSet object with a column for each automatically generated key. Calling ResultSet.getMetaData on the ResultSetobject returned by getGeneratedKeys produces a ResultSetMetaData object that can be used to determine the number, type, and properties of the generated keys. A flag indicating that any auto-generated columns should be returned is passed to the methods execute, executeUpdate, or prepareStatement when the statement is executed or prepared.

Database Protocol Packet Size and Performance

The Data Access Handbook points out that the size of the database protocol packet is an important factor for the database application performance. Each database vendor defines a protocol for communication with the database system, a format that only that database system understands. For example Microsoft SQL Serveruses communication encoded with the Tabular Data Stream (TDS) protocol, IBM DB2 uses communication encoded with the Distributed RelationalDatabase Architecture (DRDA) protocol and Oracle defines Oracle Net.

How Does OCI JDBC Application Communicate With Oracle Database Server

This post details out on what happens behind the scenes when an JDBC application using OCI driver communicates with the oracle database server.

A sample jdbc program looks like

import java.sql.*;
class DatabaseAccess{
  public static void main (String args []) throws Exception
  {
        Class.forName ("oracle.jdbc.OracleDriver");
        Connection conn = DriverManager.getConnection
             ("jdbc:oracle:oci:@hostname:1521:service_name", "scott", "tiger");

        ....
        ....
  }
}

As you can see, the program uses the JDBC OCI driver (a type 2 JDBC driver) to create a database connection. The Oracle JDBC OCI driver converts the JDBC invocations to calls to OCI (Oracle Call Interface) which are then sent over by Oracle Net to the Oracle database server.

Installing Sonar With Oracle

Installing sonar (version 2.8) is fairly easy after following the installation guide. Below is one tip which you should consider when using oracle as the database for sonar installation.

The Oracle schema for sonar should be created with the below sql commands

CREATE USER sonar IDENTIFIED BY sonar;

GRANT CREATE SESSION,
      CREATE TABLE,
      CREATE VIEW,
      CREATE SYNONYM,
      CREATE SEQUENCE,
      CREATE PROCEDURE,
      CREATE TRIGGER TO sonar;

ALTER USER Sonar Quota 300M ON Users;

Before I discovered the above queries (thanks to this blog) I tried creating the schema using create user and grant all permissions sql queries. Starting the sonar server always failed with the below error

ActiveRecord::ActiveRecordError: ORA-00904: “ENABLED”: invalid identifier: INSERT INTO users (row_identifier, login_id, full_name, email, row_version, user_id, enabled, login, name, crypted_password, salt, created_at, updated_at, remember_token, remember_token_expires_at, id) VALUES(null, null, null, ”, null, null, 0, ‘admin’, ‘Administrator’, ‘fdf038c3d9a8f1a260c7fe3c6657e91dc46ca448′, ’43b15bc57d3bd82b408970c550c03fdd8f29d95e’, TIMESTAMP’2011-06-03 21:42:54′, TIMESTAMP’2011-06-03 21:42:54′, null, null, ?)

Oracle JDBC driver and connection thread safety

Oracle JDBC developer guide recommends not to share connections across multiple threads. Here is small excerpt from the Oracle guide

“Oracle JDBC drivers provide full support for, and are highly optimized for, applications that use Java multithreading. Controlled serial access to a connection, such as that provided by connection caching, is both necessary and encouraged. However, Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously. If multiple threads must share a connection, use a disciplined begin-using/end-using protocol.”

Global Temporary Table In Oracle

Introduction

Global temporary tables are types of database tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.

Note that only table data is session specific, but physically table is available in all sessions.

Syntax

CREATE GLOBAL TEMPORARY TABLE <Table-name>
(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;

The default create statement is the equivalent of adding the clause ON COMMIT DELETE ROWS. An alternative is to specify ON COMMIT PRESERVE ROWS.

ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.
On the other hand, ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

Simple Example

--Create a simple GTT with default options:

CREATE GLOBAL TEMPORARY TABLE
temp_gtt (id NUMBER(20)) ;

--Insert some data and do a SELECT

BEGIN
  FOR i in 1..10 LOOP
    INSERT INTO temp_gtt values (i) ;
  END LOOP ;
END ;

SELECT count(*) FROM temp_gtt;

--result:

COUNT(*)
----------------------
10

--Do a commit and run the query again

COMMIT;

SELECT count(*) FROM temp_gtt;

--result:

COUNT(*)
----------------------
0

Important points about GTT

1. In Oracle temporary table and global temporary table are synonymous. You cannot create a temp table without the keyword “global”
2. GTT data is private to a session. Although there is a single table definition, each session uses a GTT as if it was privately owned. Truncating data in a GTT will not affect other users sessions.
3. Depending on the table definition, data in a GTT will either be removed or retained after a commit. However it is always removed when the session terminates even if the session ends abnormally.
4. Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
5. The table and any associated indexes are stored in the users temporary tablespace. Starting with 11g Oracle introduced the option to specify the temp tablespace for a GTT (but not its indexes).
6. Views can be created against temporary tables and combinations of temporary and permanent tables.
7. Foreign key constraints are not applicable in case of Temporary tables
8. Temporary tables can have triggers associated with them.
9. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
10. Putting data in a temporary table is more efficient than placing this data in a permanent table. This is primarily due to less redo activity when a session is applying DML to temporary tables. DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Oracle writes data for temporary tables into temporary segments and thus doesn’t require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it’s not entirely eliminated because Oracle must log the changes made to these rollback segments. To summarize – “log generation should be approximately half of the log generation (or less) for permanent tables.”
11. Temporary tables cannot be partitioned.
12. If you use a Global Temporary Table in your application code, watch where and how you run GATHER_TABLE_STATS on it.
1. If your GTT has been defined as ON COMMIT DELETE ROWS, the GATHER_TABLE_STATS call will result in rows being deleted. This is because the GATHER_TABLE_STATS issues an implicit commit.
2. If your GTT has been defined as ON COMMIT PRESERVE ROWS, the GATHER_TABLE_STATS will not delete rows in the table.

Unindexed Foreign Keys In Oracle

Reading this excellent book on Oracle architecture – Expert One-On-One Oracle – By Thomas Kyte, I came through an interesting detail on deadlocks. The author mentions that based on his experience unindexed foreign keys are the number one causes of deadlocks.

There are two cases where Oracle will place a full table lock on a child table after modification of the parent table:

•          If I update the parent tableʹs primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index.
•          If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well.

Deleting a parent table row 

Lets take an example of the second scenario which is more common. If I delete a row in table P, then the child table, C, will become locked ‐ preventing other updates against C from taking place for the duration of my transaction (assuming no one else was modifying C, of course; in which case my delete will wait). This is where the blocking and deadlock issues come in. By locking the entire table C, I have seriously decreased the  concurrency in my database ‐ no one will be able to modify anything in C. In addition, I have increased the probability of a deadlock, since I now ʹownʹ lots of data until I commit.

The probability that some other session will become blocked on C is now much higher; any session that tries to modify C will get blocked. Therefore, Iʹll start seeing lots of sessions that hold some pre‐existing locks getting blocked in the database. If any of these blocked sessions are, in fact, holding a lock that my session needs ‐ we will have a deadlock. The deadlock in this case is caused by my session obtaining many more locks then it ever needed.

Other Issues

By indexing the foreign key column, we can remove this locking issue all together. In addition to this table lock, an un‐indexed foreign key can also be problematic in the following cases:

• When you have an ON DELETE CASCADE and have not indexed the child table. For example, EMP is child of DEPT. DELETE DEPTNO = 10 should CASCADE to EMP. If DEPTNO in EMP is not indexed, you will get a full table scan of EMP. This full scan is probably undesirable, and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
• When you query from the parent to the child. Consider the EMP/DEPT example again. It is very common to query the EMP table in the context of a DEPTNO. If you frequently run the following query, say to generate a report, youʹll find that not having the index in place will slow down the queries:

select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :X;

I don’t need an indexed foreign key

So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

• You do not delete from the parent table.
• You do not update the parent tableʹs unique/primary key value (watch for unintended updates to the primary key by tools!
• You do not join from the parent to the child (like DEPT to EMP)

If you satisfy all three above, feel free to skip the index ‐ it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ʹover‐lockʹ data.