View Cost - SQL Navigator

[Trevisolli]

Environment information:
* Oracle version: 10
* Operating system: Win

Personal, beleza?

I have a question about displaying a tunning and, I would like to know if they can give me a help.
Next, when I make an Explain Plan (in SQL Navigator, for example), on a local seat on my machine, it shows me the cost.
But when I log in to a company bank, he does not give me this information.
He informs me only where he is giving full, the excerpt that is going through etc.
Would you know if it would be a bank parameter, to show the cost? Or, I do not know if this is the case, any tool parameter?

Thanks again.

[diegopedrao]

Trevisolli,

The bank is not configured by rule and not cost, if it is as a rule you have to force the use of Index.

If I'm wrong please correct me, because I'm new in the area.

Att,


Diego Monteiro

[Trevisolli]

Brother speaks, it may be that.

I would like to take a doubt here:

Know me if it is the V$PARAMETER that gives me the information if the database works for Cost (CBO) or Rule (RBO)?

Positive case, what is the name of this parameter?

Thank you very much.

[dr_gori]

This is:

Select all

SQL> select value, display_value from v$parameter 
  2  where NAME='optimizer_mode' 
  3  / 
 
VALUE 
--------------------------------------------------- 
DISPLAY_VALUE 
--------------------------------------------------- 
ALL_ROWS 
ALL_ROWS 
 
 
SQL> 

[diegopedrao]

Trevisolli,

Yes it is the V $ Parameter to give you this information.
If your instance is with the parameter optmizer_mode = CHOOSE or with ALL_ROWS FIST_ROWS or N_ROWS the queries will be by CBO.

Remembering that the bank default is CBO, to change the session to RBO will have to perform the following command:

Select all

alter session set optimizer_mode='rule'

] Remembering that ALTER SESSION is always in the session and is valid only for the session, for the Oracle database always using a particular value it is necessary to put the value in the parameterization file (pfile) or the server parameter file (spfile).


I hope I have helped.

Att,

Diego Monteiro
Senior Oracle Analyst

[Trevisolli]

Blezera Galera ... very useful even this information ...
is that on the configurations and bank parameters I am negation even ... rs ...

Just another question , could I be set up my bench for 'ALL_ROWS'?

is that it is like 'choose' but, no longer returned the cost information, it is still bringing the full or not statistics on the table.

Another issue I have is, if the bank is in CBO and the statistics are not updated, would he show me this cost, or did I say bullshit?

Valeu ...

[diegopedrao]

Another tip is the use of hints to force first rows etc ...

would show you the cost normally, the only problem is that these statictics would be outdated and may not be the reality and show a cost that In fact it could be much bigger.

Select all

dbms_stats.gather_schema_stats(ownname=>'meuschema',estimate_percent=>10,granularity=>'auto',cascade=>true);

This collects only information about the Owner, automatically, medium unintentionally, would be a way to force the use of CBO in the database on this user.

Att,

Diego Monteiro
Senior Oracle Analyst