Forum Stats

  • 3,874,521 Users
  • 2,266,749 Discussions
  • 7,911,877 Comments

Discussions

Sync check with primary

Rengudi
Rengudi Member Posts: 584 Bronze Badge
edited Nov 16, 2022 5:38PM in SQL & PL/SQL

Hi Guru,

I have response time problem on below sql

sql statement taking more time to give the results.

SQL Statement:-

WITH

  ARCH

  AS

    (SELECT THREAD# as Thread , SEQUENCE# as Sequence

      FROM V$ARCHIVED_LOG

     WHERE (THREAD#, FIRST_TIME) IN ( SELECT THREAD#, MAX (FIRST_TIME)

                       FROM V$ARCHIVED_LOG

                     GROUP BY THREAD#)),

  APPL

  AS

    (SELECT THREAD# as Thread, SEQUENCE# as sequence

      FROM V$LOG_HISTORY

     WHERE (THREAD#, FIRST_TIME) IN ( SELECT THREAD#, MAX (FIRST_TIME)

                       FROM V$LOG_HISTORY

                     GROUP BY THREAD#))

SELECT  

    ARCH.THREAD             "Thread",

    ARCH.SEQUENCE            "Last Sequence Received",

    APPL.SEQUENCE            "Last Sequence Applied",

   (ARCH.SEQUENCE - APPL.SEQUENCE)   "Difference"

 FROM ARCH, APPL

 WHERE ARCH.Thread = APPL.Thread;


Plan:-

 

---------------------------------------------------------------------------------

| Id | Operation      | Name   | Rows | Bytes | Cost (%CPU)| Time   |

---------------------------------------------------------------------------------

|  0 | SELECT STATEMENT   |     |    |    |   6 (100)|     |

|* 1 | FILTER       |     |    |    |      |     |

|* 2 |  HASH JOIN SEMI   |     |   1 |  111 |   3 (100)| 00:00:01 |

|* 3 |  HASH JOIN     |     |  94 | 8366 |   2 (100)| 00:00:01 |

|* 4 |   FIXED TABLE FULL | X$KCCAL |  94 | 4794 |   1 (100)| 00:00:01 |

|  5 |   FIXED TABLE FULL | X$KCCLH | 2336 | 88768 |   0  (0)|     |

|  6 |  VIEW       | VW_NSO_1 | 2336 | 51392 |   1 (100)| 00:00:01 |

|  7 |   HASH GROUP BY  |     | 2336 | 58400 |   1 (100)| 00:00:01 |

|  8 |   FIXED TABLE FULL| X$KCCLH | 2336 | 58400 |   0  (0)|     |

|* 9 |  FILTER       |     |    |    |      |     |

| 10 |  HASH GROUP BY   |     |  94 | 3572 |   3 (100)| 00:00:01 |

|* 11 |   FIXED TABLE FULL | X$KCCAL |  94 | 3572 |   2 (100)| 00:00:01 |

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

  1 - filter( IS NOT NULL)

  2 - access("LHTHP"="THREAD#" AND "MAX(FIRST_TIME)"=TO_DATE("LHLOT",'MM

       /DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN'''))

  3 - access("ALTHP"="LHTHP")

  4 - filter("INST_ID"=USERENV('INSTANCE'))

  9 - filter(("ALTHP"=:B1 AND TO_DATE(:B2,'MM/DD/RR 

       HH24:MI:SS','nls_calendar=''GREGORIAN''')=MAX(TO_DATE("ALLOT",'MM/DD/RR 

       HH24:MI:SS','nls_calendar=''GREGORIAN'''))))

 11 - filter("INST_ID"=USERENV('INSTANCE'))

 

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,802 Blue Diamond
    Answer ✓

    You're not applying much in the way of filters, other than getting the max first_time for each thread, so with large datasets, you might still have issues.

    But anyway, unless I'm mistaken, this is equivalent and should be more efficent:

    with
      arch as (
        select thread# as thread, max(sequence#) keep (dense_rank first order by first_time desc) sequence    
        from   v$archived_log
        group by thread#
      )
    , appl as (
        select thread# as thread, max(sequence#) keep (dense_rank first order by first_time desc) sequence    
        from   v$log_history
        group by thread#
      )
    select arch.thread                       "Thread"
         , arch.sequence                     "Last Sequence Received"
         , appl.sequence                     "Last Sequence Applied"
         , (arch.sequence - appl.sequence)   "Difference"
    from   arch, appl
    where  arch.thread = appl.thread; 
    


    Rengudi

Answers

  • Paulzip
    Paulzip Member Posts: 8,802 Blue Diamond
    Answer ✓

    You're not applying much in the way of filters, other than getting the max first_time for each thread, so with large datasets, you might still have issues.

    But anyway, unless I'm mistaken, this is equivalent and should be more efficent:

    with
      arch as (
        select thread# as thread, max(sequence#) keep (dense_rank first order by first_time desc) sequence    
        from   v$archived_log
        group by thread#
      )
    , appl as (
        select thread# as thread, max(sequence#) keep (dense_rank first order by first_time desc) sequence    
        from   v$log_history
        group by thread#
      )
    select arch.thread                       "Thread"
         , arch.sequence                     "Last Sequence Received"
         , appl.sequence                     "Last Sequence Applied"
         , (arch.sequence - appl.sequence)   "Difference"
    from   arch, appl
    where  arch.thread = appl.thread; 
    


    Rengudi
  • mathguy
    mathguy Member Posts: 10,900 Black Diamond

    You have posted here over 500 times, how difficult is it to learn how to format your code and output (Explain Plan)?

    Your Explain Plan is missing some data, for example the time estimate for the entire SELECT statement, why is that?

    In any case, all the estimates seem very reasonable; what is your question? If the query is taking a long time, it may be because something is blocking it (making it wait); the two system tables you are accessing have 94 rows and 2336 rows respectively, and the query is already written in the most efficient way, so if you are having problems with its execution, you may be looking for the reason in the wrong place.

  • mathguy
    mathguy Member Posts: 10,900 Black Diamond

    @Paulzip - I am not familiar with these views, but in general the OP's query and your rewriting of it would not be equivalent. Namely: in either system table, is it possible to have duplicates by (thread#, first_time)? If it is possible, then the FIRST/LAST aggregate function will only return one of the rows tied for most recent (ordered by first_time) for a given thread, instead of returning all the rows (possibly more than one) tied for most recent for that thread.

    In any case, the cardinality estimates in the Explain Plan (which one might assume are about right, as these are tables maintained by the system itself) suggest that the problem likely lies elsewhere.

  • Rengudi
    Rengudi Member Posts: 584 Bronze Badge

    @mathguy I am looking for one general thread that help how to post and formatting stuff. I am not finding it;

    Going forward, I will format and give more readability


    Thanks for highlighting this.


    Good day