Sometimes people get advice from internet: both Metalink or well-known consulting sites.
\nIf people need a fix or a feature, they use to trust advices.<\/p>\n
Last week I heard a collegue about a 10g RAC database with performance problems and, since I never lay on my chair, I probed both AWR and ADDM . I suddenly recognized heavy enqueues and physical reads
\nover a segment named STATS$USER_LOG. “Strange”, I said, “I cannot remember this name in neither perfstat or catalog segments”.
\nThen I searched the Internet and the Metalink and found the same thing in BOTH metalink.oracle.com and www.dba-oracle.com: a trick to trace logon and logoffs into a table using system triggers.<\/p>\n
Look at this code:<\/p>\n
Cool, every single access is kept into stats$user_log.<\/p>\n Let’s see the logoff trigger:<\/p>\n What’s the matter?<\/p>\n What happens if we have many logons?<\/p>\n NUM_ROWS What happens if the execution plan does a full scan?<\/p>\n SQL> explain plan for update stats$user_log […]<\/p>\n Explained.<\/p>\n How many reads should it take?<\/p>\n MB The database performace will decrease constantly and very slowly….. Sometimes people get advice from internet: both Metalink or well-known consulting sites. If people need a fix or a feature, they use to trust advices. Last week I heard a collegue about a 10g RAC database with performance problems and, … Continue reading create or replace trigger
\nlogon_audit_trigger
\nAFTER LOGON ON DATABASE
\nBEGIN
\ninsert into stats$user_log values(
\nuser,
\nsys_context('USERENV','SESSIONID'),
\nsys_context('USERENV','HOST'),
\nsysdate,
\nto_char(sysdate, 'hh24:mi:ss'),
\n[...]
\n);
\nCOMMIT;
\nEND;
\n\/<\/code><\/p>\ncreate or replace trigger
\nlogoff_audit_trigger
\nBEFORE LOGOFF ON DATABASE
\nBEGIN
\n-- ***************************************************
\n-- Update the last action accessed
\n-- ***************************************************
\nupdate stats$user_log [...]
\n--***************************************************
\n-- Update the last program accessed
\n-- ***************************************************
\nupdate stats$user_log [...]
\n-- ***************************************************
\n[ ... many, many updates ...]
\n-- ***************************************************
\nupdate stats$user_log [...]
\n-- ***************************************************
\n-- Compute the elapsed minutes
\n-- ***************************************************
\nupdate stats$user_log set elapsed_minutes =
\nround((logoff_day - logon_day)*1440)
\nwhere
\nsys_context('USERENV','SESSIONID') = session_id;
\nCOMMIT;
\nEND;
\n\/
\n<\/code>
\nThat’s all. It inserts a row when someone logons. It updates MANY rows when someone logoffs.
\nThere is no match between the record inserted and the records updated (but the session_id).
\nNeither indexes or constraints.<\/p>\nSQL> select num_rows from dba_tables where table_name='STATS$USER_LOG';<\/code><\/p>\n
\n———-
\n3053931<\/p>\nSQL> @?\/rdbms\/admin\/utlxpls
\n---------------------------------------------
\n| Id | Operation | Name |
\n---------------------------------------------
\n| 0 | UPDATE STATEMENT | |
\n| 1 | UPDATE | STATS$USER_LOG |
\n| 2 | TABLE ACCESS FULL| STATS$USER_LOG |
\n---------------------------------------------<\/code><\/p>\nSQL> select bytes\/1024\/1024 Mb from dba_Segments where segment_name='STATS$USER_LOG';<\/code><\/p>\n
\n———-
\n237<\/p>\n
\nRemember: never trust a solution if it involves a change on the system.<\/p>\n","protected":false},"excerpt":{"rendered":"