In Oracle 19c, a new sub-program called COMPARE_PLANS has been added to DBMS_XPLAN. With this new functionality, we can take a reference plan and compare it with some other execution plan – it could belong to a SQL statement already executed where we obtain the plan from the Cursor Cache or from the AWR history […]
Automatic Indexing is a new feature in Oracle 19c which automatically creates, rebuilds, and drops indexes in a database based on the application workload. The index management task is now dynamically performed by the database itself via a task which executes in the background every 15 minutes. Automatic indexing task analyzes the current workload and […]
In earlier releases no error was reported if an incorrect hint was used or if there was any syntax error in the hint usage. Tuning a sub-optimal execution plan became difficult and sometimes we are left wondering why a full table scan is still occurring when an INDEX hint has been specified! The database did […]
The automatic optimizer statistics collection job which calls DBMS_STATS package runs in predefined maintenance windows and these maintenance windows are open once a day during which various jobs including the gathering of statistics is performed. For volatile tables statistics can go stale between two consecutive executions of such automatic statistics collection jobs. The presence of […]
In data warehouse environment, we often have situations where tables are truncated and new data (often millions of rows) is loaded.But when reports are run against those tables with freshly loaded data unless we go and gather fresh statistics there is a possibility that the optimizer may choose a sub-optimal plan. So to address this […]
With Oracle Resource Manager, we had a way to limit and regulate use of resources like CPU and I/O as well as we had the ability to prevent the execution of any long running queries which exceeded a defined threshold. So we could ‘cancel’ or terminate a SQL query which was running longer than a […]
Real -Time Database Monitoring which is a new feature in Oracle 12c extends Real-Time SQL Monitoring which was a feature introduced in Oracle 11g. The main difference is related to the fact that SQL Monitoring only applies to a single SQL statement . Very often we run batch jobs and those batch jobs in turn […]
Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History V$ View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past. I would like to add […]
In releases prior to Oracle 10g, the gathering of statistics using DBMS_STATS resulted in immediate invalidations of dependent cached cursors. This was true unless NO_INVALIDATE parameter of the DBMS_STATS command was set to TRUE. It was felt that gathering of statistics could actually have a negative impact on performance because of the fact that invalidation […]
The CURSOR_SHARING parameter basically influences the extent to which SQL statements (or cursors) can be shared. The possible values are EXACT which is the default value and SIMILAR and FORCE. The official definitions of SIMILAR and FORCE are: cursor_sharing=similar: “Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, […]
Parsing is the first stage in processing a SQL statement – the other two being Execute and Fetch stages. Parse once – execute many is a very important performance tuning goal. What does parsing involve Syntax check – is the SQL statement syntactically correct Semantic check – is the SQL statement meaningful or semantically correct. […]
In one of my earlier posts, I had illustrated a use case of DBMS_ADVANCED_REWRITE and its use in cases where we cannot change the code, but can still influence and change the way the optimizer executes the same SQL statement. In case of many vendor provided and packaged applications, we do not have access to […]
In some cases we may find that gathering fresh statistics has led to the optimizer executing sub-optimal execution plans and we would like to restore the statistics as of a date when we had good performance. REMEMBER – we gather statistics to ensure that the optimizer chooses the optimal execution plans, but gathering statistics invalidates […]
We can use the DBMS_MVIEW.EXPLAIN_REWRITE package (which was first introduced in Oracle 9i if memory serves me right) to diagnose why Query redirection to a materialized view is not happening. We can redirect the output to a table called REWRITE_TABLE which is created via the utlxrw.sql script located under $ORACLE_HOME/rdbms/admin. Let us take a simple […]
One of the major concerns a DBA has related to performing a database upgrade to Oracle 11g is ‘Are Execution Plans Going To Change Post-Upgrade?’ I am quite sure we would have experienced some queries having different execution plans when we did earlier upgrades to both 9i as well as 10g. This is due to […]
The Cost Based optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. Based on this, the optimizer determines the selectivity of the WHERE clause condition as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no bind peeking takes place, and the […]
Very often we run AWR, ASH and ADDM reports which does highlight the Top SQL statements by disk reads, CPU usage and elapsed time. But an important piece of information is missing which is the Explain Plan. Using GUI tools like Enterprise Manager will enable us to drill down to the Explain Plan from an […]
In the Part 1 of this post, we saw how we can use the AWR history tables to compare the top wait events between two different time periods. We will now use the history tables to track and identify problem SQL statements. Suppose we have been informed that there was a I/O performance issue early […]
Using the DBA_HIST AWR history tables, we can compare the top wait events between different days or different time periods. Let us assume that we find that batch jobs which are executed at night particularly between 1 and 2 AM are experiencing performance issues. In this case we are comparing performance of a particular database […]
One of the critical aspects of any database upgrade is to ensure optimizer plan stability where tried and tested execution plans are not altered in any way by the Oracle optimizer after a database upgrade has taken place. One of the 11g new features which is SQL Plan Management and SQL Plan baselines enables us […]
