2 Options
- Run analyse with DELETE STATISTICS. This will clear all stats. It is also
documented in the sql reference
- try running analyze ona regular basis so that the stats are not 'stale'
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Unmesh" <unmesh_at_thinking-minds.com> wrote in message
news:9m3qjj$rue$1_at_clamcake.uri.edu...
> Hi,
>
> I have a nested SQL running in 2 different databases. Both have almost
> similar data in the tables against which this SQL is running. Wired thing
> is, in DEV it halts the whole system.. and in TST it runs fine. I ran
> explain plan against both databases and both shows different output in the
> plan table. The only difference between 2 databases is 'Analyze Schema
> against all tables and indexes' been run for DEV almost a week back which
it
> didn't run in TST. HEre is the SQL ..
>
> SELECT Distinct
> D.OPRID,
> A.BUSINESS_UNIT,
> A.PROPOSAL_ID,
> A.VERSION_ID,
> A.DESCR20A,
> A.EMPLID,
> A.CUST_ID,
> A.PROPOSAL_TYPE,
> A.PROPOSAL_STATUS_GR,
> B.DEPTID,
> A.TITLE56
>
> FROM
> PS_GM_PROPOSAL A,
> PS_GM_PROP_PROJ B,
> PSOPRDEFN D
>
> WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
> AND A.PROPOSAL_ID = B.PROPOSAL_ID
> AND A.VERSION_ID = B.VERSION_ID
> AND D.OPRTYPE=0
> AND (D.EMPLID = A.EMPLID
> OR EXISTS (SELECT 'x' FROM PS_GM_PROP_PROF F
> Where F.EMPLID = D.EMPLID
> And F.BUSINESS_UNIT = B.BUSINESS_UNIT
> AND F.PROPOSAL_ID = B.PROPOSAL_ID
> AND F.VERSION_ID = B.VERSION_ID
> AND F.SUB_PROP_NBR = B.SUB_PROP_NBR
> AND F.PROF_ROLE_TYPE in ('PI'))
> OR EXISTS (SELECT 'X' FROM PSTREENODE E, PS_GM_SEC_DEPT_OPR S
> WHERE E.EFFDT = S.TREE_EFFDT
> AND D.OPRID = S.OPRID
> AND E.TREE_NAME = S.TREE_NAME
> AND E.TREE_NODE = B.DEPTID
> AND E.SETID = S.TREE_SETID
> AND E.TREE_NODE_NUM
> BETWEEN S.TREE_NODE_NUM
> AND S.TREE_NODE_NUM_END
> AND S.ACCESS_CD = 'Y'
> AND NOT EXISTS
> (SELECT 'X'
> FROM PS_GM_SEC_DEPT_OPR S1
> WHERE S1.OPRID = S.OPRID
> AND S1.ACCESS_CD = 'N'
> AND S1.TREE_SETID = S.TREE_SETID
> AND S1.TREE_NAME = S.TREE_NAME
> AND S1.TREE_EFFDT = S.TREE_EFFDT
> AND S1.TREE_NODE_NUM <> S.TREE_NODE_NUM
> AND E.TREE_NODE_NUM
> BETWEEN S1.TREE_NODE_NUM
> AND S1.TREE_NODE_NUM_END
> AND S1.TREE_NODE_NUM
> BETWEEN S.TREE_NODE_NUM
> AND S.TREE_NODE_NUM_END))
> OR EXISTS (SELECT 'X' FROM PSTREELEAF F, PS_GM_SEC_DEPT_OPR S
> WHERE F.EFFDT = S.TREE_EFFDT
> AND F.TREE_NAME = S.TREE_NAME
> AND D.OPRID = S.OPRID
> AND B.DEPTID
> BETWEEN F.RANGE_FROM
> AND F.RANGE_TO
> AND F.SETID = S.TREE_SETID
> AND F.TREE_NODE_NUM
> BETWEEN S.TREE_NODE_NUM
> AND S.TREE_NODE_NUM_END
> AND S.ACCESS_CD = 'Y'
> AND NOT EXISTS
> (SELECT 'X'
> FROM PS_GM_SEC_DEPT_OPR S1
> WHERE S1.OPRID = S.OPRID
> AND S1.ACCESS_CD = 'N'
> AND S1.TREE_SETID = S.TREE_SETID
> AND S1.TREE_NAME = S.TREE_NAME
> AND S1.TREE_EFFDT = S.TREE_EFFDT
> AND S1.TREE_NODE_NUM <> S.TREE_NODE_NUM
> AND F.TREE_NODE_NUM
> BETWEEN S1.TREE_NODE_NUM
> AND S1.TREE_NODE_NUM_END
> AND S1.TREE_NODE_NUM
> BETWEEN S.TREE_NODE_NUM
> AND S.TREE_NODE_NUM_END)))
>
>
> Any help is highly appreciated...
>
> Unmesh
>
>
>
>
Received on Fri Aug 24 2001 - 03:35:08 CDT
