This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,900 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

ORA_ROWSCN - High Response time - Regarding

Rengudi
Rengudi Member Posts: 594 Bronze Badge

Hi Guru

I would also think making both queries simpler. These, in my opinion, have more table access, hence the response time for a SQL query is longer. How may this be reduced?

 WITH
    scn_time
    AS
        (  SELECT sc                                     sc_start,
                  LEAD (sc) OVER (ORDER BY sc)           sc_end,
                  start_time,
                  LEAD (end_time) OVER (ORDER BY sc)     end_time_sc
             FROM (  SELECT n.ORA_ROWSCN             sc,
                            MIN (
                                CAST (
                                    FROM_TZ (ntimestamp#, '00:00') AT LOCAL
                                        AS DATE))    start_time,
                            MAX (
                                CAST (
                                    FROM_TZ (ntimestamp#, '00:00') AT LOCAL
                                        AS DATE))    end_time
                       FROM sys.aud$ n
                      WHERE ntimestamp# > SYSDATE - 365
                   GROUP BY n.ORA_ROWSCN)
         ORDER BY sc)
SELECT *
  FROM (SELECT t.ORA_ROWSCN sc, t.*
          FROM net_hier t) table_inspect
       INNER JOIN scn_time s
           ON (table_inspect.sc BETWEEN s.sc_start AND s.sc_end)
 WHERE (end_time_sc - start_time) < 1
 
 
 Plan hash value: 3143605204
 
------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |  3542 |   307K|   344K  (1)| 00:00:14 |
|   1 |  NESTED LOOPS         |                  |  3542 |   307K|   344K  (1)| 00:00:14 |
|*  2 |   VIEW                |                  |     1 |    44 |   340K  (1)| 00:00:14 |
|   3 |    WINDOW BUFFER      |                  |     1 |    11 |   340K  (1)| 00:00:14 |
|   4 |     SORT GROUP BY     |                  |     1 |    11 |   340K  (1)| 00:00:14 |
|*  5 |      TABLE ACCESS FULL| AUD$             |     1 |    11 |   340K  (1)| 00:00:14 |
|*  6 |   TABLE ACCESS FULL   |    NET_HIER      |  3542 |   155K|  4221   (1)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("S"."END_TIME_SC"-"S"."START_TIME"<1)
   5 - filter("NTIMESTAMP#">[email protected]!-365)
   6 - filter("S"."SC_START"<="T"."ORA_ROWSCN" AND "S"."SC_END">="T"."ORA_ROWSCN")
  

Answers

  • user9540031
    user9540031 Member Posts: 247 Gold Badge

    How do the predicted cardinalities (Rows in the plan) compare to the actual ones? Especially as regards rows coming out of operation #5 (FTS on SYS.AUD$ over the past 365 days).

    Just a guess: that NESTED LOOPS (operation #1) could turn out to be very costly, so you might want to use a MERGE join instead—that will cost sorting both sides of the join on SCNs, of course.

    See if adding the following hints to the main SELECT can do that:

    /*+ leading([email protected]$1 [email protected]$2) use_merge([email protected]$2) */

    Regards,

  • User_3ABCE
    User_3ABCE Member Posts: 274 Gold Badge

    Are you sure that ora_rowscn will give a relevant result?

    select dependencies from dba_tables where table_name = 'AUD$' and owner = 'SYS';
    
    DEPENDEN
    --------
    DISABLED
    

    You can emulate the scn_to_timestamp function with v$log_history view.

    INNER JOIN may cause lines from net_hier to be skipped.

  • Rengudi
    Rengudi Member Posts: 594 Bronze Badge

    Yes, Still the same.


    Plan hash value: 742712366
     
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |                  |       |       |       |   360K(100)|          |
    |   1 |  MERGE JOIN            |                  |  3542 |   307K|       |   360K  (1)| 00:00:15 |
    |   2 |   SORT JOIN            |                  |     1 |    44 |       |   340K  (1)| 00:00:14 |
    |*  3 |    VIEW                |                  |     1 |    44 |       |   340K  (1)| 00:00:14 |
    |   4 |     WINDOW BUFFER      |                  |     1 |    11 |       |   340K  (1)| 00:00:14 |
    |   5 |      SORT GROUP BY     |                  |     1 |    11 |       |   340K  (1)| 00:00:14 |
    |*  6 |       TABLE ACCESS FULL| AUD$             |     1 |    11 |       |   340K  (1)| 00:00:14 |
    |*  7 |   FILTER               |                  |       |       |       |            |          |
    |*  8 |    SORT JOIN           |                  |  1416K|    60M|   195M| 20336   (1)| 00:00:01 |
    |   9 |     TABLE ACCESS FULL  | NET_HIER         |  1416K|    60M|       |  4218   (1)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - filter("S"."END_TIME_SC"-"S"."START_TIME"<1)
       6 - filter("NTIMESTAMP#">[email protected]!-365)
       7 - filter("S"."SC_END">="T"."ORA_ROWSCN")
       8 - access("S"."SC_START"<="T"."ORA_ROWSCN")
           filter("S"."SC_START"<="T"."ORA_ROWSCN")
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 2 (U - Unused (1), N - Unresolved (1))
    ---------------------------------------------------------------------------
     
       0 -  SEL$2
             N -  use_merge([email protected]$2)
     
       3 -  SEL$07BDC5B4
             U -  leading([email protected]$1 [email protected]$2)
     
    
  • Rengudi
    Rengudi Member Posts: 594 Bronze Badge
    edited Nov 23, 2022 3:57PM
                                                                                                                                                                                SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 23 12:04:58 2022                                                                                                       Version 19.3.0.0.0                                                                                                                                                                                                                                                                                                                                      Copyright (c) 1982, 2019, Oracle.  All rights reserved.                                                                                                                                                                                                                                                                                                 Last Successful login time: Wed Nov 23 2022 12:04:37 +05:30                                                                                                                                                                                                                                                                                             Connected to:                                                                                                                                                               Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                                                                                                      Version 19.16.0.0.0                                                                                                                                                                                                                                                                                                                                     SQL> select dependencies from dba_tables where table_name = 'AUD$' and owner = 'SYS';                                                                                                                                                                                                                                                                   DEPENDEN                                                                                                                                                                    --------                                                                                                                                                                    DISABLED                                                                                                                                                                                                                                                                                                                                                SQL>                                                                                                                                                                                   
    


    SQL> select dependencies from dba_tables where table_name = 'AUD$' and owner = 'SYS'
    
    DEPENDENCIES
    ------------
    DISABLED   
    1 row selected.
    

    still , its not giving result in acceptable level of response time