Home » RDBMS Server » Performance Tuning » Cannot force indexing on alert log query (Oracle 19c, Windows Server 2019)
Cannot force indexing on alert log query [message #687761] Sun, 28 May 2023 05:13 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi DBAs,
I am trying to query the alert log information from V$DIAG_ALERT_EXT and get bad performance.

I see that it's doing a full scan, in spite of me trying to set up parameters and hints to make it go with Index Scan:

SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

SQL> alter session set optimizer_index_cost_adj=1;

Session altered.

SQL> set autot on lines 900 pages 20000
SQL> --
SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

SQL> alter session set optimizer_index_cost_adj=1;

Session altered.

SQL> --
SQL> select /*+ index (a) */ count(*)
  2  FROM
  3      v$diag_alert_ext a
  4  WHERE a.originating_timestamp > systimestamp - 1/1440;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 751879792

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |    22 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                  |     1 |    22 |            |          |
|*  2 |   FIXED TABLE FULL| X$DIAG_ALERT_EXT |     5 |   110 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYS_EXTRACT_UTC("ORIGINATING_TIMESTAMP")>SYS_EXTRACT_UTC(SYSTIME
              STAMP(6)-.000694444444444444444444444444444444444444))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -  SEL$F5BB74E1 / X$DIAG_ALERT_EXT@SEL$2
         U -  index (a)


Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        549  bytes sent via SQL*Net to client
        476  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

When I try to check if it has indexes, I get to realize that it doesn't at all appear in DBA views:

SQL> select count(*) from dba_indexes where table_name = upper('X$diag_alert_ext');

  COUNT(*)
----------
         0

I also gather fixed_objects_statistics in a time when I have some reasonable workload.
Still I don't seem to be able to affect it or analyze it ( what indexes exist on it etc )

I could understand how to do the above in spite of reviewing different sources I've found:

https://oracle-base.com/articles/11g/read-alter-log-from-sql

http://ora-srv.wlv.ac.uk/oracle19c_doc/refrn/V-DIAG_ALERT_EXT.html#GUID-7EC93FE0-FF30-4A94-92BC-785E2BCB38F3

https://www.dba-oracle.com/t_v$diag_alert_ext.htm



Any ideas why my query on alert log info isn't going with Index Scans, and how to force it to ?

Thanks
Andrey
Re: Cannot force indexing on alert log query [message #687762 is a reply to message #687761] Sun, 28 May 2023 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I failed to see how reading an external file can be indexed.

Note: the last site is b......t, sometimes it is correct, many times it is not, the correct information are from other sites which them can be trusted, so this site provides anything useful. Put it in your black list.

Re: Cannot force indexing on alert log query [message #687763 is a reply to message #687762] Sun, 28 May 2023 07:03 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Sun, 28 May 2023 14:24

I failed to see how reading an external file can be indexed.

Note: the last site is b......t, sometimes it is correct, many times it is not, the correct information are from other sites which them can be trusted, so this site provides anything useful. Put it in your black list.

Thanks Michel, much appreciated.
Per the B-T website, I acknowledged that I need to check anything I get from the WWW, especially from non-official sources,
However, sometimes it's hard to get info from official sources, or figure out how to get your test in a lab to prove/displrove the case yourself,
so I take what I can get ( Not as an ultimate truth, but as an idea to review off course.. )

Having that said, per the idea of indexing an external table: Well, I would assume that just a a part of a text file ( alert log ) can be read by the OS and indexed by Oracle,
So I wouldn't find it totally out of reason.

However, I do understand that wishes apart and reality apart Smile

I understand then , that I might then want to reduce the time I am turning to the external table, and perhaps reduce the log file size ( although it takes some 30 seconds to query it when it's 100kb in size )
and see how it goes. Maybe create a interim table that has the data indexed and gets updated every while.

I'll do my stuff and maybe post some trace and additional metrics here if it would make sense

Many thanks again
Andrey
Re: Cannot force indexing on alert log query [message #687799 is a reply to message #687762] Wed, 07 June 2023 08:42 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Sun, 28 May 2023 14:24

I failed to see how reading an external file can be indexed.

Note: the last site is b......t, sometimes it is correct, many times it is not, the correct information are from other sites which them can be trusted, so this site provides anything useful. Put it in your black list.

I think I understand why I have a performance issue here: In spite of the alert log being "archived" by me to "alert.log.old" - I still see the old data in v$diag_alert_ext


SQL> SELECT MIN ( originating_timestamp ) FROM v$diag_alert_ext;

MIN(ORIGINATING_TIMESTAMP)
---------------------------------------------------------------------------
11-JAN-23 06.58.20.999000000 PM +02:00

Elapsed: 00:01:02.44
SQL>


As I go through documentation I realize that the alert log file that is the source for the external table data of which is displayed in the v$diag_alert_ext

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-DIAG_ALERT_EXT.html#GUID-7EC93FE0-FF30-4A94-92BC-785E2BCB38F3

And so, I went to my C:\Oracle\diag\rdbms\mydb\mydb\alert folder, where I see the 10MB alert log XML-files

I removed most, but 4 files could not be removed as they are held by some application, which likely is Oracle Database.
The oldest record inside the files dates back to the date above.

So now the performance is better

C:\Oracle\diag\rdbms\mydb\mydb\alert>dir
 Volume in drive C has no label.
 Volume Serial Number is C682-8906

 Directory of C:\Oracle\diag\rdbms\mydb\mydb\alert

06/07/2023  04:24 PM    <DIR>          .
06/07/2023  04:24 PM    <DIR>          ..
02/21/2023  10:55 PM        10,485,835 log_1.xml
05/24/2023  06:27 PM        10,485,908 log_34.xml
05/24/2023  07:06 PM        10,486,344 log_35.xml
03/29/2023  11:06 PM        10,486,030 log_4.xml
               4 File(s)     41,944,117 bytes
               2 Dir(s)  40,020,353,024 bytes free
but not optimal, as it seems to read all 4 files, that I cannot erase.

So I went and opened them for editing, removed their contents, saved.

Now I have only one current alert.xml file that is being writtent to.

Nevertheless, when I try to query it I don't see anything:


While I do have *some* content

06/07/2023  04:28 PM    <DIR>          .
06/07/2023  04:28 PM    <DIR>          ..
06/07/2023  04:38 PM             2,671 log.xml
06/07/2023  04:36 PM                 0 log_1.xml
06/07/2023  04:36 PM                 0 log_34.xml
06/07/2023  04:36 PM                 0 log_35.xml
06/07/2023  04:36 PM                 0 log_4.xml
               5 File(s)          2,671 bytes
               2 Dir(s)  40,052,617,216 bytes free


SQL> select * from v$diag_alert_ext;

no rows selected

SQL>


Any idea why is that now happening ?
What made my v$diag_ext not have any content anymore, even though some content is being added up to the alert log xml file ?

Re: Cannot force indexing on alert log query [message #687800 is a reply to message #687799] Wed, 07 June 2023 10:22 Go to previous message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

V$ views based on X$ "tables" are in fact code and, possibly, memory data.
When a X$ table is called (via V$ view), Oracle executes a specific code and not SQL usual one.
What happens cannot be known for sure unless you know the Oracle code associated to the "table".

In your case, maybe Oracle checks if the files are consistent (time, size...) with the previous call.
Try to flush the shared pool (ALTER SYSTEM) (maybe the result cache dbms_result_cache.flush).
In the end, restarting the instance should solve this point. Sad

Previous Topic: rdbms 12.2C performance issues sometimes
Next Topic: Bind by position
Goto Forum:
  


Current Time: Thu May 23 22:54:39 CDT 2024