Troubleshooting

Category

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 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

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

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

Rebuilding indexes is an activity that you shouldn’t need to do often. There are always a few special cases – like when you’ve moved or recreated a table or if you’ve done a massive delete on a table – when it’s probably a reasonable idea but, in general, there are very few cases where there’s any great benefit to be… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Troubleshooting

Rolling Forward a Physical Standby Database Using the RECOVER Command A standby database is a transactionally-consistent copy of the production database. It enables production Oracle database to survive disasters and data corruption. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch a standby database to the production role, minimizing the downtime associated… Continue Reading →

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

In the final part of this series I want to cover one last source of Parallel Execution Skew: Skew caused by Outer Joins. If you have Outer Joins where the majority of foreign key values is NULL, this can lead to the following symptoms: From a processing point of view for a parallel outer join, the NULL value in the foreign… Continue Reading →

10g, 11g, 12c, All Things Oracle Full Articles, Database Administration, Database Development, Oracle Database, Performance Tuning, Troubleshooting

In this part of the series we will have a look at different approaches to how Parallel Execution Skew can be addressed via manual re-writes of an affected query. Let’s have a look at a couple of options. Approach 1: Mimic the new 12c skew aware feature Looking at the new 12c special skew aware distribution feature that I’ve introduced… Continue Reading →

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

Since Parallel Execution Skew is a common problem that can severely limit the scalability of Parallel Execution (see my previous article for a demonstration), Oracle has introduced a new feature in 12c that can address the problem automatically. That’s great, but unfortunately – at least in the initial 12c release – it only applies to a limited number of scenarios,… Continue Reading →

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