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
Month: January 2023
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
Viewing the Library Cache Statistics
Add Space to shared pool The library cache miss ratio tells the DBA whether or not to add space to the shared pool, and it represents the…
View More Viewing the Library Cache Statistics
Oracle/ SQL Wait Events
1. What are wait events? Wait events are characteristic parts of the Oracle kernel source code that can contain Oracle sessions during execution. Oracle provides statistical information…
View More Oracle/ SQL Wait Events
Streams Pool
Sometimes conventional wisdom isn’t so conventional, or common. As a case-in-point DBAs may believe that the STREAMS pool is reserved strictly for streams processes. That…
View More Streams Pool
What is SQL PROFILE in oracle?
You might have heard of SQL profile and SQL baselines in performance tuning chapters. Lets today go through details about sql profile. What is SQL…
View More What is SQL PROFILE in oracle?
Partition Pruning and NLS Settings
NLS (National Language Support) parameters are very useful for multilingual applications in an Oracle database. But it is important to understand their impact on query…
View More Partition Pruning and NLS Settings
How to Lock, Unlock, Transfer Statistics in oracle
Locking Stats To prevent statistics being overwritten, you can lock the stats at schema, table or partition level. EXEC DBMS_STATS.lock_schema_stats(‘SCOTT’); EXEC DBMS_STATS.lock_table_stats(‘SCOTT’, ‘EMP’); EXEC DBMS_STATS.lock_partition_stats(‘SCOTT’,…
View More How to Lock, Unlock, Transfer Statistics in oracle
Finding Empty Tablespaces in Oracle
I’m given a task to find the empty tablespaces.I’ve checked dev server and majority of the tables’ stats were locked.This solution takes time but works.…
View More Finding Empty Tablespaces in Oracle