Cpu costing is off (consider enabling it)...

[JPReyes]

I came across this message while checking an SQL statement during a performace check for a table.

Cpu costing is off (consider enabling it)

Curiosity made me go around an do some research and seems like theres no recommendations on the SAP side and very little info over all of the usage of parameter _optimizer_cost_model and its side effects.. As far as I can read that parameter affects  dbms_stats.gather_system_stats.

There is info that points to performace improvement by using paremeter _optimizer_cost_model = cpu but I can't find any documenation that mentions that is supported by SAP or that is indeed recommended (or not).

Any thought's on this?

Database is Oracle 11.2.0.3

Regards, Juan

[stefan_koehler · Accepted solution]

Hello Juan,

well at first let's check the parameter "_optimizer_cost_model" and its values:

'CHOOSE' - default: chooses CPU costing based on the presence of statistics in aux_stats$, 
 'CPU' - forces CPU costing based on statistics in aux_stats$, 
 'IO' - forces IO costing

... so in your case you are using the CPU cost based model, because of you usually gather system statistics (with NO WORKLOAD option). If you do not gather system statistics, you are using defaults. You can check the current values by using the view "sys.aux_stats$".

So what's the real issue with your SQL? I really doubt it is caused by the used cost based model. You should really investigate the SQL issue deeply (by running it with "gather_plan_statistics" hint, doing a sql trace or something like that).

Regards

Stefan

[JPReyes · Accepted solution]

Hi Stefan,

Indeed Im not focusing in that message as the source of the performance issue, I just wanted that little extra info on the behaviour of _optimizer_cost_model.

Thanks for your time, I thinks this clear my doubt.

Regards, Juan