OraFAQ Forum: Performance Tuning » Tool/method for detecting changed executions plans

Home » RDBMS Server » Performance Tuning » Tool/method for detecting changed executions plans

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tool/method for detecting changed executions plans [message #298276]

Tue, 05 February 2008 09:23

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

Report message to a moderator

Re: Tool/method for detecting changed executions plans [message #298290 is a reply to message #298276]

Tue, 05 February 2008 10:17

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

AWR.

Regards
Michel

Report message to a moderator

Re: Tool/method for detecting changed executions plans [message #298332 is a reply to message #298290]

Tue, 05 February 2008 13:37

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

Report message to a moderator

Re: Tool/method for detecting changed executions plans [message #298334 is a reply to message #298276]

Tue, 05 February 2008 13:48

BlackSwan
Messages: 26766
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

Report message to a moderator