Analyze schema, how to unanalyze it? It is halting the database..

From: Unmesh <unmesh_at_thinking-minds.com>
Date: Thu, 23 Aug 2001 17:08:26 -0400
Message-ID: <9m3qjj$rue$1@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 Thu Aug 23 2001 - 16:08:26 CDT