| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL performance on index?
HP rx4640 runs on HPUX (64 bits) for IntelŪ ItaniumŪ 2 , 4*1.5G CPUs,
4G memory, 2.4G data buffer
SELECT
"AUFPL" , "OBJNR" , "APLFL"
FROM
"AFVC"
WHERE
"MANDT" = :A0 AND "ARBID" IN ( :A1 , :A2 )#
This SQL executes 11 times and get 1,215,460 blocks from disk.
Executions Disk reads Reads/Exec Buffer gets Bgets/exec Proc. rows Rproc/Exe Bgets/row 11 1,215,460 110,496.40 1,532,611 139,328.30 2,218,008 201,637.10 0.7
So far, we can not move this query into BW system.
So, my question is, is there anyway to tune it?????
SQL> select count(*) from sapccp.afvc;
COUNT(*)
4013896
SQL> select count(distinct(arbid)) from sapccp.afvc;
COUNT(DISTINCT(ARBID))
338
SQL> select count(distinct(aufpl)) from sapccp.afvc;
COUNT(DISTINCT(AUFPL))
774009
SQL> select count(distinct(aplfl)) from sapccp.afvc;
COUNT(DISTINCT(APLFL))
1
In SAP world, filed MANDT is mandatory for every application data, actually, you can say ONLY ONE VALUE for MANDT in the production system.
Table definition:
MANDT MANDT CLNT 3 0 Client AUFPL CO_AUFPL NUMC 10 0 Routing number of operations in the order APLZL CO_APLZL NUMC 8 0 General counter for order .INCLUDE AFVGI STRU 0 0 General include structure of the operation data PLNFL PLNFOLGE CHAR 6 0 Sequence PLNKN PLNKN NUMC 8 0 Number of the task list node PLNAL PLNAL CHAR 2 0 Group Counter PLNTY PLNTY CHAR 1 0 Task List Type VINTV VINTV DEC 3 0 Increment between referenced operations PLNNR PLNNR CHAR 8 0 Key for Task List Group ZAEHL CIM_COUNT NUMC 8 0 Internal counter VORNR VORNR CHAR 4 0 Operation Number STEUS STEUS CHAR 4 0 Control key ARBID CR_OBJID NUMC 8 0 Object ID of the resource PDEST CR_PDEST CHAR 4 0 Printer for shop papers WERKS WERKS_D CHAR 4 0 Plant KTSCH KTSCH CHAR 7 0 Standard text key LTXA1 LTXA1 CHAR 40 0 Operation short text LTXA2 LTXA2 CHAR 40 0 Second line of the description TXTSP SPRAS LANG 1 0 Language Key VPLTY VPLNTY CHAR 1 0 Type of the referenced task list VPLNR VPLNNR CHAR 8 0 Group of the referenced task list VPLAL VPLNAL CHAR 2 0 Group counter of the referenced task list VPLFL VPLBFL CHAR 6 0 Referenced sequence in routing VGWTS VORGSCHL CHAR 4 0 Standard value key LAR01 LSTAR CHAR 6 0 Activity Type LAR02 LSTAR CHAR 6 0 Activity Type LAR03 LSTAR CHAR 6 0 Activity Type LAR04 LSTAR CHAR 6 0 Activity Type LAR05 LSTAR CHAR 6 0 Activity Type LAR06 LSTAR CHAR 6 0 Activity Type ZERMA DZERMA CHAR 5 0 Type of standard value calculation ZGDAT DZGDAT CHAR 4 0 Date when the standard value was calculated ZCODE DZCODE CHAR 6 0 Reference number for standard value code ZULNR DZULNR CHAR 5 0 Basis for standard value calculation LOANZ LOHNANZ DEC 3 0 Number of time tickets LOART LOHNART CHAR 4 0 Wage Type RSANZ CR_RS_ANZ NUMC 3 0 Number of confirmation slips QUALF QUALF CHAR 2 0 Suitability ANZMA ANZMS DEC 5 2 Number of employees RFGRP RUEFAGRP CHAR 10 0 Setup group category RFSCH RUEFASCHLU CHAR 10 0 Setup group key RASCH RUEARSCHLU CHAR 2 0 Setup type key AUFAK AUSCHUFAK DEC 5 3 Scrap factor LOGRP LOHNGRP CHAR 3 0 Wage group UEMUS UEMUSKZ CHAR 1 0 Required overlapping UEKAN UEKANKZ CHAR 1 0 Optional overlapping FLIES FLIESSKZ CHAR 1 0 Indicator: continuous flow production SPMUS SPLITTUNG CHAR 1 0 Required splitting SPLIM SPLITTANZ DEC 3 0 Maximum number of splits ABLIPKZ ABLIPKZ CHAR 1 0 Indicator: simultaneous teardown and wait RSTRA RSTRA CHAR 2 0 Reduction strategy per operation/activity SUMNR SUMKNTNR NUMC 8 0 Node number of the superior operation SORTL SORTI CHAR 10 0 Sort string for non-stock info records LIFNR LIFNR CHAR 10 0 Account Number of Vendor or Creditor PREIS PREIS CURR 11 2 Price PEINH PEINH DEC 5 0 Price Unit SAKTO KSTAR CHAR 10 0 Cost Element WAERS WAERS CUKY 5 0 Currency Key INFNR INFNR CHAR 10 0 Number of purchasing info record ESOKZ ESOKZ CHAR 1 0 Purchasing info record category EKORG EKORG CHAR 4 0 Purchasing Organization EKGRP VG_EKGRP CHAR 3 0 Purchasing group for external processing KZLGF KZLGF CHAR 1 0 Indicator: fixed lot external processing KZWRTF KZWRTF CHAR 1 0 Indicator: Fixed price for external processing MATKL MATKL CHAR 9 0 Material Group DDEHN DAUDEHN CHAR 1 0 Indicator: flexible duration ANZZL ANZKAP INT1 3 0 Number of capacities required PRZNT APROZENT INT1 3 0 Work percentage MLSTN MILESTONE CHAR 5 0 Usage PPRIO PRIORITAET CHAR 2 0 Priority BUKRS BUKRS CHAR 4 0 Company Code ANFKO ANFKO CHAR 10 0 Requesting cost center ANFKOKRS ANFKOKRS CHAR 4 0 Controlling area of the requesting cost center INDET INDET CHAR 1 0 Key for calculation LARNT LSTAR CHAR 6 0 Activity Type PRKST PRKST CURR 11 2 Costs in the activity APLFL PLNFOLGE CHAR 6 0 Sequence RUECK CO_RUECK NUMC 10 0 Completion confirmation number for the operation RMZHL CO_RMZHL NUMC 8 0 Confirmation counter PROJN PS_PSP_ELE NUMC 8 0 Work breakdown structure element (WBS element) OBJNR J_OBJNR CHAR 22 0 Object number SPANZ SPLITTERM DEC 3 0 Actual number of splits BEDID BEDID NUMC 12 0 ID of the capacity requirements record BEDZL CIM_COUNT NUMC 8 0 Internal counter BANFN CO_BANFN CHAR 10 0 Purchase requisition number BNFPO CO_BNFPO NUMC 5 0 Item number of the purchase requisition in the order LEK01 CO_LEIKZ CHAR 1 0 Indicator: No remaining activity expected LEK02 CO_LEIKZ CHAR 1 0 Indicator: No remaining activity expected LEK03 CO_LEIKZ CHAR 1 0 Indicator: No remaining activity expected LEK04 CO_LEIKZ CHAR 1 0 Indicator: No remaining activity expected LEK05 CO_LEIKZ CHAR 1 0 Indicator: No remaining activity expected LEK06 CO_LEIKZ CHAR 1 0 Indicator: No remaining activity expected SELKZ CO_SELKZ CHAR 1 0 Selection indicator for costing line items KALID WFCID CHAR 2 0 Factory Calendar FRSP FRSP CHAR 1 0 Earliest possible activity / Latest possible activity STDKN STDKN NUMC 8 0 Node of operation within reference operation set ANLZU ANLZU CHAR 1 0 Overall condition of technical system ISTRU ISTRU CHAR 18 0 Assembly ISTTY STLTY CHAR 1 0 BOM category ISTNR STNUM CHAR 8 0 Bill of material ISTKN STLKN NUMC 8 0 BOM item node number ISTPO CIM_COUNT NUMC 8 0 Internal counter IUPOZ UPOSZ CHAR 4 0 Sub-item number EBORT EBORT CHAR 20 0 Installation Point for Subitem VERTL CR_VERTN CHAR 8 0 Distr.cap.reqmts (plant maint.,process order, network) LEKNW LEKNW CHAR 1 0 Indicator: No Remaining Work Expected NPRIO NW_PRIO CHAR 1 0 Priority PVZKN CO_APLZL NUMC 8 0 General counter for order PHFLG PHFLG CHAR 1 0 Indicator: Phase PHSEQ PHSEQ CHAR 2 0 Control Recipe Destination KNOBJ KNOBJ NUMC 18 0 Number of Object with Assigned Dependencies ERFSICHT QERFSICHT CHAR 2 0 Recording View QPPKTABS QPPKTABS CHAR 1 0 Flow Variants for Inspection Point Completion OTYPE OTYPE CHAR 2 0 Object Type OBJEKTID OBJEKTID NUMC 8 0 Object ID QLKAPAR QL_KAPART CHAR 3 0 Capacity category RSTUF RSTUF NUMC 1 0 Reduction level which reduces operation lead time NPTXTKY TXTKY CHAR 12 0 Internal text number (12-character) SUBSYS QEIFSUBSYS CHAR 6 0 Subsystem Identifier for QM Subsystem Interface PSPNR PS_SPS_ELE NUMC 8 0 Standard WBS element PACKNO PACKNO NUMC 10 0 Package number TXJCD TXJCD CHAR 15 0 Jurisdiction for Tax Calculation - Tax Jurisdiction Code SCOPE SCOPE_CV CHAR 2 0 Object Class GSBER GSBER CHAR 4 0 Business Area PRCTR PRCTR CHAR 10 0 Profit Center NO_DISP NO_DISP_PLUS CHAR 1 0 Effective for Materials Planning QKZPRZEIT QKZPRZEIT CHAR 1 0 Interval = Time (Time-Related) QKZZTMG1 QKZZTMG1 CHAR 1 0 Quantity Confirmation for Each Partial Lot QKZPRMENG QKZPRMENG CHAR 1 0 Creation Cycle = Quantity (Quantity-Reference) QKZPRFREI QKZPRFREI CHAR 1 0 Interval Without Reference to Time or Quantity KZFEAT QKZFEAT CHAR 1 0 Indicator Is Not Used Currently QKZTLSBEST QKZTLSBEST CHAR 1 0 Operation with Stock-Related Partial Lot Assignment AENNR AENNR CHAR 12 0 Change Number CUOBJ_ARB CUOBJ NUMC 18 0 Configuration (internal object number) EVGEW EV_WEIGHTD DEC 8 0 Aggregation weight for POC (PS progress) ARBII CR_OBJID_I NUMC 8 0 Actual operating resources object identification WERKI WERKS_I CHAR 4 0 Actual plant CY_SEQNRV CY_SEQNRV NUMC 14 0 Sequence number operation KAPT_PUFFR KAPT_PUFFR INT4 10 0 Operation floats after finite scheduling (in seconds) EBELN EBELN CHAR 10 0 Purchasing Document Number EBELP EBELP NUMC 5 0 Item Number of Purchasing Document WEMPF WEMPF CHAR 12 0 Goods Recipient/Ship-To Party ABLAD ABLAD CHAR 25 0 Unloading Point CLASF CLASF CHAR 1 0 Ind.: Take Activity Into Account for Project Summarization FRUNV FRUNV CHAR 1 0 Indicator: External procurement data incomplete ZSCHL AUFZSCHL CHAR 6 0 Overhead key KALSM AUFKALSM CHAR 6 0 Costing Sheet SCHED_END SCEND_ACT CHAR 1 0 Ind.: Purchase requisition at activity finish date NETZKONT NETZKONT CHAR 1 0 Indicator for the account assignment of a network(hdr/act.) OWAER OWAERS CUKY 5 0 Object currency for network activity AFNAM AFNAM CHAR 12 0 Name of requisitioner/requester BEDNR BEDNR CHAR 10 0 Requirement Tracking Number KZFIX CN_KZFIX CHAR 1 0 Indicator: Purchasing info record data are fixed PERNR CO_PERNR NUMC 8 0 Personnel Number FRDLB CO_FRDLB CHAR 1 0 Indicator: Externally processed op. with subcontracting QPART QPART CHAR 8 0 Inspection Type LOEKZ AUFLOEKZ CHAR 1 0 Deletion flag WKURS WKURS DEC 9 5 Exchange rate PROD_ACT PROD_ACT CHAR 1 0 Indicator: Activity is a production activity FPLNR FPLNR CHAR 10 0 Billing plan number / invoicing plan number OBJTYPE OCM_OBJ_TYPE CHAR 1 0 Change indicator CH_PROC OCM_CH_PROC CHAR 1 0 Process that has lead to the change of an object KLVAR CK_KLVAR CHAR 4 0 Costing Variant KALNR CK_KALNR NUMC 12 0 Cost Estimate Number for Cost Est. w/o Qty Structure FORDN SFORDN CHAR 10 0 Framework order FORDP FORDP NUMC 5 0 Item of framework order MAT_PRKST MAT_PRKST CURR 11 2 Material planning in networks: primary costs PRZ01 CO_PRZNR CHAR 12 0 Business Process RFPNT CN_RFPNT CHAR 20 0 Reference point for BOM transfer FUNC_AREA FKBER CHAR 16 0 Functional Area TECHS TECHS CHAR 12 0 Parameter Variant/Standard Variant ADPSP ADDCOMPARE_CORE CHAR 40 0 Reference Element PM/PS
Index definition, SAP standard delivered, SAP only use B-tree index for OLTP (SAP R/3) system.
index AFVC~0(key): MANDT,AUFPL,APLZL index AFVC~1: MANDT,RUECK index AFVC~2: MANDT,ARBID index AFVC~3: MANDT,PROJN Table size: Owner Object Type TablespaceKBytes
SAPCCP AFVC TABLE PSAPCCP 3,015,680 SAPCCP AFVC~0 INDEX PSAPCCP 311,296 SAPCCP AFVC~1 INDEX PSAPCCP 172,032 SAPCCP AFVC~2 INDEX PSAPCCP 253,952 SAPCCP AFVC~3 INDEX PSAPCCP172,032 Received on Sun Jun 11 2006 - 11:16:12 CDT
 |
 |