Continuing on from Parts 1, 2 and 3, we are now going to see how the optimizer comes up with the cardinality estimate after adding the index. I’ll be using some values and abbreviations from Part 3 so I suggest you make sure you have read and understood that before tackling this part. I’ll be …
OR Dear Me! When Cost Based OR Expansion Gets it Wrong – Part 4 Read More »
…continued from Part 1 and Part 2. OK, so next we are going to return to original query before with no index in place and review the optimizer’s estimate of how many rows will be returned, and try to deduce where it came from, then in Part 4 we’ll compare with the behavior after the …
OR Dear Me! When Cost Based OR Expansion Gets it Wrong – Part 3 Read More »
I promised in Part 1 that next we would drill into where the cardinality estimate for the non-transformed query comes about, but, never having been someone to refrain from kicking the Cost Based Optimizer when it’s down, I realised i can fool it even better with some skewed data, and a histogram. Note I am …
OR Dear Me! When Cost Based OR Expansion Gets it Wrong – Part 2 Read More »
The Cost Based OR Expansion transformation allows the optimizer to generate execution plans which often perform better than could be otherwise achieved. But sometimes, as in a case I have been recently investigating, it can cause things to go wrong. I have modeled the behavior and will demonstrate in it this article. Planned subsequent articles …
OR Dear Me! When Cost Based OR Expansion Gets it Wrong – Part 1 Read More »
We’ve been experiencing some issues with multiple child cursors for a given SQL statement, and I’ve just spent some time working on building a reproducible testcase of one problem that I thought I’d share in the hopes of documenting the behavior. It is a python script connecting to an Oracle Database (tested against 12.2 and …
We upgraded our first database from 12.2 to 19c last week, and encountered a nasty issue with ORDS. Credit goes to my colleagues Mingda Lu and Au Chun Kei for doing the hard work in understanding what was causing the issue. The issue can be demonstrated with the Oracle DB Developer VM. I have created a …
ORDS Issues in Oracle 19c? Check your Service Names Read More »
This is issue I always hit settitng up a test environment using Oracle REST Data Services (ORDS). Using SYSTEM user to call ords.enable_schema throws ORA-06598. According to the documentation it should succeed (note the SYSTEM user has DBA role) . Only database users with the DBA role can enable or disable a schema other than their …
ords.enable_schema fails with “ORA-06598: insufficient INHERIT PRIVILEGES privilege” Read More »
Warning please don’t blindly follow the steps here without doing your own analysis of the risks involved, and ideally without getting Oracle support involved, I was hesitant to publish this, but as I’ve been in contact with someone else and it’s helped them workaround (to some extent) an issue they have been having I think …
ORA-01017 starting DB with srvctl? Consider firing your (oracle) agent! Read More »
This is something to file under the (admittedly rather large) category of things that I wasn’t aware that the Oracle database could do. While tuning a query, I wanted to use a common technique of adding fields to an index to eliminate a “Table Access by Index RowID” operation, however this particular case was a …
A tip I picked up from Nigel Bayliss regards purging SQL Plan Directives, I’ve been using it a lot recently and can’t see it documented elsewhere. As some background these records, exposed via the DBA_SQL_PLAN_DIRECTIVES view, are cardinality corrections created and used when running with Adaptive Statistics enabled. There is a job that should automatically …