Oracle SQL*Loader and External Tables

This post gives a brief introduction to the two oracle’s technologies for loading external data into a database tables – SQL*Loader (SQLLDR) and External Table and later provides some guidelines on when to chose what.

SQL*Loader (SQLLDR)

SQL*Loader loads data from external files into tables in the Oracle database. SQL*Loader uses two primary files: the datafile, which contains the information to be loaded, and the control file, which contains information on the format of the data, the records and fields within the file, the order in which they are to be loaded, and even, when needed, the names of the multiple files that will be used for data.

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.

Checking Oracle Client Version On Windows

As most of us know, an oracle client software needs to be installed on every machine which wants to connect to an oracle database server remotely. Oracle Client for 11g release 1 can be downloaded from here and the release 2 patch for windows can be downloaded from here.

There would be times when you want to verify the client version installed. Checking the about version popup of one of the configuration tools like Oracle Net Manager doesn’t help much since it doesn’t detail out on the patch version number.

The client version could be verified by executing SQL*Plus but depending on how the installation was done, this client tool may not be installed. Executing tnsping command seems to be sure-shot way to assert the client version since this tool is installed regardless of the install options chosen and this does not change with the release of Oracle client.

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.

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, ?)

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.

Oracle Client Result Query Cache

Oracle 11g introduces a number of features to expand the use of memory caching in order to improve query performance and decrease the overall memory requirements of queries and applications. In addition to the features that support server-side caching, there is also a new feature to support a client-side result cache that allows OCI clients to fetch result sets stored in process memory instead of requiring access to the server’s memory. The “Client Result Cache” is enabled and configured at the database level and available to all OCI-based clients.

Using the client’s memory to cache query result sets significantly improves the performance of repetitive queries by OCI-based clients that would otherwise require a cache hit on the server-side. The client cache utilizes per-process memory on the OCI client and its contents can be shared across multiple sessions and threads. In certain situations, this will greatly reduce the number of round-trips between the client and the database server, reducing CPU consumption on the server and greatly increasing the response times. Situations that will benefit most from the client cache are queries that use small lookup tables or mostly read-only tables. 

Note that this feature is not supported in Standard Edition.