Starting with Oracle9i, users have the ability to view the estimated CPU, TEMP and I/O costs for every SQL execution plan step. Oracle Corporation has noted that typical OLTP databases are becomingly increasingly CPU-bound and has provided the ability for the DBA to make the optimizer consider the CPU costs associated with each SQL execution step.
The developers of Oracle10g recognized this trend toward CPU-based optimization by providing the ability to choose CPU-based or I/O-based costing during SQL optimization, with the 10g default being CPU-costing. In Oracle10g, system stats are gathered by default, and in Oracle9i the DBA must manually execute the dbms_stat.gather_system_stats package to get CBO statistics.
alter session set "_optimizer_cost_model"=choose; alter session set "_optimizer_cost_model"=io; alter session set "_optimizer_cost_model"=cpu;
Here is a good script:
select to_number(decode(SID, 65535, NULL, SID)) sid, operation_type OPERATION, trunc(WORK_AREA_SIZE/1024) WSIZE, trunc(EXPECTED_SIZE/1024) ESIZE, trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM", number_passes PASS from v$sql_workarea_active order by 1,2;
I have more complete details and scripts in my latest book, Oracle Tuning.
Dig Deeper on Oracle and SQL
Related Q&A from Don Burleson
I have created a table, Party, and in it there are two main fields, party_code and party_name, and around 2,500 records are available in it. I passed... Continue Reading
Suppose you have a table which has only two columns, namely locid (not primary) and place. In Oracle, which query should I run to get the output as ... Continue Reading
I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.