ORA_ROWSCN - High Response time - Regarding

Rengudi

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")
  

user9540031

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

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

https://community.oracle.com/tech/developers/discussion/comment/16854419#Comment_16854419

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

https://community.oracle.com/tech/developers/discussion/comment/16854453#Comment_16854453

                                                                                                                                                                            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