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
Tag: performance
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
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?
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
Understanding IOPS vs. throughput
Input/output operations per second (IOPS) and data throughput are two popular performance characteristics of any storage system. In the early days of computer technology, when…
View More Understanding IOPS vs. throughput
Selectivity vs Cardinality
What is selectivity? Selectivity is the estimated proportion of the rows that will return from our query based on the total rows of that table.…
View More Selectivity vs Cardinality
Column Histograms in Oracle 11g
Data skew in a column can make it difficult for the optimizer to accurately estimate the cardinality of an operation. Without a histogram it will…
View More Column Histograms in Oracle 11g