首页 » ORACLE 9i-23ai » Logon Storms on 11g ,audit maybe cause performance issue.(当创建很多连接时审计可能带来性能问题)

Logon Storms on 11g ,audit maybe cause performance issue.(当创建很多连接时审计可能带来性能问题)

  • 2013/11/14
  • ORACLE 9i-23ai
  • 1,134 views
  • Logon Storms on 11g ,audit maybe cause performance issue.(当创建很多连接时审计可能带来性能问题)已关闭评论

The Oracle Network Listener is the first point of contact for any client connection to the database. The Listener brokers client requests, handing them off to appropriate database servers. In a typical server configuration, the Listener is the only entity listening for client connection requests, and thus is the first line of defense against Denial-of-Service attacks.

Next week we have a exam project, at first time point( Start the exam)a large number of concurrent users , log in and establish a lot of connections to the database, as you know The process of starting a database session has inherent CPU costs associated with it – from the creation of a new OS process to attaching to the SGA. It has been said to create a connection takes 100ms cpu time and 5M memory size (Not necessarily accurate).

If it is an existing database, check first what is being audited. To find out system audited stuff run the following:

select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;

by default, create session audit is enable on 11g, and audit record write in aud$ base table. and the table in system tablespace , system tablespace is MSSM and aud$ space management using freelist.

When multiple connection created will insert a lot of rows in aud$ table, there may be contention in the segment header for the table. This contention can manifest itself as a buffer busy wait or a freelist wait.

sys@anbob> SELECT privilege,success,failure FROM DBA_PRIV_AUDIT_OPTS order by 1;

PRIVILEGE SUCCESS FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
...

sys@anbob> select freelists from dba_segments where segment_name='AUD$';
FREELISTS
----------
1
sys@anbob>SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM
FGA_LOG$ SYSTEM

sys@anbob>;select t.tablespace_name, t.extent_management,t.allocation_type,t.segment_space_management 2 from dba_tablespaces t 3
where t.tablespace_name = 'SYSTEM';
TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- --------- ------
SYSTEM LOCAL SYSTEM MANUAL

When the jboss server config a large numbers of session pool,start create connect to database ,I found “buffer busy wait” being a really problem for performance.The bitmap freelists of ASSM greatly reduce segment header contention and improve simultaneous insert concurrency (up to a point). Starting 11gR2, Oracle supports moving the AUD$ table out of the SYSTEM tablespace. Use the new DBMS_AUDIT_MGMT package move aud$ to ASSM tablespace.

CREATE TABLESPACE TBS_AUDIT   DATAFILE   '/oradata/kaoshi/kaoshi/tbs_audit01.dbf' SIZE 100M autoextend on maxsize 10g
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
SEGMENT SPACE MANAGEMENT AUTO;

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'TBS_AUDIT');
END;
/

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'TBS_AUDIT');
END;
/

sys@anbob>SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           TBS_AUDIT
FGA_LOG$                       TBS_AUDIT

analyze listener log trace

[oracle@dbserver58 ~]$ fgrep "14-NOV-2013 13:" lsnr.log|fgrep "establish"|awk '{print $1 " " $2}'| awk -F: '{print $1 ":" $2}' |sort|uniq -c
19 14-NOV-2013 13:00
2413 14-NOV-2013 13:01
48 14-NOV-2013 13:02
23 14-NOV-2013 13:03
1 14-NOV-2013 13:04
4 14-NOV-2013 13:05
2 14-NOV-2013 13:06
1102 14-NOV-2013 13:08
1376 14-NOV-2013 13:09
28 14-NOV-2013 13:11
2 14-NOV-2013 13:13
3 14-NOV-2013 13:14
2184 14-NOV-2013 13:16
294 14-NOV-2013 13:17
15 14-NOV-2013 13:18
1 14-NOV-2013 13:19
14 14-NOV-2013 13:21
862 14-NOV-2013 13:23
1615 14-NOV-2013 13:24
26 14-NOV-2013 13:26
3 14-NOV-2013 13:28
1 14-NOV-2013 13:29
1 14-NOV-2013 13:30
1949 14-NOV-2013 13:31
527 14-NOV-2013 13:32
31 14-NOV-2013 13:33
1 14-NOV-2013 13:34
2 14-NOV-2013 13:35
5 14-NOV-2013 13:36
619 14-NOV-2013 13:38
1855 14-NOV-2013 13:39
29 14-NOV-2013 13:41
3 14-NOV-2013 13:43
1 14-NOV-2013 13:44
1 14-NOV-2013 13:45
1814 14-NOV-2013 13:46

check oswatch
osw anbob
then I check Jboss connect pool conf

<metadata>
<type-mapping>Oracle10g</type-mapping>
</metadata>
<new-connection-sql>select 1 from dual</new-connection-sql>
<min-pool-size>2500</min-pool-size>
<max-pool-size>3000</max-pool-size>
<strong> <idle-timeout-minutes>5</idle-timeout-minutes></strong>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

note idle-timeout-minutes is 5 minutes, because the session just create and never execute transaction so ,after 5 minutes will kill all session and re-create min pool size sessions. solution increase idle timeout to 1 hour.

check aud$ table

sys@anbob>select username,userhost,action_name,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') etime,count(*) cnt 
from dba_audit_session 
where timestamp>sysdate-10/20/60 
group by username,userhost,action_name,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') order by 4;

USERNAME                       USERHOST             ACTION_NAME                  ETIME                                CNT
------------------------------ -------------------- ---------------------------- ------------------- --------------------
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:45                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:46                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:47                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:48                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:49                   39
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:50                   41
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:51                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:52                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:53                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:54                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:55                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:56                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:57                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:58                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:38:59                   41
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:39:00                   39
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:39:01                   41
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:39:02                   40
KAOSHI_SHF                     webtest-226          LOGON                        2013-11-14 13:39:03                   40
...

Notice:
avg 40 sessions created per second , different machine env the results may not be as same. aud$ in MSSM freelist is 1, I test Less than 20 session created per second.

Related Posts:

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：