Snapshots

Jonathan Lewis on 24 January 2017 with 0 comments

While looking at the results from a query against the dynamic performance view v$active_session_history during a recent trouble-shooting assignment I noticed a result that was so unusual that I decided I needed to get some information from the v$ash_info view over an interval of about 3 minutes – and it didn’t matter which three minutes in particular, any three (consecutive)… Continue Reading →

12c, All Things Oracle Full Articles, Oracle Database, Troubleshooting

In the previous post I described a problem with the strategy that the optimizer uses for costing an index fast full scan, and the alternative strategy that Oracle had supplied at some point in the 10g timeline to allow a more rational cost to be calculated in special cases. In an earlier post still I had described the problem with… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Troubleshooting

In the first and the second parts of this series, we learned what query parsing is, how hard and soft parsing work, what their impact on the query is, and then moved on to understand the concept of parent and child cursors. In this third article, we will look at the issue of duplicate SQL statements and how Bind Variables… Continue Reading →

All Things Oracle Full Articles, Performance Tuning

In my book “Cost Based Oracle – Fundamentals” (Apress 2005) I described a problem with the cost calculation for the index fast full scan: When you generate statistics for a table, one of the results is the number of blocks below the high water mark. When you generate the statistics for an index, you get the number of leaf blocks… Continue Reading →

10g, 11g, 12c, All Things Oracle Full Articles, Oracle Database, Troubleshooting

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

Unpivot

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