Accepted Solutions (1)
Answers (2)
Hi Peter,
> This single statement accounts for over 80% of all CPU time in the DB.
Where do you get this information from? AWR? How often is this single SQL executed in a specific time frame? Is the CPU load caused by a single run or in sum of all executed SQLs? Just looking at the shared pool (e.g. by DBACOCKPIT) gives you wrong values as other SQLs are aged out and so on.
> There are quite a lot of comments that in abap coding SELECT DISTINCT should be avoided and replaced with a select to an internal table followed by sort + delete adjacent duplicates.
That might work pretty well, if the data set is not very large, but just think about the needed memory if you got a large dataset. The large amount of data needs to be transferred to the application server first and handled afterwards.
> Because, "SELECT DISTINCT" does sort operation. If you take a look at same queries with the DISTINCT statement and without the DISTINCT statement, both execution plans should be same.
@Orkun: No, it certainly does not. This was the case in Oracle 10g pre-times. Oracle introduced hash aggregation in Oracle 10g. Here is just a tiny test case on my 11.2.0.3.2 database.
http://oracle-randolf.blogspot.de/2011/01/hash-aggregation.html
SYS@T11:133> create table TESTAB as select * from dba_objects;
SYS@T11:133> select distinct(owner) from TESTAB;
> The surprising thing was that the select WITHOUT the distinct clause was actually slower (and had higher CPU time).
The question here is - are the execution plans the same? How did you find out the "higher CPU time"? CPU is usually caused by excessive logical I/O or specific functions (like hashing and so on). Oracle SQL Monitor is perfect to determine the CPU causer.
> Running the same select, including the DISTINCT and adding a FULL hint reduced the CPU time by about half. (the table only has about 10000 rows so a full table scan doesn't take long at all).
That observation confirms the suspicion about a different execution plan. It seems like an index access and the corresponding table access (by rowid) causes unnecessary logical I/O (maybe bad clustering factor as well) and high CPU time is the consequence. Execution plan information would make everything clear (especially with possible filters). By the way the amount of rows has nothing to do with the work that needs to be done by a full table scan (high water mark). You can have a table with only one row, but read hundreds of GBs by FTS
> lack of official supporting documentation is what brought me here.
ABAP developers are usually not very good SQL developers and so they do what they know best. Transfer all to the ABAP application server and handle it there. This might work for the first time (when the dataset is not huge and the parallel usage is not high), but unfortunately it will not scale out properly.
The database engine is able to do it much smarter and better in many cases (if the SQL is written well and the database structure is fitting) , but you need to know how to handle different database platforms to get the best result.
Regards
Stefan
Hi Peter,
thanks for the provided examples. Your observations are clear now. Michael nearly hit the issue why you see such a (marginal) performance difference "00:00:00.04" to "00:00:00.09".
> I though the same number of rows would be fetched first and then the HASH UNIQUE operation applied to the fetched dataset. But what we see is that the number of consistent gets is less if the DISTINCT is included. Can you make sense of this?
At first you are using AUTOTRACE, which has no granularity about the buffer gets at all .. however ...
Yes, but in case of a DISTINCT much less data needs to be transferred/fetched from the database to the client. Fetches are limited by the array size (which is determined dynamically by SAP DBSL depending on codepage, table and column definition).
Here is a tiny example for clarification (i used literals and hints for getting the same execution plan, because it makes no difference here). My example is a more drastically case, but it demonstrates the impact of the array size even better
-- Building test environment on Oracle 11.2.0.3.2
SYS@T11:12> create table TABLEA (MANDT VARCHAR(10), FIELD1 VARCHAR(10), FIELD2 VARCHAR(10), FIELD3 VARCHAR(10), FIELD4 VARCHAR(10), FIELD5 VARCHAR(10));
SYS@T11:12> create index TABLEAI on TABLEA(MANDT, FIELD1, FIELD3, FIELD5);
SYS@T11:12> begin
for i in 1 .. 10000 loop
insert into TABLEA values('010','AAAA','BBBB','CCCC','DDDD','EEEE');
end loop;
commit;
end;
/
-- SELECTs with default array size(15)
SYS@T11:12> select /*+ GATHER_PLAN_STATISTICS INDEX(TABLEA TABLEAI) */ distinct FIELD1 , FIELD2 , FIELD3 , FIELD4
from TABLEA
where "MANDT" = '010' and "FIELD1" = 'AAAA' and "FIELD3" = 'CCCC' and "FIELD5" = 'EEEE';
As you can see 10.000 rows are fetched from index and the table (86 buffer gets in sum), but only one row needs to be transferred to the client (ID 0).
-- SELECTs with default array size (15)
SYS@T11:12> select /*+ GATHER_PLAN_STATISTICS INDEX(TABLEA TABLEAI) */ FIELD1 , FIELD2 , FIELD3 , FIELD4
from TABLEA
where "MANDT" = '010' and "FIELD1" = 'AAAA' and "FIELD3" = 'CCCC' and "FIELD5" = 'EEEE';
In this case 10.000 rows needs to be transferred to the client (ID 0), but only 15 rows are transferred by each fetch (so you maybe need to visit the same blocks several times for transferring all data).
Now let's tune it up to the max array size of 5.000 for SQL*Plus.
SYS@T11:12> set arraysize 5000
SYS@T11:12>select /*+ GATHER_PLAN_STATISTICS INDEX(TABLEA TABLEAI) */ distinct FIELD1 , FIELD2 , FIELD3 , FIELD4
from TABLEA
where "MANDT" = '010' and "FIELD1" = 'AAAA' and "FIELD3" = 'CCCC' and "FIELD5" = 'EEEE';
Look closely at the buffer gets. The "SELECT DISTINCT" needs the same amount of buffer gets as before (with an array size of 15), because of only 1 row needs to be returned to the client and this fits in one fetch call.
SYS@T11:12> set arraysize 5000
SYS@T11:12> select /*+ GATHER_PLAN_STATISTICS INDEX(TABLEA TABLEAI) */ FIELD1 , FIELD2 , FIELD3 , FIELD4
from TABLEA
where "MANDT" = '010' and "FIELD1" = 'AAAA' and "FIELD3" = 'CCCC' and "FIELD5" = 'EEEE';
Look closely at the buffer gets now. The same SELECT (without DISTINCT) needs only 90 buffer gets now. It needed 1418 buffer gets with a much smaller array size.
I am pretty sure, that this is the minimal "performance" difference that you see. You need much more fetch calls, SQL*Net Ttraffic and logical I/O for transferring the data (for the SQL without the distinct). Just try your example once again with the max array size and you will see this.
> To be cynical, if I can tune this statement it will look good to the customer based on information from the SQL cache.
It is better to explain the customer the backgrounds instead of doing useless implementations ... customers are ordering consultants to help them getting their problems solved and not only to implement their thoughts, which are wrong from time to time.
> There is no CPU bottleneck on the system, so tuning this statement is not critical.
Well your SQLs are already pretty nice (7 buffer gets per row for the DISTINCT and 6.6 buffer gets per row for the bare SELECT), but as previously explained these values (especially the last value) are currently wrong, because of the SAP DBSL is using a larger fetch size. Maybe you can reduce the buffer gets by eliminating the table access by row id or change the application logic to execute less SQLs (which is even better, if possible).
Regards
Stefan
P.S.: You can verify the client communication / fetch behavior with an OPI trace as well.
| User | Count |
|---|---|
| 90 | |
| 10 | |
| 10 | |
| 10 | |
| 7 | |
| 7 | |
| 6 | |
| 5 | |
| 4 | |
| 3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.