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  |
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 #687763 is a reply to message #687762] |
Sun, 28 May 2023 07:03  |
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 
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 |
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
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 ?
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 31 10:03:48 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
