Top Oracle technical articles

For Oracle support & training call (800) 766-1884

Recommended Oracle Articles

Extend STATSPACK to Capture disk I/O One of the current shortcomings of STATSPACK is that it cannot directly monitor disk input and output (I/O). Here's a great technique that I use for extending the capabilities of Oracle's STATSPACK performance utility

Oracle SQL tuning with column histograms The distribution of values within an index will often affect the cost-based optimizers (CBOs) decision whether to use an index or perform a full-table scan to satisfy a query. This can happen whenever the column referenced within a SQL query WHERE clause has

Optimize SQL query speed with the Oracle clustering_factor attribute The cost-based optimizer (CBO) improves with each new release of Oracle, and the most current enhancement with Oracle9i is the consideration of external influences (CPU cost and I/O cost) when formulating an execution plan. As Oracle evolves into Oracle10g we may see even more improvements in the ability of the CBO to always get the optimal execution plan for a query, but in the meantime, every Oracle developer must understand these mechanisms to properly tune her SQL.

What to expect when you migrate to Oracle9i With the impending desupport of Oracle8i in December 2003, many shops are preparing to make their move to Oracle9i. So, what can you expect when you upgrade to Oracle9i? Actually, Oracle9i is very stable and most shops are quite thrilled with the performance improvements and new features. Let’s take a closer look at what you should expect after you migrate.

UNISYS Road Show: Oracle 64-bit Technology I'm doing a road show in New York, Dallas, San Francisco and Chicago next week, and I'd like everyone who can, to stop over and say "Hi". UNISYS is sponsoring the shows and you get a free optical mouse for attending my session on "Oracle 64-bit Technology". Plus, I'll get a change to meet you personally and talk with you.

CEO Ellison reveals the Oracle10g vision of enterprise grid computing In his typical manic style, Oracle CEO Larry Ellison shared his vision of the future of enterprise computing last week at the OracleWorld conference in San Francisco. Always brilliant, and sometimes too far ahead of his time, Ellison laid the foundation for his version of grid computing: the enterprise grid computing architecture.

Hidden Oracle, part II: Using undocumented parameters In part 1 of this series we explored ways to extract undocumented packages, hints, utilities and executables from Oracle. Now we are ready to explore some ways that the undocumented Oracle parameters are used to aid the Oracle professional in managing and tuning their systems

Hidden Oracle, part I: Exploring undocumented utilities Each new release of Oracle brings new hidden utilities. They are sometimes used by internal Oracle development staff and left in Oracle for emergency support. Many of these undocumented utilities are very powerful but can be complex.

Audit design with Oracle fine-grained auditing The new HIPAA health care laws have placed a tremendous burden on enterprises using Oracle database systems. According to the law, organizations must provide complete audit trails for all DDL (i.e., schema changes), DML (e.g., updates, insert, deletes), and select audits of confidential patient information. HIPAA also requires that all health care companies (not just hospitals) archive audit trails of anyone who views patient data, and HIPAA provides severe penalties, including jail time, for companies that fail to implement select auditing. Hence, thousands of Oracle shops are rushing to implement Oracle auditing mechanisms.

Oracle Design Security from the ground-up The terms "open systems" and "security" can seem impossible to reconcile. Maintaining security for a centralized Oracle system is difficult enough, and when faced with networked databases over the Web, maintaining access and update security is a formidable challenge.

Control Access with Oracle Grant Security Let's start with the basics by looking at grant security to examine its benefits and pitfalls. The original relational model provides a method for granting privileges to users to allow for access control. This grant model was originally described by E.F. Codd and Chris Date in the original relational model and the model is standard across most commercial relational databases.

Achieve better control with Oracle's grant execute security model With the introduction of Java Development Kit (JDK) 1.1, the architecture of the Abstract Window Toolkit (AWT) has significantly improved -- especially in the event model and the ability to create lightweight components

Establish security policy with Oracle virtual private database In previous installments of this series on Oracle security, we examined Oracle grant security and grant execute security, noting the advantages and shortcomings of each approach. Now let's look at another Oracle security alternative, the virtual private database (VPD).

Choose the proper Oracle design for auditing More than ever, companies are demanding complete audit trails for everything that happens within an Oracle database. Managers want to see audits of all aspects of the database, and government regulations are spurring this interest.

Auto-Tuning Oracle Database 10g: Oracle SGA Oracle Database 10g supports the automation of more routine DBA tasks than ever before. The techniques described here can help you begin to implement Oracle SGA self-tuning right away

Track CPU and I/O cost with Oracle9i With each new release of Oracle, the database becomes more aware if its external environment. Oracle sets several important initialization parameters based on the number of CPUs on the Oracle server, and Oracle is now becoming more aware of the costs of CPU cycles and I/O operations.

Design Validation and Cost Estimation The requirements validation is performed to ensure that the system meets the infrastructure requirements of the end-user. A requirements evaluation is a simple assessment and mapping of the components of all inputs, data storage and outputs to ensure that al data items are present.

Expert offers tips on securing Oracle databases The database industry is still "plagued with substandard security," according to Oracle expert and author Don Burleson, who advises database administrators to limit access to their servers as a starting point in securing their systems.

Achieve faster SQL performance with dbms_stats When a SQL statement is executed, the database must convert the query into an execution plan and choose the best way to retrieve the data. For Oracle, each SQL query has many choices for execution plans, including which index to use to retrieve table row, what order in which to join multiple tables together, and which internal join methods to use (Oracle has nested loop joins, hash joins, star joins, and sort merge join methods). These execution plans are computed by the Oracle cost-based SQL optimizer commonly known as the CBO.

Is your Oracle system compliant with Federal Law? As part of my volunteer work as founder of the Guide Horse Foundation, (a charity to provide Guide Horses for the blind: www.guidehorse.org ), I have frequent interaction with visually impaired people all across the USA. During these interactions, I because aware of an important legal exposure for Oracle and Oracle9iAS developers. Many Oracle systems have front-end interfaces that are extremely difficult (if not impossible) for a visually impaired person to use.

Get Oracle schema DDL syntax with dbms_metadata Oracle professionals must frequently "punch," or extract, table and index definitions from Oracle and move them to different systems. Extracting Data Definition Language (DDL) for Oracle schema objects (e.g., tables, indexes, stored procedures, triggers, sequences, etc.) from the dictionary is very useful when you're migrating a system to a new platform and you want to pre-create the objects in a new tablespace.

Cost Control: Inside the Oracle Optimizer 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.

Oracle Dynamic Grid Computing On the OracleWorld Web page, we see the announcement that the next generation of Oracle will be unveiled by Larry Ellison Tuesday, September 9th from 2:00 — 3:00 p.m. It states:

A Four-phase Approach to Procedural Multi-master Replication Do you support customers whose databases are updated by users in multiple locations and across multiple time zones? If so, the challenge for the DBA is how best to manage replicated systems that allow for fast database access over Wide Area Networks.

Oracle Linux vs. Oracle Windows Intel-based servers are now moving out of the realm of personal computing and are morphing into industrial-strength servers. The low cost of Intel-based servers is taking the IT industry by storm. With costs as little as one-tenth of proprietary UNIX such as AIX, HP-UX, and Solaris, companies are saving millions of dollars by migrating to Intel platforms.

Take Advantage of Multiple CPUs with Oracle Parallel Query One of the latest trends is for systems to have more and more CPUs inside a single server. Using symmetric multiprocessing (SMP) servers, it is not uncommon for an Oracle server to have 8, 16, or 32 CPUs, along with many gigabytes of RAM for the Oracle SGA regions.

Oracle Blocksize and Index Tree Structures Each data block within the Oracle index serves as a “node” in the index tree, with the bottom nodes (leaf blocks) containing pairs of symbolic keys and ROWID values. To properly manage the blocks, Oracle controls the allocation of pointers within each data block. As an Oracle tree grows (by inserting rows into the table), Oracle fills the block, and when full, it splits, creating new index nodes (data blocks) to manage the symbolic keys within the index.

Introduction to Oracle Tuning Get the detailed information you need to effectively tune databases in the most complex Oracle environments.

Perfect Pitch The ability to analyze and correct Oracle Database physical read wait events is critical in any tuning project. The majority of activity in a database involves reading data, so this type of tuning can have a huge, positive impact on performance.

Check the efficiency of Oracle caching with this report One of the most important areas of Oracle tuning is the management of the RAM data buffers. Oracle performance will be dramatically improved if you can avoid a physical disk I/O by storing a data block inside the RAM memory of the data buffer caches.

Oracle9i Data Security Internals Back in the days of Oracle7, Oracle security was a relatively trivial matter. Individual access privileges were granted to individual users, and this simple coupling of privileges-to-users comprised the entire security scheme of the Oracle database. However, with Oracle's expansion into enterprise data security, the scope of Oracle security software has broadened.

How to get an Oracle DBA job In just 48 hours, 120 resumes from Oracle DBAs landed on the desk of Debbie Reames, a senior technical recruiter for Los Angeles-based staffing company Commercial Programming Systems. So Reames did what a lot of recruiters do. She wiped out half the pile in a three-step process. Need an H-1B visa? Gone. Need to relocate? Not for this job. Only worked at small companies? This job might be too big for you.

Tweak Oracle data buffer parameters to cache entire databases In this advanced Oracle article, I will explore the internal mechanisms of the Oracle data buffers, the RAM that Oracle uses to prevent unnecessary rereads of data blocks from disk. Understanding how Oracle’s data buffers operate is an important key to successfully using them in performance tuning a database.

Three ways to speed up SQL execution in Oracle Oracle provides several methods for reducing the time spent parsing Oracle SQL statements, which can cause a drag on performance when executing complex queries with a large number of possible execution plans. Let’s briefly examine some of these methods.

Introducing Database Area Networks Corporations have invested millions of dollars in computing hardware. As a result, managing server resources has become extremely challenging. This article explores a new approach to Oracle technology that “virtualizes” the database layer, allowing for transparent relocation of Oracle database instances onto the most available servers, and enabling optimal utilization and just-in-time expansion of server resources. To fully comprehend the solution, you must first understand the problem.

Proactively Monitor Oracle9i performance via scripts and queries With over 250 configuration parameters and thousands of metrics to monitor, it’s no small task for Oracle administrators to monitor the overall health of their Oracle databases. Oracle offers a variety of tools for performance monitoring, but there are quite a few of these as well. To be able to effectively monitor the health of your Oracle database, you’ll need to be familiar with the following scripts and queries:

How Oracle9i supports dynamic reconfiguration In my opinion, the single most important new feature of Oracle9i is the ability to dynamically modify almost all of Oracle’s performance parameters. This lets an Oracle professional dynamically reconfigure the Oracle instance while it's running, whether in reaction to a current performance problem or in anticipation of an impending performance demand.

Inside Oracle9i Tablespace Management Over the past few releases Oracle has been automating and improving the internal administration of tables and indexes. It has gradually recognized the benefits of bitmap data structures in all areas of the database.

Boost Oracle Performance with Intelligent Optimizer Limits The most important component of Oracle SQL execution time is the time spent preparing a new SQL statement for execution. But by understanding the internal mechanisms of the generation of the execution plan, you can control the amount of time Oracle spends evaluating table join order, and boost the performance of queries in general.

An Enterprise Security Primer For many system administrators, the terms "open systems" and "security" can seem impossibly opposite. Maintaining security for a centralized database system is difficult enough, and when faced with a network of networked databases, maintaining a level of access and update security is a formidable challenge. Security is often an afterthought, and the database industry is plagued with sub-standard security, especially for enterprise databases that are cobbled-together as a result of external factors such as corporate acquisitions.

Oracle9i's automatic segment space management improves segment storage internals To keep its position as the most powerful and flexible database, Oracle has been creating new mechanisms to simplify and block storage of tables and indexes over the last few releases. Starting in Oracle8i, Oracle began to automate the management of objects within a tablespace. The first enhancement was called locally managed tablespaces (or LMTs). In an LMT, Oracle moves the tablespace information out of the data dictionary tablespace and stores it directly within the tablespace itself. This has become a de facto standard within Oracle9i because it relieves data dictionary contention.

Using Oracle9i to Create Custom Web Content Savvy Web sites are watching every move of their customers, tracking their page viewing preferences and buying habits. This tracking is fed into sophisticated correlation engines that can accurately predict those products and services that the customer is likely to buy. Using this information, the Web page content is customized. We are seeing the dawn of applied artificial intelligence in eCommerce, and the major database vendors are creating products to help.

Automatic Space Management improves table management in Oracle9i Over the past few years, Oracle has gradually recognized the benefits of bitmap data structures. As Oracle has evolved, we’ve seen the following progressive introduction of bitmaps into the database engine:

Use Oracle9i full-index scans to read data quickly In keeping with Oracle’s commitment to add intelligence to SQL query optimization, the full-index SQL execution plan has been enhanced in Oracle9i to provide support for function-based indexes (FBIs). With Oracle8, intelligence was added to the SQL optimizer to determine if a query might be resolved exclusively within an existing index. Oracle’s index-organized table (IOT) structure is an excellent example of how Oracle is able to bypass table access whenever an index exists. In an IOT struct

Oracle9i database parameters reduce downtime Sophisticated database management systems such as Oracle need maintenance, and many database maintenance procedures require you to modify the database system’s parameters. Because Oracle is so flexible, it requires over 250 documented parameters and nearly 100 undocumented parameters. Some of these parameters require you to stop and restart the database, while others let you make changes without downtime. In this Daily Feature, I’ll point out some of the common Oracle parameter types and show you what they do.

DBAs advance with Burlesons Oracle9i Tips Corporations have invested millions of dollars in computing hardware. As a result, managing server resources has become extremely challenging. This article explores a new approach to Oracle technology that “virtualizes” the database layer, allowing for transparent relocation of Oracle database instances onto the most available servers, and enabling optimal utilization and just-in-time expansion of server resources. To fully comprehend the solution, you must first understand the problem.

Using Oracle9i cache Advice The ability to compute the optimal size of the data buffers is a critical task for large databases. As databases grow to hundreds of billions of bytes, it becomes economically impractical to cache the entire database in RAM. So Oracle professionals must find the point of diminishing marginal returns for the addition of RAM resources. Being able to

Steps you can take to optimizer your Oracle servers One of the primary jobs of a database administrator is to predict when the system will need additional machine resources and ensure that it gets those resources before the database experiences performance problems. To meet this duty, the administrator must have the information that shows how the database server consumes resources. In this Daily Feature, I’ll show you how to gather the data you need to form a cohesive picture of server performance and use that information to keep your database from bogging down.

How to use Oracle9i bitmap join indexes Oracle9i has added the bitmap join index to its mind-boggling array of table join methods. This new table access method requires that you create an index that performs the join at index creation time and that creates a bitmap index of the keys used in the join. But unlike most relational database indexes, the indexed columns don't reside in the table. Oracle has revolutionized index creation by allowing a WHERE clause to be included in the index

Optimizing Oracle RAM Usage The goal of server optimization for any Oracle databases is to manage the RAM and CPU resources of the machine, and make sure that expensive RAM is not under-allocated. When we talk about optimizing Oracle database performance in an MS-Windows environment, the techniques that we use are very similar to those used on larger UNIX platforms.

Oracle Web Hacker Tricks Hardly a week goes by that we do not see a news report about a major web-based application being hacked. Since most of these system use Oracle, the savvy DBA must be on the alert for attempts to hack into the database server. As we will see, a hacker who can get onto your database server can change the UNIX file permission on the Oracle data files, and completely bypass Oracle security.

Auditing Oracle9i security Oracle9i now has three totally separate and distinct ways for managing data access.

Oracle9i provides enhanced views on RAM usage Prior to Oracle9i, most relational databases couldn't show the individual RAM memory usage for processes connected to a database. Because many performance problems are directly related to a shortage of RAM memory, it's important for the database professional to see RAM memory utilization within the database, both for connected sessions and the database processes.

Oracle Wait Analysis Techniques Corporations have invested millions of dollars in computing hardware. As a result, managing server resources has become extremely challenging. This article explores a new approach to Oracle technology that “virtualizes” the database layer, allowing for transparent relocation of Oracle database instances onto the most available servers, and enabling optimal utilization and just-in-time expansion of server resources. To fully comprehend the solution, you must first understand the problem.

Creating tablespaces with multiple blocksizes Oracle9i supports multiple data buffer sizes, which you can use to segment and partition disk I/O patterns within the database. Oracle9i also allows segregated RAM memory regions for multiple block sizes, giving you complete control over disk I/O patterns. Let’s take a closer look at how you can create data files with different block sizes and move Oracle tables and indexes into partitioned RAM data buffers.

Lock down database security in your Web-enabled apps Many managers are concerned about opening up mission-critical applications to the Internet. With dozens of potential entry points and almost daily news about large companies being hacked, proper database security is critical. In general, security concerns over Internet access are similar to security issues in an internal network.

Dynamic Adjustment of the Oracle9i SGA The ability to dynamically reconfigure Oracle9i was the most exciting new feature of the new version's release, propelling Oracle into the world of true 24/7 systems for shops that need constant availability. But there's another important benefit to dynamic parameters. Because all 250 Oracle parameters can now be changed in real time, Oracle DBAs can reconfigure the database at any time, a

Measure Oracle database server performance with vmstat When deploying Oracle as the database server platform in your environment, one of the most important things you should be aware of is your server’s performance. While Oracle includes some nice tools to help track server performance, the built-in utilities don’t give you all the information you need. In this Daily Feature, I’ll show you how to effectively measure Oracle database server performance using vmstat.

Tablespace creation is a snap with Oracle Managed Files Oracle9i introduces a new feature that simplifies tablespace creation. This new feature, Oracle Managed Files (OMF), makes

Introducing DAN - A New Database Architecture Database management systems generally run on only one server at a time. Without sophisticated solutions such as Oracle’s Real Application Clusters, databases must be manually moved between servers. This limitation has led to a situation in which huge amounts of computer resources are wasted because it's difficult to balance server load at the database level.

Scalar subqueries simplify complex SQL statements Scalar subqueries are a powerful enhancement to Oracle9i SQL. They allow for quick formulation of extremely complex SQL statements. Oracle’s introduction of scalar subquery support is another example of the company’s commitment to keeping pace with the evolution of the SQL language.

Database Benchmark Wars: What you need to know With a flood of advertising from leading database platform providers like Microsoft, Oracle, Sybase, and Informix, development managers may find themselves fishing for reliable benchmarking figures for relational database management systems (RDBMS).

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Fast-Track Oracle Support
PO Box 511
Kittrell, NC 27544

Email BC: