Busting the Oracle Myth Busters
The Oracle database of the early 1990s is very different from the database of the early 21st century. RAM was very expensive, disk was slow, and RAID was not yet popular for disk load balancing. As a direct consequence of these hardware limitations, the Oracle tuning rules that we used back in 1994 were very different than the rules we accept for Oracle tuning today.
Don't Let Referential Integrity Degrade Your Database Performance
Oracle databases can experience severe performance problems when foreign key constraints aren't indexed. Oracle Pro Boris Milrud shows you how to avoid performance issues during SQL queries and massive Data Manipulation Language operations in case those important indexes are missing...
Generic Architecture for Caching Table Data: Hello Cache, How Are You Doing?
Now that you have set up a functioning cache, are you sure it's giving you the performance enhancements you need? Keep reading to learn three ways to check the performance of your cache. At this stage, we have all the basic functionality in place for our cache to be useful and usable. We can read and write to it, refresh it, flush it and show its content.
Generic Architecture for Caching Table Data: Supercharge Your Cache
In the first part of this series we started of by putting the basic structures in place for a cache and wrote some code to manage the cache. In this next part, we will extend the functionality of our cache.
Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications
In this series I am going to introduce you to a generic PL/SQL caching architecture. The goal is to give you an understanding of how you can store Table data in PL/SQL collections (the cache) and how to retrieve data from those same PL/SQL collections. The proposed architecture will be generic enough for you to use throughout your applications everywhere you need to retrieve data from the database (and isn’t that what PL/SQL applications are all about?).
Improve Results with Query Rewrite
Are you tired of waiting for your queries to return results? Have you already tried adding indexes and tuning the SQL, and still can't improve query performance? Well, have you considered creating materialized views? With materialized views, reports that used to take hours to run can complete in minutes. A materialized view can contain joins and aggregates, providing a way of storing precomputed results.
Inside OCP: Efficient Space Management
Oracle Database 10g provides a variety of features for managing the space usage of a tablespace. These features include the monitoring of tablespace usage, the ability to suspend transactions that require more space and successfully resume them when the space is available, and a database advisor that lets you determine whether an object has space available for reclamation.
Inside OCP: Surveying Diagnostics
This article focuses on how AWR, ADDM, and the advisors work to make performance tuning easy. It presents sample questions of the type that you may encounter when taking the Oracle Database 10g Administration Workshop I exam. The Oracle Database 10g Administration Workshop I exam enables you to earn the Oracle Certified Associate (OCA) level of certification. Note that the sample question format has been adjusted for presentation in this column.
Inside Oracle10g Dynamic Sampling
One of the greatest problems with the Oracle Cost-based Optimizer (CBO) was not a problem with the CBO at all, but with the failure of the Oracle DBA to gather accurate schema statistics. Even with the dbms_stats package, the schema statistics were often stale, and the DBA did not always create histograms for skewed data columns and data columns that are used to estimate the size of SQL intermediate result sets.
More New Features for OCPs
This article focuses on the Automatic Shared Memory Management and Online Segment Shrink features of Oracle Database and presents sample questions of the type you may encounter when taking the Oracle Database 10g: New Features for Administrators exam. Note that the sample question format has been adjusted for presentation in this article.
Optimize the Massive DELETE Operation in Oracle, Part 2
Perform the massive DELETE operation as a swap delete with parallel execution. This technique can offer significant performance gains over a straight or in-place delete operation.
Oracle 10g AWR SQL Tuning Scripts
The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will explore these AWR tables and expose their secrets.
Oracle 10g dynamic sampling - table hint
One of the greatest problems with the Oracle Cost-based Optimizer (CBO) was not a problem with the CBO at all, but with the failure of the Oracle DBA to gather accurate schema statistics. Even with the dbms_stats package, the schema statistics were often stale, and the DBA did not always create histograms for skewed data columns and data columns that are used to estimate the size of SQL intermediate result sets.
Oracle Database 10g OLAP Performance Tips & Techniques
The OLAP Option to Oracle 10g gives you the ability to store multidimensional cubes of data in your Oracle database, and perform OLAP queries on them using OLAP DML, regular SQL or query tools such as Oracle Discoverer Plus OLAP. Part 1 and 2 of this article provides tips and best practices for designing, loading, aggregating and querying Oracle OLAP cubes and in addition takes a look at some of the new features coming with 10g Release 2.
Oracle Performance Tuning - Part 1
Performance tuning is a broad and somewhat complex topic area when it comes to Oracle databases. Two of the biggest questions faced by your average DBA concern where to start and what to do. All you may know is that someone (a user) reports a problem about a slow or poor performing application or query. Where do you even begin to start when faced with this situation?
Oracle Performance Tuning - Part 2
As mentioned in Part 1, there are several relatively easy steps you can take to improve performance. From the user's perspective, one of the most frequently used interfaces with a database involves SQL statements, so getting a handle on them is a good place to start in terms of being able to see an immediate improvement.
Oracle Performance Tuning - Part 3
As mentioned in Parts 1 and 2, there are several relatively easy steps you can take to improve performance. One of those steps involves using an automated tool to "guide" you in writing SQL statements. There are several vendors who manufacture analysis or performance tuning tools, and in the next two articles, we will look at one of them.
Oracle PGA pga_max_size undocumented parameter
Almost every Oracle professional agrees that the old-fashioned sort_area_size and hash_area_size parameters imposed a cumbersome one-size-fits-all approach to sorting and hash joins. Different tasks require different RAM areas, and the trick has been to allow enough PGA RAM for sorting and hash joins without having any high-resource task hog all of the PGA, to the exclusion of other users.
Run Oracle Stored Procedures in Parallel - inside the Database
If you need a solution for running Oracle stored procedures in parallel, don't reinvent the wheel with solutions that run outside the database. Execute stored procedures in parallel inside the database using Oracle's native APIs and PL/SQL.
Solving performance issues using Oracle Function-Based Indexes
Function-Based Index is one of the many new features
introduced in Oracle 8i. This functionality allows the database administrator to create indexes on functions or on even expressions. This article presents a practicla scenario where this new feature can help us.
Talking Tuning: Sharing Memory Automatically
Put away your scripts and let Oracle automatically resize your memory pools. It used to be a challenge to size the various memory pools that comprise the Oracle system global area (SGA) for optimal performance. But that was before Oracle Database 10g.
Talking Tuning: Tuning Undo Tablespace
Let Oracle do the math and right-size the undo tablespace for you. A company's quarter-end processing includes several long-running transactions. At the same time, several customer service representatives are updating information contained in the same underlying tables.
Tuning and Performance
Bigger blocks means more data transfer per I/O call. This usually leads to an increase in performance since the cost of I/O setup (disk seek + rotation, data transfer) makes up the majority of the cost of an I/O. The performance increase does not benefit just the data blocks. A bigger block size means more space for key storage in the branch nodes of B-tree indexes, which reduces index height and improves the performance of indexed queries.
Network World, an International Data Group publication, is the industry's only newsweekly shaping the future of network computing in the enterprise.
subscribe now
