I’ve been working on a few test cases and I’m in search of some real-world data. If your production Oracle database uses system statistics, either Workload Statistics or Noworkload Statistics, and you are willing to share them, please post a comment with the output from the following two queries: select version from v$instance; select...
Since the introduction of the Cost Based Optimizer (CBO) in Oracle 7.0, people have been both fascinated and terrified by it and the statistics that feed it. There has long been a belief that a degree in witchcraft or black magic is required to successfully work with the CBO. Some people feel this shroud...
One of the most common performance issues DBAs encounter are bad execution plans. Many try to resolve bad executions plans by setting optimizer related parameters or even hidden underscore parameters. Some even try to decipher a long and complex 10053 trace in hopes to find an answer. While changing parameters or analyzing a 10053...
Oracle OpenWorld 2007 has come and gone and from the Real-World Performance Group’s perspective we’d consider it a successful one. The content of this year’s presentations seems to have gone over quite well as shown by the number of hallway comments and emails we received. At least two Oracle bloggers have put up their...
In the Real-World Performance Roundtable, Part 2: The Optimizer, Schema Statistics, SQL Tuning at Oracle OpenWorld 2006, I worked an example of how the optimizer can have difficulty estimating the correct cardinality when there is data correlation. (The Zodiac example can be found on pages 46-49 of the presentation.) In Oracle 11g, there has...
The other day I was reading the 11g Database VLDB and Partitioning Guide and came across the below execution plan for a partial partition-wise join between sales and customers. --------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PX...
Here is some code to display the HIGH_VALUE/LOW_VALUE columns from USER_TAB_COL_STATISTICS which are stored as RAW datatypes. COLUMN_NAME LOW_VAL HIGH_VAL DATA_TYPE -------------------- ---------------- ---------------- --------- ORGANIZATION_ID 00D000000000062 00D300000000tgk CHAR UG_ID 00500000000008U 00GD0000000mBda CHAR USERS_ID 005000000000063 00G30000000mBcq CHAR IS_TRANSITIVE 0 1 CHAR SUPPRESS_RULES 0 1 CHAR
Many of you are aware of the Oracle 11g Database New Features and while some may be generally interested in new features, one area that I focus on is new features that yield gains in performance. Some of these features can be found in the General Server Performance section of the Oracle 11g Database...
For those of you who will be attending Oracle OpenWorld 2007 and have interests in the database performance area, I would recommend to you to try and attend the Real-World Performance Group Roundtable session. The session starts with a brief “Hot List” of performance issues that have been predominant in the field over the...
Recently, I’ve been quite busy with performance projects and haven’t had the spare time I would like to keep up on my blog. Now that those projects are behind me, I wanted to blog about the use of one feature that made a significant difference in performance on a number of queries for this...
Recent Comments