Sometimes it is required to flush the BAD SQL_PLAN from shared_pool so that new (or old) better execution plan can be picked by SQL_ID 1)…
View More Flush Bad SQL Plan from Shared Pool
Category: Oracle
Oracle
Oracle CBO is ignoring SYS_DL_CURSOR
Some days ago i came across with a pl/sql code where they used (for some reason) an Oracle hint called SYS_DL_CURSOR. I got it when i…
View More Oracle CBO is ignoring SYS_DL_CURSOR
APPEND_VALUES and SYS_DL_CURSOR Hints with HCC
The APPEND_VALUES hint was introduced in 11.2 to allow direct path inserts with variables using the VALUES clause. i.e. INSERT INTO XYZ (COL1, COL2) VALUES…
View More APPEND_VALUES and SYS_DL_CURSOR Hints with HCC
How to Increase MEMORY_TARGET
Before you can increase memory in oracle database, you must first understand four oracle memory parameters which govern the instance. SGA_TARGET and SGA_MAX_SIZE MEMORY_TARGET and…
View More How to Increase MEMORY_TARGET
Adaptive Cursor Sharing in Oracle Database 11g Release 1
DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less…
View More Adaptive Cursor Sharing in Oracle Database 11g Release 1
How to Swap SQL_Profiles?
The central idea behind “swapping” SQL profiles is simple. You define a SQL profile that specifies the SQL statement that you want to tune, and an…
View More How to Swap SQL_Profiles?
SQL Profiles in the Data Dictionary
In the paper SQL Profiles (page 22) I described the data dictionary tables where the hints belonging to SQL profiles are stored. For example, with the following…
View More SQL Profiles in the Data Dictionary
Useful Gather Statistics Commands In Oracle
This article contains all the useful gather statistics related commands. 1. Gather dictionary stats: — It gathers statistics for dictionary schemas ‘SYS’, ‘SYSTEM’ and other…
View More Useful Gather Statistics Commands In Oracle
CURSOR_SHARING=SIMILAR AND FORCE – SOME EXAMPLES
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…
View More CURSOR_SHARING=SIMILAR AND FORCE – SOME EXAMPLES
12c: Optimizer_Dynamic_Sampling = 11
With default sampling level of 2 (from 10g onwards) , dynamic sampling is performed only for the objects for which statistics do not exist. If…
View More 12c: Optimizer_Dynamic_Sampling = 11