Question: I am tuning an Oracle SQL query and I've noted that the costs don't match the execution speed and SQL where the optimizer shows a lower cost actually take more time to execute. I want to know why in second case the cost is less but time taken to execute the query is more?
Answer: It's important to note that the cost figures that are displayed in an execution plan are not described in any Oracle documentation, and it's been demonstrated that the plan with the lowest ?cost? number is not always the plan chosen by the optimizer. Further, the ?cost? figures do not always indicate the ?best:? execution plan for a query, given the divergent optimizer goals of first_rows (optimizer for response time) and all_rows (optimize for minimizing computing resources).
In general, the lower the CBO-calculated cost, the faster the query will run, but we have to remember that the costs used by the CBO are only estimates, and the numbers are based on metadata statistics collected by dbms_stats. The cost based optimizer (CBO) creates these estimates based on many variables:
you can refer below link for more details:
1) http://www.dba-oracle.com/t_cbo_cost_does_not_match_performance.htm
2) http://www.dba-oracle.com/t_display_sql_optimizer_features.htm
3) http://www.dba-oracle.com/t_system_session_fix_control.htm
Answer: It's important to note that the cost figures that are displayed in an execution plan are not described in any Oracle documentation, and it's been demonstrated that the plan with the lowest ?cost? number is not always the plan chosen by the optimizer. Further, the ?cost? figures do not always indicate the ?best:? execution plan for a query, given the divergent optimizer goals of first_rows (optimizer for response time) and all_rows (optimize for minimizing computing resources).
In general, the lower the CBO-calculated cost, the faster the query will run, but we have to remember that the costs used by the CBO are only estimates, and the numbers are based on metadata statistics collected by dbms_stats. The cost based optimizer (CBO) creates these estimates based on many variables:
you can refer below link for more details:
1) http://www.dba-oracle.com/t_cbo_cost_does_not_match_performance.htm
2) http://www.dba-oracle.com/t_display_sql_optimizer_features.htm
3) http://www.dba-oracle.com/t_system_session_fix_control.htm
No comments:
Post a Comment