How to speed up a Trustcenter PKI system
From OraWiki
Contents |
Author
| (c) 2007 by Steven of Oz | ||
|---|---|---|
| Author | ||
| Name | Gerald Roehrbein | |
| http | www.OraForecast.com | |
| Gerald.Roehrbein@OraForecast.com | ||
| Release | 0.9 | |
| Created | 2008.07.10 | |
| Last update | 2008.07.10 | |
| Changes | Started article | |
| Estimated duration of installation | a few seconds | |
| License | Freeware | |
Purpose
- How to tune a Trustcenter PKI system
This hint will reduce the waits for certificate changes from a few minutes to a few seconds.
Major problem:
Having long waits at "read by other session" hitting Oracle ASSM Bug as described in Metalink document 6474009 .
Minor problem: 8k block size is too small for certificates.
Trustcenter PKI is a well known security software. It have some performance bottlenecks I've analyzed and solved. Probably Trustcenter have implemented them in their new product line and you do not need to change this.
But you should know who is the guy behind the scene. That's me!
Installation
- Setup SGA as required to add some additional caches like KEEP, RECYCLE and 16k.
Sample:
alter system set db_cache_size=1024M scope=both; alter system set db_16k_cache_size=768M scope=both;
Correct size requires an analysis of your PKI system.
It seem to be a benefit to change the behaviour of the optimizer:
alter system set optimizer_index_cost_adj=1 scope=both; alter system set optimizer_mode=first_rows scope=both;
You need also an additional tablespace with a 16k blocksize:
create tablespace SCMPKILMU4M16K datafile '<your path>' size 2557M extent management local uniform size 4M blocksize 16k;
A reorg of all of the certificate tables in your system and their indexes is required. The statement below move the tables to the tablespace above:
ALTER TABLE <owner>.certificate MOVE TABLESPACE SCMPKILMU4M16K LOB (TCUSERCERTIFICATE) STORE AS (TABLESPACE SCMPKILMU4M16K ) LOB (TCPSETOKEN) STORE AS (TABLESPACE SCMPKILMU4M16K ) ; Maybe there are some other certificate tables in your system.
After this you have to rebuild all of the indexes of all of the certificate tables you moved to the new tablespace:
alter index <owner>.IDX_CERTIFICATE_TOKENTYPE rebuild; alter index <owner>.IDX_CERTIFICATE_PKIX509STATE rebuild; alter index <owner>.XPKCERTIFICATE rebuild; alter index <owner>.IDX_CERTIFICATE_PKIUSERREF rebuild; alter index <owner>.XU002CERTIFICATE rebuild; alter index <owner>.XU003CERTIFICATE rebuild; alter index <owner>.XUF001CERTIFICATE rebuild;
This reduces the duration of changes to certificates from a few minutes down to a few seconds.
Usage
- Example
Just implement changes as described above and enjoy.
Recommendations
- Always implement changes in a production environment after having some tests in a lab.
Example(s)
An Oracle database trace shows a huge number of waits on 'read by other session'.
WAIT #4: nam='read by other session' ela= 399 file#=8 block#=136918 class#=1 obj#=45383 tim=56482114743091 WAIT #4: nam='read by other session' ela= 804 file#=8 block#=136924 class#=1 obj#=45383 tim=56482114744038 WAIT #4: nam='read by other session' ela= 515 file#=8 block#=136943 class#=1 obj#=45383 tim=56482114744785 WAIT #4: nam='read by other session' ela= 26420 file#=8 block#=136949 class#=1 obj#=45383 tim=56482114771337 WAIT #4: nam='read by other session' ela= 10537 file#=8 block#=137088 class#=1 obj#=45383 tim=56482114783178 WAIT #4: nam='read by other session' ela= 24503 file#=8 block#=137090 class#=1 obj#=45383 tim=56482114807806 WAIT #4: nam='read by other session' ela= 557 file#=8 block#=137101 class#=1 obj#=45383 tim=56482114808572 WAIT #4: nam='read by other session' ela= 16699 file#=8 block#=137114 class#=1 obj#=45383 tim=56482114828333 WAIT #4: nam='read by other session' ela= 802 file#=8 block#=137128 class#=1 obj#=45383 tim=56482114829361 WAIT #4: nam='read by other session' ela= 6363 file#=8 block#=137131 class#=1 obj#=45383 tim=56482114835814 WAIT #4: nam='read by other session' ela= 7755 file#=8 block#=137141 class#=1 obj#=45383 tim=56482114843799
Table V$SYSTEM_EVENT ( Fast_Small_Oracle_Remote_Analyzer function system_event) shows that the waits at 'read by other session' are significant.
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT WP -------------------------------------------------- ----------- -------------- ----------- -------------- ---------- control file parallel write 1705461 0 796470 .46701 .01% ARCH wait on SENDREQ 54024 0 601409 11.13226 .01% direct path read 6144870 0 1568641 .25528 .02% log file parallel write 5924443 0 2104418 .35521 .02% Backup: sbtwrite2 1675681 0 1596309 .95263 .02% SQL*Net more data to client 47982489 0 1717937 .03580 .02% log file sync 3398564 630 1621037 .47707 .02% imm op 52314 18541 2995344 88.69049 .03% read by other session 7945066 11 4082225 .51381 .04% db file sequential read 56172980 0 7937321 .14130 .08% db file scattered read 40283295 0 18867732 .46838 .19% i/o slave wait 1760943 861891 84818277 94.34190 .84% Streams AQ: waiting for time management or cleanup 16881 9968 304701366 44,076.57544 3.02% tasks smon timer 18273 14014 415015376 97,444.32402 4.11% Streams AQ: qmn slave idle wait 156177 9 428073058 2,741.10610 4.24% Streams AQ: qmn coordinator idle wait 323779 167657 427950186 2,741.12672 4.24% pmon timer 1590697 1590151 429076672 785,854.71062 4.25% jobq slave wait 1468632 1400204 429200571 6,272.29454 4.25% SQL*Net message from client 47092670 0 2921330108 62.03365 28.93% rdbms ipc message 19860269 14124241 4610050680 803.70087 45.65%
After this changes 'read by other session' waits do not happen anymore.
Security related issues
- None.
How to make this feature as secure as possible
- None.
Source code of this extension
- See above
Planned enhancements
- Will follow
Discussions
You can also use this article as a generic description on "How to analyze performance issues".
First thing I allways do if there are performance issues using fsora the Fast_Small_Oracle_Remote_Analyzer developed at OraForecast.com:
- fsora all_session_waits - Search for sessions actual waiting (execute frequently)
- fsora locks_by_session - Search for waiters and blocker
- fsora system_event - Look at system wide statistics
- fsora session <sid from all_session_waits> - Look at details of a session actual waiting
- Have a session or database trace and analyze the trace using TKPROF or the JAVA Trace Analyzer offered by OraForecast.com
Basic required knowledge of the different wait events is a must to solve such problems.
- If you need further information or assistance with performance tuning or PL/SQL development feel free to contact mailto:performance@oraforecast.com
History
- 2008.07.10 Start of documentation
Known bugs
- Reported Description of problem Fixed Release
- 2008.07.10 No issues known
Running example
- Not required
