James Yang wrote:
> 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.
>
snip
>
> 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
Since MANDT is the first field in every index and the column has only
one distinct value, the indexes are not very helpful. The system will
probably do a full table scan.
For our app (not SAP) we would decide to switch the fields of the 2nd
index in this case (ARBID, MANDT) because ARBID has 338 distinct values.
But also with this you get ~20.000 rows per query (4013896/338*2).
Adding the selected fields to the index will lead to "key only" access.
But if this is a measurable benefit or not is hard to say without
testing it.
Received on Sun Jun 11 2006 - 11:35:48 CDT
