Oracle 9i CPU costing

Oracle 9i introduced CPU costing and it is turned off by default. However, there are bugs in some 9.2 revisions inadvertantly turned on CPU costing when you set a non-default optimizer_index_cost_adj. CPU costing can be annoying when you dig into details of CBO and compare two different machines. To turn it off in 9.2 is:
alter session set “_optimizer_cost_model”=io;
alter session set “_optimizer_system_stats_usgae”=0;

There is a no_cpu_costing hint, but it doesn’t seem to work, at least for me.
Usual warnings with undocumented init parameters, don’t use it on a production system without blessing by Oracle’s support.

Advertisements

2 Responses to “Oracle 9i CPU costing”

  1. Siva Nekkalapudi Says:

    You can try by putting a hint /*+ INDEX_JOIN(PAI) */ or /*+ NO_CPU_COSTING */. Try it and let me know if you need any asssitance

  2. Siva Nekkalapudi Says:

    You can also try

    /*+ USE_NL(HOI,HOU,HSC,PAI,PTP,RPAC,RPPA,PPG,PJOB,PPO) ORDERED */

    /*+ NO_USE_NL(HOI) */

    /*+ NO_USE_NL(HOI,HOU,HSC) */

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: