As table size grows due to more data, the performance of insert, update, delete and select SQL operations, as well as maintenance operations tasks such as taking backups, index maintenance and update statistics may take longer to perform. In order to improve the performance of these operations it is therefore advisable to partition tables greater than 2GB in separate chunks…. Continue Reading →

All Things Oracle Full Articles

In Oracle, under certain circumstances, an implicit data type conversion precludes the use of indexes. Perhaps you have a vague idea of what an implicit data type conversion hierarchy is, and you might even ignore the subtlety of the implicit data type conversion direction within a query predicate expression. Hopefully this article will shed some light on this concept. 1…. Continue Reading →

All Things Oracle Full Articles

In an earlier article on investigating indexes I described some methods for looking at the internal structure of an index to get an idea of its current state of health. Although this type of knowledge helps you to make informed decisions about which indexes might need special treatment (like being permanently dropped, perhaps) it doesn’t immediately answer questions like: “what’s… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning, Troubleshooting

In Oracle Database 12c, you can set a column in table as invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command. By default, table columns are always visible. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, and so is not included in the “SELECT *” or “INSERT INTO VALUES”… Continue Reading →

12c, All Things Oracle Full Articles

In the first article of this series, we learned what query parsing is and what impact it has on query performance. In this part of the series, we shall continue the discussion and look at Soft Parsing of the query. Query Processing Workflow Revisited As we saw in the previous installment, a query has to undergo Hard Parsing at least… Continue Reading →

All Things Oracle Full Articles


Jonathan Lewis on 08 December 2016 with 0 comments

One of the sessions at the recent annual UKOUG technical conference (Tech16) was a panel session on optimisation for which I had posted a request for a few advance questions, and one of the questions that appeared in my inbox was as follows: I have a report which is a union all of 27 versions of the statement: select ‘ColumnX_name’… Continue Reading →

All Things Oracle Full Articles

Recently I wanted to create a policy managed database in my flex cluster setup, but free servers are not available. Hence I had to unregister an administrator managed RAC database (amdbh12) to make the servers available. Having completed my testing, I dropped the policy managed database and then wanted to drop the database amdbh12 using DBCA. It was then… Continue Reading →

All Things Oracle Full Articles

In this fifth article in our series on JSON and the Oracle database we’ll be pulling the curtains back on the magic tricks that can convert your boring old relational data to JSON. OK, maybe that’s overselling it a little. Obviously there’s no magic involved; indeed some may say it’s anything but. But before we get to all of that,… Continue Reading →

All Things Oracle Full Articles

Oracle In-Memory is fully compatible with Oracle multitenant database architecture, allowing us to take advantage of fast In-Memory and low-cost storage. This multitenant architecture was also introduced in Oracle 12c, featuring multiple pluggable databases (PDBs) within a container database (CDB). This makes it easy to consolidate databases onto the cloud, deliver a high density of schema-based consolidation without making changes… Continue Reading →

All Things Oracle Full Articles

Hacking Indexes

Jonathan Lewis on 03 October 2016 with 3 comments

Indexes are expensive to maintain so we want to get the greatest benefit we can from them and make sure that Oracle uses them whenever it’s appropriate. Sometimes, though, the optimizer seems to ignore indexes that we think would be very effective and when this happens it’s often because the statistics that Oracle has gathered do not reflect the quality… Continue Reading →

All Things Oracle Full Articles, Performance Tuning, Troubleshooting