This discussion is archived
9 Replies Latest reply: Sep 17, 2013 1:44 PM by mseberg RSS

Prereq query for Logical Standby runs for days

r_reynoldson Newbie
Currently Being Moderated

Have had an SR open for a month that isn't getting anywhere, thought I'd check here and see if anybody has run into this.

 

We're looking at setting up a logical standby database, Oracle 11.2.0.3 on AIX running PeopleSoft.

 

There is a prereq query in the Oracle manual which is also run via the GC wizard to find tables without unique logical identifiers.

 

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE

WHERE (OWNER, TABLE_NAME) NOT IN

(SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)

AND BAD_COLUMN = 'Y';

 

My problem is that the darned thing runs for 5.5 days.

 

The view in that query runs the function  logstdby$tabf, which runs the following SQL over and over and over and over:

SELECT * FROM LOGSTDBY_UNSUPPORT_TAB_11_2B

 

Each time it runs the LOGSTDBY_UNSUPPORT_TAB_11_2B query is about 8 seconds. Run it 100,000 times and that adds up. Looks like the big problem in that query is full table scans of TAB$ and COL$.


Anybody seen it? Anybody fixed it?

  • 1. Re: Prereq query for Logical Standby runs for days
    mseberg Guru
    Currently Being Moderated

    Hello;

     

    Try this:

     

    SELECT * FROM DBA_LOGSTDBY_NOT_UNIQUE;

     

    You should get much better results without the sub-query.

     

    Another query to try is:

     

    SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE ORDER BY OWNER,TABLE_NAME;

     

    Best Regards

     

    mseberg

  • 2. Re: Prereq query for Logical Standby runs for days
    r_reynoldson Newbie
    Currently Being Moderated

    Good suggestion - in 15 minutes you got farther than support did in 2 weeks.

     

     

    Rather than remove the sub-query, I can change it into a minus, which is functionally equivalent to the original but changes the executions on DBA_LOGSTDBY_UNSUPPORTED from many times to just once.

    select owner, table_name from dba_logstdby_not_unique
    where bad_column = 'Y'
    minus
    select distinct owner, table_name from dba_logstdby_unsupported;


     

     

    The top part of the query still takes 9.5 hours to run though, with the time now being spent the following SQL which is called from the function logstdby$tabf which is called from the view DBA_LOGSTDBY_NOT_UNIQUE :

    SELECT * FROM LOGSTDBY_SUPPORT_TAB_11_2B

     

     

    Sorry for not putting that in the original post, but I want the original query to run if possible so the GC wizard will work - and I didn't want to clutter up the question.

  • 3. Re: Prereq query for Logical Standby runs for days
    mseberg Guru
    Currently Being Moderated

    OK.

     

    Thanks for adding the additional information. Many people probably don't stop and think that the forum is a knowledge base too so that extra info is great.

     

    In any event I have always used:

     

    SELECT * FROM DBA_LOGSTDBY_NOT_UNIQUE;

     

    Best Regards

     

    mseberg

  • 4. Re: Prereq query for Logical Standby runs for days
    r_reynoldson Newbie
    Currently Being Moderated

    I would have added the info at the end in case anybody else ever needed it.

     

    Unfortunately that query you use still takes 9+ hours on my system. Still very unacceptable.

  • 5. Re: Prereq query for Logical Standby runs for days
    mseberg Guru
    Currently Being Moderated

    Agreed. On my largest DB this runs in about 20 seconds and returns just over 1000 rows.

     

    My knee jerk would be to check the SYS schema for an issue, Invalids etc.

     

    Something is wrong. If you find it please consider posting what it was.

     

    Thanks!

     

    mseberg

  • 6. Re: Prereq query for Logical Standby runs for days
    r_reynoldson Newbie
    Currently Being Moderated

    Can't find any problems in SYS or data dictionary, have tried gathering system stats and dictionary stats and even stats on the col$/tab$/seg$/etc tables involved at the lowest level, no change.

     

    How many tables/columns in your largest database?

     

    I run the original query with the sub-query in place on a non-PeopleSoft database with 1800 tables and 69,000 columns and it finishes in 4 minutes. Your shorter query runs in 20 seconds.But my PeopleSoft instance has 33,000 tables and 742,000 columns. So not sure if it's just the table/column count difference that's the big difference, or if it's one of the lovely database settings I have to put in place for PeopleSoft that's mucking up the optimizer.

     

    Going to try to find time to compare the execution plans on the "SELECT * FROM LOGSTDBY_SUPPORT_TAB_11_2B" queries between the two database if I can find time today, since it's the actual problem child.

  • 7. Re: Prereq query for Logical Standby runs for days
    r_reynoldson Newbie
    Currently Being Moderated

    Okay... I've zeroed in on the performance problem for the SELECT * FROM DBA_LOGSTDBY_NOT_UNIQUE query.

     

    DBA_LOGSTDBY_NOT_UNIQUE view invokes the logstdby$tabf function, which does a query against the LOGSTDBY_SUPPORT_TAB_11_2B view.

     

    The LOGSTDBY_SUPPORT_TAB_11_2B view has 6 columns. The last column is named GENSBY and consists of a 132 line case statement with lots of exists and not exists subclauses. THIS column and it's case statement is the 9 hour issue. Do a SELECT OWNER, NAME, TYPE#, OBJ#, CURRENT_SBY FROM LOGSTDBY_SUPPORT_TAB_11_2B without the GENSBY column and it runs in 0.3 seconds. Add in the GENSBY column and it goes 9 hours.

     

     

    So we are down to a couple of possibilities.

    1) One of my PeopleSoft database settings is causing this to run really slow even with the large number of tables and columns

    2) Or I need Oracle development to investigate and fix the LOGSTDBY_SUPPORT_TAB_11_2B.GENSBY view code. As well as change the NOT IN for the original query to a MINUS.

  • 8. Re: Prereq query for Logical Standby runs for days
    r_reynoldson Newbie
    Currently Being Moderated

    As another piece of the puzzle, the LOGSTDBY_SUPPORT_TAB_11_2B view is only invoked on 11.2.0.3.0 databases with the compatible parameter set to 11.2.0.3 or higher. If the compatible parameter is lower than 11.2.0.3 it hits the view LOGSTDBY_SUPPORT_TAB_11_2 view (or others), which runs extremely fast.

     

    And, of course, once you set your compatible parameter up to 11.2.0.3.0 you can not set it back down. So no bumping it down for the logical standby creation and then bumping it back up.

     

    41+ days with an SR with Oracle Support, and nothing constructive from them despite it being escalated and reassigned to a Data Guard specialist. Sigh.

  • 9. Re: Prereq query for Logical Standby runs for days
    mseberg Guru
    Currently Being Moderated

    Hello;

     

    Any chance an ALL_ROWS Hint or FIRST_ROWS Hint could help the Select of this view?

     

    Best Regards

     

    mseberg

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points