Global Temporary Table ( GTT) – key facts
The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior…
Read MoreOracle 12c SQL Plan Directives – Disable | Enable | Use As Hint
What is it? In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a…
Read MoreORA-20005: object statistics are locked (stattype = ALL) – Solution
During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table: SQL> execute…
Read MoreIgnore Duplicate Rows (ORA-00001) with the ignore_row_on_dupkey_index Hint
The simplest method is to add a hint to the query to overcome ORA-00001 Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values: insert /*+ ignore_row_on_dupkey_index ( acct (…
Read MoreCreate a Virtual Column:
A virtual column applies a function to a column in the table. The database only computes this at runtime. The value is not stored in the table. So instead of…
Read MoreHow to Fix ORA-01450: Maximum Key Length (6398) Exceeded Errors:
The smallest unit of data storage in Oracle Database is the block. This defaults to 8k (8,192 bytes). Each index entry must fit within one block. So the maximum size…
Read MoreHow to Find underscore_parameters in a database during Audit process
Impact: Unknown underscore parameters without proper advice from metalink, would impact the database. Solution: We need to ensure that we have proper advises from Oracle Support on those underscore parameters…
Read MorePerformance Tuning Using Active Session History
Diagnosing performance issues in old sessions is easy with an Oracle Database feature called Active Session History. Every second, Active Session History polls the database to identify the active sessions…
Read MoreHow to Read an Execution Plan
What Is an Execution Plan? When you send a query to the database, it’s the job of the SQL optimizer to figure out how to execute it, and that process…
Read MoreFaster way to Delete Lakhs of Rows from a Oracle Table
Deleting lots of rows can be slow. And there’s a chance it’ll take even longer because another session has locked the data you want to remove. We can do this…
Read More