Home » RDBMS Server » Performance Tuning » Tool/method for detecting changed executions plans
Tool/method for detecting changed executions plans [message #298276] Tue, 05 February 2008 09:23 Go to next message
fcastill69
Messages: 4
Registered: February 2008
Junior Member
Hi

I'm looking for a tool or method that helps to identify rapidly if an execution plan has changed.

Any ideas?

Thanks


Felix
Re: Tool/method for detecting changed executions plans [message #298290 is a reply to message #298276] Tue, 05 February 2008 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AWR.

Regards
Michel
Re: Tool/method for detecting changed executions plans [message #298332 is a reply to message #298290] Tue, 05 February 2008 13:37 Go to previous messageGo to next message
fcastill69
Messages: 4
Registered: February 2008
Junior Member
It's 9iR2!

I need to automate the process of detection. Were in AWR is it? I've not seen such an option... Surprised

Felix
Re: Tool/method for detecting changed executions plans [message #298334 is a reply to message #298276] Tue, 05 February 2008 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to open your eyes before you can see.

SQL> desc dba_hist_sql_plan
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DBID                                      NOT NULL NUMBER
 SQL_ID                                    NOT NULL VARCHAR2(13)
 PLAN_HASH_VALUE                           NOT NULL NUMBER
 ID                                        NOT NULL NUMBER
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(30)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT#                                            NUMBER
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(31)
 OBJECT_ALIAS                                       VARCHAR2(65)
 OBJECT_TYPE                                        VARCHAR2(20)
 OPTIMIZER                                          VARCHAR2(20)
 PARENT_ID                                          NUMBER
 DEPTH                                              NUMBER
 POSITION                                           NUMBER
 SEARCH_COLUMNS                                     NUMBER
 COST                                               NUMBER
 CARDINALITY                                        NUMBER
 BYTES                                              NUMBER
 OTHER_TAG                                          VARCHAR2(35)
 PARTITION_START                                    VARCHAR2(5)
 PARTITION_STOP                                     VARCHAR2(5)
 PARTITION_ID                                       NUMBER
 OTHER                                              VARCHAR2(4000)
 DISTRIBUTION                                       VARCHAR2(20)
 CPU_COST                                           NUMBER
 IO_COST                                            NUMBER
 TEMP_SPACE                                         NUMBER
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER
 QBLOCK_NAME                                        VARCHAR2(31)
 REMARKS                                            VARCHAR2(4000)
 TIMESTAMP                                          DATE
 OTHER_XML                                          CLOB

Re: Tool/method for detecting changed executions plans [message #298712 is a reply to message #298334] Thu, 07 February 2008 04:52 Go to previous messageGo to next message
fcastill69
Messages: 4
Registered: February 2008
Junior Member
And how on 9iR2?
Re: Tool/method for detecting changed executions plans [message #298773 is a reply to message #298712] Thu, 07 February 2008 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it manually.
Periocally query v$sql/v$sql_plan and record it in a table.

Regards
Michel
Re: Tool/method for detecting changed executions plans [message #298907 is a reply to message #298773] Fri, 08 February 2008 04:18 Go to previous message
fcastill69
Messages: 4
Registered: February 2008
Junior Member
Thank for your advice!


Felix
Previous Topic: Index Entry
Next Topic: Improvong performance of SQL script
Goto Forum:
  


Current Time: Sat Dec 10 07:15:19 CST 2016

Total time taken to generate the page: 0.05019 seconds