Oracle query tuning : query with Order-by clause

[Former Member]

Hi

I am having a query in my database :

SELECT * FROM SAPR3.HRP1001 WHERE "MANDT" = 990

ORDER BY

"MANDT" , "OTYPE" , "OBJID" , "PLVAR" , "RSIGN" , "RELAT" , "ISTAT" , "PRIOX" , "BEGDA" , "ENDDA" ,"VARYF" , "SEQNR" ;

-

---

Autotrace output is :

Execution Plan

-

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4649 Card=171895 Byt

es=22862035)

1 0 SORT (ORDER BY) (Cost=4649 Card=171895 Bytes=22862035)

2 1 TABLE ACCESS (FULL) OF 'HRP1001' (Cost=1170 Card=171895

Bytes=22862035)

Statistics

-

---

0 recursive calls

5 db block gets

12157 consistent gets

11543 physical reads

0 redo size

38253080 bytes sent via SQL*Net to client

376841 bytes received via SQL*Net from client

34201 SQL*Net roundtrips to/from client

0 sorts (memory)

1 sorts (disk)

512992 rows processed

-

---

Since it is a issue with order by , it seems a PGA memory issue. there is 12GB PGA available but only 3GB gets allocated. pga_aggregate target is set in the DB. There is a index created for al the columns on order by, but it is not getting used.

pleas suggest me as I am running into major problems, i can post the output of any query u require from my side. Any help wil be highly apprciated.

Rishi

[lbreddemann · Accepted solution]

Hi Rishi,

> Since it is a issue with order by , it seems a PGA memory issue. there is 12GB PGA available but only 3GB gets allocated. pga_aggregate target is set in the DB.

Please be aware that for a non-parallel statement Oracle will allow max. 5% of the overall PGA to be used by one statement/session at a time.

If more temporary data is needed, Oracle will spill it out to the temporary tablespace.

> There is a index created for al the columns on order by, but it is not getting used.

I would guess that the table itself has more columns than the ones in the index.

As there are no selection criteria except the MANDT = predicate given, it's fair to assume that all data from the table need to be read.

This is the classic case where a FULL TABLE SCAN will be faster than a INDEX + TABLE ACCESS, even if the data needs to be sorted afterwards.

> pleas suggest me as I am running into major problems, i can post the output of any query u require from my side. Any help wil be highly apprciated.

You may think about making the Full Table Scan faster, e.g. by using parallel query.

regards,

Lars

[Former Member]

The query was alwasy spilling over to the One-Parse execution . It can be seen thru ST04N ->resource consumption-> sql work area trace.

An undocumented oracle parameter smmmax_size was set which allowed for more usage of physical memory by single process and there was no spillover to the TEMP tablespaces.

Also the File read time was analysed from Unix level ( From SAP thru ST04 ->filesystem wait s-> Avg rd (ms) and Ang writes (ms) which showed that reading from the File was not happening well. )

[lbreddemann]

> The query was alwasy spilling over to the One-Parse execution . It can be seen thru ST04N ->resource consumption-> sql work area trace.

>

> An undocumented oracle parameter smmmax_size was set which allowed for more usage of physical memory by single process and there was no spillover to the TEMP tablespaces.

>

> Also the File read time was analysed from Unix level ( From SAP thru ST04 ->filesystem wait s-> Avg rd (ms) and Ang writes (ms) which showed that reading from the File was not happening well. )

Hi Rishi,

the provided execution statistics prove the opposite:

>Statistics

>...

>0 sorts (memory)

> 1 sorts (disk)

>512992 rows processed

This indeed was a single-pass sort, which means it had to use the temp tablespace for one pass of the sorting/grouping.

Remember that Oracle distinguishes three kinds of sorts: 1. "in memory", 2. "single-pass" and 3. "multi-pass".

Only the first one won't need to spill out data to the disks. The others do this by definition.

BTW: the file read times in ST04 are aquired through Oracle V$ views and not directly from the OS - that can make a big difference sometimes.

regards,

Lars

[former_member204746]

what Oracle version are you using? which patches were installed? have you run update_STATS lately?