Since the introduction of 11.2 I receive on a regular basis questions related to “strange” workload system statistics. The last email on that topic was sent to me yesterday. So, instead to send, again, a private reply, I decided to write this short post. What’s wrong with 11.2 and workload system statistics? Let’s have a […]
TKPROF New Features in 11gR2 – 11.2.0.2
The release 11.2.0.2 not only provides a lot of documented new features, but also provides undocumented ones. Today, I would like to spend few words about the undocumented changes introduced in the TKPROF output. In this area it is interesting to point out that after the introduction of wait events in Oracle9i, there were really […]
TKPROF New Features in 11gR2 – Release 11.2.0.1
While writing a post about the TKPROF new features in 11.2.0.2 I noticed that I didn’t write one about an important change introduced in 11.2.0.1. So, before finishing the other one, let’s have a look to what changed in 11.2.0.1. One problem with TKPROF up to 11gR1 is that when the AGGREGATE parameter is set […]
DBMS_AUTO_SQLTUNE: ORA-01748 and Documentation Bugs
As of 11.2.0.2 a new package, DBMS_AUTO_SQLTUNE, is available for accessing and configuring Automatic SQL Tuning. The package provides three features: Execution of the Automatic SQL Tuning task (EXECUTE_AUTO_TUNING_TASK) Generation of a report showing the output generated by the Automatic SQL Tuning tasks (REPORT_AUTO_TUNING_TASK) Configuration of the Automatic SQL Tuning parameters (SET_AUTO_TUNING_TASK_PARAMETER) In this post […]
Deferred Segment Creation as of 11.2.0.2
One year ago I wrote a post entitled Deferred Segment Creation. If you read the comments related to it you can see that several people were concerned by the fact that it was not possible to easily get rid of segments associated to empty tables. That was with version 11.2.0.1. Now we have version 11.2.0.2 […]
Parallel Full Table Scans Do Not Always Perform Direct Reads
Even though in general parallel full table scans performs direct reads, some exceptions exist. The aim of this post is to show such an exception. For test purposes I build in my own schema a copy of the SH.SALES table (the one distributed by Oracle with the demo schemas…). On that table I build an […]
Parallel Processing With Standard Edition
As clearly stated in the Licensing Information guide, all features related to parallel processing (parallel backup and recovery, parallel query/DML, parallel statistics gathering, parallel index build/scans, parallel Data Pump export/import, in-memory parallel execution, parallel statement queuing and parallel spatial index builds) are only available with the Enterprise Edition. However, as of Oracle Database 11g Release […]
Exadata Storage Server and the Query Optimizer – Part 4
When I started writing the series of posts about Exadata Storage Server and the query optimizer, I didn’t expect to write more than three posts (part 1, part 2, part 3). Of course, things change. Hence, here is part 4 to cover a couple of things that I learned in the last couple of months. […]
Partially Index a Table
Recenty the following question was posted on oracle-l (I paraphrase…): With Oracle Database it is possible to create something similar to Teradata’s sparse indexes? Since the question is an interesting one, I decided to write this short post. First of all, I have to say that such a feature is not supported by the CREATE […]
Partition-Wise Join of List-Partitioned Tables
When two tables are equi-partitioned on their join keys, the query optimizer is able to take advantage of partition-wise joins. To make sure that the tables are equi-partitioned, as of Oracle Database 11g reference partitioning can be used. In fact, per definition, with reference partitioning all “related” tables have exactly the same partitioning schema. If […]