David Fitzjarrell

Query tuning is part art, part science and usually occurs on a case-by-case basis. There may be occasions though, where a series of queries, similar in structure and differing in predicate values, need to be tuned. Rather than go through each and every query, setting up a SQL Profile and enabling it, it may be […]

Transactions are the lifeblood of a relational database and Oracle is no exception. Key to this concept is the ability to replay those transactions should the need arise to recover ‘lost’ data due to a database crash, or worse, corruption requiring a restore and recovery. Yes, recording the transactions does take time but that time […]

Security is the watchword for almost all businesses today, especially for computer systems and databases. For legal reasons, any ‘snooping’ the administrators may do on the users logged into such systems needs to be disclosed in a security banner. Such banners are commonplace in UNIX/Linux systems but are rarely found for database logins; I suppose […]

In Oracle’s most recent release, the audit options have been expanded and modified to create the Unified Audit Trail, a ‘one-stop shop’ for recording and reporting audit information. A new view, UNIFIED_AUDIT_TRAIL, provides access to the audit details: Name Null? Type —————————————————————————————————– ——– —————————- AUDIT_TYPE VARCHAR2(64) SESSIONID NUMBER PROXY_SESSIONID NUMBER OS_USERNAME VARCHAR2(30) USERHOST VARCHAR2(128) TERMINAL […]

Oracle has provided access to its wait interface for several releases and with each new release it expands the range of wait information available, so much so that it’s hard to not find something to examine. Of course, examination leads to investigation, which leads to tuning, even when there is nothing to tune. Such constant […]

Since version 9.2, Oracle has provided an interesting construct called the Index-Organized Table, or IOT. Such tables are basically indexes with the leaf nodes as the actual table data. Under the right conditions using IOTs can be very beneficial, and under the wrong conditions an IOT can be more of a problem than a solution. […]

There may be times when a query is taking longer to execute than expected. With Oracle, the Automatic Workload Repository collects statistics on a regular schedule to report on how the system is performing, including elapsed time for queries. Since a full AWR report may not be necessary a script using some of the AWR […]

Oracle provides two parameters that affect the PGA that look very similar but operate very differently. One of these parameters is the well-known pga_max_size and the other is a hidden parameter, _pga_max_size. Let’s look at both and see how one can be very effective while the other can create problems with respect to PGA memory […]

In an Oracle forum recently the question was raised regarding the auto-registration of a database with the listener. Actually, the question went on to ask how to prevent the listener from registering a database automatically. The reason given was that the database that was not to be auto-registered has the same name as the current […]

Oracle provides a a function named sys_guid() that generates unique identifiers that can be used for a number of purposes, one of which is as a generated primary key. Occasionally discussions in the Oracle forums discuss the merits and issues of using such global unique identifiers as primary keys; one current discussion asked if using […]

The predominance of business applications designed with no particular database in mind is growing/ With that growth arises an interesting issue: designing tables to contain various types of data in a single column. It may be due to the nature of the data needing to be stored — telephone numbers, Social Security numbers, ID numbers […]

Oracle has provided a number of pre-defined error conditions to be used in PL/SQL blocks and many of these may be unfamiliar to most programmers. To be honest some of these conditions won’t be thrown because programmers usually catch these conditions before any code goes into test. For those just starting to seriously code PL/SQL […]