Update Your Oracle Knowledge . . .

Oracle and technological feasibility

2010-01-18T10:34:00.000+07:00

Technological feasibility is a problem resulting from the public at-large’s gross misunderstanding of the capability of modern computers. Back in the 1980's, many people assumed that computers like the HAL-9000, from the movie 2001: A Space Odyssey, really existed, and it was very difficult for practicing computer scientists to explain the real world limitations of the hardware of the day.

Today, some intelligence sources must process more than four petabytes (1PB = 1000 TB) of data per month, and the bar on what constitutes technological feasibility is constantly being raised. Today, most of the technological limitations are driven by the limitations of the hardware rather than those of the software. In 2010, there are still many limits to our ability to process information:

* Drinking from the garden hose: With today's memory limitations, processing more than two million rows per second is not possible.

* Searching: Even with the fastest supercomputers, complex searching through petabytes of information cannot be done with sub-second response time.

* Storage density: As of 2010, it takes 37 megabytes just to store the DNA genome from a single human being, and it could be a century before we can match the storage density of a mammalian brain.

* Artificial intelligence: Even with the advances in computing power, scientists still cannot replicate the intelligence of a fly's brain.

* Networking: Even with the "dark fiber" high-speed links, real limits exist on the speed at which we can transfer large volumes of information.

Every computer science student is taught that hardware advances always precede advances in software, and Oracle is not an exception. However, Oracle has been far more visionary in the area of emerging technologies than other database vendors, and Oracle is often years ahead of the technology.

One reason that Oracle continues to dominate the database market is their forward-thinking vision, and they recognize that their software must be tested and ready to use as soon as the new hardware technology becomes available.

Follow the link below to discover more about how Oracle is a technology-driven database and how Oracle leverages on emerging technologies to retain their place as the world's most flexible and robust database.

..more..

Hypercharge SQL Performance with Function-based indexing

2009-12-31T08:43:00.000+07:00

I am now one year into writing my next book "Oracle SQL Tuning: The Definitive Reference", and I have had an epiphany about SQL tuning that all Oracle professionals should know.

The vast majority of SQL tuning can be avoided by following a few simple rules:

- Carefully formulate the SQL syntax to conform to standard practices
- Always use complete optimizer statistics (using dbms_stats)
- Make sure that all indexes exist, especially function-based indexes

In my experience, the single most common mistake that I have observed is SQL that performs poorly because the DBA or developer did not know how to create a function-based index. A function-based index (FBI) is a Godsend because it allows the matching of virtually any WHERE clause predicate with a corresponding index.

With an FBI in place, Oracle goes directly to the rows needed, minimizing database I/O and improving overall performance. Better yet, adding missing function-based indexes serves as a silver bullet (an act that changes the entire workload landscape). When an FBI is added, Oracle instantly recognizes the new index and re-parses all SQL that might benefit from it such that a single FBI might improve the performance of thousands of SQL statements.

Follow the link below to see why function-based indexes are so critical to SQL performance:

..more..

Understanding Oracle knowledge engineering

2009-12-31T08:39:00.000+07:00

Since the earliest days of business computing, the idea has been to identify well structured activities and automate them.

The first business processes to be automated were well structured redundant tasks such as payroll processing, tasks that take repetitive and well structured components of a system and automate them.

As the decades passed, Information Systems became more sophisticated at capturing and deploying human intelligence within computer systems, and we see these types of systems:

Expert Systems - These online system capture a well structured task and mimic human processing. An example would be Mycin, a system that applies physician intelligence at analyzing blood samples. An expert system makes the decision without the aid of any human intuition.

Decision Support Systems - A DSS is a computerized system that recognizes that human intuition is difficult to quantify and automate. In a DSS the human makes the decision, guided by software that automates the well structured aspects of the problem domain.

The line between an expert system and a decision support system blurs in some cases when what is thought to be an intuitive process is actually a well structured problem with extremely complex decision rules.

To see examples of knowledge engineering and how it can be used for data cleansing follow the link below:

..more..

Rewriting complex SQL for high performance

2009-12-10T09:28:00.001+07:00

I am now about one year into writing my next book "Oracle SQL Tuning: The Definitive Reference," and I've discovered some fascinating new principles of SQL tuning, counterintuitive approaches that can make your SQL run faster than ever before.

Many experts correctly state that a problem should always be solved using SQL without using PL/SQL unless absolutely necessary, but there is a limit to that technology. Once a SQL query has subqueries nested within subqueries, subqueries in the select clause (a scalar subquery), subqueries in the from clause (an in-line view), the SQL becomes difficult to read, hard to maintain, and challenging to optimize. In these cases, we can use some powerful Oracle tools to divide and conquer complex SQL statements.

Because SQL is a declarative language, we can formulate equivalent queries with numerous techniques. SQL is a "state space" query language where you simply request the desired rows, and there are many ways to write the same query.

In the past, programming competitions were not concerned with who could solve a problem the fastest but with who could write the solution that ran the most efficiently. Today this is no longer the case, and SQL developers are charged with getting the right rows back as quickly as possible, regardless of the internal execution plan or the time required to execute the query!

Follow the link below to take a closer look at several powerful SQL re-writing techniques:

..more..

To pre-order my SQL tuning book, follow the link below where you can pre-order it for 30% off by buying it directly from the publisher:

..more..

A guide to Oracle virtualization

2009-12-10T09:24:00.001+07:00

To our friends in the Burleson Oracle community:

Before you start planning an Oracle virtualization strategy, be sure to familiarize yourself with the basics, gain an understanding of Oracle’s licensing and support policies, and learn the pros and cons of Oracle VM vs. VMware.

For an introduction to Oracle virtualization and to discover how Oracle VM fits into the virtualization market, check out this exclusive guide:

..more..

Also, don’t forget to check out additional sections in this Oracle virtualization guide, including an overview of:

* Oracle's recent acquisition of Virtual Iron
* Oracle VM Template Builder
* Oracle VM vs. VMware

Become a member of SearchOracle.com today to access this resource for Oracle professionals seeking to create a uniquely tailored virtualization strategy:

..more..

Secret Oracle Utilities

2009-12-10T09:22:00.000+07:00

Oracle has "secret" undocumented utilities that are so powerful that the company is reluctant to release the details to the general public. Oracle deliberately glosses over these powerful utilities, which can be extremely dangerous in the hands of Oracle professionals without the required work experience. Oracle must write their documentation with the lowest common denominator in mind, and the complexity of these utilities is such that it can far exceed the skill and understanding level of that lowest common denominator user.

These utilities are often left tucked inside an Oracle distribution for use exclusively by Oracle technical support, but expert Oracle DBAs also find them invaluable for advanced database operations. These undocumented utilities are described in the new book "Advanced Oracle Utilities: The Definitive Reference".

A hidden Oracle utility is not a utility that is physically hidden in the Oracle software. Rather, a hidden utility is an executable or PL/SQL package that is either undocumented or is included such that the documentation is difficult to find. For example, some PL/SQL packages are never loaded by the Oracle installer, yet their definitions remain in the operating system files.

Follow the link below to discover how to identify and use these secret Oracle Utilities:

..more..

See below to get the book on advanced Oracle utilities for 30%-off, by buying it directly from the publisher:

..more..

How to read the steps in a SQL execution plan listing

2009-12-10T09:20:00.000+07:00

When Codd and Date created the relational data model, the execution plan was an afterthought. This was largely due to the fact the SQL optimizer was always supposed to generate the best execution plan; therefore, there was no real need to understand the internal machinations of Oracle execution plans.

In the real world, all SQL tuning experts must be proficient in reading Oracle execution plans and understanding the steps within a explain plans, including the sequence in which the steps are executed. To successfully understand an explain plan, you must be able to know the order that the plan steps are executed.

Reading an explain plan is important for many reasons, and Oracle SQL tuning experts reveal the explain plans to check many things:

* Ensure that the tables will be joined in optimal order.
* Determine the most restrictive indexes to fetch the rows.
* Determine the best internal join method to use (e.g. nested loops, hash join).
* Determine that the SQL is executing the steps in the optimal order.

Reading SQL execution plans has always been difficult, but there are some tricks to help determine the correct order that the explain plan steps are executed. Follow the link below for some insight into this challenging undertaking:

..more..

Oracle 11gR2 new parallel query management enhancements

2009-12-10T09:19:00.001+07:00

The database industry is clearly in the midst of massive server consolidation, an economic imperative whereby the old-fashioned one database/one server approach of the client-server days has been rendered obsolete. Today, single servers with 32 and 64 CPU’s and hundreds of gigabytes of RAM can host dozens of large Oracle databases.

While the 2nd age of mainframe computing came about to facilitate easier DBA management, there remain the impressive benefits of having a back of dozens of CPU’s to perform full scans very quickly.

When invoking Oracle parallel query, there are many perils and pitfalls:

* Setting parallelism on at the table or system level influences the optimizer, and sometimes makes full-scan operations appear cheaper than they really are.

* Determining the optimal degree of parallelism is tricky. The real optimal degree of parallelism depends on the physical placement of the data blocks on disk as well as the number of processors on the server (cpu_count).

To relieve these issues with parallel query, in Oracle 11g Release 2, the following new parallel query parameters are included:

* The parallel_degree_policy parameter
* The parallel_min_time_threshold parameter
* The parallel_degree_limit parameter
* The parallel_force_local parameter

Follow the link below to take a closer look at these important enhancements to Oracle parallel query in 11g Release 2.

..more..

Oracle flash_cache tips

2009-12-10T09:17:00.000+07:00

Oracle 11g Release 2 includes a feature called flash_cache, a table and index argument that resembles the assignment of high use objects to the KEEP pool.

However, unlike the KEEP pool which uses volatile RAM disk, the flash_cache is used for tertiary storage on solid-state disk (SSD). On Oracle, SSD is up to 600 times faster than platter disks, and at only $2,000.00 per gigabyte, SSD technology is going to replace platter disks for most systems by 2015. In fact, the 2009 Sun servers have on-board SSD flash memory.

The Oracle documentation suggests enabling the flash_cache when the data buffer advisor suggests that Oracle wants more RAM, which is very common, when the system is disk I/O bound and when the system has spare CPU cycles.
They mention CPU because moving to solid-state flash storage removes disk I/O but changes the workload bottleneck to CPU in many cases.

In other words, flash_cache is for every system that is not already running an SSD back end! It should be considered if:

* The Buffer Pool Advisory section of your Automatic Workload Repository
(AWR) report or STATSPACK report indicates that doubling the size of the buffer cache would be beneficial.

* db file sequential read is a top wait event.

* You have spare CPU.

Of course, many Oracle shops have been running solid-state disks for many years now, so it is not really a new technology. In traditional SSD, the SSD looks just like a platter disk, except that the I/O can be hundreds of times faster!

See below for expert Oracle flash_cache tips:

..more..

Review: Oracle's 11g R2 database has some good and bad

2009-12-06T13:08:00.000+07:00

To our friends in the Burleson Oracle community:

Although Oracle made no major architectural enhancements to the new version of 11g, Release2 serves as a mix of patches and fixes to existing 11g features. And there are some interesting new features, alongside a couple of clunkers, that you should know about.

Take a closer look at these new features in 11g R2, and see why they are important to an Oracle professional:

..more..

In this review, Don Burleson delves into the more interesting features, as well as the clunkers, including:

* Column level data storage for Exadata
* Oracle flash_cache
* ASM Cluster Filesystem (ACFS)
* And more!

Get the scoop on Oracle 11g R2’s useful changes and learn how to work around the flops. Simply become a SearchOracle.com member today to access this review:

..more..

RAC One Node tips

2009-12-06T13:05:00.000+07:00

Traditionally, Oracle RAC is used in a multi-node architecture, with many separate instances of RAC residing on separate servers. This protects against unplanned server outages because transparent application failover will redirect to another server. It also aids the RAC goal of scalability when a new server instance is genned into the RAC cluster.

Now in Oracle 11g R2, there is a new feature dubbed RAC One Node. RAC One Node claims to be multiple instances of RAC running on a single node in a cluster with a fast instance relocation feature in cases of catastrophic server failure.

This instance relocation uses a new featured called Oracle Omotion.

Unlike regular RAC, running nodes on many servers, RAC One node will not protect you in a case of server failure. It does, however, offer instance duplication within a single server environment.

Oracle Corporation is now embracing the IT industry’s concept of instance consolidation, a movement toward collected instances from the bad old days of client server computing where there existed a one instance/one server architecture. This is a “back to the future” approach, a movement back to the monolithic server environments of the 1980's, with all of their benefits:

* Centralized patching and software maintenance.
* Redundant instances mean zero outages during patches and upgrades.
* On demand RAM and CPU sharing within the single large server.
* Less Oracle DBA resources required to manage dozens of instances.

Follow the link below for expert RAC One Node tips:

..more..

Oracle column level compression

2009-12-06T13:04:00.000+07:00

In traditional relational theory, the internal representation of the data on the physical blocks is not supposed to matter, but in the real world, the placement of the data on blocks is critical. Oracle provides tools like sorted hash clusters to group related rows together, and row sequencing can dramatically improve the performance of SQL queries by placing all information on a single data block.

Related tables can be grouped together on the same data block using Oracle cluster tables. For example, this can be done if you have a busy OLTP database where millions of people query customer and related order rows all day long.

If you use Oracle cluster tables to put the customer and order rows together on a single data block, it will greatly reduce the number of trips to the database to fetch the desired result set.

There is a tradeoff in using Oracle 11g table compression between run-time performance of the SQL vs. the processing overhead of compressing and de-compressing the rows. This type of compression requires overhead, and the less volatile the table, the better the overall performance.

Follow the link below for the advantages and obstacles in using Oracle 11g table compression:

..more..

Important tips for tuning SQL joins

2009-12-06T13:03:00.000+07:00

Most Oracle professionals are aware of the logical ways to join tables, such as equi-joins, outer joins and semi-joins; however, Oracle also has internal ways of joining tables, including physical join methods that are critical to understanding SQL tuning.

Built into Oracle is a vast array of internal algorithms, including several types of nested loops and merge joins as well as six types of hash joins.
There are also many data access methods, such as a variety of internal sorts and table access methods.

When we examine an execution plan for a SQL statement, we may see the physical join implementations with names like nested loops, sort merge and hash join.

The propensity of the SQL optimizer to invoke a hash join is heavily controlled by the setting for the hash_area_size and pga_aggregate_target Oracle parameters. The larger the value for hash_area_size, the more hash joins the optimizer will invoke. In some releases of Oracle, the hash_area_size defaults to double the value of the sort_area_size parameter, but it highly dependent upon parameter settings and the Oracle release level.

When tuning SQL, we must always remember that it is possible for the optimizer to fail to choose the best table join method. This is especially true for cases where a hash join is wrongly chosen over nested loops. This is frequently the case when we have sub-optimal schema statistics, especially column histograms, which can lead to cases where the optimizer makes an incorrect guess about the cardinality of a result set and wrongly invokes a join that requires a full-table scan rather than choosing nested loops.

See below for expert SQL tuning tricks for forcing nested loops joins over hash joins:

..more..

Choose one: High throughput or fast response time

2009-12-06T13:02:00.000+07:00

Many people assume that the defaults for the Oracle parameters are just-right for their systems, not realizing the incompatible goals of optimizing for throughput vs. optimizing for fast response time.

The default in Oracle 11g for the optimizer_mode is all_rows, an optimizer mode that has the goal of maximizing total throughout by choosing SQL access plans that minimize server resources. While this is a worthy goal, optimizing for maximum system throughput is not the same as optimizing for the fastest response time, a goal set with optimizer_mode=first_rows.

- Response time – This is a “selfish” goal; minimizing response time for individual queries, regardless of the expense to other tasks on the system. Fast response time is often associated with index access.

- Throughput – This is a holistic system-wide optimizer goal that is concerned with optimizing your entire workload to maximize total throughout as a whole. High throughput is often associated with parallelized large-table full-table scans.

To get the fastest response time we often need to use the first_rows optimizer_mode, a setting that directs the SQL optimizer to choose plans that start returning rows back to the calling application as quickly as possible. Optimizing for the fastest response time often involves using indexes, an access technique that returns rows quickly but incurs more I/O operations.

The optimizer_mode is a “silver bullet” parameter, case where a single change will have a profound impact on the entire database.

Follow the link to read more about optimizing for fast response time vs. optimizing for high system throughput:

..more..

Important changes in 11g data buffer management

2009-12-06T13:00:00.000+07:00

When the Oracle database was first created, Oracle knew that the database need a place to buffer-up frequently referenced data block in faster RAM storage. The data buffer region was originally defined by the single parameter db_cache_size, but Oracle now allows you to customize your I/O by the data characteristics, using multiple block sizes for different data workload characteristics.

However, these multiple data buffers do not address the impending explosion of solid-state disk technology, high-speed RAM disks that are poised to replace the ancient "spinning rust" platters technology that is half a century old.

In addition to being up to 600 times faster than platters, prices are falling to under $1,000 per gigabyte, making SSD flash disks an obvious replacement for the elderly spinning platters. We also see the Exadata database machine, a million dollar monster with terabytes of SSD storage.

Today, when Oracle reads in a data block from disk, it gets inserted at the buffer midpoint, and it is "pinged" to the most-recently-used end of the data buffer each time that the block is required by a SQL query. Oracle also uses the RAM data buffer to make read consistent clones of data blocks and uses the buffer space for some internal space management operations.

But how are things different when our storage is on the same super-fast RAM as the data buffers? Do we even need a data buffer?
The answer is yes, but it's no longer the primary goal of keeping popular data blocks on faster storage. Oracle must still need RAM to clone the data block for multi-version read consistency (a CR copy).

Let's take a closer look and see how the flash_cache data buffer differs from a regular buffer and learn more about these important new changes to data buffer management for solid-state storage SSD flash RAM disk:

..more..

How hardware advances drive Oracle DBMS advances

2009-10-05T12:14:00.001+07:00

It is well known that hardware advances always precede advances in software technology, and this is especially true for Oracle. As hardware becomes faster and cheaper, Oracle professionals change the ways in which we process information. Changes in hardware affect the way we design databases, the way we implement database and most importantly, the way that we tune databases.

While the costs of a DBA have remained relatively constant over the past 30 years, the costs of hardware have fallen dramatically, leading to a condition where applying hardware resources is often faster and cheaper than applying human resources to fix an acute performance problem. To read more about this, click below:

..click..

This change in the costs of people vs. hardware has led to a technique called "throwing hardware at a problem". Even though hardware may not address the root cause of an acute performance problem, it's often the smart thing to do for these reasons:

* Hardware upgrades can be done fast, in just a few hours. Root cause fixes can take months.

* Hardware upgrades have very little risk. Re-designing an Oracle system can be very risky.

* Hardware upgrades are a cheaper, guaranteed fix. You can cache a 100 gigabyte database on SSD for under $100,000, while a root cause fix may cost millions of dollars.

Read below to get the fascinating story about how hardware advances precede Oracle advances:

..more..

See here how hardware advances will change the Oracle job roles:

..more..

How to avoid an Oracle layoff

2009-10-05T12:12:00.000+07:00

In this down economy, companies are forced to make tough decisions. Layoffs have become commonplace, even among Oracle professionals. When a corporation is forced to trim their Oracle professional workforce, they commonly hire outside risk assessment advisors to determine which people are to be laid off.

I have participated in this unsavory process on many occasions, where I am charged with determining which Oracle employees will retain their jobs and which will be terminated. This process generally starts with an unobtrusive analysis of the Oracle professionals and includes the obvious criteria like measuring the quality and quantity of their work product; however, we also look at habits, e-mail habits and web surfing histories.

The goal is to identify those professionals who are indispensible to the ongoing success of the database and separate out those who contribute less to the bottom line. This is especially challenging when evaluating Oracle DBAs because one of the hallmarks of a good DBA is a database that rarely experienced an unplanned outage. Hence, the best DBAs are often those who have automated their tasks and use carefully crafted jobs to monitor and tune their workloads.

Read below some important tips for avoiding layoffs and getting fired from an Oracle job:

..more..

Are you ready for Business Intelligence?

2009-10-05T12:11:00.000+07:00

Changes in hardware technology always precede changes in software technology, and Oracle is no exception. As disk platter devices experience an unprecedented fall in cost, disk has become insanely cheap, with over a terabyte available for under $10,000.00. As a direct result of this hardware priced change, Oracle DBAs are being told to keep larger and larger amounts of historical data and to start providing management with tools to analyze and leverage this critical business information. This process is called Oracle business intelligence.

All Oracle databases collect information about ongoing business processes and it is only a matter of time before management starts to seek specialized information about their data. Eventually, all Oracle DBAs are faced with these requests for analysis of operational data, and these requests form the foundation of Oracle data warehousing and business intelligence.

As disk becomes insanely inexpensive, business managers are now choosing keep their operational data over years and use this valuable data to help them with basic Oracle business intelligence, the planning and execution of business analysis processes. Let’s take a quick look at the stages of this evolution into Oracle business intelligence:

- Data collection – This phase involved the ETL (Extract, test and Load) processes, taking operational data and saving it for analysis. The Oracle Data Warehouse Builder tool provides a framework for business intelligence, allowing for data extraction and loading of advanced data warehouse applications.

- Summary and aggregation – Managers start the business intelligence process by making requests for summaries and rollups of critical business metrics.

- Predictive analytics – At this stage, business intelligence requests become more sophisticated, and management will ask the DBA to extract historical data and make predictions using regression techniques.

- Hypothesis testing – As the managers begin to see the value of their data they will start requesting advanced business intelligence, full-blown modeling and the capability to perform hypothesis testing.

Sooner or later, all Oracle DBAs will be asked to perform business intelligence tasks and all Oracle professionals should become familiar with the common BI tools and techniques for Oracle business intelligence operations. See the link below for more details:

..more..

Popular RAC Storage Options in 11g

2009-09-15T14:23:00.000+07:00

When one of my clients is working on a new RAC configuration, I’m guaranteed to receive tons of questions. One of the most common is: what is the best storage option for RAC?

Despite the plethora of articles and information regarding storage options, most companies end up going with the advice of their storage vendor.

Some of these options include:

* Raw storage, which is often demanded yet rarely used. Popular misconceptions and difficult management make this a fading technology.

* ASM, the new standard for RAC storage. As a one-time skeptic of this technology, I have found myself consistently pleased with it.

* Direct NFS, 11g’s new networked storage product sure to excite users of NAS filers.

* OCFS2, a cluster file system developed for Oracle RAC environments.

To read more on these options as well as a discussion on udev rules, a device management solution which replaces traditional methods in RHEL5 see here:

..more..

Important ASM changes in 11g Release 2

2009-09-15T14:20:00.001+07:00

There are some super-important changes to storage management in Oracle 11g Release 2, Oracle ACE Lutz Hartmann has offered the following items that delve into these important new enhancements in 11gR2.

ASM Cluster Filesystem (ACFS) - This new feature allows is also named "Unified Storage Management" (UFS) and allows for storing Application File Data , the Oracle Cluster Registry (OCR), the Cluster Voting Disk, and the Oracle Binaries, all within ASM

Cluster Voting Disk and Cluster Registry in ASM - This is an important RAC enhancement where it is now possible to create Quorum Disk Groups and Quorum Disk FailGroups which cannot hold any other data than OCR or Voting Disk data.

ASM Dynamic Volume Manager (ADVM) - ADVM is a new device driver which is integrated into the Oracle Kernel and functions as a standard I/O interface for normal file systems to utilize ASM functionalities. This driver communicates with the ASM instance about ASM extent maps, rebalancing operations and I/O failure issues.

ASM FS Snapshots in 11gR2 - It is now possible to create up to 63 image copies of file systems as a point in time capture. These snapshots a read only and can be used to view a file system as of the point in time in the past when they were created.

ASM Optimal Disk Placement in 11gR2 - It is now possible to specify on which disk regions the ASM Disks will be placed. This enables us to make use of faster disk zones on the outer regions of disks for better I/O performance.

..more..

The secrets of Oracle scalability

2009-08-31T12:00:00.002+07:00

One of the reasons that Oracle became a leader in database technology was their flexible tool set, a group of utilities that allows a database to grow seamlessly from a small departmental system to a giant multinational behemoth. Being the world’s most flexible and robust database, Oracle offers a wide array of tools and techniques for scaling. It is the challenge of the IT manager to apply these tools at the appropriate time to ensure seamless growth while minimizing the investment in hardware resources.

Even though hardware prices fall by a order of magnitude every decade, the investment in computing resources remains a large, critical expense that requires careful management. Hardware depreciates regardless of use, and a “too much too soon” approach can be wasteful. On the other hand, waiting until the system experiences stress related response time delays is also bad, especially since today’s end-user community has very little tolerance for sluggish response time.

The answer is the “scale up, scale out” approach.

This scalability secret allows Oracle database to grow seamlessly from tiny to behemoth. Read below for the trick:

..more..

Costs of SQL Server vs. Oracle

2009-08-31T12:00:00.001+07:00

Microsoft and Oracle are engaged in a price war. Microsoft SQL Server struggles to grow "up" into the midsized database market, while Oracle pushes "down", to server the market for small departmental database systems.

As a result, we are flooded with marketing from both sides. There is some debate in the market about Oracle being too expensive for small users.

Comparing SQL Server to Oracle is like comparing a moped to a Maserati! Oracle is an order of magnitude more flexible and robust that SQL Server.

When comparing the costs of Oracle vs. SQL Server we must remember that it’s not just about features. SQL Server only runs on Intel, no HPUX, no Solaris, no AIX, nothing "industrial strength", with the exception of the UNISYS ES series monster servers.

Read more about the real costs of SQL Server vs. Oracle:

..more..

How tune Oracle SQL remotely

2009-08-31T11:38:00.000+07:00

If you use a vendor package with Oracle, you may be one of the thousands of shops that struggle with optimizing and tuning vendor systems that you did not write and cannot change. So, how do you tune SQL when you cannot access the SQL source code.

In traditional SQL tuning, changes are made to re-writing the SQL statement into a more efficient form or by changing the execution plan by adding hints to the select clause. With SQL that hides inside a compiled program, changing the SQL is impossible. In other cases, the software vendor may explicitly prohibit any changes to the source code, you must come up with a creative way to tune the SQL without touching the source code.

Oracle provides a “trick” technique for tuning SQL when you cannot “touch” the source code. Cases where you cannot change the SQL source code include dynamically generated SQL, SQL inside 3rd party vendor packages, and compiled programs with embedded SQL.

Read more below to see examples of tuning SQL when you cannot touch the source code:

..more..

How to find the fastest execution plans

2009-07-30T09:32:00.000+07:00

Learn SQL tuning tricks

While many Oracle professionals learned about the relational model and SQL in college, they make the common mistake of thinking that SQL tuning is governed by well-defined decision rules.

Oracle's SQL is only one of a host of different SQL optimizer implementations, and, like any other vendor implementation, there are many nuances and tricks to SQL tuning.

Not all Oracle SQL tuning tricks make sense, but nonetheless, there are powerful yet counterintuitive tricks that can make SQL run at optimal performance levels.

Becoming an expert in Oracle SQL tuning is not trivial, and there are no shortcuts. Only by tuning thousands of SQL statements can you fully understand the subtleties and nuances of Oracle SQL and be able to effective tune large and complex SQL statements.

..more..

Integrating Streams Replication and Standby Databases

2009-07-29T11:09:00.001+07:00

The use of Oracle Streams Replication has become more and more common, replacing the more cumbersome Multimaster Advanced Replication. Many shops use Stream Replication to off load reporting and high impact loads from production databases. Based on my experience, more than half of the shops have the destination database in the same rack as the source database. With both databases located in close proximity, many shops are also implementing physical standbys of one or both of the replicated databases in a remote location. Oracle Streams and Dataguard Physical Standby database integrate easily, once you understand how the two technologies fit together.

..more..

Tracking & auditing changes to your init.ora parameters

2009-07-29T11:06:00.000+07:00

A very important auditing task for any production database is the ability to track changes to the powerful initialization parameters. Many of these parameters are "silver bullets", single parameters that have a profound impact on system-wide behavior. This is especially true for SQL optimizer changes.

A single change to an optimizer parameter might affect thousands of SQL statements, a disaster in a controlled production environment.

Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task. Sometimes, users having “alter system” privileges can make unauthorized changes to the initialization parameters in the spfile on a production database. Hence, auditing changes to parameters is a critical DBA task.

..more..

SQL tuning with views

2009-07-29T11:05:00.000+07:00

Oracle views provide a benefit in these important areas:

·Code reuse: Views ensure that everyone uses the exact same SQL to access their tables.

·Column access security: Using the "grant" security model, views can be used for column-level security, such that some columns in a table may be "hidden" by not specifying them in a view.

For all of the benefits that Oracle views provide, they do come at a cost. One downside to using views is that it adds complexity to the SQL and makes it harder for the optimizer to service a query with the minimum amount of resources. This can pertain to either I/O or CPU resources, depending on the optimizer goal.

While it is clear that views are useful for end-user ad hoc queries and cases where the goal is to simplify the syntax of complex SQL queries, the following serious problems can occur when queries contain views:

·Predicate pushing: The downside to reusable code is that WHERE clause predicates must be "pushed" down the hierarchy of views to the base query. This adds processing overhead to the optimizer and increases the chances of a bad execution plan

·Non mergeable views: Because a view is an encapsulation of a complex query, it is used as if it were a discrete relational table. Hence, Oracle must pre-materialize a view whenever it is used in a query. This creates a hidden sub-plan that must be exposed for SQL tuning.

·Unnecessary overhead: Views are abused when they introduce unnecessary complexity. For example, there may be a call to a view that is composed of 10 tables where the result set only requires data from two tables.

·Excessive hard parsing: Predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure bind variables are used instead of literals in SQL code calling views.

Read on to see important tips and tricks for tuning SQL that contains views.

..more..

Ethics for the Oracle DBA

2009-07-29T11:03:00.000+07:00

Do DBAs have a social or ethical responsibility to the organizations they work for?

The role of a database administrator has changed according to the technology of database management systems (DBMSs) as well as the needs of the owners of the databases. For example, although logical and physical database designs are traditionally the duties of a database analyst or database designer, a DBA may be tasked to perform those duties.

Why we need a code of ethics:

System Administrators (SAs) and DBAs generally have high levels of access into computer systems at major corporations, financial institutions, educational facilities and brokerage firms. The IT professionals have access to highly-confidential information, prior to it becoming public knowledge, such as IPOs, stock ratings, debt ratings, test questions and answers just to name a few.

.. more ..

Secrets for managing multiple instances

2009-06-25T20:06:00.000+07:00

In the world of the working Oracle DBA, it is possible one could be expected to manage dozens of production instances concurrently. Almost all working Oracle experts interact with their database with OS Commands, SQL*Plus scripts and shell scripts. The Oracle Enterprise Manager (OEM) GUI is primarily used only by the newbies and the more inexperienced DBAs.

OEM makes it easy for a neophyte to “pretend” to be an experienced DBA. Since it takes several years of full-time work to be familiar with the command-line syntax, OEM has been a great favorite among the “poser” crowd.

Anytime I work with a DBA who can only administer a database from OEM, I know that they are likely to belong to one of these groups:

Raw beginners - OEM is a great tool for beginners who have not yet learned that the command line interface is infinitely more flexible than the limited possibilities offered in OEM.

Posers - Fraudulent credentials and fake resumes are all too common in the Oracle industry, and reliance on GUI tools should be a tip-off that you are dealing with someone who has a very limited background in managing large computer systems.

Non-techies - Oracle managers with a non-technical background (no degree in Information Systems or Computer science) love to use OEM because it allows them to do tasks that would normally be far beyond their ability level.

A seasoned DBA knows that a firm knowledge of shell scripting, OS commands and and native SQL and DDL is indispensible for managing multiple instances.

Read more here about tips for managing a server with multiple instances without relying on OEM:

more

Advanced SQL tips - reading an execution plan

2009-06-21T11:53:00.000+07:00

When Codd and Date created the relational data model, the execution plan was an afterthought, largely because the SQL optimizer was always supposed to generate the best execution plan, and hence, there was not real need to understand the internal machinations of Oracle execution plans.

However, in the real world, all SQL tuning experts must be proficient in reading Oracle execution plans and understand the steps within an explain plan and the sequence in which the steps are executed. To successfully understand an explain plan, you must be able to know the order that the plan steps are executed. Reading an explain plan is important for many reasons, and Oracle SQL tuning experts use the explain plans to check many things. For example:

Reading SQL execution plans has always been difficult, but there are some tricks to help determine the correct order that the explain plan steps are executed.

Ordering the sequence of execution plan steps

SQL execution plans are interpreted using a preorder (reverse) transversal algorithm. This is a fancy way of saying:

1 - To read an execution plan, look for the innermost indented statement. That is generally the first statement executed.
2 - In most cases, if there are two statements at the same level, the first statement is executed first.

In other words, execution plans are read inside-out, starting with the most indented operation. There are some general rules for reading an explain plan.

Read on and learn how to read the sequence of steps in any SQL execution plan:

more

The Care and Feeding of a Standby Database

2009-06-21T11:41:00.001+07:00

The Oracle Managed Standby Database is one of the most efficient and effective disaster recovery options for Oracle databases. The Managed Standby provides the capability to very quickly recover from the loss of a production database. It also can temporarily take over if the production database requires maintenance. Using an Oracle Managed Standby database can allow the DBA to provide near 24/7 availability of the database, year in and year out.

The standby database can be either local or in a remote location. Many shops have both a local and a remote standby. The local is to support the management of the primary, and the remote supports disaster recovery. One primary database can have up to nine standby databases.

Find the steps critical to maintaining a healthy standby database:

more

Be careful with Oracle silver bullets

2009-06-21T11:38:00.000+07:00

Oracle has codified the "silver bullet" approach to Oracle tuning with the Oracle Real Application Testing (RAT) facility. RAT is used along with the SQL Performance Analyzer to provide a complete framework for system-wide testing of Oracle silver bullet changes.

RAT allows the DBA to perform holistic tests when introducing silver bullets, a way to ensure that the global change will have the maximum effect and not clobber the database.

A silver bullet is any single Oracle change that has a profound impact in system-wide performance, both good and bad!

Should you fail to optimize the system for its workload, SQL tuning can be undone with subsequent changes to any of the Silver Bullets, changes that could alter the execution plans of thousands of SQL statements! A silver bullet can be any of these Oracle changes:

- SGA pool sizes (shared_pool_size, db_32k_cache_size, etc.): Pool sizes have a dramatic effect on Oracle performance.

- Materialized Views: MV can pre-join popular tables together and prefetch commonly referenced summary data.

- Indexing : Intelligent indexing ensures SQL query completion with a minimum amount of buffer gets.

- Optimizer knobs (optimizer_mode, Optimizer_index_cost_adj): Changing these global parameters can have a profound impact on Oracle performance, both good and bad.

- I/O subsystem changes (RAID level, multiple blocksizes, SSD): I/O remains the most significant performance bottleneck in most production databases.

- Optimizer statistics : Applying CBO histograms can have a huge benefit to an entire system.

These "broad brush" settings change the way that Oracle processes data in a profound way, and a single change can either make you a hero or get you fired.

Click below to learn more about global tuning with Oracle Silver Bullets:

more

Indexing....The Good, The Bad, and the Ugly

2009-05-26T18:55:00.002+07:00

Indexes are the easiest way to improve the performance of long running queries with full table scans. Indexes allow the database to search the smaller indexes as opposed to searching the large table. This can improve not only the SELECT queries, but also UPDATEs and DELETEs.

Finding the most efficient index is difficult. Most indexes will be the default B_Tree type. This type of index has been in use for years and has been highly optimized.

Having too many indexes can slow down performance of UPDATEs and DELETEs. Use too few indexes, and all types of queries may run slower, even UPDATEs and DELETEs. If it were just a trade off, performance of UPDATEs and INSERTs versus performance of SELECTs, optimizing indexes in a database would be fairly easy. However, it is not that simple and finding the right balance is the challenge.

Learn secrets for finding balance when index planning:

http://oracle-tips.c.topica.com/maam5JAabQj2zbLGJrib/

*****************************************

Can you see your Oracle bottlenecks?

After several years of hard work, Ion for Oracle is finally available for general release. Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access. Follow the link to try Ion for free:

http://oracle-tips.c.topica.com/maam5JAabQj2AbLGJrib/

Finding trends and signatures for proactive tuning

2009-05-26T18:53:00.000+07:00

One shortcoming with reactive tuning approaches like Oracle's automatic memory management (AMM) is that it is reactive, and it waits until a problem is signaled before taking remedial actions. To be truly proactive, we must develop techniques that will analyze repeating trends and identify processing signatures.

These trends, in turn, will predict changes in workload characteristics. If you know in-advance when a workload is going to change, you can adjust the SGA just in time to accommodate the new workload, fixing the issue before it bothers your end-user community.

This is the idea behind creating a self-tuning Oracle database, a proactive technique that I've been working on for over a decade.

To get started, you must use AWR or STATSPACK queries to identify changing workloads, analyzed in a linear regression, as well as by hour of the day and day of the week. Once you do this, "signatures" will become apparent, and you will see when your workload changes, the first step in proactive self-tuning for an Oracle database.

I am excited to announce that after years of work, I have finally completed a comprehensive tool for identifying hidden trends and signatures, Ion for Oracle. Ion uses applied artificial intelligence to remove the tedium from proactive analysis, leaving you free to do the intuitive work:

http://oracle-tips.c.topica.com/maam4PSabQcc1bLGJrib/

Special Report: The Oracle - Sun deal

2009-05-26T18:44:00.001+07:00

Oracle's announcement of its acquisition of Sun Microsystems will provide it with a complete lineup of hardware and software technologies -- an advantage few companies can boast. Stay tuned to SearchOracle.com for all of the latest headlines circulating around the exciting acquisition.

Check out our Special Report, offering a complete round-up of all the latest news stories buzzing around Oracle’s big move:

http://oracle-tips.c.topica.com/maam385abP6qObLGJrib/

Read news articles from across the TechTarget network including:

* Oracle-Sun combo: What does it mean for enterprise Java?
* Oracle-Sun acquisition raises MySQL, hardware concerns
* Analysts see Oracle-Sun deal as storage 'game changer'
* In Oracle-Sun deal, analysts predict identity management fallout
* VARs turn wary eye on Sun-Oracle combo

Access this Special Report today to stay up-to-date on all aspects of the latest Oracle acquisition:

http://oracle-tips.c.topica.com/maam385abP6qObLGJrib/

Experts tricks for Oracle SQL Tuning

2009-05-07T18:52:00.005+07:00

The Oracle cost-based SQL optimizer (the Cost Based Optimizer - CBO) is one of the world's most complex software packages ever created. Imagine, writing a program that will always generate the "best" execution plan for any SQL statement. Oracle's implementation of SQL optimization is the best in the world, and along with this great power and flexibility comes great complexity.

Oracle SQL tuning is phenomenally complex, but there are some secrets that can help you succeed. Here are a few tricks that they do not teach you in Oracle University:

- Do the system-level tuning first - Setting the optimizer parms properly (especially optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching) can tune thousands of SQL statements in a single action.

- Try a /*+ RULE */ hint - The first thing I do when tuning a query is to invoke the rule-based optimizer. If the Rule Based Optimizer (RBO) generates a fast plan, then you know that it is not a problem with missing indexes, and you can focus on sub-optimal schema statistics (especially histogram).

- Choose the optimizer_mode wisely - The default optimizer mode of all_rows is designed to minimize computing resources. If you want to optimizer your SQL for fast response time, you need first_rows (or first_rows_10, first_rows_100).

- Avoid hints whenever possible - In Oracle, hints are a last resort, and you can adjust your CBO statistics (with dbms_stats) and use histograms to reproduce the execution plan from a hint. This has the side benefit of tuning other SQL statements as well!

- Use Histograms wisely - Oracle sometimes makes incorrect "guesses" (cardinality estimates), resulting in incorrect index usage and improper table join order. Make sure to deploy histograms to resolve these issues. Here are my notes on tuning with histograms: link

Remember, adjusting initialization parms, adding missing indexes and re-computing schema statistics can impact thousands of SQL statements, reducing the amount of manual SQL tuning. These system-wide approaches are codified in the 11g SQL Performance Analyzer, and many of these "silver bullets" are described in the book "Oracle Silver Bullets"

http://oracle-tips.c.topica.com/maam3F4abP15obLGJrib/

To learn more secrets for SQL tuning, see my book Oracle Tuning: The Definitive Reference:
more

Oracle Troubleshooting tips and secrets

2009-04-30T19:24:00.000+07:00

More than any other area of Oracle, teaching Oracle troubleshooting is extremely challenging. Troubleshooting is largely an on-the-job skill, and no amount of book learning can match the skill of an experienced Oracle troubleshooter.

Oracle has become the world's most sophisticated and flexible database, and the sheer complexity of Oracle makes it very difficult to troubleshoot.

So, how can you learn Oracle troubleshooting skills? First, it is important that students have a strong background in the internal machinations of Oracle and understand the underlying mechanisms of the Oracle engine. It is also important to recognize how Oracle alerts the DBA to problems. Finally, it is vital to know how to properly interpret error messages.

When an end user gets hung up with a process, the source of the problem could be system wise or it could be unique to their session. It is up to the Oracle DBA to understand the right questions to ask and know the right places to look for further information. The goals of Oracle troubleshooting include:

* Learn a troubleshooting methodology for use in analyzing any Oracle database

* Understand ratio-based and bottleneck troubleshooting analysis

* Learn techniques for monitoring and optimizing memory usage

* Know how to quickly pinpoint and resolve I/O hotspots at the database, storage and object level

* Using scripts to uncover session-related bottlenecks

* Understand techniques for locating and fixing problem SQL

There are also specific tip for Oracle troubleshooting that can guide a neophyte in the right direction:

* Always check the first error message - Oracle will frequently throw multiple errors, but it's always the first error that will lead you to the root cause of the problem.

* Root cause analysis is not always practical

* Know your OFA structure

* Don't be afraid to bounce

Click this link for detailed tips for the neophyte as well as other Oracle troubleshooting tips:

more

Find opportunities for materialized views

2009-04-25T13:41:00.000+07:00

Oracle says that a well defined set of materialized views can be made to cover a whole schema, but that is easier said than done. Resourceful DBAs know about the SQLTuning Advisor, but the wisest are always on the lookout for techniques they can use to find all opportunities for materialized views.

Materialized views are one of the single most important SQL tuning tools.

They are a true silver bullet, allowing the DBA to pre-join complex views and pre-compute summaries for super fast response time.

Read more about how to use your existing workload to determine the best materialized view for super fast response time:

more

Inside Oracle Fully Automated SQL Tuning

2009-04-25T13:24:00.000+07:00

Each new release of Oracle brings new tools and techniques for performance optimization, but it is a formidable challenge to create tools that can identify and correct database bottlenecks for every possible situation. I run a large remote DBA operation, monitoring and tuning mission-critical databases around the globe. With such a large base of experience, I am in a unique position to see hundreds of systems in action and understand the best approaches to achieving optimal performance.

Drawing upon my vast experience base, I will attempt to present the unvarnished truth about which Oracle tuning tools and techniques are the most effective. Oracle Corporation is a cheerleader for their product, saying that all of their features are wonderful; however, they are not always forthcoming about the risks and rewards of using new tools and techniques. For example, back in the last days of Oracle 7, word came forth from Oracle Corporation that the rule-based optimizer (RBO) was being removed from Oracle 8, and all shops must move quickly to adopt the cost-based optimizer (CBO).

The CBO was not quite ready for primetime, much to the consternation of those shops who had attempted to migrate to it back in Oracle 8. Even in 11g, we still see improvements to the CBO, and despite dire warnings that the RBO will disappear, RULE hints appear in hundreds of Oracle’s own internal SQL statements.

Read on for more facts about the evolution of Oracle’s tuning tools and techniques:

More

What's the Value of a Professional DBA?

2009-04-03T09:53:00.000+07:00

In these recessionary times, it's tempting to cut corners and replace well-trained (and well compensated) DBA's with cheap foreign pretenders. It's penny-wise and pound-foolish behavior, sure to cause issues in the future. Oracle customers are often not aware of the inherent complexities of Oracle that make it the world's most robust and flexible data platform.

Penny wise and pound foolish:

You get what you pay for. If you want a DBA with an MBA and good technical experience, it can easily cost over $100,000 per year.

In the hands of a competent expert, Oracle gives a level of control over that data that is unprecedented, and a good DBA can control every aspect of their mission-critical database. In the hands of an inept reckless beginner, Oracle's powerful features become a weapon of destruction.

Untrained and inexperienced Oracle DBA's are the bane of corporate America. Oracle Corporation white papers suggest that more than 75% of all Oracle database outages are attributable to human error.

Read more on the value of a professional DBA here:

http://oracle-tips.c.topica.com/maamXYHabPbQ3bLGJrib/

Inside Adaptive Cursor Sharing

2009-04-01T11:17:00.000+07:00

Oracle has improved cursor_sharing several times over the years. Remember, adaptive cursor sharing is only deployed in rare cases where a skewed column distribution (as noted by the histogram) indicates that a different execution plan would be faster. For example, a query with a popular bind variable value would be best served with a full table scan while an unpopular bind variable value would benefit from an index access plan.

But remember, this is a rare occurrence in many systems.

Based on extensive experience in the industry, it appears that about 80% of shops have uniformly distributed data. Large tables remain large, and the distribution of values within a column remain unchanged.

On the other hand, roughly 20% of databases experience highly volatile data loads, where tables are small on one day and huge the next, or cases where there is a "difference that makes a difference". In these databases, huge changes in the tables data (usually associated with high DML) changes the distribution of data values, necessitating a re-analysis of column histograms.

Read more about 11g adaptive cursor sharing:

http://oracle-tips.c.topica.com/maamW0iabO3zjbLGJrib/

Inside Oracle Cloud Computing

2009-04-01T11:13:00.000+07:00

Cloud computing has been touted as a radical "paradigm shift", where IT power is delivered to an application over the internet as you need it, rather than drawn from a centralized server.

What they don’t want you to know is that cloud computing is a new name for a feature that’s almost half a century old! Back in the olden-days of data processing, customers rented all of the resources they needed, software, disk, CPU and RAM, all under the umbrella of a MVS mainframe, and they were billed according to their actual usage.

These cloud computing announcements in the media are complete nonsense, and the idea of "cloud computing" is nothing new. Back in the 1980’s, companies regularly rented usage of mainframe computers to third parties, and the mechanisms for apportioning shared computing resources have been around for almost half a century.

Read more about Oracle's implementation of cloud computing and learn the truth behind the hype:

http://oracle-tips.c.topica.com/maamVKuabOS1TbLGJrib/

Oracle 11g Data Compression Tips for the DBA

2009-04-01T10:54:00.000+07:00

One of the exciting new features of Oracle 11g is their inline data compression utility. While it is true that data storage prices have fallen dramatically over the last decade, and continue to fall rapidly, Oracle data compression has far more appealing benefits than simply saving on disk storage cost. Because indexes and the data itself can be highly compressed, information can be fetched off of the disk devices with less physical IO, which radically improves query performance under certain conditions.

Let's take a closer look at how one would implement Oracle 11g Data Compression in order to achieve the optimal results.

Understanding data compression

Data compression techniques, such as the Huffman algorithm, have been around for nearly a century, but only today are being put to use within main stream information systems processing. Using these techniques, a decompression utility is called immediately upon the data block fetch. Within the Oracle data buffers, the fully uncompressed version of the data remains in the data buffers, even though the information remains compressed on the data blocks themselves. This leads to an anomaly between the size of information on the data blocks and the size of the information within the data buffers. Upon applying Oracle data compression, people will find that far more rows will fit on a data block of a given size, but there is still no impact on the data base management system from the point of view of the SGA (system global area). Because the decompression routine is called upon block fetch, the Oracle data buffers remain largely unchanged while the data blocks themselves tend
to have a lot more data on them.

Tests show that 11g compression results in slower transaction throughput but creates less writes because of higher row density on the data block. Overall, the benchmark slows that I/O writes being reduced while CPU increases, resulting in slowing SQL throughput:

* Slower transaction throughput – As we expect, Oracle transactions run faster without the encryption/decryption processing overhead. This encryption benchmark shows significantly slower throughput when deploying TDE, almost 20% (81 transactions/second with TDE, 121 transactions/second with TDE).

* Less Disk Writes – Since transparent data encryption compresses the data, the benchmark with TDE required less disk writes.

* More CPU required - As we would expert, TDE required CPU cycles for the encrypt/decrypt operations, and in this benchmark test we see User CPU rise from 46 to 80 when using TDE data encryption.

See more details on data compression here:
http://oracle-tips.c.topica.com/maamRxVabOkFObLGJrib/

Global SQL Optimization

2009-04-01T10:26:00.000+07:00

True Oracle professionals will routinely undertake a holistic tuning approach prior to delving into the tuning of specific SQL statements. This holistic approach has been the bread and butter of successful DBAs since the earliest days of Oracle.

In Oracle 11g, this holistic approach is codified in the SQL Performance Analyzer (SPA), a new tool used to simplify the setting of optimizer statistics and initialization parameters. In the SPA, the DBA chooses a representative workload and runs it, comparing the overall SQL execution plans with different sets of CBO statistics and the settings for the silver bullet initialization parameters.

Click below to read more about Global SQL Optimization:

http://oracle-tips.c.topica.com/maamRxLabOkFabLGJrib/

Would you care to CTAS?

2009-04-01T10:18:00.000+07:00

If you don't want to use dbms_redefinition, the Create Table As Select (CTAS) statement is one method for reorganizing an Oracle table or moving the table to another tablespace. Instead of spending hours setting up parameter files and job steps, you can copy and rename the table in three simple SQL statements. The CTAS statement can be used to change storage parameters for a table (INITIAL, NEXT, FREELISTS) and also change the physical sequence of the table rows. CTAS has the following syntax:

create table xxx_new
tablespace new_tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )
as
select * from xxx
order by primary_index_key_values;

There are several ways to execute CTAS to reorganize table; many of the options depend upon the version of Oracle and the particular configuration of the Oracle database.

Read more about the CTAS here:

http://oracle-tips.c.topica.com/maamRxyabOkDebLGJrib/

The High Cost of Oracle Data Breaches

2009-04-01T10:08:00.002+07:00

Maintaining Oracle data security is essential, and all Oracle managers have a fiduciary responsibility to protect confidential information. The Oracle professional must be aware of the financial risk associated with inadvertent disclosure of their Oracle data.

Whether it's an external breach (hacker attack) or internal data management malfeasance (i.e. using an overseas remote DBA provider), disclosure of confidential data has expensive consequences, and these costs can be identified.

I’ve seen firsthand how choosing a bargain offshore Oracle remote DBA provider has lead to widespread data theft, leaving the customer with little or no legal recourse. Many Oracle shops choose their remote DBA provider within the USA because of our strict data protection statutes.

Read more about these risks, Oracle hacker horror stories, and data loss calculators as this article continues:

http://oracle-tips.c.topica.com/maamP6kabN8rNbLGJrib/

Database Management for Business Intelligence

2009-02-12T18:44:00.000+07:00

Consumer data has been used since the ancient Greek times to guide shipments of olive oil and other commodities throughout the Ancient Grecian Empire and beyond. While the foundations of the data storage have changed dramatically from the Mesopotamian clay tablets to today's modern database management systems, the goals of business intelligence and data mining remain unchanged. The basic tenet of business intelligence is that one can predict the future by analyzing the past and grouping together related consumers to develop highly sophisticated and accurate predictive models. These predictive models can save tens of billions of dollars a year in advertising. At the same time, consumers are provided with targeted marketing which is most appropriate to their needs.

Business intelligence is not limited exclusively to the area of marketing and sales. Today, hospitals group patients together in terms of their age and symptoms, and analyze treatment regimens in order to determine the best course of treatment for specifically identifiable populations.

Though the use of business intelligence no doubt saves lives, it has even more wide ranging social implications. First and foremost is the issue of data privacy. As consumer monitoring becomes more and more ubiquitous, we see that many privacy advocates do not want even the most innocuous behaviors recorded. For example, many supermarkets monitor consumer purchasing habits via a buyers’ club card. Fortunately, most consumers don't care whether anyone else knows they prefer peas to string beans. Through the use of the buyers’ club card, consumers passively allow point of sale systems to readily track purchases, and tie individual purchases to background demographic information. When consumers apply for buyers’ club cards, they provide basic demographic information which is in turn analyzed with publicly available information on major life events, such as the purchase of a house, a divorce, the presence or absence of children, and income, such that the database has
detailed information not only about what products are being purchased, but the basic demographics of the person who is purchasing.

The issue of data storage has always been important to business intelligence because of the dynamics of changing technology. Disk prices are falling radically each and every year. Back in the 1980’s, 1.2 gigabytes of storage could cost upwards of $200,000.00; whereas, today you can purchase the same amount for less than $100.00. Given our ability to store large amounts of empirical information cheaply, the goal of the business intelligence manager is to somehow be able to cleanse and manipulate this data in such a way that accurate predictive models can be built.

Follow the link below to take a closer look at the evolution of business intelligence from the perspective of the database manager and explore how the database influences the manipulation of these vast quantities of observable data in the real world.

Read more

Super Fast Database Copying/Cloning

2009-02-12T18:42:00.004+07:00

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

An Oracle database cloning procedure can be used to quickly migrate a system from one UNIX server to another. The process can clone an Oracle database, ultimately resulting in the fastest way to copy an existing Oracle database.

To find out more on the secrets of super fast database cloning, see the full article here:

Read more

The Top 10 Reasons for Poor Oracle Performance

2009-02-12T18:42:00.003+07:00

The Oracle Documentation lists reasons for poor performance, but they are not as comprehensive or useful as our BC top-10 list. Oracle Corporation has never been as good at tuning their own software as third-party experts, and their performance cause list is missing many important factors.

Some factors, like a mis-configured I/O sub system, are obvious causes of poor Oracle performance; however, some of the others may come as a surprise.

Click below to see the BC top-10 list of the most common Oracle performance problems:

Read more

Managing an Aging Oracle Database

2009-02-12T18:40:00.001+07:00

The Geriatric Instance – Managing the legacy Oracle database is not an easy task.

As Oracle databases pass the decade mark, they begin to suffer from a host of age-related problems. Just like people, the sheen is gone, parts become unwieldy and creaky and some areas of the database need ongoing medical attention.

See these important tips and tricks for caring for an elderly legacy Oracle database:

Read more

Oracle hash joins tips

2009-02-12T18:37:00.002+07:00

In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table.

However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins. For large tables, hash joins requires lots of RAM.

Oracle places limits of the amount of RAM available for hash joins so that no single session will "hog" all of the RAM.

Read more about Oracle hash

Auditing and reporting Oracle user activity

2009-02-02T10:23:00.000+07:00

Changes in U.S. federal laws have mandated increased security for auditing Oracle user activity. HIPAA, the Sarbanes-Oxley Act, and the Gramm-Leach-Bliley Act have all produced serious constraints on Oracle professionals who are now required to produce detailed audit information for Oracle system users.

Starting with Oracle8i, Oracle introduced special triggers that are not associated with specific DML events (e.g., INSERT, UPDATE, and DELETE). These system-level triggers included database startup triggers, DDL triggers, and end-user login/logoff triggers.

While Oracle provided the functionality for these new triggers, it was not clear how they could be used in order to track system-wide usage.

Read more about Oracle auditing and reporting here:

http://oracle-tips.c.topica.com/maamFPSabM2uEbLGJrib/

Forecasting Oracle Performance

2009-01-01T10:19:00.001+07:00

One of the most effective tools for forecasting Oracle performance is the time-series data in STATSPACK and the Automatic Workload Repository in Oracle 10g. Using this time-series information, the Oracle DBA can identify hidden performance trends and forecast Oracle Performance problems before they cripple the database.

George Santanaya once said "Those who forget the past are condemned to repeat it". In order to accurately tune any Oracle database, you need a historical data collection mechanism and the ability to translate the data into reports that forecast repeating Oracle performance bottlenecks.

The process of forecasting Oracle performance trends uses the well-known quantitative techniques of predictive modeling and predictive analytics, whereby multivariate correlation techniques are applied to Oracle performance data from STATSPACK and AWR. Once the trends are identified, scheduled job are employed to reconfigure Oracle to relieve the bottleneck in anticipation of the cyclic performance issue.

Read more about forecasting Oracle performance here:

http://oracle-tips.c.topica.com/maamFPyabMZcHbLGJrib/

Cost Control: Inside the Oracle Optimizer

2009-01-01T10:17:00.000+07:00

Oracle's cost-based SQL optimizer (CBO) is an extremely sophisticated component of Oracle that governs the execution for every Oracle query. The CBO has evolved into one of the world's most sophisticated software components, and it has the challenging job of evaluating any SQL statement and generating the "best" execution plan for the statement.

Because the CBO determines the execution speed for every Oracle query, the Oracle professional must understand how the CBO is influenced by Oracle external issues, internal statistics, and data distribution.

Read more about the Oracle Optimizer here:

http://oracle-tips.c.topica.com/maamADPabMv0pbLGJrib/

Tips for Using Oracle Stored Procedures

2008-12-22T10:39:00.001+07:00

Oracle stored procedures and triggers are faster than traditional code, which leads to an increase in popularity. As application code moves away from external programs and into the database engine, DBAs need to understand the related memory requirements for Oracle stored procedures and know how to manage stored procedures for optimal database performance.

There are many compelling benefits to putting all Oracle SQL inside stored procedures, including:

* Better performance. Oracle stored procedures load once into the shared pool and remain there unless they become paged out. Subsequent executions of the stored procedure are far faster than executions of external code.

* Coupling of data with behavior. DBAs can use naming conventions to couple relational tables with the behaviors associated with a table (using Oracle stored procedures as "methods"). If all behaviors associated with the employee table are prefixed with the table name--employee.hire, employee.give_raise, for example--the data dictionary can be queries to list all behaviors associated with a table (select * from dba_objects where owner = 'EMPLOYEE'), and it's easy to identify and reuse code.

* Isolation of code. Since all SQL is moved out of the external programs and into the Oracle stored procedures, the application programs become nothing more than calls to stored procedures. As such, it becomes very simple to swap out one database and swap in another one.

Read more about using stored procedures here:

http://oracle-tips.c.topica.com/maamAC1abMvSPbLGJrib/

Professional Dress Code Tips

2008-12-22T10:38:00.001+07:00

First impressions count. A professional consultant who doesn’t take the time to maintain a professional appearance presents the image of not being able to perform adequately on the job. This professional dress code is codified because many professionals have never been taught appropriate professional appearance and demeanor.

Professional dress code standards are alive and well in major financial and executive management. Anyone who aspires to top management knows that personal appearance counts.

If you look and behave like a highly trained and well-groomed professional, you will win the respect and honor of valued clients.

A fresh haircut, spit-shined shoes and a crisp suit go a long way in establishing a professional demeanor.

Read more about professional dress code tips here:

http://oracle-tips.c.topica.com/maamACVabMvSybLGJrib/

Oracle downsizing: How to Avoid Layoffs

2008-12-22T10:34:00.001+07:00

The collapse of major financial institutions has rippled through the economy and managers everywhere are searching for ways to stretch their dollars. In this article, we will explore the ways that Oracle Managers trim their budgets to survive the looming worldwide recession.

The market for Oracle professionals has been a boom-or-bust market. In years where demand exceeds supply (1996-1999), anyone with even the most marginal skills could secure a job in Oracle technology. But bank failures, global insecurity, the credit crunch and declining sales have made Corporations faced with tough choices. Some must either limp along with fewer resources or face bankruptcy, and managers everywhere are struggling to find cost cutting opportunities throughout the organization.

This has had a direct and noticeable impact on the Oracle community. Layoffs are rampant, and those Oracle professionals who are fortunate enough to have their jobs are faced with cutting corners. Many perks that were considered essential to an Oracle professional a few years ago are gone.

When faced with the reality of doing layoffs most managers try to be as fair as possible, preparing an objective comparison of cost vs. performance for each employee. However, you must also consider intangible aspects such as the loss of institutional knowledge which is especially critical for Oracle DBA’s who understand the complex nuances of your systems.

Read more about how Oracle managers handle layoffs in uncertain times here:

http://oracle-tips.c.topica.com/maamypyabMiYxbLGJrib/

Speed Oracle SQL with Temporary Tables

2008-12-22T10:30:00.001+07:00

Using temporary tables with Dictionary Views

The prudent use of temporary tables can dramatically improve Oracle SQL performance. To illustrate the concept, consider the following example from the DBA world. In the query that follows, we want to identify all users who exist within Oracle who have not been granted a role. We could formulate the query as an anti-join with a noncorrelated subquery (against a complex view) as shown here:

select
username
from
dba_users
where
username NOT IN
(select grantee from dba_role_privs);
This query runs in 18 seconds. As you may remember from Chapter 12, these anti-joins can often be replaced with an outer join. However, we have another option by using CTAS. Now, we rewrite the same query to utilize temporary tables by selecting the distinct values from each table.

create table
temp1
as
select
username
from
dba_users;

create table
temp2
as
select distinct
grantee
from
dba_role_privs;

select
username
from
temp1
where
username not in
(select grantee from temp2);
With the addition of temporary tables to hold the intermediate results, this query runs in less than three seconds, a 6× performance increase. Again, it is not easy to quantify the reason for this speed increase, since the DBA views do not map directly to Oracle tables, but it is clear that temporary table show promise for improving the execution speed of certain types of Oracle SQL queries.

Using Temporary Tables
If the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table. A GLOBAL TEMPORARY table has a persistent definition but data is not persistent and the global temporary table generates no redo or rollback information. For example if you are processing a large number of rows, the results of which are not needed when the current session has ended, you should create the table as a temporary table instead:

create global temporary table
results_temp (...)
on commit preserve rows;

The “on commit preserve rows” clause tells the SQL engine that when a transaction is committed the table should not be cleared.

The global temporary table will be created in the users temporary tablespace when the procedure populates it with data and the DIRECT_IO_COUNT will be used to govern the IO throughput (this usually defaults to 64 blocks).

Oracle 11g New Features

2008-12-22T10:29:00.000+07:00

At Oracle Openworld 2006, Oracle announced some exciting new features of Oracle 11g, and they promise 482 new Oracle11g features. For me, the most exciting new features relate to tools that automate the well-structured DBA tasks, freeing-up the DBA to pursue more challenging work.

- 11g SQL new features
- 11g language support new features
- 11g PL/SQL New Features
- 11g DBA features
- 11g RAC new features & enhancements
- 11g performance features
- 11g security new features
- 11g Enterprise Manager new features

As of 2007, the commercial database market is very mature and expectations are high. All of the leading databases do a good job in storing and retrieving data, and customers are now demanding self-tuning databases, intelligent engines that detect and correct sub-optimal conditions.

Oracle 11g is the clear leader in this area. Oracle has invested heavily into self-tuning capabilities including automated storage and memory management and intelligent tuning advisors. Now in 11g, Oracle closes the loop and offers intelligent automation tools to create a self-healing database. The most important 11g new automation features include a SQL tuning advisor that automatically tunes SQL statements.

How to Export Oracle Data

2008-12-22T10:24:00.000+07:00

Data export is a common task for an Oracle DBA, but it is not necessarily a straightforward one. DBAs have to know how to handle Oracle export errors, work with multiple tables and rows and export stored procedures and functions. That's why SearchOracle.com created the Oracle data export guide, packed with best practices, step-by-step instructions and example scripts on how to export Oracle data.

Explore this learning guide to get all the data export errors and solutions recommended by our experts:

http://oracle-tips.c.topica.com/maamt05abLTMfbLGJrib/

Turbocharge SQL with Advanced Oracle Indexing

2008-12-22T10:23:00.000+07:00

Oracle includes numerous data structures to improve the speed of Oracle SQL queries. Taking advantage of the low cost of disk storage, Oracle includes many new indexing algorithms that dramatically increase the speed with which Oracle queries are serviced. This article explores the internals of Oracle indexing; reviews the standard b-tree index, bitmap indexes, function-based indexes, and index-only tables (IOTs); and demonstrates how these indexes may dramatically increase the speed of Oracle SQL queries.

Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query. I begin our look at Oracle indexing with a review of standard Oracle b-tree index methodologies.

The Oracle b-tree index

The oldest and most popular type of Oracle indexing is a standard b-tree index, which excels at servicing simple queries. The b-tree index was introduced in the earliest releases of Oracle and remains widely used with Oracle.
Read more on Turbocharging SQL:

http://oracle-tips.c.topica.com/maamrWGabLGVXbLGJrib/

Tuning individual SQL statements

2008-12-22T10:22:00.000+07:00

The acronym SQL stands for Structured Query Language. SQL is an industry standard database query language that was adopted in the mid-1980s. It should not be confused with commercial products such as Microsoft SQL Server or open source products such as MySQL, both of which use the acronym as part of the title of their products.

Do this before you start individual SQL statement tuning

This broad-brush approach can save thousands of hours of tedious SQL tuning because you can hundreds of queries at once. Remember, you MUST do this first, else later changes to the optimizer parameters or statistics may un-tune your SQL Remember, you must ALWAYS start with system-level SQL tuning, else later changes might undo your tuned execution plans:

* Optimize the server kernel - You must always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency.

* Adjusting your optimizer statistics - You must always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent execution plans. Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates.

* Adjust optimizer parameters - Optimizer optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.

* Optimize your instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.

* Tune your SQL Access workload with physical indexes and materialized views - Just as the 10g SQLAccess advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a Godsend for SQL tuning.

11g Note: The Oracle 11g SQL Performance Analyzer (SPA), is primarily designed to speed up the holistic SQL tuning process.

Once you create a workload (called a SQL Tuning Set, or STS), Oracle will repeatedly execute the workload, using sophisticated predictive models (using a regression testing approach) to accurately identify the salient changes to SQL execution plans, based on your environmental changes.

Read more on SQL tuning steps:
http://oracle-tips.c.topica.com/maamqe5abLw4obLGJrib/

Inside Oracle Cloud Computing

2008-11-01T14:38:00.000+07:00

How to become an Oracle DBA

2008-10-16T17:15:00.001+07:00

Every year, young computer professionals leave the hallowed halls and ivory towers of college and survey the landscape for computer jobs. They look at the salary surveys and drool at the prospect of earning up to $250k per year as a DBA. Many of them don't know what a DBA does, but they sure like the money.
Since I've been a DBA for 25 years I'm often asked "How do I become a DBA?", many from neophytes who are enticed with the glamour and power of the DBA role. While being a DBA is exciting and lucrative, it's a career choice that requires years of preparation. The most important thing to remember is that the job of a DBA requires a 24x7 commitment. Being an Oracle DBA can be a very stressful, thankless job, and many DBA jobs require the DBA to be on-call on Thanksgiving and Christmas to perform downtime maintenance. Plus, the DBA is expected to constantly keep-up with the rapidly-changing technology, working nights and weekends on a regular basis.
It's not uncommon for a DBA to earn as much as a mid-level manager, and in larger shops the DBA is a vice president. However, the high high pay is a double-edged sword. The DBA must constantly justify their salary, and a good DBA who automates many of their job functions may find themselves looking for a new job.

What classes should I take to prepare for a job as a DBA?

In grad school, all IT and CS students take courses like Operations Research where they learn to develop complex decision rules and them apply them to real world datasets. Using Oracle as the back-end storage of data and decision rules is a great way to prepare for real-world applications of expert systems, DSS and AI. Also, advanced statistics courses (multivariate analysis) are a good way to prepare for a career in Oracle data mining and Business Intelligence (BI)
.
What College Degrees are best for the DBA?

Companies are now requiring a combination of technical and managerial skills and the best-fits are those with an undergraduate degree in Computer Science and a MBA in Information Systems. Employers need a DBA who can understand business systems areas (accounting, finance, marketing) and MBA's are a perfect fir for the DBA job role.

How much can I earn as an Oracle DBA?

Here are my notes on Oracle Salary Compensation, and there is a wide variation according to the responsibilities of the DBA job, the quality of the DBA, the cost of living, and the experience of the DBA. Here is a good Oracle DBA salary survey showing the national average at $65,000 per year.
Read more on Becoming an Oracle DBA:

http://oracle-tips.c.topica.com/maamfocabKwkSbLGJribafpMfn/

What are Oracle worst practices?

2008-10-16T17:14:00.000+07:00

There is a great deal of discussion about following Oracle best practices (e.g. using OFA, etc.) but little has been said about Oracle worst practices. Contrary to an Oracle "best practice", an Oracle "worst practice" are actions (or non-actions) that cause poor performance and create excessive management overhead, essentially a non-standard approach to Oracle database management. Let's look at a few of my Oracle worst practices:

Many Oracle shops commission an Oracle Health Check to find these issues, but worst practices are alive-and-well in the Oracle community.

http://www.dba-oracle.com/oracle_health_check.htm

I describe Oracle worst practices into several general areas:

- PL/SQL worst practices

- Oracle DBA worst practices

- Oracle design worst practices

- Oracle architecture worst practices

Read my complete treatise on Oracle worst practices here:

http://www.dba-oracle.com/t_worst_practices.htm

For complete details, see my book "Oracle Tuning: The Definitive Reference":

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm

Tricks in Oracle SQL to compare two tables

2008-10-16T17:13:00.000+07:00

Oracle Corporate developer Vadim Tropashko has some interesting notes on tuning Oracle SQL queries that compare the contents of two tables, showing several SQL solutions and their performance within the Oracle cost-based optimizer. Tropashko has a remarkable approach to solving this problem, a worthy read for anyone who writes SQL in Oracle.

Vadim also shows a great example of using the hidden parameter _convert_set_to_join to improve SQL execution speed for queries that find the semantic difference between two tables, and he shows how to compare the rows and columns of two tables with Oracle SQL syntax.

http://oracle-tips.c.topica.com/maamcioabJ7adbLGJrib/

Vadim Tropashko is the author of the book "SQL Design Patterns: The Expert Guide to SQL Programming".

This is the definitive reference for SQL design patterns, a critical knowledge area for any Oracle developer.

http://oracle-tips.c.topica.com/maamcioabJ7abbLGJrib/

Get that Oracle job you crave!

2008-10-16T17:12:00.000+07:00

Tired of searching the Web for relevant career information on how to obtain that Oracle position you've been vying for?
Look no further! Whether you're looking to score your first Oracle job as a DBA or developer or just concerned about the future of your current position -- you're not alone. That's why SearchOracle.com has compiled this fast guide to finding and keeping an Oracle job. You'll get tips and techniques that are sure to help you stand out against the crowd.
Whether you're interested in brushing up on interview questions and techniques, checking out Oracle salaries or simply searching for open positions, this guide has what you're looking for:

http://oracle-tips.c.topica.com/maamfF2abKyppbLGJrib/

Get expert advice, tips, resources and more on everything you need to know about your Oracle dream job including:
* Salary and hiring information.
* Interview and job hunting resources.
* How to make smart Oracle career decisions.
* Job search sites.
* And more!

Become a member of SearchOracle.com to access the entire guide and kick-start your Oracle career today:

http://oracle-tips.c.topica.com/maamfF2abKyppbLGJrib/

ABOUT SEARCHORACLE.COM

On SearchOracle.com, you'll find independent resources that are specifically designed for Oracle professionals, plus
in-depth news, analysis, peer advice and expert tips to help with strategic issues, day-to-day IT management and
Oracle challenges. Membership is free -- sign up today!

Inside Oracle 10g Automatic Memory Management

2008-10-16T17:08:00.001+07:00

As we migrate from Oracle9i to Oracle10g, Oracle will automatically enable Automatic Storage Memory Management (ASMM), a automation features that adjusts the sizes of your default data buffer and shared pool.

The Oracle documentation shows that Oracle uses the memory advisories from Oracle9i and applies heuristics (rules of thumb) to determine the best shift in RAM pool sizes. These heuristics consist of hypothesis testing with "what if" scenarios, and chooses the size with the greatest overall marginal benefit.

To learn how ASMM works and how to monitor ASMM, check-out my research:

http://oracle-tips.c.topica.com/maamch5abJ69nbLGJrib/

Oracle Monitoring Best Practices

2008-10-16T17:07:00.000+07:00

All DBA's are required to set-up an Oracle monitoring infrastructure and the architecture of the monitoring system is directly dependent on the demands of management. Oracle system cost the end-user community millions of dollars, and the end-user often demand service-level agreements (SLA's) that impose strict monitoring tasks for the Oracle professional.

- In many shops, the end-user community is demanding Oracle performance statistics at a detailed level.

- A financial services company demands that 95% of their Oracle transactions complete within one wallclock second. The DBA most develop a monitoring strategy to ensure compliance. (Note: there are special techniques that can be employed to get end-to-end response time within Oracle).

- A stock brokerage demands that the DBA write a sophisticated real-time proactive monitor. This monitor will quickly diagnose and pinpoint Oracle bottlenecks, but getting the data is a problem. It requires real-time access to all internal Oracle control structures. Running the monitor imposes a measurable burden on overall system load.

- A manufacturing plant requires measuring response time by hour-of-the-day to ensure fast throughput for all shifts.
These are just a few examples of the business demands that drive the Oracle professional to create sophisticated monitoring infrastructures. Let's example some of the most important issues when making these decisions.

Read the entire article on Oracle monitoring tips here:
http://oracle-tips.c.topica.com/maal6WNabJhjibLGJrib/

Sending e-mail from Oracle

2008-09-05T13:46:00.001+07:00

Sending e-mail from Oracle

Many systems have online e-mail alerts and Oracle provides the utl_smtp PL/SQL package (first introduced in Oracle8i) to facilitate e-mailing from PL/SQL. The PL/SQL can then be embedded to any application for a slick e-mail alert interface. Here is the PL/SQL code to define the e-mail environment, with references to working PL/SQL Oracle e-mailing script by Dr. Tim Hall and Dave Wotton. If you are developing with HTML-DB you can also send Oracle e-mail with the htmldb_mail package.

Also, Stephen Rea suggests his e-mail package, which was derived from his and other's work, and now allows attachments from Oracle LOB objects (CLOB, BLOB), in addition to text and binary file attachments.

Step 1: Run the Jserver code for PL/SQL e-mail

You must install the utl_smtp package and Jserver by running the following DBA scripts as SYSDBA (SYS user):
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql

Step 2: Prototype the PL/SQL e-mail stored procedure

The next step is to write the a prototype for the PL/SQL to process the e-mail message. We will call our procedure e_mail_message, and this will be a sample invocation:

e_mail_message
(
from_name => 'oracle' ,
to_name => 'info@remote-dba.net' ,
subject => 'A test',
message => 'A test message'
);

Step 3: Create your PL/SQL e-mail stored procedure

The following is modified from Dr. Hall's script, and another outstanding and well-documented Oracle emailing script published by Dave Wotton. Wotton has graciously published his sophisticated Oracle e-mailing PL/SQL procedure here.

create or replace procedure
e_mail_message
(
from_name varchar2,
to_name varchar2,
subject varchar2,
message varchar2
)
is
l_mailhost VARCHAR2(64) := 'burleson.cc';
l_from VARCHAR2(64) := 'linda@remote-dba.net';
l_to VARCHAR2(64) := 'don@burleson.cc';
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));

FOR i IN 1 .. 10 LOOP
UTL_SMTP.write_data(l_mail_conn, 'This is a test message. Line ' || To_Char(i) || Chr(13));
END LOOP;

UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END;
/

Invoking multi-line Oracle e-mail messages

You can invoke multiple line Oracle email messages by using the PL/SQL assignment operator and concatenating the desired message lines together:

mesg := 'This is a text message' || CHR(13)|| CHR(10) ||
'split over two lines' ;

Oracle utl_mail e-mail PL/SQL package

Oracle Database 10g has made it easier than ever before to interface PL/SQL with e-mail.

The new DBMS package is called utl_mail, and it makes it easier than ever before to send e-mail from inside PL/SQL. Unlike the cumbersome utl_smtp package, the new utl_mail package does not require any knowledge of the underlying protocols. OTN has a great summary of the features:

"This new package makes it possible for a PL/SQL programmer to send programmatically composed emails. It requires only the normal mental model of a user of a GUI email client rather than an understanding of the underlying protocol (SMTP) features. This distinguishes it from Utl_Smtp which was introduced in Oracle8i Database. Utl_Smtp requires that the programmer understands the details of the SMTP protocol. Utl_Mail is much simpler to use because it supports just a limited, but very common, subset of the functionality that Utl_Smtp provides."

The top-10 reasons for poor Oracle performance

2008-08-29T09:57:00.000+07:00

The Oracle Documentation lists reasons for poor performance, but they are not as comprehensive or useful as our BC top-10 list. Oracle Corporation has never been as good at tuning their own software as third-party experts, and their performance cause list is missing many important factors.
Obviously, a mis-configured I/O sub-system is a major cause of poor Oracle performance, but some of the others will surprise you.

Click below to see the BC top-10 list of the most common Oracle performance problems:

http://oracle-tips.c.topica.com/maal8FqabJAW6bLGJrib/

Important 10g upgrade gotchas revealed

2008-08-21T16:04:00.002+07:00

Oracle has improved the cost-based Oracle optimizer in 9.0.5 and again in 10g, so one of the most common causes of bad performance right after moving to 10g are related to environmental parameter settings (init.ora parms) and your optimizer statistics. Properly configured, Oracle 10g should always faster than earlier releases, both for PL/SQL and SQL, so it is likely that any slow performance after an Oracle 10g upgrade is due to initialization parameter settings or incomplete CBO statistics.

Remember, Oracle 10g is the world's most flexible and complex database, and upgrading to Oracle 10g is very tricky. Prior to putting your Oracle 10g upgrade into production, it's a best practice to thoroughly test the migration with a real-world workload. As an alternative, many shops obtain an independent Oracle health check to identify their sub-optimal configuration settings. Experts who frequently perform 10g upgrades know exactly where to look, and they can save you weeks of frustration.

To see the secrets, click below to see the most common causes of poor performance after a 10g upgrade:

http://oracle-tips.c.topica.com/maal8FjabJATTbLGJrib/

Also, see these notes on justifying an Oracle10g migration:

http://oracle-tips.c.topica.com/maal8FjabJATUbLGJrib/

Important research on pre-joining Oracle tables

2008-08-21T15:48:00.003+07:00

One serious performance issue with highly-normalized, non-redundant Oracle table designs (e.g. third normal form) is that Oracle experiences a high degree of overhead (especially CPU consumption) when joining dozens of tables together, over-and-over again.

Using materialized views we can pre-join the tables together, resulting in a single, fat, wide and highly-redundant table. This can reduce logical I/O from tens of thousands to a single row fetch, resulting in blisteringly fast response time, but careful attention must be paid to choosing the proper materialized view partition keys and the best refresh interval.

The problem with materialized view for pre-joined tables is keeping them refreshed. Because the materialized view is built from many tables, and changes to the base tables require an update to the materialized view.

Read more on this issue here:

http://oracle-tips.c.topica.com/maal8GhabJA8ebLGJrib/

For more details, see my book "Oracle Tuning: The Definitive Reference". It's 30% off, directly from the publisher:

http://oracle-tips.c.topica.com/maal8GhabJA8dbLGJrib/

Oracle LIKE clause searches with text indexes

2008-08-19T13:25:00.001+07:00

One serious SQL performance problem occurs when you use the SQL “LIKE clause” operator to find a string within a large Oracle table column (e.g. VARCHAR(2000), CLOB, BLOB):

Select stuff from bigtab where text_column like ‘%ipod%’;
Select stuff from bigtab where full_name like ‘%JONES’;

Because standard Oracle cannot index into a large column, there “like” queries cause full-table scans, and Oracle must examine every row in the table, even when the result set is very small. These unnecessary full-table scans are a problem:

1) Large-table full-table scans increase the load on the disk I/O sub-system

2) Small table full table scans (in the data buffer) cause high consistent gets and drive-up CPU consumption

One obscure trick for indexing queries with a leading wildcard character (like '%SON') is to create a REVERSE index and them programmatically reverse the SQL like clause to read "like 'NOS%'", effectively indexing on the other side of the text, clumsy, yet effective.

Read more about indexing on Oracle text based searches here:

http://oracle-tips.c.topica.com/maalZ3vabItU3bLGJrib/

PL/SQL features by release

2008-07-31T09:22:00.001+07:00

A Mini-History of Oracle and PL/SQL

This chapter answers two questions: where did PL/SQL come from and why is it the best database development language ever developed?

In the late 70s, around the time Ingres was getting started at UC Berkeley, three guys working on a contract for the CIA got together and started a company called Relational Software, Inc.

Their first product was a relational database called Oracle. The founders decided to use the C language for development. This would later become important when they decided to start porting to different platforms.

They also decided to support SQL as the internal data access language. This would also become a very important factor to its success. In 1979, Relational Software was the only company making an SQL compliant database. If anyone ever asks you who wrote the first SQL database, you now know the answer: Oracle.

To access the database, to write an application for example, you had to use an external language and compiler. In the early days of Oracle, that was C but, in time, several other languages were added: COBOL, ADA, Fortran, PL/1, and others.

In the early 1980s, the company was renamed Oracle Corporation. That would just be the beginning of Oracle’s desire to rename its products. In my time using the Oracle database, I think every tool I have used has been renamed at least once. In the case of CDE/Developer 2000/Developer Suite, it has been renamed enough to be confusing.

Oracle did not have an embedded language for many years. Having come from a government background, when they chose a language for the database, they modeled it on ADA.

I programmed in ADA for a few years in the 1980s while I was working as a consultant for the US Department of Defense. It is a very powerful, but very wordy, object oriented language. ADA, and by extension PL/SQL, are descendants of Pascal.

Oracle named this new language PL/SQL; the Procedural Language extension to SQL. I pronounce it pee ell sequel but many others pronounce it pee ell ess que ell. Feel free to pronounce it however you like though.

Oracle Monitoring Best Practices

2008-07-25T10:02:00.001+07:00

Important SSD changes coming to Oracle servers

2008-07-21T13:40:00.003+07:00

Last week Sun Microsystems made the stunning announcement that virtually all of their new servers will come with 32 gigabytes of super-fast solid-state disk (SSD). Sun notes that their internal SSD consumes one-fifth the power and is a hundred times faster than the magnetic-coated spinning platter disk. They note that SSD is perfect for I/O intensive systems like Oracle applications:. Sun notes:

"The majority of enterprises building I/O-intensive applications will use some amount of flash within a year, Fowler predicted. Databases like Oracle, MySQL and IBM DB2 are ideal candidates, he says."

Today, 99.99% of all Oracle data is stored on the ancient spinning platter disks that were introduced in the 1970's, but disk technology is rapidly being displaced with RAM-based SSD which is hundreds of time faster than magnetic-coated spinning platter disks.

In the 1980's, platter disks was very expensive, and a 1.2 gigabyte 3380 disk sold for more than $200k. Today, you can buy high-speed SSD for Oracle for under $1k per gigabyte. For terabyte sized Oracle shops, SSD is being selectively used for redo and other tablespaces where I/O is a bottleneck.

Following Sun's lead, several other major hardware providers are following suit and hundreds of hardware vendors will be providing SSD for use by Oracle systems.

Oracle benchmarks have shown that SSD can be up to 600 times faster for some operations, and SSD does not experience platter or controller latency, leading to far faster throughput.

As prices continue to fall, SSD will replace platter disks for all mission critical Oracle databases, and this will lead to some fundamental changes in Oracle administration, especially within the SGA. Because all data is stored on RAM, the data buffer cache size becomes far less important.

Read more about the huge benefits of Solid State Oracle here:

2008 Survey of rack-mount SSD vendors:
http://oracle-tips.c.topica.com/maalZ3gabItTubLGJrib/

SSD clobbers disk speed for Oracle:
http://oracle-tips.c.topica.com/maalZ3gabItTvbLGJrib/

Using RAC with SSD:
http://oracle-tips.c.topica.com/maalZ3gabItTwbLGJrib/

Oracle tuning Guru achieves a 20x performance boost

2008-07-10T09:33:00.003+07:00

Steve Karam, the Oracle wunderkind (the world's youngest Oracle ACE and Oracle Certified Master), has published an astute case study describing how he tuned a batch update down from 45 minutes to only 10 seconds!

>> Read "Does Size Matter? by Steve Karam, OCM, Oracle ACE

http://oracle-tips.c.topica.com/maalYRxabInjZbLGJrib/

In order to have a statistically valid test, Karam used a the real-world workload, using identical server, disk and instance settings. Karam notes how moving a high-DML table to a smaller blocksize resulted in a 20x throughput improvement:

"By going from a 16k blocksize to a 4k blocksize with all other things being equal, we experienced roughly a twenty times improvement."

Steve is now investigating further areas in the environment that could benefit from block optimization, targeting frequently used and highly concurrent segments as candidates for a smaller blocksize. Let's take a closer look at the use of multiple blocksizes to reduce waste and improve performance throughput.

Read the whole story of tuning Oracle I/O blocksize here:

http://oracle-tips.c.topica.com/maalYRxabInj0bLGJrib/

Invoking hash joins for faster Oracle SQL

2008-07-04T17:42:00.001+07:00

Enabling Oracle to perform hash joins

In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table.

However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins. For large tables, hash joins requires lots of RAM.

The 200 megabyte limit for hash joins

Oracle places limits of the amount of RAM available for hash joins so that no single session will "hog" all of the RAM. According to this research using Oracle 10g, the hash join maximum is only to only 200 megabytes (5% of pga_aggregate_target).

The Oracle DBA controls the optimizers' propensity to invoke hash joins because the DBA must allocate the RAM resources to Oracle (using the hash_area_size and pga_aggregate_target parameters) for the optimizer to choose a hash join. The CBO will only choose a hash join if you have allocated Oracle enough RAM area in which to perform the hash join.

Sizing your PGA for hash joins

The rules are quite different depending on your release, and you need to focus on the hash_area_size OR the pga_aggregate_target parameters.

Important Changes in 10g Statistics Collection

2008-06-26T09:16:00.002+07:00

Each new release of Oracle brings enhancements, and 10g has radically changed some important defaults for Oracle statistics collection (using dbms_stats). The one-size-fits-all approach is a great baseline, but the automatic 10g statistics collection may not be just-right for your database.

Oracle 10g does automatic statistics collection and your original customized dbms_stats job (with your customized parameters) will be overlaid.

You may also see a statistics deficiency (i.e. not enough histograms) causing performance issues. Re-analyze object statistics using dbms_stats and make sure that you collect system statistics.

execute dbms_stats.gather_system_stats('start');
-- wait an hour or so
execute dbms_stats.gather_system_stats('stop');

With Oracle Database 10g, there are some new arguments available for the dbms_stats package subprograms. Those changed 10g dbms_stats parameters are granularity and degree.

There are also cases where you need to disable the automatic statistics collection on Oracle10g.

Read more about importatnt changes in 10g statistics collection here:

http://oracle-tips.c.topica.com/maalOG5abHNJ0bLGJrib/

Important SSD changes coming to Oracle servers

2008-06-20T11:13:00.003+07:00

Last week Sun Microsystems made the stunning announcement that virtually all of their new servers will come with 32 gigabytes of super-fast solid-state disk (SSD). Sun notes that their internal SSD consumes one-fifth the power and is a hundred times faster than the magnetic-coated spinning platter disk. They note that SSD is perfect for I/O intensive systems like Oracle applications:. Sun notes:

"The majority of enterprises building I/O-intensive applications will use some amount of flash within a year, Fowler predicted. Databases like Oracle, MySQL and IBM DB2 are ideal candidates, he says."

Today, 99.99% of all Oracle data is stored on the ancient spinning platter disks that were introduced in the 1970's, but disk technology is rapidly being displaced with RAM-based SSD which is hundreds of time faster than magnetic-coated spinning platter disks.

In the 1980's, platter disks was very expensive, and a 1.2 gigabyte 3380 disk sold for more than $200k. Today, you can buy high-speed SSD for Oracle for under $1k per gigabyte. For terabyte sized Oracle shops, SSD is being selectively used for redo and other tablespaces where I/O is a bottleneck.

Following Sun's lead, several other major hardware providers are following suit and hundreds of hardware vendors will be providing SSD for use by Oracle systems.

Oracle benchmarks have shown that SSD can be up to 600 times faster for some operations, and SSD does not experience platter or controller latency, leading to far faster throughput.

As prices continue to fall, SSD will replace platter disks for all mission critical Oracle databases, and this will lead to some fundamental changes in Oracle administration, especially within the SGA. Because all data is stored on RAM, the data buffer cache size becomes far less important.

Read more about the huge benefits of Solid State Oracle here:

**************************************************************

2008 Survey of rack-mount SSD vendors:
http://oracle-tips.c.topica.com/maalUXYabH1w9bLGJrib/

SSD clobbers disk speed for Oracle:
http://oracle-tips.c.topica.com/maalUXYabH1xabLGJrib/

Using RAC with SSD:
http://oracle-tips.c.topica.com/maalUXYabH1xbbLGJrib/

Oracle SQL Loader

2008-06-17T10:31:00.000+07:00

Table Content :

The SQL*Loader Environment
A Short SQL*Loader Example
SQL*Loader's Capabilities
Issues when Loading Data
Invoking SQL*Loader
---------------------------------

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. That's it. That's the sole reason for SQL*Loader's existence.
The basis for almost everything you do with SQL*Loader is a file known as the control file. The SQL*Loader control file is a text file into which you place a description of the data to be loaded. You also use the control file to tell SQL*Loader which database tables and columns should receive the data that you are loading.
Do not confuse SQL*Loader control files with database control files. In a way, it's unfortunate that the same term is used in both cases. Database control files are binary files containing information about the physical structure of your database. They have nothing to do with SQL*Loader. SQL*Loader control files, on the other hand, are text files containing commands that control SQL*Loader's operation.
Once you have a data file to load and a control file describing the data contained in that data file, you are ready to begin the load process. You do this by invoking the SQL*Loader executable and pointing it to the control file that you have written. SQL*Loader reads the control file to get a description of the data to be loaded. Then it reads the input file and loads the input data into the database.
SQL*Loader is a very flexible utility, and this short description doesn't begin to do it justice. The rest of this chapter provides a more detailed description of the SQL*Loader environment and a summary of SQL*Loader's many capabilities.

The SQL*Loader Environment
When we speak of the SQL*Loader environment, we are referring to the database, the SQL*Loader executable, and all the different files that you need to be concerned with when using SQL*Loader.
The functions of the SQL*Loader executable, the database, and the input data file are rather obvious. The SQL*Loader executable does the work of reading the input file and loading the data. The input file contains the data to be loaded, and the database receives the data.
SQL*Loader is capable of loading from multiple files in one session. You'll read more about this in Chapter 2, The Mysterious Control File. When multiple input files are used, SQL*Loader will generate multiple bad files and discard files--one set for each input file.

The SQL*Loader Control File
The SQL*Loader control file is the key to any load process. The control file provides the following information to SQL*Loader:
The name and location of the input data file
The format of the records in the input data file
The name of the table or tables to be loaded
The correspondence between the fields in the input record and the columns in the database tables being loaded
Selection criteria defining which records from the input file contain data to be inserted into the destination database tables.
The names and locations of the bad file and the discard file
Some of the items shown in this list may also be passed to SQL*Loader as command-line parameters. The name and location of the input file, for example, may be passed on the command line instead of in the control file. The same goes for the names and locations of the bad files and the discard files.
It's also possible for the control file to contain the actual data to be loaded. This is sometimes done when small amounts of data need to be distributed to many sites, because it reduces (to just one file) the number of files that need to be passed around. If the data to be loaded is contained in the control file, then there is no need for a separate data file.

The Log File
The log file is a record of SQL*Loader's activities during a load session. It contains information such as the following:
The names of the control file, log file, bad file, discard file, and data file
The values of several command-line parameters
A detailed breakdown of the fields and datatypes in the data file that was loaded
Error messages for records that cause errors
Messages indicating when records have been discarded
A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load
Always review the log file after a load to be sure that no errors occurred, or at least that no unexpected errors occurred. This type of information is written to the log file, but is not displayed on the terminal screen.

The Bad File and the Discard File
Whenever you insert data into a database, you run the risk of that insert failing because of some type of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.
Discard files, on the other hand, are used to hold records that do not meet selection criteria specified in the SQL*Loader control file. By default, SQL*Loader will attempt to load all the records contained in the input file. You have the option, though, in your control file, of specifying selection criteria that a record must meet before it is loaded. Records that do not meet the specified criteria are not loaded, and are instead written to a file known as the discard file.
Discard files are optional. You will only get a discard file if you've specified a discard file name, and if at least one record is actually discarded during the load. Bad files are not optional. The only way to avoid having a bad file generated is to run a load that results in no errors. If even one error occurs, SQL*Loader will create a bad file and write the offending input record (or records) to that file.
The format of your bad files and discard files will exactly match the format of your input files. That's because SQL*Loader writes the exact records that cause errors, or that are discarded, to those files. If you are running a load with multiple input files, you will get a distinct set of bad files and discard files for each input file.
You'll read more about bad files and discard files, and how to use them, in Chapter 7, Validating and Selectively Loading Data.

A Short SQL*Loader Example
This section contains a short example showing how SQL*Loader is used. For this example, we'll be loading a file of geographic place names taken from the United States Geological Survey's (USGS) Geographic Name Information System (GNIS).
TIP:
Learn more about GNIS data or download it for yourself by visiting http://mapping.usgs.gov/www/gnis/. The specific data file used for this example is also available from http://www.oreilly.com/catalog/orsqlloader and http://gennick.com/sqlldr.

The Data
The particular file used for this example contains the feature name listing for the State of Michigan. It's a delimited text file containing the official names of the many thousands of lakes, streams, waterfalls, and other geographic features in the state. The following example shows three records from that file. The lines wrap on the printed page in this book, but in the file each name is on its own line:

"MI","Agate Falls","falls","Ontonagon","26","131","462851N","0890527W",
"46.48083","-89.09083","","","","","","","Trout Creek"

"MI","Agate Harbor","bay","Keweenaw","26","083","472815N","0880329W",
"47.47083","-88.05806","","","","","","","Delaware"

"MI","Agate Point","cape","Keweenaw","26","083","472820N","0880241W",
"47.47222","-88.04472","","","","","","","Delaware"

As you can see, the data in the file is comma-delimited, and each field is enclosed within double quotes. Table 1-1 shows the contents and maximum length of each field.

Table 1-1: Fields in the GNIS Feature Names File

Field Number Maximum Length Contents

1 2 Alphanumeric state code

2 60 Feature name

3 9 Feature type

4 35 County name

5 2 FIPS state code

6 3 FIPS county code

7 7 Primary latitude in degrees, minutes, and seconds

8 8 Primary longitude in degrees, minutes, and seconds

9 8 Primary latitude in decimal degrees

10 8 Primary longitude in decimal degrees

11 7 Source latitude in degrees, minutes, and seconds

12 8 Source longitude in degrees, minutes, and seconds

13 8 Source latitude in decimal degrees

14 8 Source longitude in decimal degrees

15 5 Elevation (feet above sea level)

16 10 Estimated population

17 30 The name of the USGS 7.5 minute series map on which the feature can be found

We used the following SQL statement to create the table into which all this data will be loaded:

CREATE TABLE gfn_gnis_feature_names (
gfn_state_abbr CHAR(2),
gfn_feature_name VARCHAR2(60),
gfn_feature_type VARCHAR2(9),
gfn_county_name VARCHAR2(35),
gfn_primary_latitude_dms CHAR(7),
gfn_primary_longitude_dms CHAR(8),
gfn_elevation NUMBER(7,2),
gfn_population NUMBER(10),
gfn_cell_name VARCHAR2(30)
) TABLESPACE gnis_data;

As you can see, not all fields in the data file are to be loaded into the table. The source latitude and longitude fields will not be loaded, nor will the decimal versions of the primary latitude and longitude. The FIPS coding will also be omitted.

The Control File
The following control file will be used to load the feature name data for the State of Michigan:

LOAD DATA
APPEND INTO TABLE gfn_gnis_feature_names
(
gfn_state_abbr CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_feature_name CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_feature_type CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_county_name CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_fips_state_code FILLER INTEGER EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_fips_county_code FILLER INTEGER EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_primary_latitude_dms CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_primary_longitude_dms CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_primary_latitude_dec FILLER DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_primary_longitude_dec FILLER DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_source_latitude_dms FILLER CHAR
TERMINATED BY "," ENCLOSED BY '"',
gfn_source_longitude_dms FILLER CHAR
TERMINATED BY "," ENCLOSED BY '"',
gfn_source_latitude_dec FILLER DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_source_longitude_dec FILLER DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_elevation DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_population INTEGER EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_cell_name CHAR TERMINATED BY "," ENCLOSED BY '"'
)

Some explanations are in order. The LOAD DATA command tells SQL*Loader that you are going to load data from an operating system file into a database table. Everything else that you see in this particular control file represents a clause of the LOAD DATA command.
The destination table is identified by the following INTO TABLE clause:

APPEND INTO TABLE gfn_gnis_feature_names
The APPEND keyword tells SQL*Loader to preserve any preexisting data in the table. Other options allow you to delete preexisting data, or to fail with an error if the table is not empty to begin with.
The field definitions are all contained within parentheses, and are separated from each other by commas. The fields in the data file are delimited by commas, and are also enclosed by double quotes. The following clause is used at the end of each field definition to pass this delimiter and enclosure information to SQL*Loader:

TERMINATED BY "," ENCLOSED BY '"'

The following three datatypes are used in this control file. They have no bearing on, or relationship to, the database datatypes of the columns being loaded. The purpose of the datatypes in the control file is to describe the data being loaded from the input data file:

CHAR
Tells SQL*Loader that a field is a text field.

INTEGER EXTERNAL
Tells SQL*Loader that a field is an integer represented using the text digits "0" through "9".

DECIMAL EXTERNAL
Tells SQL*Loader that a field is a decimal value represented using the text digits "0" through "9" and an optional decimal point (".").
Each field is given a name that is meaningful to SQL*Loader. For the nine fields being loaded into the table, the SQL*Loader name must match the corresponding column name in the table. The keyword FILLER identifies the eight fields that are not being loaded into the database. Their names do not matter, but the same naming convention has been followed as for all the rest of the fields.
TIP: FILLER fields are a new feature in Oracle8i. If you are using a release prior to the Oracle8i release, SQL*Loader will not recognize the FILLER keyword.

The Command Line
The command used to initiate this load needs to invoke SQL*Loader and point it to the control file describing the data. In this case, since the input file name is not provided in the control file, that name needs to be passed in on the command line as well. The following sqlldr command will do the job:
sqlldr gnis/gnis@donna control=gnis log=gnis_michigan data=mi_deci.
There are four parameters for this command:

gnis/gnis@donna
The first parameter consists of a username, password, and net service name. SQL*Loader uses this information to open a connection to the database. The "gnis" user owns the table to be loaded.

control = gnis
The second parameter tells SQL*Loader that the control file name is gnis.ctl. The default control file extension is .ctl, so the parameter needs to specify only the file name in this case.

log = gnis_michigan
The third parameter specifies a log file name of gnis_michigan.log. The default log file extension is .log, so it's not specified explicitly in the parameter setting.

data = mi_deci.
The fourth parameter specifies an input file name of mi_deci. This name ends with an explicit period, because the file name has no extension. Without the period on the end, SQL*Loader would assume the default extension of .dat.

By not including the input file name in the control file, but instead passing it as a command-line parameter, we've made it easy to use the same control file to load feature name data for all 50 states. All we need to do is change the value of the DATA and LOG parameters on the command line. Here's what it looks like to issue this sqlldr command and load the data:

$ sqlldr gnis/gnis@donna control=gnis log=gnis_michigan data=mi_deci.

SQL*Loader: Release 8.1.5.0.0 - Production on Wed Apr 5 13:35:53 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Commit point reached - logical record count 28
Commit point reached - logical record count 56
Commit point reached - logical record count 84
...
Commit point reached - logical record count 32001
Commit point reached - logical record count 32029
Commit point reached - logical record count 32056

Pretty much all you see on the screen when you run SQL*Loader are these "Commit point" messages. If nothing else, they provide some reassurance that the load is progressing, and that your session is not hung. All other information regarding the load is written to the log file.

The Log File
The log file resulting from the load of Michigan's feature name data begins with the SQL*Loader banner. It goes on to list the names of the files involved in the load, and also the values of some important command-line parameters. For example:

SQL*Loader: Release 8.1.5.0.0 - Production on Wed Apr 5 13:35:53 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Control File: gnis.ctl
Data File: mi_deci.
Bad File: mi_deci.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

You can see that the names of the control file, bad file, and data file are recorded in the log. This information is invaluable if you ever have problems with a load, or if you ever need to backtrack in order to understand what you really did. The log also displays the number of records to be loaded, the number to be skipped, the number of errors to allow before aborting the load, the size of the bind array, and the data path. The data path is an important piece of information. The load in this example is a conventional path load, which means that SQL*Loader loads the data into the database using INSERT statements. There is another type of load called a direct path load, which has the potential for far better performance than a conventional path load. Direct path loads are discussed in Chapter 10, Direct Path Loads.

The next part of the log file identifies the table being loaded, indicates whether or not preexisting data was preserved, and lists the field definitions from the control file:

Table GFN_GNIS_FEATURE_NAMES, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ------------
GFN_STATE_ABBR FIRST * , " CHARACTER
GFN_FEATURE_NAME NEXT * , " CHARACTER
GFN_FEATURE_TYPE NEXT * , " CHARACTER
GFN_COUNTY_NAME NEXT * , " CHARACTER
GFN_FIPS_STATE_CODE NEXT * , " CHARACTER
(FILLER FIELD)
GFN_FIPS_COUNTY_CODE NEXT * , " CHARACTER
(FILLER FIELD)
GFN_PRIMARY_LATITUDE_DMS NEXT * , " CHARACTER
GFN_PRIMARY_LONGITUDE_DMS NEXT * , " CHARACTER
GFN_PRIMARY_LATITUDE_DEC NEXT * , " CHARACTER
(FILLER FIELD)
GFN_PRIMARY_LONGITUDE_DEC NEXT * , " CHARACTER
(FILLER FIELD)
GFN_SOURCE_LATITUDE_DMS NEXT * , " CHARACTER
(FILLER FIELD)
GFN_SOURCE_LONGITUDE_DMS NEXT * , " CHARACTER
(FILLER FIELD)
GFN_SOURCE_LATITUDE_DEC NEXT * , " CHARACTER
(FILLER FIELD)
GFN_SOURCE_LONGITUDE_DEC NEXT * , " CHARACTER
(FILLER FIELD)
GFN_ELEVATION NEXT * , " CHARACTER
GFN_POPULATION NEXT * , " CHARACTER
GFN_CELL_NAME NEXT * , " CHARACTER

The last part of the log file contains summary information about the load. If there were any errors, or any discarded records, you would see messages for those before the summary. The summary tells you how many rows were loaded, how many had errors, how many were discarded, and so forth. It looks like this:

Table GFN_GNIS_FEATURE_NAMES:
32056 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 65016 bytes(28 rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 32056
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Wed Apr 05 13:35:53 2000
Run ended on Wed Apr 05 13:36:34 2000

Elapsed time was: 00:00:41.22
CPU time was: 00:00:03.81

You can see from this summary that 32,056 feature names were loaded into the gfn_gnis_feature_names table for the state of Michigan. There were no errors, and no records were discarded. The elapsed time for the load was a bit over 41 seconds.

SQL*Loader's Capabilities
SQL*Loader is very flexible, and the example in the previous section shows only a small amount of what can be done using the utility. Here are the major SQL*Loader capabilities that you should be aware of:

SQL*Loader can read from multiple input files in a single load session.

SQL*Loader can handle files with fixed-length records, variable-length records, and stream-oriented data.

SQL*Loader supports a number of different datatypes, including text, numeric, zoned decimal, packed decimal, and various machine-specific binary types.

Not only can SQL*Loader read from multiple input files, but it can load that data into several different database tables, all in the same load session.

SQL*Loader allows you to use Oracle's built-in SQL functions to manipulate the data being read from the input file.

SQL*Loader includes functionality for dealing with whitespace, delimiters, and null data.

In addition to standard relational tables, SQL*Loader can load data into object tables, varying arrays (VARRAYs), and nested tables.

SQL*Loader can load data into large object (LOB) columns.

SQL*Loader can handle character set translation between the input data file and the database.

The capabilities in this list describe the types of data that SQL*Loader can handle, and what SQL*Loader can do to with that data. SQL*Loader also implements some strong, performance-related features. SQL*Loader can do direct path loads, which bypass normal SQL statement processing, and which may yield handsome performance benefits. SQL*Loader can also do parallel loads and even direct-path parallel loads; direct path parallel loads allow you to maximize throughput on multiple CPU systems. You'll read more about these performance-related features in Chapter 9, Transaction Size and Performance Issues, and in Chapter 10.

Issues when Loading Data
There are a number of issues that you need to be concerned about whenever you use SQL*Loader to load data into your database--indeed, you need to be concerned about these whenever you load data, period. First, there's the ever-present possibility that the load will fail in some way before it is complete. If that happens, you'll be left with some data loaded, and some data not loaded, and you'll need a way to back out and try again. Other SQL*Loader issues include transaction size, data validation (including referential integrity), and data transformation. Transaction size is partly a performance issue, but it also has an impact on how much data you need to reload in the event that a load fails. Data validation and referential integrity both relate to the need for clean, reliable data.

Recovery from Failure
There are really only two fundamental ways that you can recover from a failed load. One approach is to delete all the data that was loaded before the failure occurred, and simply start over again. Of course, you need to fix whatever caused the failure to occur before you restart the load. The other approach is to determine how many records were loaded successfully, and to restart the load from that point forward. Regardless of which method you choose, you need to think things through before you start a load.

Deleting data and restarting a load from scratch really doesn't require any special functionality on the part of SQL*Loader. The important thing is that you have a reliable way to identify the data that needs to be deleted. SQL*Loader does, however, provide support for continuing an interrupted load from the point where a failure occurred. Using the SKIP command-line parameter, or the SKIP clause in the control file, you can tell SQL*Loader to skip over records that were already processed in order to have the load pick up from where it left off previously. Chapter 6, Recovering from Failure, describes the process for continuing a load in detail, and some of the issues you'll encounter. It's a chapter worth reading, because there are some caveats and gotchas, and you'll want to learn about those before you have a failure, not afterwards.

Transaction Size
Transaction size is an issue related somewhat to performance, and somewhat to recovery from failure. In a conventional load, SQL*Loader allows you to specify the number of rows that will be loaded between commits. The number of rows that you specify has a direct impact on the size of the bind array that SQL*Loader uses, and consequently on the amount of memory required for the load. The bind array is an area in memory where SQL*Loader stores data for rows to be inserted into the database. When the bind array fills, SQL*Loader inserts the data into the table being loaded, and then executes a COMMIT.

The larger the transaction size, the more data you'll need to reprocess if you have to restart the load after a failure. However, that's usually not a significant issue unless your bind array size is quite large. Transaction size can also affect performance. Generally, the more data loaded in one chunk the better. So a larger bind array size typically will lead to better performance. However, it will also lead to fewer commits, resulting in the use of more rollback segment space. Chapter 9 describes these issues in detail.

Data Validation
Data validation is always a concern when loading data. SQL*Loader doesn't provide a lot of support in this area, but there are some features at your disposal that can help you ensure that only good data is loaded into your database.

The one thing that SQL*Loader does do for you is ensure that the data being loaded into a column is valid given the column's datatype. Text data will not be loaded into NUMBER fields, and numbers will not be loaded into DATE fields. This much, at least, you can count on. Records containing data that doesn't convert to the destination datatype are rejected and written to the bad file.

SQL*Loader allows you to selectively load data. Using the WHEN clause in your SQL*Loader control file, you can specify conditions under which a record will be accepted. Records not meeting those conditions are not loaded, and are instead written to the discard file.

Finally, you can take advantage of the referential integrity features built into your database. SQL*Loader won't be able to load data that violates any type of primary key, unique key, foreign key, or check constraint. Chapter 7, Validating and Selectively Loading Data, discusses using SQL*Loader and Oracle features to ensure that only good data gets loaded.

TIP: You don't always have to rely on SQL*Loader's features for data validation. It's entirely feasible to load data into a staging table, run one or more external programs to weed out any rows that are invalid, and then transfer that data to a production table.

Data Transformation
Wouldn't it be great if the data we loaded was always in a convenient format? Unfortunately, it frequently is not. In the real world, you may deal with data from a variety of sources and systems, and the format of that data may not match the format that you are using in your database. Dates, for example, are represented using a wide variety of formats. The date 1/2/2000 means one thing in the United States and quite another in Europe.

For dates and numbers, you can often use Oracle's built-in TO_DATE and TO_NUMBER functions to convert a character-based representation to a value that can be loaded into a database DATE or NUMBER column. In fact, for date fields, you can embed the date format into your control file as part of the field definition.

SQL*Loader allows you access to Oracle's entire library of built-in SQL functions. You aren't limited to just TO_DATE, TO_NUMBER, and TO_CHAR. Not only can you access all the built-in SQL functions, you can also write PL/SQL code to manipulate the data being loaded. This opens up a world of possibilities, which are discussed in Chapter 8, Transforming Data During a Load.

Invoking SQL*Loader
On Unix systems, the command used to invoke SQL*Loader is sqlldr. On Windows systems running Oracle8i, release 8.1 or higher, the command is also sqlldr. Prior to release 8.1, the SQL*Loader command on Windows systems included the first two digits of the Oracle release number. Thus you had sqlldr80 (Oracle8, release 8.0), sqlldr73 (Oracle7, release 7.3), and so forth.

SQL*Loader can be invoked in one of three ways:

sqlldr

sqlldr keyword=value [keyword=value ...]

sqlldr value [value ...]

Issuing the sqlldr command by itself results in a list of valid command-line parameters being displayed. Command-line parameters are usually keyword/value pairs, and may be any combination of the following:

USERID={username[/password][@net_service_name]|/}
CONTROL=control_file_name
LOG=path_file_name
BAD=path_file_name
DATA=path_file_name
DISCARD=path_file_name
DISCARDMAX=logical_record_count
SKIP=logical_record_count
SKIP_INDEX_MAINTENANCE={TRUE | FALSE}
SKIP_UNUSABLE_INDEXES={TRUE | FALSE}
LOAD=logical_record_count
ERRORS=insert_error_count
ROWS=rows_in_bind_array
BINDSIZE=bytes_in_bind_array
SILENT=[(]keyword[,keyword...][)]
DIRECT={TRUE | FALSE}
PARFILE=path_file_name
PARALLEL={TRUE | FALSE}
READSIZE=bytes_in_read_buffer
FILE=database_datafile_name

Command-line parameters may be passed by position instead of by keyword. The rules for doing this are described at the end of the next section.

Command-Line Parameters
The SQL*Loader parameter descriptions are as follows:

USERID = {username[/password] [@net_service_name]|/}
Specifies the username and password to use when connecting to the database. The net_service_name parameter optionally allows you to connect to a remote database. Use a forward-slash character ( / ) to connect to a local database using operating system authentication. On Unix systems, you may want to omit the password and allow SQL*Loader to prompt you for it. If you omit both the username and the password, SQL*Loader will prompt you for both.

TIP: On Unix systems you should generally avoid placing a password on the command line, because that password will be displayed whenever other users issue a command, such as ps -ef, that displays a list of current processes running on the system. Either let SQL*Loader prompt you for your password, or use operating system authentication. (If you don't know what operating system authentication is, ask your DBA.)

CONTROL = control_ file_name
Specifies the name, which may include the path, of the control file. The default extension is .ctl.

LOG = path_ file_name
Specifies the name of the log file to generate for a load session. You may include a path as well. By default, the log file takes on the name of the control file, but with a .log extension, and is written to the same directory as the control file. If you specify a different name, the default extension is still .log. However, if you use the LOG parameter to specify a name for the log file, it will no longer be written automatically to the directory that contains the control file.

BAD = path_ file_name
Specifies the name of the bad file. You may include a path as part of the name. By default, the bad file takes the name of the control file, but with a .bad extension, and is written to the same directory as the control file. If you specify a different name, the default extension is still .bad. However, if you use the BAD parameter to specify a bad file name, the default directory becomes your current working directory. If you are loading data from multiple files, then this bad file name only gets associated with the first file being loaded.

DATA = path_ file_name
Specifies the name of the file containing the data to load. You may include a path as part of the name. By default, the name of the control file is used, but with the .dat extension. If you specify a different name, the default extension is still .dat. If you are loading from multiple files, you can only specify the first file name using this parameter. Place the names of the other files in their respective INFILE clauses in the control file.

DISCARD = path_ file_name
Specifies the name of the discard file. You may include a path as part of the name. By default, the discard file takes the name of the control file, but it has a .dis extension. If you specify a different name, the default extension is still .dis. If you are loading data from multiple files, then this discard file name only gets associated with the first file being loaded.

DISCARDMAX = logical_record_count
Sets an upper limit on the number of logical records that can be discarded before a load will terminate. The limit is actually one less than the value specified for DISCARDMAX. When the number of discarded records becomes equal to the value specified for DISCARDMAX, the load will terminate. The default is to allow an unlimited number of discards. However, since DISCARDMAX only accepts numeric values, it is not possible to explicitly specify the default behavior.

TIP: There is also an undocumented parameter named DISCARDS that functions the same as DISCARDMAX. The use of DISCARDMAX is preferred, but you may occasionally encounter references to DISCARDS.

SKIP = logical_record_count
Allows you to continue an interrupted load by skipping the specified number of logical records. If you are continuing a multiple table direct path load, you may need to use the CONTINUE_LOAD clause in the control file rather than the SKIP parameter on the command line. CONTINUE_LOAD allows you to specify a different number of rows to skip for each table that you are loading.

SKIP_INDEX_MAINTENANCE = {TRUE | FALSE}
Controls whether or not index maintenance is done for a direct path load. This parameter does not apply to conventional path loads. A value of TRUE causes index maintenance to be skipped. Any index segments (partitions) that should have been updated will be marked as unusable. A value of FALSE causes indexes to be maintained as they normally would be. The default is FALSE.

SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
Controls the manner in which a load is done when a table being loaded has indexes in an unusable state. A value of TRUE causes SQL*Loader to load data into tables even when those tables have indexes marked as unusable. The indexes will remain unusable at the end of the load. One caveat is that if a UNIQUE index is marked as unusable, the load will not be allowed to proceed.

A value of FALSE causes SQL*Loader not to insert records when those records need to be recorded in an index marked as unusable. For a conventional path load, this means that any records that require an unusable index to be updated will be rejected as errors. For a direct path load, this means that the load will be aborted the first time such a record is encountered. The default is FALSE.

LOAD = logical_record_count
Specifies a limit on the number of logical records to load. The default is to load all records. Since LOAD only accepts numeric values, it is not possible to explicitly specify the default behavior.

ERRORS = insert_error_count
Specifies a limit on the number of errors to tolerate before the load is aborted. The default is to abort a load when the error count exceeds 50. There is no way to allow an unlimited number of errors. The best you can do is to specify a very high number for this parameter.

ROWS = rows_in_bind_array
The precise meaning of this parameter depends on whether you are doing a direct path load or a conventional load. If you are doing a conventional load, then you can use this parameter to control the number of rows in the bind array. This represents the number of rows that SQL*Loader loads with each INSERT statement, and also represents the commit frequency. The default is 64 rows.

If you are doing a direct path load, then ROWS specifies the number of rows to read from the input file before saving the data to the database. SQL*Loader will round up the ROWS value to coincide with an even number of database blocks. A data save in a direct path load is analogous to a commit in a conventional path load. The default, when a direct path load is done, is to do one save at the end of the load.

TIP: The BINDSIZE and ROWS parameters both affect the size of the bind array. Chapter 9 discusses this topic in greater detail.

BINDSIZE = bytes_in_bind_array
Specifies the maximum size, in bytes, of the bind array. This parameter overrides any bind array size computed as a result of using the ROWS parameter. The default bind array size is 65,536 bytes, or 64K.

SILENT = [( ]keyword [,keyword... ] [ )]
Allows you to suppress various header and feedback messages that SQL*Loader normally displays during a load session. Table 1-2 describes the effect of each of the keywords.
Table 1-2: Keywords for Use with the SILENT Parameter Keyword
Effect

ALL
Is the same as specifying all the other keywords.

DISCARDS
Suppresses the message that is normally written to the log file each time a record is discarded.

ERRORS
Suppresses the error messages that are normally written to the log file when a record generates an Oracle error.

FEEDBACK
Suppresses the "commit point reached" messages that are normally
displayed each time SQL*Loader executes a commit or a save.

HEADER
Suppresses the messages that SQL*Loader displays on the screen when you first launch the executable. Note, however, that the header
messages are always written to the log file.

PARTITIONS
Suppresses the per-partition statistics that are normally written to the log file when doing a direct path load of a partitioned table.

There are two ways you can specify values for the SILENT parameter. If you have only one keyword, you can supply it following the equals sign (=), as follows:
SILENT = ALL

If you have several keywords to use, you can place them in a comma-delimited list. You may optionally place that list inside parentheses. For example:
SILENT = (DISCARDS,ERRORS)

DIRECT = {TRUE | FALSE}
Determines the data path used for the load. A value of FALSE results in a conventional path load. A value of TRUE results in a direct path load. The default is FALSE.

PARFILE = path_ file_name
Tells SQL*Loader to read command-line parameter values from a text file. This text file is referred to as a parameter file, and contains keyword/value pairs. Usually, the keyword/value pairs are separated by line breaks. Use of the PARFILE parameter can save a lot of typing if you need to perform the same load several times, because you won't need to retype all the command-line parameters each time. There is no default extension for parameter files.

PARALLEL = {TRUE | FALSE}
Indicates whether or not you are doing a direct path parallel load. If you are loading the same object from multiple direct path load sessions, then set this to TRUE. Otherwise, set it to FALSE. The default is FALSE.

READSIZE = bytes_in_read_buffer
Specifies the size of the buffer used by SQL*Loader when reading data from the input file. The default value is 65,536 bytes, or 64K. The values of the READSIZE and BINDSIZE parameters should match. If you supply values for these two parameters that do not match, SQL*Loader will adjust them.

FILE = database_datafile_name
Specifies the database data file from which to allocate extents. Use this parameter when doing parallel loads, to ensure that each load session is using a different disk. If you are not doing a direct path load, this parameter will be ignored.

In addition to being passed by keyword, parameters may also be passed by position. To do this, you simply list the values after the sqlldr command in the correct order. For example, the following two SQL*Loader commands yield identical results:

sqlldr system/manager profile.ctl profile.log
sqlldr userid=system/manager control=profile.ctl log=profile.log

You can even mix the positional and keyword methods of passing command-line parameters. The one rule when doing this is that all positional parameters must come first. Once you start using keywords, you must continue to do so. For example:

sqlldr system/manager control=profile.ctl log=profile.ctl

When you pass parameters positionally, you must not skip any. Also, be sure to get the order right. You must supply parameter values in the order shown earlier in this section. Given the fact that you typically will use only a few parameters out of the many that are possible, it's usually easier to pass those parameters as keyword/value pairs than it is to pass them positionally. Using keyword/value pairs also makes long SQL*Loader commands somewhat self-documenting. The one exception to this rule is that you might wish to pass the username and password positionally, since they come first, and then pass in the rest of the parameters by name.

Command-Line Syntax Rules
There are several syntax rules to be aware of when writing SQL*Loader commands. These rules fall into the following areas:

Case-sensitivity

Separation of parameters
Special characters in the command line
SQL*Loader itself is not case-sensitive. Keywords on the command line may be in either upper- or lowercase--it doesn't matter. However, some operating systems, notably Unix, are case-sensitive. When running SQL*Loader on a case-sensitive operating system, you do need to be careful of the case used in file names. You also need to pay attention to the case used for the command to invoke SQL*Loader. On Unix and other case-sensitive operating systems, the SQL*Loader executable name is usually lowercase. So on Unix, Linux, and so forth, use sqlldr. Under Windows, and other operating systems where case doesn't matter, you can use SQLLDR or sqlldr as you prefer.
Parameters on the command line may be separated by spaces, by commas, or by both spaces and commas. All three of the following commands for example, are legitimate:

sqlldr system/manager,control=product.ctl,log=product.log
sqlldr system/manager, control=product.ctl, log=product.log
sqlldr system/manager control=product.ctl log=product.log

Spaces are acceptable as well, on either side of the equals sign (=), in keyword/value pairs.
Special characters are rarely needed on the command line, but when you do use them in an option value, you must enclose that value within quotes. For example, beginning with release 8.1.7, if you connect as the user SYS, you also need to specify "AS SYSDBA" as part of your connect string. Because of the spaces, you'll need to enclose your entire connect string within quotes. For example, for Windows:
sqlldr 'sys/password AS SYSDBA' control=product.ctl (Windows)
And for Unix:
sqlldr \'sys/password AS SYSDBA\' control=product.ctl (Unix)
The backslash characters that you see in the second command are there because some Unix operating systems require that quotes on the command line be escaped. In this example, the backslash was used as the escape character.

Parameter Precedence
The term "command-line" notwithstanding, most SQL*Loader command-line parameters can actually be specified in three different places:

On the command line
In a parameter file, which is then referenced using the PARFILE parameter

In the control file
Parameters on the command line, including those read in from a parameter file, will always override values specified in the control file. In the case of the bad and discard file names, though, the control file syntax allows for each distinct input file to have its own bad file and discard files. The command line syntax does not allow for this, so bad file and discard file names specified on the command line only apply to the first input file. For any other input files, you need to specify these bad and discard file names in the control file or accept the defaults.
The FILE parameter adds a bit of confusion to the rules stated in the previous paragraph. As with the bad file and discard file names, you can have multiple FILE values in the control file. However, when you specify the FILE parameter on the command line, it does override any and all FILE values specified in the control file.
Parameters read from a parameter file as a result of using the PARFILE parameter may override those specified on the command line. Whether or not that happens depends on the position of the PARFILE parameter with respect to the others. SQL*Loader processes parameters from left to right, and the last setting for a given parameter is the one that SQL*Loader uses.

Calling Oracle Reports from Oracle 10g Forms

2008-06-17T10:29:00.000+07:00

Calling Oracle Reports from Oracle 10g Forms

Introduction
Oracle Reports can add some great reporting functionality to your 10g Forms applications making data available in a wide variety of output formats such as XML and PDF. In this document I will explain how to configure 10g Oracle Reports on your PC, how to call Oracle Reports from 10g Forms applications, and application server deployment considerations. This information should advance your usage of Oracle Reports and speed adaptation in your applications.

Configuring the OC4J Runtime Environment for Oracle Reports
Assuming that Oracle Reports 10g is installed on your PC, the following steps will ensure that you are able to successfully use Oracle Reports in your OC4J runtime environment.

Defining the Report Server
The first step is to define a report server. This server will run in your OC4J runtime environment handling all Oracle Report execution requests. You will not be able to run reports in a 10g application unless you have defined and started a Report server. To define a report server do the following:

1. Start your OC4J runtime environment
2. Open a Command window (Start Menu, Run option, type in CMD)
3. Type this command and press enter: rwserver server=rep_fsprod
a. It is important to name the Reports server rep_fsprod. This is the name of the Report server on all of the Application server machines and will greatly simply your deployment if you keep the names synchronized.

Congratulations you just defined a report server! You should now see a dialog box indicating the Report Server is initializing.

Configuring the Report Server
The second step is to configure the Report Server created in step 1. Specifically you need to specify the Source Directory of the report files as a search path for the Report Server to use when individual report requests are made. Here is what you need to do:

1. Open Windows Explorer and navigate to c:\\reports\conf
2. Open rep_fsprod.conf using a text editor.
3. Using the Find or Search tool (depending on the editor used), look for sourceDir
a. You do not need to do a case sensitive search.
b. This variable is typically located near the top of the file. In mine, it is the 14th line of the file.
4. Edit the sourceDir variable to include the path of each directory where you wish the Report server to search for reports. Below is an example with two directory paths specified. One is required, but you can have as many as you need.
(property name="sourceDir" value="c:\env\aap\reports;c:\env\eu_asc\reports;)

5. Save and close the file.

Modifying the Application .env file
The third and final step is modifying each application .env file including a variable specifying the reports path. Here is what you need to do:

1. Open Windows Explorer and navigate to c:\\forms90\server
2. Open one of the application.env files that you have created using a text editor.
3. Add the REPORTS_PATH variable, setting its' value to the location of that applications report directory as shown in the example below:
REPORTS_PATH=c:\env\aap\reports
4. Save and close the file.
5. Repeat for each remaining application.env file.

Calling Reports from 10g Forms
Now that the OC4J environment is configured, we now can focus on programming a solution to call Oracle Reports from Oracle Forms. I'll walk you through the process using a reporting solution that I have used in 5 applications deployed at the Forest Service.
In the applications I have developed, end users typically call reports by selecting a report from a drop list.
A report specific form is display which allows report parameters to be specified.
When the end user has entered the desired parameter values a button is pressed initiating the report.
The WHEN-BUTTON-PRESSED trigger calls a program unit to gather the report parameters and
build the string that is eventually executed by the WEB.SHOW_DOCUMENT command below:

DECLARE
v_host varchar2(100);
v_port varchar2(10);
v_parameter_string varchar2(4000);

v_username varchar2(50);
v_password varchar2(50);
v_database varchar2(50);

BEGIN

-- Get the full domain name of the server from the formsweb.cfg
tool_env.getvar('SERVER_URL',v_host);

-- Get the username/password and database from the .env file
tool_env.getvar('APPLICATION_USERNAME',v_username);
tool_env.getvar('APPLICATION_PASSWORD',v_password);
tool_env.getvar('APPLICATION_DATABASE',v_database);

-- If it is localhost then we are running on windows and need to use the windows port, otherwise we
-- are on AIX and need to use the AIX port
IF v_host = 'localhost' THEN
v_port := '8889';
ELSE
v_port := '7778';
END IF;

-- Now build the parameter string for running the report
v_parameter_string := 'server=rep_fsprod';
v_parameter_string := v_parameter_string||'&report=AAP_TOTAL_CONTRACT_VALUE_RPT.rdf';
v_parameter_string := v_parameter_string||'&userid='||v_username||'/'||v_password||'@'||v_database;
v_parameter_string := v_parameter_string||'&execution_mode=batch';
v_parameter_string := v_parameter_string||'&comm_mode=synchronous';
v_parameter_string := v_parameter_string||'&destype=cache';
v_parameter_string := v_parameter_string||'&desformat=pdf';
v_parameter_string := v_parameter_string||'&p_region='||:rpt_parms.region;
v_parameter_string := v_parameter_string||'&p_province='||:rpt_parms.province;
v_parameter_string := v_parameter_string||'&p_forest='||:rpt_parms.forest;
v_parameter_string := v_parameter_string||'&p_district='||:rpt_parms.district;
v_parameter_string := v_parameter_string||'&p_fiscal_year='||:rpt_parms.fiscal_year;

-- Now run the report
web.show_document('http://'||v_host||':'||v_port||'/reports/rwservlet?'||v_parameter_string,'_blank');

END;

There are a couple of important items to note when reviewing this program unit:

1. Important data elements are not hard coded! Instead, they are stored in the application.env file and referenced at runtime using tool_env.get_var. The username, password, database and server are all referenced this way.
2. The program unit supports execution on Windows (my development platform) and AIX (the deployment platform) without requiring any code modifications.
a. By referencing the SERVER_URL environment variable in the application.env file, I am able to specify the server and port dynamically.
3. When building the string of parameters to be passed you need to:
a. Include the report name as a parameter
b. Prepend an '&' to second parameter and any additional parameters there after.
c. Except for the report name, be sure that all additional parameters specified match a parameter name (system or user) in the Report definition.
4. Mandatory parameters for each report include:
a. Server, report, userid, execution_mode, comm_mode, destype and desformat.
i. Report is the only parameter that will change. The others (likely) will remain constant for all reports in your application.

When the WEB.SHOW_DOCUMENT command is issued from the Form, a new browser window is opened and the request is executed by the Report Server.

Application Server Deployment
When you have completed development in OC4J and are ready to deploy to the Application Server there is a short list of activities that need to be completed to insure success.

First, it is important to note that the Oracle reports in your application need to be deployed in the /reports subdirectory such as /fsapps/fsprod/leimars/reports.
Be sure to have your application.env file modified accordingly by having the REPORTS_PATH variable specify the proper location.

The second and most important item to note is that the rep_fsprod.conf file will not be modified to include each applications /reports directory. Instead the reports in each applications /reports directory are linked to /fsapps/fsprod/fs_reports by an eDBA using a standard utility that has been distributed on each application server. Each time the script is run on a particular /reports directory, symbolic links are created for each report. If you add a new report, you need to have the links created for your application before it will be available.

The third and final thing to note is that Report Definitions do not need to be compiled when you deploy to the application server. Simply move the .rdf file to you application /reports directory, have the links recreated and you are good to go.

Conclusion
You now have the information you need to include Oracle Reports in your 10g Forms applications.
It is a fairly painless process to complete if your application calls for this functionality.
I hope that you found the contents of this paper useful and wish you good luck.

2008-06-17T10:28:00.000+07:00

The SQL Optimizers

Whenever you execute a SQL statement, a component of the database known as the optimizer must decide how best to access the data operated on by that statement.
Oracle supports two optimizers: the rule-base optimizer (which was the original), and the cost-based optimizer.

To figure out the optimal execution path for a statement, the optimizers consider the following:
a. The syntax you've specified for the statement
b. Any conditions that the data must satisfy (the WHERE clauses)
c. The database tables your statement will need to access
d. All possible indexes that can be used in retrieving data from the table
e. The Oracle RDBMS version
f. The current optimizer mode
g. SQL statement hints
h. All available object statistics (generated via the ANALYZE command)
i. The physical table location (distributed SQL)
j. INIT.ORA settings (parallel query, async I/O, etc.)

Oracle gives you a choice of two optimizing alternatives:
the predictable rule-based optimizer and the more intelligent cost-based optimizer.

Understanding the Rule-Based Optimizer

The rule-based optimizer (RBO) uses a predefined set of precedence rules to figure out which path it will use to access the database.
The RDBMS kernel defaults to the rule-based optimizer under a number of conditions, including:

a. OPTIMIZER_MODE = RULE is specified in your INIT.ORA file
b. OPTIMIZER_MODE = CHOOSE is specified in your INIT.ORA file, and no statistics exist for any table involved in the statement
c. An ALTER SESSION SET OPTIMIZER_MODE = RULE command has been issued
d. An ALTER SESSION SET OPTIMIZER_MODE = CHOOSE command has been issued, and no statistics exist for any table involved in the statement
e. The rule hint (e.g., SELECT /*+ RULE */. . .) has been used in the statement

The rule-based optimizer is driven primarily by 20 condition rankings, or "golden rules."
These rules instruct the optimizer how to determine the execution path for a statement, when to choose one index over another,
and when to perform a full table scan.
These rules, shown in Table 1, are fixed, predetermined, and, in contrast with the cost-based optimizer, not influenced by outside sources (table volumes, index distributions, etc.).

Table 1: Rule-based optimizer condition rankings

Rank Condition

1 ROWID = constant
2 Cluster join with unique or primary key = constant
3 Hash cluster key with unique or primary key = constant
4 Entire Unique concatenated index = constant
5 Unique indexed column = constant
6 Entire cluster key = corresponding cluster key of another table in the same cluster
7 Hash cluster key = constant
8 Entire cluster key = constant
9 Entire non-UNIQUE CONCATENATED index = constant
10 Non-UNIQUE index merge
11 Entire concatenated index = lower bound
12 Most leading column(s) of concatenated index = constant
13 Indexed column between low value and high value or indexed column LIKE "ABC%" (bounded range)
14 Non-UNIQUE indexed column between low value and high value or indexed column like `ABC%' (bounded range)
15 UNIQUE indexed column or constant (unbounded range)
16 Non-UNIQUE indexed column or constant (unbounded range)
17 Equality on non-indexed = column or constant (sort/merge join)
18 MAX or MIN of single indexed columns
19 ORDER BY entire index
20 Full table scans

While knowing the rules is helpful, they alone do not tell you enough about how to tune for the rule-based optimizer. To overcome this deficiency, the following sections provide some information that the rules don't tell you.

What the RBO rules don't tell you #1
Only single column indexes are ever merged. Consider the following SQL and indexes:

SELECT col1, ...
FROM emp
WHERE emp_name = 'GURRY'
AND emp_no = 127
AND dept_no = 12

Index1 (dept_no)
Index2 (emp_no, emp_name)

The SELECT statement looks at all three indexed columns. Many people believe that Oracle will merge the two indexes, which involve those three columns, to return the requested data. In fact, only the two-column index is used; the single-column index is not used. While Oracle will merge two single-column indexes, it will not merge a multi-column index with another index.
There is one thing to be aware of with respect to this scenario. If the single-column index is a unique or primary key index, that would cause the single-column index to take precedence over the multi-column index. Compare rank 4 with rank 9 in Table 1.
NOTE: Oracle8i introduced a new hint, INDEX_JOIN, that allows you to join multi-column indexes.

What the RBO rules don't tell you #2
If all columns in an index are specified in the WHERE clause, that index will be used in preference to other indexes for which some columns are referenced. For example:

SELECT col1, ...
FROM emp
WHERE emp_name = 'GURRY'
AND emp_no = 127
AND dept_no = 12

Index1 (emp_name)
Index2 (emp_no, dept_no, cost_center)

In this example, only Index1 is used, because the WHERE clause includes all columns for that index, but does not include all columns for Index2.

What the RBO rules don't tell you #3
If multiple indexes can be applied to a WHERE clause, and they all have an equal number of columns specified, only the index created last will be used. For example:

SELECT col1, ...
FROM emp
WHERE emp_name = 'GURRY'
AND emp_no = 127
AND dept_no = 12
AND emp_category = 'CLERK'

Index1 (emp_name, emp_category) Created 4pm Feb 11th 2002
Index2 (emp_no, dept_no) Created 5pm Feb 11th 2002

In this example, only Index2 is used, because it was created at 5 p.m. and the other index was created at 4 p.m. This behavior can pose a problem, because if you rebuild indexes in a different order than they were first created, a different index may suddenly be used for your queries. To deal with this problem, many sites have a naming standard requiring that indexes are named in alphabetical order as they are created. Then, if a table is rebuilt, the indexes can be rebuilt in alphabetical order, preserving the correct creation order. You could, for example, number your indexes. Each new index added to a table would then be given the next number.

What the RBO rules don't tell you #4
If multiple columns of an index are being accessed with an = operator, that will override other operators such as LIKE or BETWEEN. Two ='s will override two ='s and a LIKE. For example:

SELECT col1, ...
FROM emp
WHERE emp_name LIKE 'GUR%'
AND emp_no = 127
AND dept_no = 12
AND emp_category = 'CLERK'
AND emp_class = 'C1'

Index1 (emp_category, emp_class, emp_name)
Index2 (emp_no, dept_no)

In this example, only Index2 is utilized despite Index1 having three columns accessed and Index2 having only two column accessed.

What the RBO rules don't tell you #5
A higher percentage of columns accessed will override a lower percentage of columns accessed. So generally, the optimizer will choose to use the index from which you specify the highest percentage of columns. However, as stated previously, all columns specified in a unique or primary key index will override the use of all other indexes. For example:

SELECT col1, ...
FROM emp
WHERE emp_name = 'GURRY'
AND emp_no = 127
AND emp_class = 'C1'

Index1 (emp_name, emp_class, emp_category)
Index2 (emp_no, dept_no)

In this example, only Index1 is utilized, because 66% of the columns are accessed. Index2 is not used because a lesser 50% of the indexed columns are used.

What the RBO rules don't tell you #6
If you join two tables, the rule-based optimizer needs to select a driving table. The table selected can have a significant impact on performance, particularly when the optimizer decides to use nested loops. A row will be returned from the driving table, and then the matching rows selected from the other table. It is important that as few rows as possible are selected from the driving table.

The rule-based optimizer uses the following rules to select the driving table:

1. A unique or primary key index will always cause the associated table to be selected as the driving table in front of a non-unique or non-primary key index.
2. An index for which you apply the equality operator (=) to all columns will take precedence over indexes from which you use only some columns, and will result in the underlying table being chosen as the driving table for the query.
3. The table that has a higher percentage of columns in an index will override the table that has a lesser percentage of columns indexed.
4. A table that satisfies one two-column index in the WHERE clause of a query will be chosen as the driving table in front of a table that satisfies two single-column indexes.
5. If two tables have the same number of index columns satisfied, the table that is listed last in the FROM clause will be the driving table. In the SQL below, the EMP table will be the driving table because it is listed last in the FROM clause.

SELECT ....
FROM DEPT d, EMP e
WHERE e.emp_name = 'GURRY'
AND d.dept_name = 'FINANCE'
AND d.dept_no = e.dept_no

What the RBO rules don't tell you #7
If a WHERE clause has a column that is the leading column on any index, the rule-based optimizer will use that index. The exception is if a function is placed on the leading index column in the WHERE clause. For example:

SELECT col1, ...
FROM emp
WHERE emp_name = 'GURRY'

Index1 (emp_name, emp_class, emp_category)
Index2 (emp_class, emp_name, emp_category)

Index1 will be used, because emp_name (used in the WHERE clause) is the leading column. Index2 will not be used, because emp_name is not the leading column.

The following example illustrates what happens when a function is applied to an indexed column:

SELECT col1, ...
FROM emp
WHERE LTRIM(emp_name) = 'GURRY'

In this case, because the LTRIM function has been applied to the column, no index will be used.

Understanding the Cost-Based Optimizer
The cost-based optimizer is a more sophisticated facility than the rule-based optimizer. To determine the best execution path for a statement, it uses database information such as table size, number of rows, key spread, and so forth, rather than rigid rules.

The information required by the cost-based optimizer is available once a table has been analyzed via the ANALYZE command, or via the DBMS_STATS facility. If a table has not been analyzed, the cost-based optimizer can use only rule-based logic to select the best access path. It is possible to run a schema with a combination of cost-based and rule-based behavior by having some tables analyzed and others not analyzed.

NOTE: The ANALYZE command and the DBMS_STATS functions collect statistics about tables, clusters, and indexes, and store those statistics in the data dictionary.

A SQL statement will default to the cost-based optimizer if any one of the tables involved in the statement has been analyzed. The cost-based optimizer then makes an educated guess as to the best access path for the other tables based on information in the data dictionary.

The RDBMS kernel defaults to using the cost-based optimizer under a number of situations, including the following:

1. OPTIMIZER_MODE = CHOOSE has been specified in the INIT.ORA file, and statistics exist for at least one table involved in the statement
2. An ALTER SESSION SET OPTIMIZER_MODE = CHOOSE command has been executed, and statistics exist for at least one table involved in the statement
3. An ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (or ALL_ROWS) command has been executed, and statistics exist for at least one table involved in the statement
4. A statement uses the FIRST_ROWS or ALL_ROWS hint (e.g., SELECT /*+ FIRST_ROWS */. . .)

ANALYZE command
The way that you analyze your tables can have a dramatic effect on your SQL performance. If your DBA forgets to analyze tables or indexes after a table re-build, the impact on performance can be devastating. If your DBA analyzes each weekend, a new threshold may be reached and Oracle may change its execution plan. The new plan will more often than not be an improvement, but will occasionally be worse.
I cannot stress enough that if every SQL statement has been tuned, do not analyze just for the sake of it. One site that I tuned had a critical SQL statement that returned data in less than a second. The DBA analyzed each weekend believing that the execution path would continue to improve. One Monday, morning I got a phone call telling me that the response time had risen to 310 seconds.
If you do want to analyze frequently, use DBMS_STATS.EXPORT_SCHEMA_STATS to back up the existing statistics prior to re-analyzing. This gives you the ability to revert back to the previous statistics if things screw up.
When you analyze, you can have Oracle look at all rows in a table (ANALYZE COMPUTE) or at a sampling of rows (ANALYZE ESTIMATE). Typically, I use ANALYZE ESTIMATE for very large tables (1,000,000 rows or more), and ANALYZE COMPUTE for small to medium tables.
I strongly recommend that you analyze FOR ALL INDEXED COLUMNS for any table that can have severe data skewness. For example, if a large percentage of rows in a table has the same value in a given column, that represents skewness. The FOR ALL INDEXED COLUMNS option makes the cost-based optimizer aware of the skewness of a column's data in addition to the cardinality (number-distinct values) of that data.
When a table is analyzed using ANALYZE, all associated indexes are analyzed as well. If an index is subsequently dropped and recreated, it must be re-analyzed. Be aware that the procedures DBMS_STATS.GATHER_SCHEMA_STATS and GATHER_TABLE_STATS analyze only tables by default, not their indexes. When using those procedures, you must specify the CASCADE=>TRUE option for indexes to be analyzed as well.

Following are some sample ANALYZE statements:

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

If you analyze a table by mistake, you can delete the statistics. For example:

ANALYZE TABLE EMP DELETE STATISTICS;

Analyzing can take an excessive amount of time if you use the COMPUTE option on large objects. We find that on almost every occasion, ANALYZE ESTIMATE 5 PERCENT on a large table forces the optimizer make the same decision as ANALYZE COMPUTE.

Tuning prior to releasing to production
A major dilemma that exists with respect to the cost-based optimizer (CBO) is how to tune the SQL for production prior to it being released. Most development and test databases will contain substantially fewer rows than a production database. It is therefore highly likely that the CBO will make different decisions on execution plans. Many sites can't afford the cost and inconvenience of copying the production database into a pre-production database.

Oracle8i and later provides various features to overcome this problem, including DBMS_STATS and the outline facility. Each is explained in more detail later in this book.

Inner workings of the cost-based optimizer
Unlike the rule-based optimizer, the cost-based optimizer does not have hard and fast path evaluation rules. The cost-based optimizer is flexible and can adapt to its environment. This adaptation is possible only once the necessary underlying object statistics have been refreshed (re-analyzed). What is constant is the method by which the cost-based optimizer calculates each possible execution plan and evaluates its cost (efficiency).

The cost-based optimizer's functionality can be (loosely) broken into the following steps:

Parse the SQL (check syntax, object privileges, etc.).

Generate a list of all potential execution plans.

Calculate (estimate) the cost of each execution plan using all available object statistics.

Select the execution plan with the lowest cost.

The cost-based optimizer will be used only if at least one table within a SQL statement has statistics (table statistics for unanalyzed tables are estimated). If no statistics are available for any table involved in the SQL, the RDBMS will resort to the rule-based optimizer, unless the cost-based optimizer is forced via statement-level HINTS or by an optimizer goal of ALL_ROWS or FIRST_ROWS.

To understand how the cost-based optimizer works and, ultimately, how to exploit it, we need to understand how it thinks.

Primary key and/or UNIQUE index equality
A UNIQUE index's selectivity is recognized as 100%. No other indexed access method is more precise. For this reason, a unique index is always used when available.

Non-UNIQUE index equality
For non-UNIQUE indexes, index selectivity is calculated. The cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option of the ANALYZE. That option will make the cost-based optimizer aware of how the data in the indexed columns is skewed.

Range evaluation
For index range execution plans, selectivity is evaluated. This evaluation is based on a column's most recent high-value and low-value statistics. Again, the cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option when analyzing the table.

Range evaluation over bind variables
For index range execution plans, selectivity is guessed. Prior to Oracle9i, because bind variable values are not available at parse time (values are passed to the cursor after the execution plan has been decided), the optimizer cannot make decisions based on bind variable values. The optimizer assumes a rule of thumb of 25% selectivity for unbounded bind variable ranges (e.g., WHERE dept_no = :b1) and 50% selectivity for bounded ranges (WHERE dept_no > :b1 AND dept_no < :b2). Beginning with Oracle9i, the cost-based optimizer obtains bind variable values prior to determining an execution plan.

Histograms
Prior to the introduction of histograms in Oracle 7.3, The cost-based optimizer could not distinguish grossly uneven key data spreads.

System resource usage
By default, the cost-based optimizer assumes that you are the only person accessing the database. Oracle9i gives you the ability to store information about system resource usage, and can make much better informed decisions based on workload (read up on the DBMS_STATS.GATHER_SYSTEM_STATS package).

Current statistics are important
The cost-based optimizer can make poor execution plan choices when a table has been analyzed but its indexes have not been, or when indexes have been analyzed but not the tables.

You should not force the database to use the cost-based optimizer via inline hints when no statistics are available for any table involved in the SQL.

Using old (obsolete) statistics can be more dangerous than estimating the statistics at runtime, but keep in mind that changing statistics frequently can also blow up in your face, particularly on a mission-critical system with lots of online users. Always back up your statistics before you re-analyze by using DBMS_STATS.EXPORT_SCHEMA_STATS.

Analyzing large tables and their associated indexes with the COMPUTE option will take a long, long time, requiring lots of CPU, I/O, and temporary tablespace resources. It is often overkill. Analyzing with a consistent value, for example, estimate 3%, will usually allow the cost-based optimizer to make optimal decisions

Combining the information provided by the selectivity rules with other database I/O information allows the cost-based optimizer to calculate the cost of an execution plan.

EXPLAIN PLAN for the cost-based optimizer
Oracle provides information on the cost of query execution via the EXPLAIN PLAN facility. EXPLAIN PLAN can be used to display the calculated execution cost(s) via some extensions to the utility. In particular, the plan table's COST column returns a value that increases or decreases to show the relative cost of a query. For example:

EXPLAIN PLAN FOR
SELECT count(*)
FROM winners, horses
WHERE winners.owner=horses.owner
AND winners.horse_name LIKE 'Mr %'

COLUMN "SQL" FORMAT a56

SELECT lpad(' ',2*level)||operation||''
||options ||' '||object_name||
decode(OBJECT_TYPE, '', '',
'('||object_type||')') "SQL",
cost "Cost", cardinality "Num Rows"
FROM plan_table
CONNECT BY prior id = parent_id
START WITH id = 0;

SQL Cost Num Rows
-----------------------------------------------
SELECT STATEMENT 44 1
SORT AGGREGATE
HASH JOIN 44 100469
INDEX RANGE SCAN MG1(NON-UNIQUE)
2 1471
INDEX FAST FULL SCAN OWNER_PK(UNIQUE)
4 6830

By manipulating the cost-based optimizer (i.e., via inline hints, by creating/removing indexes, or by adjusting the way that indexes or tables are analyzed), we can see the differences in the execution cost as calculated by the optimizer. Use EXPLAIN PLAN to look at different variations on a query, and choose the variation with the lowest relative cost.

For absolute optimal performance, many sites have the majority of the tables and indexes analyzed but a small number of tables that are used in isolation are not analyzed. This is usually to force rule-based behavior on the tables that are not analyzed. However, it is important that tables that have not been analyzed are not joined with tables that have been analyzed.

Some Common Optimizer Misconceptions
Let's clear up some common misconceptions regarding the optimizers:

Oracle8i and Oracle9i don't support the rule-based optimizer
This is totally false. Certain publications mentioned this some time ago, but Oracle now assures us that this is definitely not true.

Hints can't be used with the rule-based optimizer
The large majority of hints can indeed be applied to SQL statements using the rule-based optimizer.

SQL tuned for rule will run well in cost
If you are very lucky it may, but when you transfer to cost, expect a handful of SQL statements that require tuning. However, there is not a single site that I have transferred and been unable to tune.

SQL tuned for cost will run well in rule
This is highly unlikely, unless the code was written with knowledge of the rule-based optimizer.

You can't run rule and cost together
You can run both together by setting the INIT.ORA parameter OPTIMIZER_MODE to CHOOSE, and having some tables analyzed and others not. Be careful that you don't join tables that are analyzed with tables that are not analyzed.

Which Optimizer to Use?
If you are currently using the rule-based optimizer, I strongly recommend that you transfer to the cost-based optimizer. Here is a list of the reasons why:

1. The time spent coding is reduced.
2. Coders do not need to be aware of the rules.
3. There are more features, and far more tuning tools, available for the cost-based optimizer.
4. The chances of third-party packages performing well has been improved considerably. Many third-party packages are written to run on DB2, Informix, and SQL*Server, as well as on Oracle. The code has not been written to suit the rule-based optimizer; it has been written in a generic fashion.
5. End users can develop tuned code without having to learn a large set of optimizer rules.
6. The cost-based optimizer has improved dramatically from one version of Oracle to the next. Development of the rule-based optimizer is stalled.
7. There is less risk from adding new indexes.
8. There are many features that are available only with the cost-based optimizer. These features include recognition of materialized views, star transformation, the use of function indexes, and so on. The number of such features is huge, and as time goes on, the gap between cost and rule will widen.
9. Oracle has introduced features such as the DBMS_STATS package and outlines to get around known problems with the inconsistency of the cost-based optimizer across environments.

2008-06-17T10:25:00.000+07:00

Oracle Export Options

In addition to export modes, the export utility enables the user to specify runtime parameters interactively, on the command line, or defined in a parameter file (PARFILE). These options include:

· buffer – Specifies the size, in bytes, of the buffer used to fetch the rows. If 0 is specified, only one row is fetched at a time. This parameter only applies to conventional (non direct) exports.

· compress – When “Y”, export will mark the table to be loaded as one extent for the import utility. If “N”, the current storage options defined for the table will be used. Although this option is only implemented on import, it can only be specified on export.

· consistent – [N] Specifies the set transaction read only statement for export, ensuring data consistency. This option should be set to “Y” if activity is anticipated while the exp command is executing. If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error.

· constraints – [Y] Specifies whether table constraints should be exported with table data.

· direct – [N] Determines whether to use direct or conventional path export. Direct path exports bypass the SQL command, thereby enhancing performance.

· feedback – [0] Determines how often feedback is displayed. A value of feedback=n displays a dot for every n rows processed. The display shows all tables exported not individual ones. From the output below, each of the 20 dots represent 50,000 rows, totaling 1 million rows for the table.

About to export specified tables via Direct Path ...

. . exporting table TABLE_WITH_ONE_MILLION_ROWS

....................

1000000 rows exported

· file – The name of the export file. Multiple files can be listed, separated by commas. When export fills the filesize, it will begin writing to the next file in the list.

· filesize – The maximum file size, specified in bytes.

· flashback_scn – The system change number (SCN) that export uses to enable flashback.

· flashback_time – Export will discover the SCN that is closest to the specified time. This SCN is used to enable flashback.

· full – The entire database is exported.

· grants – [Y] Specifies object grants to export.

· help – Shows command line options for export.

· indexes – [Y] Determines whether index definitions are exported. The index data is never exported.

· log – The filename used by export to write messages. The same messages that appear on the screen are written to this file:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...

. . exporting table TABLE_WITH_ONE_MILLION_ROWS 1000000 rows exported

Export terminated successfully without warnings.

· object_consistent – [N] Specifies whether export uses SET TRANSACTION READ ONLY to ensure that the data being exported is consistent.

· owner – Only the owner’s objects will be exported.

· parfile – The name of the file that contains the export parameter options. This file can be used instead of specifying all the options on the command line for each export.

· query – Allows a subset of rows from a table to be exported, based on a SQL where clause (discussed later in this chapter).

· recordlength – Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk. If not specified, this parameter defaults to the value specific to that platform. The highest value is 64KB.

· resumable – [N] Enables and disables resumable space allocation. When “Y”, the parameters resumable_name and resumable_timeout are utilized.

· resumable_name – User defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unless resumable = Y.

· resumable_timeout – [7200 seconds] The time period in which an export error must be fixed. This parameter is ignored unless resumable = Y.

· rows – [Y] Indicates whether or not the table rows should be exported.

· statistics – [ESTIMATE] Indicates the level of statistics generated when the data is imported. Other options include COMPUTE and NONE.

· tables – Indicates that the type of export is table-mode and lists the tables to be exported. Table partitions and sub partitions can also be specified.

· tablespaces – Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported. This option requires the EXP_FULL_DATABASE role.

· transport_tablespace – [N] Enables the export of metadata needed for transportable tablespaces.

· triggers – [Y] Indicates whether triggers defined on export tables will also be exported.

· tts_full_check – [FALSE] When TRUE, export will verify that when creating a transportable tablespace, a consistent set of objects is exported.

· userid – Specifies the userid/password of the user performing the export.

· volsize – Specifies the maximum number of bytes in an export file on each tape volume.

The functionality of the export utility has been significantly enhanced in recent versions of Oracle.
To check which options are available in any release use:
exp help=y

Overview of Oracle JInitiator 1.1.7.11

2008-06-17T10:23:00.000+07:00

Overview of Oracle JInitiator 1.1.7.11
Oracle JInitiator enables end users to run Oracle Developer Server applications directly within Netscape Navigator or Internet Explorer on the Windows 95 and Windows NT4.0 platforms.Oracle JInitiator is implemented as a plug-in (Netscape Navigator) or ActiveX Object (Microsoft Internet Explorer), Oracle JInitiator allows you to specify the use of the Oracle certified Java Virtual Machine (JVM) on web clients instead of relying on the default JVM provided by the browser.Oracle JInitiator delivers several key benefits to enterprises deploying Oracle Developer Server applications:Full JDK 1.1 support: Oracle JInitiator allows enterprise developers to develop and deploy Oracle Developer applications taking full advantage of JDK 1.1 features and functionality.Full Java Compatible support: Oracle JInitiator utilizes the latest release of the Oracle certified JDK (OJDK) which is fully compliant with the Java Compatibility Kit (JCK) test suite.Future-ready JDK Architecture: Oracle JInitiator features an architecture that makes it easy for Oracle to bring new JDK features, functionality, and bug fixes - including those contained in JDK 1.2 and the high performance Java HotSpot virtual machine - to Windows desktops much more quickly than has been previously possible.Easy install: Oracle JInitiator utilizes the plug-in/ActiveX Object installation mechanism provided by the browsers to install and update itself when necessary. Oracle JInitiator requires minimal intervention on the end users behalf
Supports several MIME types: Oracle JInitiator supports the following MIME typesapplication/x-jinit-appletapplication/x-jinit-applet;version=1.1.5.3application/x-jinit-applet;version=1.1.5.21.1application/x-jinit-applet;version=1.1.7.10application/x-jinit-applet;version=1.1.7.11
JAR file caching: Oracle JInitiator caches the downloaded JAR file on the client-machines hard disk. Cache allocations can be modified on the client via the Oracle JInitiator control panel.Supported configurations
The Oracle JInitiator supports the following configurations: Platform Internet Explorer 4.0 Navigator 4.0** Navigator 4.5 Windows 95* Windows NT*
* Oracle JInitiator 1.1.7.11 does not currently support Microsoft's Active Desktop.
** Oracle JInitiator 1.1.7.11 does not support Netscape Navigator 4.04 with the JDK 1.1 Smart Update, due to a bug in the Netscape Smart Update. Please use Netscape Navigator 4.04 without the Smart Update, or use other versions of Netscape Navigator.
About using Oracle JInitiatorOracle JInitiator does not replace or modify the default JVM provided by the browser. Rather, it provides an alternative JVM via the standard plug-in architecture of the browser which allows web application developers to specify the use of the Oracle certified JVM instead.Oracle JInitiator is automatically downloaded to a client machine from the application server the first time the client web browser encounters an HTML file that specifies the use of Oracle JInitiator. The installation and updating of Oracle JInitiator is performed using the standard plug-in mechanism provided by the browser.Oracle JInitiator installation performs the required steps to run Oracle Developer applications as trusted applets in the Oracle JInitiator environment.About using with Netscape NavigatorOracle JInitiator leverages the Netscape Navigator plug-in architecture in order to run inside the browser in the same way other plug-ins such as QuickTime movies or Shockwave animation's operate. Using the Netscape HTML (embed) tag, web application developers can specify plug-ins to be run as part of a web page. This is what makes it possible for Oracle JInitiator to run inside the web browser with minimal user intervention.When Navigator first encounters an HTML page that specifies the use of Oracle JInitiator, users will see a "Plug-in Not Loaded" dialog on the HTML page, which directs the user to the Oracle JInitiator download page. Users can then download the version of Oracle JInitiator for their operating system and install it. Once Oracle JInitiator is installed, users must shut down Navigator, restart it, and then revisit the original HTML page. Oracle JInitiator will then run and use the parameters in the (embed) tag to render the applet. The next time Navigator encounters a web page that specifies Oracle JInitiator, Navigator will seamlessly load and run the plug-in from the local disk, requiring no user intervention.About using with Microsoft Internet ExplorerOracle JInitiator leverages the Microsoft Internet Explorer extension mechanism for downloading and caching ActiveX controls and COM components. Using the HTML (object) tag, web application developers can specify to run ActiveX controls or COM components as part of a web page, including Oracle JInitiator.When Internet Explorer first encounters an HTML file that has been modified to specify the use of Oracle JInitiator, Internet Explorer will ask the user if it is okay to download an ActiveX control signed with a VeriSign digital signature by Oracle Corporation. If the user clicks "Yes," Internet Explorer will begin downloading Oracle JInitiator. Oracle JInitiator will then run and use its parameters in the (object) tag to render the applet. The next time Internet Explorer encounters a web page modified to support Oracle JInitiator, Internet Explorer will seamlessly load and run Oracle JInitiator from the local disk, requiring no user intervention.Oracle Java Runtime Environment version
Oracle JInitiator includes a version of Oracle Java Runtime Environment (JRE), which is downloaded and installed together with Oracle JInitiator.The version of the Oracle JRE in this release of Oracle JInitiator is Oracle JRE 1.1.7.11o. This is based on Oracle JDK 1.1.7.11o, which is in turn based on JavaSoft JDK 1.1.7.

Copyright ©1999 Oracle Corporation All Rights Reserved. Last update: 02/18/1999. For questions, comments, etc.: Please contact your local Oracle support representative. Desktop Considerations Java applets are particularly CPU intensive since they are run locally on the user's PC. A faster CPU has a more noticeable affect on performance, all other things being equal. JInitiator 1.1.8.7 and certain other Oracle tools are incompatible with Pentium 4 Processors (minimum of 1.1.8.10 required) Automatic download and installation of the JInitiator requires temporary changes to IE security settings Periodic "cleaning" of cache and other temporary files is necessary to reduce browser/memory issues while accessing the Oracle Applications. (see Clean Cache in Free Tools section at http://www.solutionbeacon.com )

Display Iconic Images In Forms Using Oracle AS

2008-06-17T10:20:00.000+07:00

Display Iconic Images In Forms Using Oracle AS

PURPOSE
=======

This document describes how to implement gif/jpeg icons on buttons for Forms using Oracle Application Server. This is done by defining a virtual directory containing the icons and directing the Forms Runtime to use that directory.

SCOPE & APPLICATION
====================
Audience: Forms Developer and Application Architects responsible for the deployment of forms applications over the web.This document ONLY describes the steps required for deploying icons in the Forms Services/ Runtime environment within Oracle 9iAS Rel 2. For information on deploying icons for Forms 9i/10g Builder / Runtime as part of 9i/10g Developers Suite, refer to:
Note 203846.1 How to Display Iconic Images Using Forms 9.0.x/ 10.x?
When running an Oracle Forms 9i/10g application the icon files used must be in a web enabled format such as JPG or GIF (GIF is the default format) (This is unlike older versions of forms running in client-server mode when the file format is .ico.)
Icon image files can either be retrieved by Forms as individual files on the filesystem or from a Java Archive (JAR file). If an application uses lots of icon images it is recommended that they are stored in a JAR file to reduce the number of HTTP round trips.
Reference:Note 232413.1 How to Deploy Iconic Images via a JAR File in Forms 9i/10g?
The example below details steps to configure iconic images as individual gif or jpeg files. It is a first approach recommended for initial testing and understanding.
Example:
========
This document has been written for use with both Windows and UNIX operating systems: Windows will use the "\" character to delimit directories and UNIX uses the "/" character. Any other differences are noted in the document.
1) Determine the physical location of the icons on your web server. For this example, assume that the icons are stored in D:\Myfiles\icons.
2) Create the virtual directory in the forms90.conf file that point to the location of your Forms 9i/10g icons. This file is located in:
$ORACLE_HOME\forms90\server\forms90.conf
To define a virtual directory forms90/icons, use the following syntax:
# Virtual path for ICONS (used to show icons in a form ) AliasMatch ^/forms90/icons/(..*) "D:\Myfiles/icons/$1"
Below is an example of the forms90.conf after it has been modified to include the /forms90/icons virtual directory.
# Virtual path mapping for Forms Java jar and class files (codebase) AliasMatch ^/forms90/java/(..*) "E:\IASR2\APP/forms90/java/$1" # Virtual path for JInitiator downloadable executable and download page AliasMatch ^/forms90/jinitiator/(..*) "E:\IASR2\APP/jinit/$1" # Virtual path for runform.htm (used to run a form for testing purposes) AliasMatch ^/forms90/html/(..*) "E:\IASR2\APP/tools/web90/html/$1"
# Virtual path for ICONS (used to show icons in a form ) AliasMatch ^/forms90/icons/(..*) "D:\Myfiles/icons/$1"
( For 10g use instead of "forms90" -> "forms")
3) Direct forms to use the /forms90/icons or /forms/icons virtual directory when running on the web by modifying the Registry.dat file in the $ORACLE_HOME\forms90\java\oracle\forms\registry or $ORACLE_HOME\forms\java\oracle\forms\registry
directory. Note that for UNIX, both the path name and file name are case sensitive. You must specify "Registry.dat". Modify the default.icons.iconpath entry as follows: default.icons.iconpath=http:///forms90/icons/ or default.icons.iconpath=http:///forms/icons/

If the URL used to bring up forms on the web is: http://abc.oracle.com:7778/forms90/f90servlet or http://abc.oracle.com:7778/forms/frmservlet

Then the entry required for the Registry.dat will be: default.icons.iconpath=http://abc.oracle.com:7778/forms90/icons or default.icons.iconpath=http://abc.oracle.com:7778/forms/icons default.icons.iconextension=gif
4) Now use EM Website to: a) Restart the OC4J-BI-Forms Instance b) Restart the BI-FORMS HTTP Server Instance.
5) Verify that the virtual directory has been defined properly and that the icons can be viewed in a browser. Assuming that exeqry.gif exists in the D:\Myfiles\icons directory, the following URL should show the icon:
http://abc.oracle.com:7778/forms90/icons/exeqry.gif or http://abc.oracle.com:7778/forms/icons/exeqry.gif

6) Check the form : http://abc.oracle.com:7778/forms90/f90servlet?form=D:\Testcases\F90\ICON_CHECK_IASR2.fmx or http://abc.oracle.com:7778/forms/frmservlet?form=D:\Testcases\F10g\ICON_CHECK_IASR2.fmx

CHECKLISTS
=============
Icons do not appear in the browser using the URL: o Verify that you are using the correct machine name and port for the OC4J_BI_Forms object.o Check the virtual directory name to make sure that there are no typographical errors. o Check for typographical errors in the forms90.conf or forms.conf for the virtual directory. o Check the name of the icon on the file system. If the file is saved as Exeqry.gif, then it will not be found using the URL http://abc.oracle.com:7778/forms90/ Close all your browser sessions and open a new browser window. Execute the URL to bring up from the Form Builder runtime or from the browser.
( For 10G use "forms" instead of "forms90")
USEFUL REFERENCES:==================
[PDF] Oracle9iAS Forms Services Deployment Guide (P/N B10170-01)Configuring Oracle9iAS Forms Services -> Deploying Icons and Images Used by Oracle9iAS Forms Services http://otn.oracle.com/products/forms/pdf/B10170_01.pdf
[PDF] Oracle9iForms Services - How to Deploy Iconshttp://otn.oracle.com/products/forms/pdf/webicons.pdf
Copyright © 2005, Oracle. All rights reserved.

Oracle Data Pump

2008-06-17T10:19:00.000+07:00

Oracle Data Pump

Since we are all familiar with Oracle’s original export (exp) utility, and in my opinion Data Pump will be replacing exp soon, I thought it would be good to start off getting familiar with this utility by some relatively simple Data Pump exports (expdp) that are similar to the way we have used exp in the past. In particular the FULL export.
As a word of caution, the Data Pump exports (expdp) are not compatible with exp. So as you go forth and play with this utility please at least name the exports something that will signify that the dump file was created by expdp so that you won’t get confused. Also since this utility is not backward compatible, if you have any databases prior to 10g and you are using exp, you may want to hold off on implementing the new expdp utility as you will not be able to import into any pre-10g databases.
Where are my dmp files
Different from the original export utility in Oracle, Data Pump runs only on the server side. You may initiate the export from a client but the job(s) themselves will run inside an Oracle server. There are no dump files (expdat.dmp) or log files that will be created on your local machine if you initiate a Data Pump Export (expdp) there. But, as we will see through a couple examples, if you have an Oracle server on your local machine you can get Oracle to produce dump files there.
Oracle creates dump and log files through DIRECTORY objects. So before you can use Data Pump you must create a DIRECTORY object. There are a few different “default” mechanisms for Oracle to determine an appropriate DIRECTORY to use. Mostly through environment variables and a default directory name that Oracle will look for. But as we all should know, we should not leave this to chance and instead explicitly create and use a directory object name of our choice. As soon as you create an object, a DIRECTORY here, that is a default you open yourself up to security breaches and thus this practice should be avoided. So for here I have logged into my S1 database and will create a DIRECTORY named datapump.
SQL-S1> CREATE DIRECTORY datapump AS 'C:\user\datafile\datapump';
Then, as you use Data Pump you can reference this DIRECTORY as a parameter for export where you would like the dump or log files to end up. It is good to note here that as dump and log files are created, log files that are written to will overwrite existing log files of the same name but dump files that have the same name will only create an error condition and error out the Data Pump job. This was not the case with Oracle’s original export utility (exp). Subsequent exports would overwrite all files. With Data Pump this is a nice safeguard but can also create problems for those of us who did nightly exports to the same location and file names. Now we have to think about cleanup routines. A small price to pay for additional security that could save your life one day when the scraping utility fails.
Just like the original exp utility Data Pump requires some authorization to allow users to export. Here I am granting EXP_FULL_DATABASE to a user JKOOP on database S1 that will allow the user to perform a full database export. If not given the JKOOP user could only export their own schema. Also I need to grant READ and WRITE privileges on the recently created DIRECTORY. Also on database S1.
SQL-S1 > GRANT EXP_FULL_DATABASE to jkoop;SQL-S1 > GRANT READ, WRITE ON DIRECTORY datapump to jkoop;
Now for the Examples
We are all familiar with the FULL database export. Data Pump easily performs this with the following command line. Notice there are just a few name changes and instead of specifying the directory path in the file locations the additional parameter for your DIRECTORY is supplied. This command line assumes you are on the database server and environment variables are properly set for a direct connection.
E:> expdp jkoop/pwd FULL=y DIRECTORY=datapump DUMPFILE=expdata.dmp LOGFILE=expdata.log
We have also used the exp utility to connect through a TNS entry to perform an export on a remote database. Data Pump can also easily do this by adding a connection identifier to the user/password parameter. The exact same way done in exp.
E:> expdp jkoop/pwd@S1 FULL=y DIRECTORY=datapump DUMPFILE=byTNS.dmp LOGFILE=byTNS.log
Now for a few export trickeries. These next two examples assume an additional database named S2. They allow for a connection to the target database that we want to export through a database link. So the first thing to do is create a database link.
SQL-S2> CREATE DATABASE LINK S1 CONNECT TO JKOOP IDENTIFIED BY PWD USING 'S1';
The key item to remember with Data Pump and where files will end up is the fact that wherever you Data Pump runs it requires a DIRECTORY to place dump and log files in. So since we will be connecting to the S2 database there will be required a DIRECTORY for placing these files in. Here I create a new DIRECTORY named mydump on database S2.
SQL-S2> CREATE DIRECTORY mydump AS 'D:\mydump';
Now for the command line options. Here we are running on the server where database S2 resides and will be producing a full dump of database S1 through the NETWORK_LINK. But placing the dump and log files on the server where database S1 resides. This was great news for me as when I first read the documentation I thought all dumps would have to reside on the server the database resided on. Now I can almost produce an environment where a single database is a ‘gateway’ for my database exports if needed.
E:> expdp jkoop/pwd FULL=y DIRECTORY=mydump NETWORK_LINK=S1 DUMPFILE=byDB.dmp LOGFILE=byDB.log
Ok, suppose we do produce that gateway for exports. Do we need to execute all commands from that server? No! With Data Pump we need only connect to the S2 database through a TNS entry and then supply the appropriate NETWORK_LINK to the database we want to export.
E:> expdp jkoop/pwd@S2 FULL=y DIRECTORY=mydump NETWORK_LINK=S1 DUMPFILE=TNSDB.dmp LOGFILE=TNSDB.log
Introduction to Monitoring Data Pump
A simple way to gain insight into the status of a Data Pump job is to look into a few views maintained within the Oracle instance the Data Pump job is running. These views are DBA_DATAPUMP_JOBS, DBA_DATAPUMP_SESSIONS, and V$SESSION_LOGOPS. These views are critical in the monitoring of your export jobs so, as we will see in a later article, you can attach to a Data Pump job and modify the execution of the that job.
DBA_DATAPUMP_JOBSThis view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.
SQL> select * from dba_datapump_jobs
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS---------- ---------------------- ---------- ---------- ------------- --------- -----------------JKOOP SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 1 1JKOOP SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA EXECUTING 1 1DBA_DATAPUMP_SESSIONSThis view give gives the SADDR that assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information.
SQL> SELECT * FROM DBA_DATAPUMP_SESSIONS
OWNER_NAME JOB_NAME SADDR---------- ------------------------------ --------JKOOPMANN SYS_EXPORT_FULL_01 225BDEDCJKOOPMANN SYS_EXPORT_SCHEMA_01 225B2B7CV$SESSION_LONGOPSThis view helps determine how well a Data Pump export is doing. Basically gives you a progress indicator through the MESSAGE column.
SQL> select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS
USERNAME OPNAME TARGET_DES SOFAR TOTALWORK MESSAGE-------- -------------------- ---------- ----- ---------- ------------------------------------------------JKOOP SYS_EXPORT_FULL_01 EXPORT 132 132 SYS_EXPORT_FULL_01:EXPORT:132 out of 132 MB doneJKOOP SYS_EXPORT_FULL_01 EXPORT 90 132 SYS_EXPORT_FULL_01:EXPORT:90 out of 132 MB doneJKOOP SYS_EXPORT_SCHEMA_01 EXPORT 17 17 SYS_EXPORT_SCHEMA_01:EXPORT:17 out of 17 MB doneJKOOP SYS_EXPORT_SCHEMA_01 EXPORT 19 19 SYS_EXPORT_SCHEMA_01:EXPORT:19 out of 19 MB doneThe original export utility (exp) may or may not be going away soon. The documentation clearly states that Data Pump will handle data types that exp will not and we should begin our migration to this new utility. Except for those instances where you must export between 10g and pre-10g databases. This article stepped through the process of performing FULL exports as these are typical in Oracle environment. If you are doing schema or table exports the change is simple and we will visit those in subsequent parts to this series.

Killing the Oracle DBMS_JOB

2008-06-17T10:18:00.000+07:00

Killing the Oracle DBMS_JOB
Take control of Oracle's queue with a step by step approach to getting rid of those pesky DBMS_JOBs.Let's face it, Oracle's job scheduling facility is a wonderful tool for scheduling Oracle related jobs without having to maintain a cron job on Unix or an AT job in windows. It is also very robust and reliable. It is that very reliability and robustness that gives many of us our problems.If you have any form of jobs running on your system, you will at one time or another come across the issue of a run-away job that just doesn't seem to want to end. Or maybe you will try and shutdown the database only to find out that it is waiting to complete a job. I would like to offer some help in the management of those job queues when they just don't seem to want to end or go away.A while back I needed to find information on how to clear the job queue for jobs running with no apparent end in sight. Some had hung, while others just were taking a bad access path to data. I needed to bring down these jobs, do a bit of tuning and then restart the jobs. Well, to my amazement, there wasn't very much information out on the web that gave good insight into this process. Basically the method suggested was to first break the job and then issue an ALTER SYTEM KILL SESSION command. This method does not always work and unfortunately--never on my system, for the jobs I had. I then called Oracle support and basically got the same answer as I found out on the web. They did give me one added piece of information. They said, if the ALTER SYSTEM KILL SESSION didn't work, I was supposed to bounce my database in order to bring down the job queue processes. First of all, this wasn't an option and when I did get the opportunity to bounce the database box, many of the jobs seemed to come right back as strong as ever.Before writing this article I did another quick search on the topic of killing dbms_jobs and to my amazement there still wasn't much good information out there. This is why I want to share my method, so that you won't be stuck up against the wall with this problem and nowhere to turn, as I was.Lets first go through a few different methods of viewing the information about job queues.Viewing scheduled dbms_jobs
When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.
scheduled_dbms_jobs.sql
set linesize 250col log_user for a10col job for 9999999 head 'Job'col broken for a1 head 'B'col failures for 99 head "fail"col last_date for a18 head 'LastDate'col this_date for a18 head 'ThisDate'col next_date for a18 head 'NextDate'col interval for 9999.000 head 'RunInterval'col what for a60
select j.log_user,j.job,j.broken,j.failures,j.last_date':'j.last_sec last_date,j.this_date':'j.this_sec this_date,j.next_date':'j.next_sec next_date,j.next_date - j.last_date interval,j.whatfrom (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHATfrom dba_jobs dj) j;
What Jobs are Actually Running
A simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.
running_jobs.sql
set linesize 250col sid for 9999 head 'SessionID'col log_user for a10col job for 9999999 head 'Job'col broken for a1 head 'B'col failures for 99 head "fail"col last_date for a18 head 'LastDate'col this_date for a18 head 'ThisDate'col next_date for a18 head 'NextDate'col interval for 9999.000 head 'RunInterval'col what for a60select j.sid,j.log_user,j.job,j.broken,j.failures,j.last_date':'j.last_sec last_date,j.this_date':'j.this_sec this_date,j.next_date':'j.next_sec next_date,j.next_date - j.last_date interval,j.whatfrom (select djr.SID, dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHATfrom dba_jobs dj, dba_jobs_running djrwhere dj.job = djr.job ) j;
What Sessions are Running the Jobs
Now that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.
session_jobs.sql
set linesize 250col sid for 9999 head 'SessionID'col spid head 'O/SProcessID'col serial# for 9999999 head 'SessionSerial#'col log_user for a10col job for 9999999 head 'Job'col broken for a1 head 'B'col failures for 99 head "fail"col last_date for a18 head 'LastDate'col this_date for a18 head 'ThisDate'col next_date for a18 head 'NextDate'col interval for 9999.000 head 'RunInterval'col what for a60select j.sid,s.spid,s.serial#,j.log_user,j.job,j.broken,j.failures,j.last_date':'j.last_sec last_date,j.this_date':'j.this_sec this_date,j.next_date':'j.next_sec next_date,j.next_date - j.last_date interval,j.whatfrom (select djr.SID, dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHATfrom dba_jobs dj, dba_jobs_running djrwhere dj.job = djr.job ) j,(select p.spid, s.sid, s.serial#from v$process p, v$session swhere p.addr = s.paddr ) swhere j.sid = s.sid;
Now that we have a good handle on how we can look at the jobs and the key columns involved, let's go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.
Bringing Down a DBMS_JOB
1. Find the Job You Want to Bring DownIn order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.
2. Mark the DBMS_JOB as BrokenUse the following command for the job that you have to deal with.
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.
As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.3. Kill the Oracle Session
Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.
ALTER SYSTEM KILL SESSION 'sid,serial#';
4. Kill the O/S Process
More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill '9 spid
The orakill is an Oracle command, while kill is a Unix command.5. Check if the Job is Still Running
Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.
6. Determine the Current Number of Job Queue Processes
SQL> col value for a10SQL> select name,value from v$parameter where name = 'job_queue_processes';
7. Alter the Job Queue to Zero
SQL> ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job queue processes.
8. Validate that No Processes are Using the Job QueueRe-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.
9. Mark the DBMS_JOB as Not BrokenYou can now reset the broken job to not broken so they can run again. Just issue the command.
SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):
10. Alter the Job Queue to Original ValueSet the job queue to its' original value so that the jobs can run again.
ALTER SYSTEM SET job_queue_processes = original_value;
11. Validate that DBMS_JOB Is RunningTo make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.
Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.

Displays a TIF image on multiple pages in Reports 10G.

2008-06-17T10:16:00.000+07:00

Notice that it can be any object (field, frame, image JPG, BMP, etc.), we are not treating here multi-page tiff!!Important! This document is NOT about "Multi page TIF" which is in fact a file that contains several images. Multi-page : TIFF defines support for multiple images in a single file. Such a file is then called 'multi-page' TIFF. Thus, the TIFF format is very well suited to e.g. store the many pages of a single fax in a single file. "Multi page TIF" is not supported in Reports, i.e. Oracle Reports does not have this feature implemented !The document first describes how to insert a TIF (or JPG, or BMP) image in the DB as a BLOB using a procedure and then how to implement the report:
SolutionThe following steps should be followed:
1. Check the write rights in utl_file_dir.
SQL> conn /as sysdba Connected. SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------- --------------------- utl_file_dir string /u01/app/oracle/product/10.1. 0/Db_1/demo/schema/log
If it is * it means that the DIR can point anywhere.

2. Use a table named images.
desc images; Name Null? Type
----------------------------------------- -------- ----------------------------
IMAGE_NAME NOT NULL VARCHAR2(30) CONTENT_IMG BLOB

3. Create the virtual directory to the utl_file_dir value.CREATE OR REPLACE DIRECTORY TESTTIFF AS '/u01/app/oracle/product/10.1.0/Db_1/demo/schema/log';

4. Create and execute the following procedure that inserts the TIF into the DB.
create or replace PROCEDURE INSERT_TIF AS v_filename varchar2(16); p_file_loc varchar2(100); v_file bfile; v_file_blob blob; begin v_filename:='010021004160.tif'; -- name of the file (the file must be copied into the already defined oracle directory) p_file_loc:='TESTTIFF'; -- name of the oracle directory where the image file is located v_file:=bfilename(p_file_loc,v_filename);
INSERT INTO images values(v_filename,empty_blob() ) RETURN content_img INTO v_file_blob;
dbms_output.put_line('file size 'v_filename' is : 'dbms_lob.GETLENGTH(v_file)' Kb'); dbms_lob.fileopen(v_file, dbms_lob.file_readonly); dbms_lob.loadfromfile( v_file_blob, v_file, dbms_lob.getlength(v_file) ); dbms_lob.fileclose(v_file); commit; exception when others then dbms_output.put_line(sqlerrm); end;

5. Verify that the TIF was correctly inserted into the DB.
SQL> select image_name,dbms_lob.GETLENGTH(content_img) from images;
IMAGE_NAME DBMS_LOB.GETLENGTH(CONTENT_IMG)
------------------------------ -------------------------------
010021004160.tif 528120

6. Create a test report in Reports Builder 10g release 2 (10.1.2.0.2) to display the TIF as a BLOB image.
6.a. In the Main Section, press F4 for the Property Palette and set the "Horizontal Panels per Page" to 2 and "Vertical Panels" per Page 2, for example.
6.b. Afterwards, in the Paper Layout, zoom out to see all the panels and expand the BLOB field by dragging its margins in order to expand over the 4 panels.
Note!!! The field containing the BLOB must have "Fixed" Vertical Elasticity, otherwise the picture will shrink!
7. Execute the report.=> It is correctly displayed on multiple pages.

8. Export the report in PDF and printed it. => There are 4 pages resulted, each one containing a part of the image.

Advanced PL/SQL Code Tuning

2008-06-17T10:06:00.001+07:00

Introduction
This article discusses several performance enhancements that are valuable in PL/SQL, but are not complex enough to merit individual articles. In this article, we will discuss the use of an alternative DUAL table, use of NOCOPY, use of HINTs, and several other coding techniques to make your PL/SQL code more efficient.
An alternative DUAL
There is a cost when selecting from DUAL, even if we’re only selecting a constant value. To demonstrate:
SQL> select 1 from dual;Elapsed: 00:00:00.01
Execution plan
---------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF 'DUAL'

Statistics
---------------------------------------------------
0 recursive calls0 db block gets3 consistent gets
0 physical reads0 redo size380 bytes sent via SQL*Net to client499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

SQL>
We see that each select from DUAL costs 3 consistent gets. Now, if you do a lot of selecting from DUAL your code might benefit from using an alternative DUAL, one that only requires 1 consistent get. Here’s how it’s done:
create table xdual ( dummy varchar2(1) primary key)organization index;
insert into xdual varchar2(9);
analyze table xdual compute statistics for table for all indexes for all indexed columns;
The trick is to create the XDUAL as an index-organized table and analyze it properly. This allows the cost-based optimizer to generate the most efficient plan possible, which requires only 1 consistent get:

SQL> select 1 from xdual;Elapsed: 00:00:00.01
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1
0 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_33973' (UNIQUE) (Cost=1Card=1)
Statistics
----------------------------------------------------------
0 recursive calls0 db block gets1 consistent gets0 physical reads0 redo size380 bytes sent via SQL*Net to client499 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed

To see how these two compare, consider the following code example:
declare x number;
begin
for i in 1 .. 10000 loop
select 1 into x from dual;
end loop;
end;

This required about 0.32 seconds on my machine using dual, whereas if I exchanged XDUAL with DUAL I had to wait only 0.25 seconds. Not a whole lot, but it’s still about 20% reduction. Also keep in mind that consistent gets = CPU resources so if you reduce the required number of consistent gets you reduce the need for CPU.
Note that in Oracle 10g this situation disappears since the DUAL table now has become a special internal table that requires no consistent gets!

Passing Large Data Structures with NOCOPY
The PL/SQL runtime engine has two different methods for passing parameter values between stored procedures and functions, by value and by reference.
When a parameter is passed by value the PL/SQL runtime engine copies the actual value of the parameter into the formal parameter. Any changes made to the parameter inside the procedure has no effect on the values of the variables that were passed to the procedure from outside.
When a parameter is passed by reference the runtime engine sets up the procedure call so that both the actual and the formal parameters point (reference) the same memory location that holds the value of the parameter.
By default OUT and IN OUT parameters are passed by value and IN parameters are passed by reference. When an OUT or IN OUT parameter is modified inside the procedure the procedure actually only modifies a copy of the parameter value. Only when the procedure has finished without exception is the result value copied back to the formal parameter.
Now, if you pass a large collection as an OUT or an IN OUT parameter then it will be passed by value, in other words the entire collection will be copied to the formal parameter when entering the procedure and back again when exiting the procedure. If the collection is large this can lead to unnecessary CPU and memory consumption.
The NOCOPY hint alleviates this problem because you can use it to instruct the runtime engine to try to pass OUT or IN OUT parameters by reference instead of by value. For example:

procedure get_customer_orders( p_customer_id in number, p_orders out nocopy orders_coll);theorders orders_coll;get_customer_orders(124, theorders);

In the absence of the NOCOPY hint the entire orders collection would have been copied into the theorders variable upon exit from the procedure. Instead the collection is now passed by reference.
Keep in mind, however, that there is a downside to using NOCOPY. When you pass parameters to a procedure by reference then any modifications you perform on the parameters inside the procedure is done on the same memory location as the actual parameter, so the modifications are visible. In other words, there is no way to “undo” or “rollback” these modifications, even when an exception is raised midway. So if an exception is raised inside the procedure the value of the parameter is “undefined” and cannot be trusted.
Consider our get_customer_orders example. If the p_orders parameter was half-filled with orders when an exception was raised, then upon exit our theorders variable will also be half-filled because it points to the same memory location as the p_orders parameter. This downside is most problematic for IN OUT parameters because if an exception occurs midway then not only is the output garbage, but you’ve also made the input garbage.
To sum up, a NOCOPY hint can offer a small performance boost, but you must be careful and know how it affects program behavior, in particular exception handling.
Using Reusable SQL
Except for single run SQL (such as weekly reports or infrequently used procedures) you should attempt to use bind variables instead of literals in your PL/SQL code. Use of bind variables allows the code to be reused multiple times. The entire purpose of the shared pool is to allow reuse of SQL statements that have already been parsed.
You can have the DBA set the CURSOR_SHARING parameter to FORCED in Oracle8i or to FORCED or SIMILAR in Oracle9i. In Oracle9i the hint CURSOR_SHARING_EXACT can be used for specific code that shouldn’t be shared. In Oracle8i there is no CURSOR_SHARING_EXACT parameter and all literals in SELECT statements will be changed to bind variables. In Oracle9i Oracle uses bind variable peaking for the first time a SQL is parsed to allow more optimal code paths to be selected.
Identify SQL using Comments
It can be very difficult to pull your PL/SQL procedure code out of the background code in an instance shared pool. You can place a comment in each SQL statement that identifies the SQL within the shared pool. An example of this is:
CURSOR get_latch ISSELECT /* DBA_UTIL.get_latch */ a.name,100.*b.sleeps/b.gets FROM v$latchname a, v$latch b WHERE a.latch# = b.latch# and b.sleeps > 0;
Now to find all SQL code in the shared pool from the DBA_UTILITIES package you can simply query the V$SQLAREA or V$SQLTEXT to find code entries with ‘%DBA_UTIL%’ in the SQL_TEXT column.
Using Hints
There are many hints available to the developer for use in tuning SQL statements that are embedded in PL/SQL. You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ hints can tame a wild optimizer and give you optimal performance.
Hints are enclosed within comments to the SQL commands DELETE, SELECT or UPDATE or are designated by two dashes and a plus sign. To show the format the SELECT statement only will be used, but the format is identical for all three commands.
SELECT /*+ hint --or-- text */ statement body -- or -- SELECT --+ hint --or-- text statement body
Where:
a. /*, */ — These are the comment delimiters for multi-line comments b. -- — This is the comment delimiter for a single line comment (not usually used for hints) c. + — This tells Oracle a hint follows, it must come immediately after the /* d. hint — This is one of the allowed hints e. text — This is the comment text

Hint and Meaning

+
Must be immediately after comment indicator, tells Oracle this is a list of hints.

ALL_ROWS
Use the cost based approach for best throughput.

CHOOSE
Default, if statistics are available will use cost, if not, rule.

FIRST_ROWS
Use the cost based approach for best response time.

RULE
Use rules based approach; this cancels any other hints specified for this statement.

Access Method Hints:

CLUSTER(table)
This tells Oracle to do a cluster scan to access the table.

FULL(table)
This tells the optimizer to do a full scan of the specified table.

HASH(table)
Tells Oracle to explicitly choose the hash access method for the table.

HASH_AJ(table)
Transforms a NOT IN subquery to a hash anti-join.

ROWID(table)
Forces a rowid scan of the specified table.

INDEX(table [index])
Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.

INDEX_ASC (table [index])
Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.

INDEX_DESC(table [index])
Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.
INDEX_COMBINE(table index)Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.

INDEX_FFS(table index)
Perform a fast full index scan rather than a table scan.

MERGE_AJ (table)
Transforms a NOT IN subquery into a merge anti-join.

AND_EQUAL(table index index [index index index])
This hint causes a merge on several single column indexes. Two must be specified, five can be.

NL_AJ
Transforms a NOT IN subquery into a NL anti-join (nested loop).

HASH_SJ(t1, t2)
Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

MERGE_SJ (t1, t2)
Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

NL_SJ
Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

Hints for join orders and transformations:

ORDERED
This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.

STAR
Forces the largest table to be joined last using a nested loops join on the index.

STAR_TRANSFORMATION
Makes the optimizer use the best plan in which a start transformation is used.

FACT(table)
When performing a star transformation use the specified table as a fact table.

NO_FACT(table)
When performing a star transformation do not use the specified table as a fact table.

PUSH_SUBQ
This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan.

REWRITE(mview)
If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.

NOREWRITE
Turns off query rewrite for the statement, use it for when data returned must be concurrent and can’t come from a materialized view.

USE_CONCAT
Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the UNION ALL set operator.

NO_MERGE (table)
This causes Oracle to join each specified table with another row source without a sort-merge join.

NO_EXPAND
Prevents OR and IN processing expansion.

Hints for Join Operations:

USE_HASH (table)
This causes Oracle to join each specified table with another row source with a hash join.

USE_NL(table)
This operation forces a nested loop using the specified table as the controlling table.

USE_MERGE(table,[table,…])
This operation forces a sort-merge-join operation of the specified tables.

DRIVING_SITE
The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.

LEADING(table)
The hint causes Oracle to use the specified table as the first table in the join order.

Hints for Parallel Operations:

[NO]APPEND
This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands.

NOPARALLEL (table)
This specifies the operation is not to be done in parallel.

PARALLEL(table, instances)
This specifies the operation is to be done in parallel.

PARALLEL_INDEX
Allows parallelization of a fast full index scan on any index.

Other Hints:

CACHE
Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.

NOCACHE
Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.

[NO]APPEND
For insert operations will append (or not append) data at the HWM of table.

UNNEST
Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE.

NO_UNNEST
Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to TRUE.

PUSH_PRED
Pushes the join predicate into the view.
As you can see, a dilemma with a stubborn index can be easily solved using FULL or NO_INDEX hints. You must know the application to be tuned. The DBA can provide guidance to developers but in all but the smallest development projects, it will be nearly impossible for a DBA to know everything about each application. It is clear that responsibility for application tuning rests solely on the developer’s shoulders with help and guidance from the DBA.

Using Global Hints
While hints normally refer to table in the query it is possible to specify a hint for a table within a view through the use of what are known as GLOBAL HINTS. This is done using the global hint syntax. Any table hint can be transformed into a global hint.
The syntax is:
/*+ hint(view_name.table_in_view) */
For example:
/*+ full(sales_totals_vw.s_customer)*/
If the view is an inline view, place an alias on it and then use the alias to reference the inline view in the global hint.
Don’t Over-specify Variable Length
Because a VARCHAR2 can be up to 32k in PL/SQL it is easy to fall into the trap of thinking that you can always specify the length to be many times what you feel you need. An example is setting VARCHAR2 to 2000 when you only need 80 characters.
The problem with over-specifying variable size is that the PL/SQL engine believes you and reserves the memory size you ask for in each variable declaration. Now this may not be a problem with one or two over-specified variables but if this is a PL/SQL table containing a thousand records it can place considerable memory overhead on your system.
Proper Use of Data Types
Improper use of datatypes can result in implicit type conversions. Implicit type conversions can result in the statement not being able to use appropritate indexes thus forcing a full table scan. Using %TYPE and %ROWTYPE to capture the types from either the specific table or cursor alleviates this.
Proper IF Statements
When using nested IF statements always place the IF logic that will be exercised most often first. An example would be the IF exit, usually programmers will place the various nested IF constructs that actually perform code operations first, even if they are rarely exercised, by placing the exit IF first the processing associated with the other IF processing is avoided.
This is especially true of IF constructs contained within LOOP structures. Using the PROFILER_CONTROL procedure as an example look at Figure 1.
PROCEDURE profiler_control(start_stop IN VARCHAR2, run_comm IN VARCHAR2, ret OUT BOOLEAN) AS ret_code INTEGER;
BEGIN
ret_code:=dbms_profiler.internal_version_check;
IF ret_code !=0 THEN
ret:=FALSE;
ELSIF start_stop NOT IN ('START','STOP') THEN
ret:=FALSE;
ELSIF start_stop = 'START' THEN
ret_code:=DBMS_PROFILER.START_PROFILER(run_comment1=>run_comm);
IF ret_code=0 THEN
ret:=TRUE;
ELSE
ret:=FALSE;
END IF;

ELSIF start_stop = 'STOP' THEN
ret_code:=DBMS_PROFILER.FLUSH_DATA;
ret_code:=DBMS_PROFILER.STOP_PROFILER;

IF ret_code=0 THEN
ret:=TRUE;
ELSE
ret:=FALSE;
END IF;

END IF;
END profiler_control;
Figure 1: The PROFILER_CONTROL Package

Notice that most of time the procedure will exit without generating an action, however, the exit is at the bottom of the IF-ELSIF stack. By rearranging the code and using a BOOLEAN as the go-no go we get a simplified, more efficient program as shown in Figure 2.

PROCEDURE profiler_control(start_stop IN BOOLEAN, run_comm IN VARCHAR2, ret OUT BOOLEAN) ASret_code INTEGER;
BEGIN
IF start_stop IS NULL THEN
Ret:=TRUE;
ELSIF start_stop THEN
ret_code:=dbms_profiler.internal_version_check;
IF ret_code !=0 THEN
ret:=FALSE;
END IF;

ELSIF start_stop AND ret_code=0 THEN
ret_code:=DBMS_PROFILER.START_PROFILER(run_comment1=>run_comm);
IF ret_code=0 THEN ret:=TRUE;
ELSE ret:=FALSE;
END IF;
ELSIF NOT start_stop THEN
ret_code:=DBMS_PROFILER.FLUSH_DATA;
ret_code:=DBMS_PROFILER.STOP_PROFILER;
IF ret_code=0 THEN ret:=TRUE;
ELSE ret:=FALSE;
END IF;
END IF;
END profiler_control;

Figure 2: New and Improved PROFILER_CONTROL
We make the code more efficient and reduce the amount of CPU cycles for all other packages that call PROFILER_CONTROL. If the IF construct involved in contained within a LOOP structure the savings can be substantial.
Using Temporary TablesIf the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table. A GLOBAL TEMPORARY table has a persistent definition but data is not persistent and the global temporary table generates no redo or rollback information. For example if you are processing a large number of rows, the results of which are not needed when the current session has ended, you should create the table as a temporary table instead:
create global temporary table results_temp (...)on commit preserve rows;
The “on commit preserve rows” clause tells the SQL engine that when a transaction is committed the table should not be cleared.
The global temporary table will be created in the users temporary tablespace when the procedure populates it with data and the DIRECT_IO_COUNT will be used to govern the IO throughput (this usually defaults to 64 blocks).
Use Native compilation in 9iThe native compilation option in Oracle9i allows you to convert internal stored packages and procedures into compiled shared C libraries on the host server. This native compilation can reduce code execution time by up to a factor of four. The packages and procedures are compiled as native C routines and linked into the Oracle code. This new option in Oracle9i is most effective for computational intensive PL/SQL and shouldn’t be used for PL/SQL that does a great deal of SQL execution.
To speed up one or more procedures using this technique follow this procedure: Update the supplied makefile and enter the appropriate paths and other values for your system. The path of this makefile is:
$ORACLE_HOME/plsql/spnc_makefile.mk.
Use the ALTER SYSTEM or alter session command, or update your initialization file, to set the parameter PLSQL_COMPILER_FLAGS to include the value NATIVE. The default setting includes the value INTERPRETED, and you must remove this keyword from the parameter value.
Compile one or more procedures, using one of these methods:
Use the ALTER PROCEDURE or ALTER PACKAGE command to recompile the procedure or the entire package. Drop the procedure and create it again. Use CREATE OR REPLACE to recompile the procedure. Run one of the SQL*Plus scripts that sets up a set of Oracle-supplied packages. Create a database using a preconfigured initialization file with PLSQL_COMPILER_FLAGS=NATIVE. During database creation, the UTLIRP script is run to compile all the Oracle-supplied packages. To be sure that the process worked, you can query the data dictionary to see that a procedure is compiled for native execution. To check whether an existing procedure is compiled for native execution or not, you can query the data dictionary views USER_STORED_SETTINGS, DBA_STORED_SETTINGS, and ALL_STORED_SETTINGS. For example, to check the status of the procedure MY_PROC, you could enter:
SELECT param_value FROM user_stored_settings WHERE param_name = 'PLSQL_COMPILER_FLAGS' and object_name = 'MY_PROC';
The PARAM_VALUE column has a value of NATIVE for procedures that are compiled for native execution, and INTERPRETED otherwise. After the procedures are compiled and turned into shared libraries, they are automatically linked into the Oracle process. You do not need to restart the database, or move the shared libraries to a different location. You can call back and forth between stored procedures, whether they are all compiled in the default way (interpreted), all compiled for native execution, or a mixture of both.
Because the PLSQL_COMPILER_FLAGS setting is stored inside the library unit for each procedure, procedures compiled for native execution are compiled the same way when the procedure is recompiled automatically after being invalidated, such as when a table that it depends on is recreated.
You can control the behavior of PL/SQL native compilation through the ALTER SYSTEM or alter session commands or by setting or changing these parameters in the initialization file:
PLSQL_COMPILER_FLAGS PLSQL_NATIVE_LIBRARY_DIR (cannot be set by alter session for security reasons) PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT PLSQL_NATIVE_MAKE_UTILITY PLSQL_NATIVE_MAKE_FILE_NAME An example of compiling a PL/SQL Procedure for Native Execution is shown in Figure 3.
connect scott/tiger;set serveroutput on;alter session set plsql_native_library_dir='/home/orauser/lib';alter session set plsql_native_make_utility='gmake';alter session set plsql_native_make_file_name='/home/orauser/spnc_makefile.mk';alter session set plsql_compiler_flags='NATIVE';create or replace procedure hello_native_compilationasbegin dbms_output.put_line('Hello world'); select sysdate from dual;end;

Figure 3: Example use of Native Compilation
As the procedure is compiled, you see the various compilation and link commands being executed. The procedure is immediately available to call, and runs as a shared library directly within the Oracle process.
Limitations of Native CompilationWhen a package specification is compiled for native execution, the corresponding body should be compiled using the same settings. The Oracle provided debugging tools for PL/SQL do not handle procedures compiled for native execution.
When many procedures and packages (typically, over 5000) are compiled for native execution, having a large number of shared objects in a single directory might affect system performance. In this case, you can have the DBA set the initialization parameter PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT in the initialization file before creating the database or compiling the PL/SQL packages or procedures. Set this parameter to a value that makes sense for your environment and operating system, Oracle suggests 1000 but this seems like overkill to me. Once the parameter is set and the DB restarted, create subdirectories underneath the directory specified in the PLSQL_NATIVE_LIBRARY_DIR parameter. The subdirectories must be named d0, d1, d2 ... d999, up to the value specified for the subdirectory count. When the procedures are compiled for native execution, the DLLs will be automatically distributed among these subdirectories by the PL/SQL compiler.

Fast guide to solving common Oracle errors

2008-06-16T17:15:00.000+07:00

Stumped by an Oracle error? This fast guide is sure to have the answer!

SearchOracle.com has compiled a list of every expert response pertaining to Oracle errors so you can find the answers you need, quickly and easily!
If the error you're dealing with is not listed, or if your dilemma is not specifically addressed, just ask one of our experts for help and we'll add the response to this ever-growing guide to common Oracle errors.
Stop searching for the answer and start resolving the problem, download this fast guide to common Oracle errors now:http://oracle-tips.c.topica.com/maalmgXabG0XTbLGJrib/

By becoming a SearchOracle.com member you'll also gain access to learning guides on the following topics:
* Backup and recovery
* Becoming an Oracle Certified Professional
* Oracle security
* Performance tuning
* PL/SQL
* RDBMS fundamentals
* And more!
Become a SearchOracle.com member and get the answers to common Oracle errors today: http://oracle-tips.c.topica.com/maalmgXabG0XTbLGJrib/

Expert secrets on oradebug

2008-06-12T12:42:00.000+07:00

Oracle provides an internal and poorly documented utility called oradebug.The oradebug utility provides useful functions for debugging and tracing Oracle database errors and can also be quite handy for tracing SQL statements to output to the tkprof utility for analysis by Oracle technicians. However, there are some caveats with the use of the oradebug tool and it should be used only under the careful guidance of Oracle support to avoid potential damage to production and other critical Oracle databases.
Read more about Oracle debugging and tracing with the Oradebug utility here:http://oracle-tips.c.topica.com/maalAIOabHodCbLGJrib/

Determining the optimal number of Oracle RAC nodes

2008-06-12T12:39:00.000+07:00

Question: We are using RAC for High Availability. What is better two nodes or three nodes? It's been suggested that "the more nodes, the better". What is the optimal number of RAC nodes? Answer: First, remember that RAC only protects against server failure, and you must still mirror the disk and have network redundancy for guaranteed 100% availability. In my experience, RAC is not as good as some other options for scalability (i.e. the "scale up" approach), and the most common use of RAC is for HA, and covering server failover.In deciding about the number of nodes for HA, just multiply the Mean Time Between Failure's (MTBF) for each node together and get an overall probability of failure. It's all about covering the probability of server failure. When used exclusively for HA, two-node RAC is ideal, especially when the nodes are geographically distributed and connected via super-fast dark fiber networks. Oracle ACE Andy Kerber notes that two node clusters are ideal under some circumstances: "Well, 3 nodes can be better than two if your workload is fully scalable, and there is no resource contention among the three nodes. I would venture to say that the majority of RAC systems are 2 node clusters, and are set up that way as part of a HA system instead of a workload scaling system."Read more about choosing the optimal number of RAC nodes here:http://oracle-tips.c.topica.com/maalM2iabHLsnbLGJrib/

Secrets for optimizing Oracle table inserts

2008-06-12T12:38:00.000+07:00

When loading large-volumes of data, you have several choices:1 - Tune INSERTS - Parallelized programs doing concurrent INSERT statements2 - Tune SQL*Loader - Using sqlldr Direct Load, and adjusting parameters improves INSERT performance.3 - Tune imports - Use Oracle Data Pump (Formally Oracle import utility) - Here are tips for hypercharging Oracle import.Optimizing Oracle INSERT performanceWhen using standard SQL statements to load Oracle data tables, there are several tuning approaches:a - Manage segment header contention for parallel inserts - Make sure to define multiple freelist (or freelist groups) to remove contention for the table header. Multiple freelists add additional segment header blocks, removing the bottleneck. You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML, but ASSM has some limitations.
Read more about secrets for optimizing Oracle table inserts here:http://oracle-tips.c.topica.com/maalfseabGRCBbLGJrib/

Understanding automatically tuned multi-block reads

2008-06-12T12:31:00.000+07:00

Question: I did play around with different settings of db_file_multiblock_read_count within 10g with no appreciable results. My setting of the db_file_multiblock_read_count parameter had almost no effect on query performance, I guess this is related to a read ahead cache on os/filesystem/disk device level with at least 128 blocks.
I am concerned about switching to sequential reads with setting db_file_multiblock_read_count=0, using the new 10g feature "Automatically Tuned Multiblock Reads".
Is it already reliable to use "Automatically Tuned Multiblock Reads" with 10g or should one still run with an as high as possible setting for db_file_multiblock_read_count? Are there any drawbacks having db_file_multiblock_read_count on a high value ?
Find the answer to this question and more information on automatically tuned multi-block reads here:http://oracle-tips.c.topica.com/maakPNjabGerxbLGJrib/

Using STATSPACK to monitor Oracle performance

2008-06-07T15:44:00.000+07:00

The Oracle STATSPACK utility (and it's successor, AWR), is one of the most exciting Oracle enhancements. With STATSPACK and AWR, the Oracle professional now has an easy-to-use tool that can be quickly installed and provides a great deal of performance information about an Oracle database. This article will introduce you to the STATSPACK and AWR utility and show you how to put it to work.

To find out how to implement STATSPACK and AWR here:

http://dba-oracle.com/art_statspack.htm

Oracle log buffer optimization techniques

2008-06-07T15:39:00.000+07:00

Overview of redo log tuning

The steps for tuning redo log performance are straightforward:

1 - Determine the optimal sizing of the log_buffer.

2 - Size online redo logs to control the frequency of log switches and minimize system waits.

3 - Optimize the redo log disk to prevent bottlenecks. In high-update databases, no amount of disk tuning may relieve redo log bottlenecks, because Oracle must push all updates, for all disks, into a single redo location.

Once you have optimized your redo and I/O sub-system, you have few options to relieve redo-induced contention. This can be overcome by employing super-fast solid-state disk for your online redo log files, since SSD has far greater bandwidth than platter disk.

Optimizing the log_buffer region

The log_buffer is one of the most complex of the Oracle RAM region parameters to optimize, but it's a low-resource parameter (only using a few meg of RAM), so the goal in sizing log_buffer is to set a value that results in the least overall amount of log-related wait events.

The big issue with the log buffer is determining the optimal sizing for the log_buffer in a busy, high-DML database. Common wait events related to a too-small log_buffer size include high "redo log space requests" and a too-large log_buffer may result in high "log file sync" waits.

Read more about Oracle log buffer optimization techniques here:

http://oracle-tips.c.topica.com/maalfrWabGRCsbLGJrib/