Oracle DBA tips: TIP 89# : CPU cost is not shown in the execution plan

Saturday, October 09, 2010

TIP 89# : CPU cost is not shown in the execution plan


You may notice that sometimes that you get the execution plan, the CPU column is not shown. Also you may get the following messge :  cpu costing is off (consider enabling it) 
The followings could be the main reasons :

  - old plan_table.  
          [@?/rdbms/admin/utlxpls.sql]
  - optimizer_features_enable is still 9i. 
          [Bump up the parameter to DB version]
  - System stats does not exist.          
          [Gather system stats]

0 comments:

Post a Comment

Answers to your questions.(Sorted by date)

Question
-------------
I would like to know whether there is any tool to scan for patches related to oracle and IBM Websphere offline. we dont have internet access to our data centers due to security reasons, I want to do the scanning offline. Kindly send me the details if there is such software to scan offline for patches.

Answer
------------
You need OCM (Oracle Configuration manager), this tool could be installed on the database server and could run in disconnect (offline) mode to gather information and save it to a file, later you could upload the file to metalink and get Oracle patch recommendation.
Do not know about IBM Websphere.

Question
-------------
Hi, I have a question on 9i RBO to 10g CBO database upgrade. we were trying to migrate 9i RBO to 9iCBO first and then to 10g CBO. Can you put some light on performing tis operation. Whats things need to be taken care and how best i can proceed on it.

Answer
------------
Oracle best practice is to follow the following steps for mission-critical application.

1. Gather all SQL statements executed by the application

2. Do you have a full-sized test system?

(yes) Go to step 3.

(no) Gather statistics on the production system (after setting OPTIMIZER_MODE to RULE) and export statistics to test system

3. Validate that RBO execution plans on production system are identical to RBO execution plans on test system

4. Gather the execution plans with CBO using two or more different OPTIMIZER_MODE settings and with RBO for every SQL statement. If your application uses bind variables, compare CBO execution plans with and without peeking

5. Compare the plans. Identify the optimizer mode and bind variable setting with the least number of plan differences.

6. For all queries with different execution plans, compare the performance results.

7. Tune all queries with degraded performance .Revalidate performance on test system.

8. Devise and test statistics-gathering plan. Recommended approach is to enable dml-monitoring and use 'GATHER AUTO'. Old statistics should be saved before gathering new statistics.

9. Roll out test-system changes and CBO into production system

Question
-------------
I am using oracle10g. I have emp table in scott schema. I analyzed the table today and last_analyzed field is showing today's date. it make sense.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Code: [Select all] [Show/ hide]SQL> select last_analyzed from dba_tables where table_name='EMP';

LAST_ANALYZED
--------------------------------------------------------------------------------
07-NOV-09

I have statistics history since from 6-oct-2009.

Code: [Select all] [Show/ hide]SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
06-OCT-09 09.52.45.351000000 PM -05:00

Now i want to restore the statistics as of date 10-OCT-2009

Code: [Select all] [Show/ hide]SQL> execute dbms_stats.restore_table_stats('SCOTT','EMP','10-OCT-09 09.52.45.351000000 PM -05:00')

PL/SQL procedure successfully completed.

SQL>
Let me check the last_analyzed column....

Code: [Select all] [Show/ hide]SQL> select last_analyzed from dba_tables where table_name='EMP';

LAST_ANALYZED
--------------------------------------------------------------------------------
15-SEP-09
It is showing 15-SEP-2009. But is is supposed to show 10-OCT-09.
Any clarification is highly appreciated..

Answer
------------
Please run the following query to see history of statistics for your specific object

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='EMP';

This shows when stats were updated, now if you specify time between to time when stats are changed, Oracle restores stats for time earlier than the specified time. The result that you see determines that stats were changed in Sep 15th and at least were the same till Oct 10th.

Question
-------------
I saw a running report server from midtier. after open this server from System Components, I do not see any engineer is running in there. The former dba set up this application server 10GR2 at window 2003.Currently, end user request report URL.we have a form server and URL. I know to confugured it.form application URL ashttp://www.xxsaleinformation.com/forms/frmservlet?config=sale-prodid Based on above, I changes to ashttp://www.xxsaleinformation.com/reports/rwservletIt is openning page as OracleAS Reports Services - Servlet Command HelpSO where can I find a report URL? how to configure oracle report server.( for example. i configured forsweb.cfg for form application)
Thanks for your help in detail

Answer
------------
Test report URL is : http://localhost:port/reports/rwservlet?server=test+destype=cache+desformat=html+report=test.rdf

To test specifc report :
http://localhost:port/reports/rwservlet?server=test+destype=cache+desformat=html+report=myproductionreport.rdf+userid=scott/tiger@mydb

Default directory for reports is ORACLE_HOME/reports/samples/demo, If report is in another directory, in URL the full path needs to be mentioned.

Port can be found from portlist.ini file in ORACLE_HOME/install.

Question
-------------
Single command to backup a table.

Answer
------------
I do not want to know what would be a purpose of just backing up a table.
But RMAN is not capable to take a backup of single table.So I represent you with some options.

Option 1
-------------
1. Move table to a separate tablespace.
2. Backup that tablespace based on RMAN TS backup

Option 2
-------------
1. Use logical export using (expdp or exp)

Based on the purpose, other options may be available such as recyclebin, rdbms_redef.

Question
-------------
Hi, I am not able to understand why do I have a time difference of one second between sysdate and current_date results.I tried on both my test Db and also on prod db, but result was the same. (pls note:I took care of zones while comparing).

Answer
------------
It seems that current_date round the fraction of 1/100 second while sysdate truncate it.To demonstrate, I ran the following , when the 1/100 is more than 50, you can see difference of 1 second, when it is less than 50, current_date and sysdate shows the same value:

alter session set nls_timestamp_format='YYYY-MM-DD HH:MI:SS.FF6';

select current_timestamp,sysdate,current_date from dual;
CURRENT_TIMESTAMP SYSDATE CURRENT_DATE
------------------------------------------------------
10-DEC-08 10.50.18.057349 PM -05:00 10-12/2008 22:50:18 10-12/2008 22:50:18

CURRENT_TIMESTAMP SYSDATE CURRENT_DATE
---------------------------------------------------
10-DEC-08 10.50.52.888957 PM -05:00 10-12/2008 22:50:52 10-12/2008 22:50:53

CURRENT_TIMESTAMP SYSDATE CURRENT_DATE
---------------------------------------------------
10-DEC-08 10.51.11.974794 PM -05:00 10-12/2008 22:51:11 10-12/2008 22:51:12

Question
-------------
we are facing "disk device busy(%)" wait event in our oracle 10g database on windows OS.can you tell us meaning of this wait event .and how to resovle.

Answer
---------
Disk utilization is shown as disk device busy(%) in Grid 10.2.
This is related to MS counter problem which you can find more in Microsoft KB article Q310067.
Also there is a way in Grid to disable this metric.
Nothing to worry about.

More answers :

http://oradbatips.blogspot.com/2008/09/tip-75-most-recent-asked-questions-2.html

http://oradbatips.blogspot.com/2008/01/tip-67-most-recent-asked-questions.html

Oracle DBA tips

Saturday, October 09, 2010

TIP 89# : CPU cost is not shown in the execution plan

0 comments:

About Me

Twitter Updates

Ask Tom - Recent Questions

Answers to your questions.(Sorted by date)

Blog Archive

Labels

Links which I check mostly

Jobs Oracle RSS Feed