8 Replies Latest reply on Aug 26, 2019 10:19 AM by Jonathan Lewis

    Query Taking Long Time

    Ramaraju

      Hi All,

      We are on Database 12.1.0.2 and the following query taking very long time. Till now it took 15 hours

       

      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'

       

      Thanks,

      Ramaraju

        • 1. Re: Query Taking Long Time
          John Thorton

          Ramaraju wrote:

           

          Hi All,

          We are on Database 12.1.0.2 and the following query taking very long time. Till now it took 15 hours

           

          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'

           

          Thanks,

          Ramaraju

          click on URL below, read content & provide requested details

           

          How to  improve the performance of my query? / My query is running slow.

          • 2. Re: Query Taking Long Time
            Jonathan Lewis

            Ramaraju wrote:

             

             

            We are on Database 12.1.0.2 and the following query taking very long time. Till now it took 15 hours

             

            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'

             

             

            The first question is always - how good are the statistics: you may need some on the SYS objects to get a sensible plan.

            Before you change anything, though, check two things:

             

            a) How long does it take to get the full result set from

            SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE BAD_COLUMN = 'Y'

            and how many rows is that.

             

            b) How long does it take to get the full result set from

            SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED

            and how many rows is that.

             

            Worst case scenario the total run time of the original query could be reduced to the sum of those two times - one way might be a rewrite of the form:

            select * from (

            select query_a

            minus

            select query_b

            )

             

             

             

            Regards

            Jonathan Lewis

            • 3. Re: Query Taking Long Time
              Ramaraju

              Hi Lewis,

              We are trying to create logical standby database(Doc ID 738643.1),  and we are at step 2.1.

               

              Yes, I have already run those individual queries and working fine. Below are the details. I have checked this query in other databases also and result is same.

               

              SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE BAD_COLUMN = 'Y';

              --> The above query took 5 seconds and number of rows are 208

               

              SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;

              --> The above query took 9 seconds and number of rows are 815.

               

              Thanks,

              Ramaraju

              • 4. Re: Query Taking Long Time
                Jonathan Lewis

                Ramaraju wrote:

                 

                We are trying to create logical standby database(Doc ID 738643.1), and we are at step 2.1.

                 

                Yes, I have already run those individual queries and working fine. Below are the details. I have checked this query in other databases also and result is same.

                 

                SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE BAD_COLUMN = 'Y';

                --> The above query took 5 seconds and number of rows are 208

                 

                SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;

                --> The above query took 9 seconds and number of rows are 815.

                 

                 

                That means you can run a different query from the one supplied, and get the required result in roughly 14 seconds.

                 

                Regards

                Jonathan Lewis

                • 5. Re: Query Taking Long Time
                  Ramaraju

                  I have tried with following query and executed successfully(No rows selected). If you don't mine can you please confirm these queries are equal or not?

                   

                  SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED

                  WHERE (OWNER, TABLE_NAME) IN (SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE BAD_COLUMN = 'Y')

                   

                  Actual Query:

                  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'

                   

                  Thanks,

                  Ramaraju

                  • 6. Re: Query Taking Long Time
                    Jonathan Lewis

                    When in doubt, create a model and test it.

                    Being able to create suitable models is one of the most important skills you can acquire for trouble-shooting:

                     

                    drop table DBA_LOGSTDBY_UNSUPPORTED;

                    drop table DBA_LOGSTDBY_NOT_UNIQUE;

                     

                    create table DBA_LOGSTDBY_UNSUPPORTED (owner_name varchar2(10), table_name varchar2(10));

                    create table DBA_LOGSTDBY_NOT_UNIQUE  (owner_name varchar2(10), table_name varchar2(10), bad_column varchar2(1));

                     

                    insert into DBA_LOGSTDBY_UNSUPPORTED values('FRED','BLOGGS');

                    insert into DBA_LOGSTDBY_UNSUPPORTED values('JOHN','SMITH');

                    insert into DBA_LOGSTDBY_UNSUPPORTED values('JOHN','DOE');

                     

                    insert into DBA_LOGSTDBY_NOT_UNIQUE values('FRED','BLOGGS','Y');

                    insert into DBA_LOGSTDBY_NOT_UNIQUE values('JOHN','DOE','Y');

                     

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: Query Taking Long Time
                      Ramaraju

                      Thank you Lewis.

                      Finally query completed and it took 58 hours.

                      • 8. Re: Query Taking Long Time
                        Jonathan Lewis

                        And did you try executing:

                         

                        select *

                        from (

                        SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE BAD_COLUMN = 'Y'

                        minus

                        SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED

                        )

                         

                        And if you did, did this query complete in about 14 seconds, and were the results the same as the original - apart from the order (and the elimination of any duplicates).

                         

                        Regards

                        Jonathan Lewis