Oracle OCA Exam 1Z0-062 Chapter 14 Managing Performance: SQL Tuning 2023/2024

Oracle OCA Exam 1Z0-062 Chapter 14 Managing Performance: SQL Tuning 2023/2024 14.01 Manage Optimizer Statistics Objects stats are imperfect, why? - ANSWER-a) they're static, must be refreshed periodically or they are stale b) they're usually gathered via sampling, not 100% all rows are examined Define: cardinality estimate - ANSWER-The estimated number of rows that will be returned by a given access method. if cardinality estimate is wrong (stale stats or stats gathered with insufficient sample rate) = slow query List two ways to correct for incorrect cardinality estimate: - ANSWER-a) Sql Plan Directive b) Adaptive Execution Plans Define: Sql Plan Directive - ANSWER-During stmt execution, optimizer compares card est. to actual card. If significantly different, generates a SQL Plan Directive for future use. Directive == tells optimizer to gather additional information regarding the objects through dynamic sampling (ex: next time, check the actual size of table before running stmt, or check for logical correlation b/w column values next time) Define: Adaptive Execution Plans - ANSWER-The optimizer chooses different execution plan part way through running the stmt.