"Just because you have a choice, it doesn't mean that any of them 'has' to be right."
Norton Juster, The Phantom Tollbooth
In an Oracle forum that I frequent a user posted that he found cardinality values to be severely inflated when using an analytic function. This user posted an example that will be used here to demonstrate the problem. It will also be used to demonstrate another issue that can make this problem (more...)
"There are no wrong roads to anywhere."
- Norton Juster, The Phantom Tollbooth
Oracle can do some, well, strange things when fixing issues caused by the optimizer. For 10g releases up to 10.2.0.x Oracle chose to silently ignore a message and eliminate an outer join on the MERGE statement under certain conditions. Let’s examine this and see exactly what Oracle implements.
Occasionally in a 10046 trace file generated from 10g releases (more...)
A very interesting question was posted in one of the forums I participate in:
is there any way how I can revoke the right from a normal user to change it's own password in the database?
This, obviously, sparked a fairly lively thread (which I will not re-post here) regarding security and the underlying reason such a request was made. The user who posted the original question continued in the thread to explain:
Of course (more...)
“You can swim all day in the Sea of Knowledge and not get wet.”
― Norton Juster, The Phantom Tollbooth
In previous posts compression options have been discussed, and now it’s time to see how Oracle performs basic compression. It isn’t really compression, it’e de-duplication, but it does result in space savings for data that won’t be modified after it’s ‘compressed’. Let’s look at how Oracle saves space with your data.
Oracle de-duplicates the (more...)
A recent MOS document discusses, albeit briefly, an issue with AWR reports that’s been going on since 10.2.0, namely that the elapsed time numbers for queries executed in parallel are considerably greater than the actual elapsed clock time. Let’s look at why that is and what can be done about it.
AWR reports were a tremendous improvement over Statspack reports, primarily due to the depth and breadth of the sample data collected. A (more...)
In a forum I contribute to the following question was asked:
Can adaptive cursor sharing (ACS) depend on execution order ?
The issue described a relatively basic query that changed execution plans apparently due to the order the query statements were run based on bind variable values. It’s an interesting issue that testing has verified. The tests are reproduced below, in abbreviated form, so let’s look at what was executed and what execution plans were used.
It would appear that some DBAs are still using the optimizer_index_cost_adj parameter to make index access paths more ‘desirable’ to the optimizer. In decades past this might have been a good strategy however with the improvement in statistics gathering in recent relesaes of Oracle this might not be the case. Let’s look at an example to see why this might do more ‘harm’ than good.
The optimizer_index_cost_adj parameter was first provided in Oracle 9i as (more...)
An interesting ‘problem’ surfaced a while ago, one where a user with zero quota on every tablespace could successfully create tables. Of course once it was time to insert data the inserts failed, but this was confusing the user creating the tables. The ‘problem’ stems from enabling deferred segment creation in the database. Let’s see how that can create a confusing situation,
Deferred segment creation allows tables and indexes to be created without physical segments (more...)
Oracle 12c (version 188.8.131.52) offers the option of using in-memory processing to speed things along. Called the In-memory option it’s installed when you install the 184.108.40.206 software. Using it can make a considerable difference in processing speed, provided you have sufficient resources (RAM) available. Let’s revisit an older example, on Bloom filters, and see if Oracle processes things any faster in-memory.
Looking again at the Bloom filter example using (more...)
An interesting issue presented itself just recently with a logical standby database I manage. The database is used for generating reports and the client wanted to skip all DML activity for a given schema as it wasn’t necessary for reporting purposes. I had done this in version 10.2.0.x; it was a simple procedure on a low-traffic database:
alter database stop logical standby apply;
alter database start logical standby apply;