Performance problems of an ORACLE parallel server due database to excessive pinging.
- 1. Insert-intensive tables and indexes with standard FREELISTS and FREELIST GROUPS parameters
The segment header block is read and changed when inserting records. It contains the free storage management for the segment. If you increase the FREELISTS and the FREELIST GROUPS storage parameters for the table or index, freespace management can be processed in parallel and the pinging for the INSERT can be avoided to a large extent.
- 2. Update-intensive tables for which different instances change different records.
If hotspot records that are changed by different instances happen to ocur in the same block, then the block is pinged. This is caused by the fact that distributed locks work on block and not on record level. The probability of this unwanted effect is higher the narrower and smaller the table is. With the help of the PCTUSED and PCTFREE storage parameters, you can reduce the number of records in a block.
- 3. Remaining cases
If excessive pinging cannot be attributed to a table or index of category (1) or (2), it cannot be eliminates using ORACLE means. In this case, the performance can only be improved by a better partitioning of the workload.
A change of the FREELISTS and FREELIST GROUPS parameters is only possible with a reorganization of the table or index. They can no longer be changed later by an ALTER command.
The optimal value for FREELIST GROUPS is the number of database instances. If you plan to increase the number of instances in the future, you should use the higher value from the beginning. The number of FREELISTS should be as large as the maximum number of update processes that are connected to a database instance.
PCTUSED and PCTFREE should only be changed with good cause and only for small tables. To ensure that only one record is stored per data block, you should set PCTUSED to 1 and PCTFREE to 99. We recommend here that you reorganize the table so that the STORAGE parameters also take effect for the existing records. To achieve a satisfactory performance during number assignment (Customizing setting "Buffering in local file"), the described change must be made for table NRIV_LOKAL.
List of tables for which increased FREELIST GROUPS and FREELISTS parameters are recommended:
AABLG AEOI AFFL AFKO
AFPO AFRU AFVC AFVU
AFVV ANLB ANLC AUAK
AUFK BKPF BPIG BSAD
BSAS BSID BSIK BSIM
BSIS CDCLS CMFK COBK
COBRA COBRB COEP COEPL
COKA COSL COSP COSPD
COSS DBSNP DBTABPRT EBAN
EIPA EKBE EKET EKKO
EKPO INDX JEST JSTO
KBKO KNVD KOCLU LIKP
LIPS LQUA LTAK LTAP
LTBK LTBP MDKP MKPF
MONI MSEG MSTA M_AANL
M_MBRK M_MEKK M_ORDE M_PREM
M_PRPM M_REVN M_VMSK M_VMVA
M_VMVL NAST ONR00 ONROK
ONROR ONROS ONROV PCL1
PCL2 PROW REGUC REGUH
REGUV RESB RFBLG RKPF
S031 S033 S502 S507
S508 SNAP SOC3 SOFM
SOOD STXH STXL TBTCO
TBTCS TSP01 TSP02 TST01
TST03 VAKPA VAPMA VARI
VBAK VBAP VBBE VBEP
VBFCL VBKD VBPA VBRK
VBRP VBUK VBUP VEPVG
VKDFS VLKPA VLPMA VRKPA
Additional key words
Parallel server, OPS, PSO, FREELISTS, FREELIST GROUPS, PCTUSED, PCTFREE, pinging
VRPMA
No comments:
Post a Comment