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.
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 184.108.40.206.0 databases with the compatible parameter set to 220.127.116.11 or higher. If the compatible parameter is lower than 18.104.22.168 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 22.214.171.124.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.