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;
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'
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.
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;
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.
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.
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.
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.
As another piece of the puzzle, the LOGSTDBY_SUPPORT_TAB_11_2B view is only invoked on 220.127.116.11.0 databases with the compatible parameter set to 18.104.22.168 or higher. If the compatible parameter is lower than 22.214.171.124 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 126.96.36.199.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.
Any chance an ALL_ROWS Hint or FIRST_ROWS Hint could help the Select of this view?