Learn how to leverage Oracle DB Performance Tuning optimizer statistics for max performance! PART-1

What are Optimizer Statistics? Optimizer statistics are a collection of data that describe the database and the objects in the database. These statistics are used by the optimizer to choose the best execution plan for each SQL statement.
The optimizer cost model relies on statistics collected about the objects involved in a query, and the database and host where the query runs.
The optimizer uses statistics to get an estimate of the number of rows (and number of bytes) retrieved from a table, partition, or index. The optimizer estimates the cost for the access, determines the cost for possible plans, and then picks the execution plan with the lowest cost.
For more Information Visit:
asrblogger.com/
#oracledatabase #oracledatabasetutorial #performancetuning #databasetuning #performanceoptimization #asrblogger #asrbloggerdba

[@DevMore]

Nice

[@satyaa7502]

👏👏

[@atursingh3639]

Sir very good video sir please create one video index and rebuild index session

[@anishkumarvideos]

Thanks !!
I have already created one video on Index rebuilding - czcams.com/video/YiBCspv4WjI/video.htmlsi=RXXEkKVQwtzp3PoA

[@mdak-df2eb]

what is the use of estimate_percent parameter in gathering statistics?

[@anishkumarvideos]

In Oracle, the estimate_percent parameter is used when gathering statistics on tables or indexes.
- Automatic Statistics Collection
- Manual Statistics Collection
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', estimate_percent => 20);

[@wbtopg]

Wow 🎉

[@nikitanamdev9955]

What is the difference between schema stats and Database stats?

[@anishkumarvideos]

1. Schema statistics refer to the statistics collected for objects within a specific schema.
2. These statistics provide information about the distribution of data within tables, indexes, and other objects owned by a particular schema.
3. Schema statistics are useful for understanding the characteristics and distribution of data within a specific schema, which can be helpful for query optimization and performance tuning at the schema level.
4. These statistics are typically collected using the DBMS_STATS package or through the ANALYZE command with specific options.
==============================================
1. Database statistics, on the other hand, encompass statistics collected for the entire database.
2. These statistics provide a broader view of the database, including information about the distribution of data across all schemas and objects within the database.
3. Database statistics include metrics such as total database size, number of objects, average row length, and other aggregate information about the database as a whole.
4. Database statistics are essential for monitoring overall database health, identifying trends, and making high-level decisions related to database management and resource allocation.
5. Database statistics are typically collected using tools such as Oracle Enterprise Manager (OEM), Oracle's Automatic Workload Repository (AWR), or by querying system views such as DBA_TABLES, DBA_INDEXES, etc.