Oracle Database Product Manager with a passion for SQL, the Optimizer and performance.

How to identify which indexes can be safely dropped in Oracle?

The longer an application has been successfully run, the more likely you are to have indexes that are no longer used or beneficial. Removing these indexes not only saves space but can also improve the performance of any DML operations. But knowing which indexes can be safely dropped can be tricky. In Oracle Database 12c, things got a little easier with the introduction of a new view called DBA_INDEX_USAGE.

How to find differences between Oracle production and test envs?

Determining why things behave differently in production versus a test environment can be time-consuming and painful. That is until you realize there is a convenient package that allows you to compare the two environments quickly.

How to determine which view is the right view to use in Oracle?

Database views are a handy tool to help obfuscate complex joins from developers and analysts. However, knowing which view to use can be tricky, especially when faced with multiple views with similar-sounding names. After all, you don’t want to join various tables only to discover that all the information you needed was in just a single table.

How do I add a SLEEP to a PL/SQL Stored Procedure?

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. First up, DBMS_SESSION.SLEEP(). Oracle has always enabled you to add a sleep command to your stored procedures to suspend a session for a specified number of seconds, as shown in the code below.

How to create a parameterized view in Oracle?

Database views have been used for decades to help simplify both ad-hoc queries and reporting for developers, analysts, and end-users. But the problem with defining views is they tend to be either too specific (not easily reused) or not specific enough (too generic to be performant).