demopaar@ORCL> create function my_slow_function return number
2 is
3 begin
4 -- Wait 1/100 sec
5 dbms_lock.sleep(.01);
6 return 42;
7 end;
8 /
Functie is aangemaakt.
demopaar@ORCL> create table mytable (id,name,status,mydate,filler)
2 as
3 select level
4 , 'Name ' || to_char(level)
5 , mod(level,20) + 1
6 , trunc(sysdate,'yyyy') + numtodsinterval(dbms_random.value * (trunc(sysdate)+1-trunc(sysdate,'yyyy')),'day')
7 , lpad('*',1000,'*')
8 from dual
9 connect by level <= 50000
10 /
Tabel is aangemaakt.
demopaar@ORCL> create table mytable_details (id,mytable_id,name)
2 as
3 select level
4 , level
5 , 'Name ' || to_char(level)
6 from dual
7 connect by level <= 50000
8 /
Tabel is aangemaakt.
demopaar@ORCL> alter table mytable add constraint mytable_pk primary key (id)
2 /
Tabel is gewijzigd.
demopaar@ORCL> alter table mytable_details add constraint mytable_details_pk primary key (id)
2 /
Tabel is gewijzigd.
demopaar@ORCL> alter table mytable_details add constraint mytable_details_mytable_fk foreign key (mytable_id) references mytable(id)
2 /
Tabel is gewijzigd.
demopaar@ORCL> create index myindex on mytable(status,mydate)
2 /
Index is aangemaakt.
demopaar@ORCL> create index mytable_details_fk_i on mytable_details(mytable_id)
2 /
Index is aangemaakt.
demopaar@ORCL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)
PL/SQL-procedure is geslaagd.
demopaar@ORCL> exec dbms_stats.gather_table_stats(user,'mytable_details',cascade=>true)
PL/SQL-procedure is geslaagd.
So both tables contain 50,000 rows. Table mytable contains 20 different statuses, each status occurring 2500 times. So the query below, with predicate "status = 3" retrieves 2500 rows. And therefore the query will take at least 25 seconds, because of the my_slow_function() call in its select list.
select t.id
, t.name
, t.mydate
, td.name detail_name
, my_slow_function()
from demopaar.mytable t
inner join demopaar.mytable_details td on (t.id = td.mytable_id)
where t.status = 3
order by t.mydate desc
And the execution plan of this query (with the database parameter optimizer_mode at its default value all_rows) is using a nice hash join:
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 104K| 2288 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 2500 | 104K| 2288 (1)| 00:00:01 |
|* 2 | HASH JOIN | | 2500 | 104K| 2287 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| MYTABLE | 2500 | 67500 | 2234 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| MYTABLE_DETAILS | 50000 | 781K| 53 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ID"="TD"."MYTABLE_ID")
3 - filter("T"."STATUS"=3)
Typically, in a web report, you only want the first few rows -say 15- out of a sorted set to appear on the page. And the page contains buttons to navigate to the next/previous set of 15 rows. To only select the first 15 rows, wrap up the query above and use pseudocolumn rownum twice, for the lower and upper bound, like this:
select *
from ( select x.*
, rownum rn
from ( select t.id
, t.name
, t.mydate
, td.name detail_name
, my_slow_function
from demopaar.mytable t
inner join demopaar.mytable_details td on (t.id = td.mytable_id)
where t.status = 3
order by t.mydate desc
) x
where rownum <= 15
)
where rn >= 1
Looking at the brackets, it looks like this query first needs to identify the 2500 rows, then sort them, execute the slow function, and only then limit the number of rows to 15. However, Oracle is smarter than that and uses an optimization technique for pagination queries, as you can see here:
rwijk@ORCL> set serveroutput off
rwijk@ORCL> alter session set statistics_level = all
2 /
Sessie is gewijzigd.
rwijk@ORCL> select *
2 from ( select x.*
3 , rownum rn
4 from ( select t.id
5 , t.name
6 , t.mydate
7 , td.name detail_name
8 , my_slow_function
9 from demopaar.mytable t
10 inner join demopaar.mytable_details td on (t.id = td.mytable_id)
11 where t.status = 3
12 order by t.mydate desc
13 ) x
14 where rownum <= 15
15 )
16 where rn >= 1
17 /
ID NAME MYDATE DETAIL_NAME MY_SLOW_FUNCTION RN
------- ---------- ------------------- ----------- ---------------- ----
32102 Name 32102 04-11-2016 16:38:57 Name 32102 42 1
8842 Name 8842 04-11-2016 14:18:24 Name 8842 42 2
44942 Name 44942 04-11-2016 13:47:10 Name 44942 42 3
5662 Name 5662 04-11-2016 13:34:54 Name 5662 42 4
12422 Name 12422 04-11-2016 12:46:42 Name 12422 42 5
41562 Name 41562 04-11-2016 11:35:30 Name 41562 42 6
38002 Name 38002 04-11-2016 09:36:56 Name 38002 42 7
26922 Name 26922 04-11-2016 09:03:07 Name 26922 42 8
3442 Name 3442 04-11-2016 08:38:25 Name 3442 42 9
13842 Name 13842 04-11-2016 08:10:28 Name 13842 42 10
8042 Name 8042 04-11-2016 06:13:13 Name 8042 42 11
14522 Name 14522 04-11-2016 06:11:02 Name 14522 42 12
7642 Name 7642 04-11-2016 01:45:08 Name 7642 42 13
31122 Name 31122 04-11-2016 01:35:38 Name 31122 42 14
36322 Name 36322 04-11-2016 00:57:18 Name 36322 42 15
15 rijen zijn geselecteerd.
rwijk@ORCL> pause
rwijk@ORCL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'))
2 /
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6p620zht2qhp8, child number 1
-------------------------------------
select * from ( select x.* , rownum rn from
( select t.id , t.name ,
t.mydate , td.name detail_name
, my_slow_function from
demopaar.mytable t inner join
demopaar.mytable_details td on (t.id = td.mytable_id)
where t.status = 3 order by t.mydate desc
) x where rownum <= 15 ) where rn >= 1
Plan hash value: 2786083199
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 |00:00:00.17 | 52 |
|* 1 | VIEW | | 1 | 15 | 15 |00:00:00.17 | 52 |
|* 2 | COUNT STOPKEY | | 1 | | 15 |00:00:00.17 | 52 |
| 3 | VIEW | | 1 | 15 | 15 |00:00:00.17 | 52 |
| 4 | NESTED LOOPS | | 1 | 15 | 15 |00:00:00.01 | 52 |
| 5 | NESTED LOOPS | | 1 | 15 | 15 |00:00:00.01 | 37 |
| 6 | TABLE ACCESS BY INDEX ROWID | MYTABLE | 1 | 2500 | 15 |00:00:00.01 | 19 |
|* 7 | INDEX RANGE SCAN DESCENDING| MYINDEX | 1 | 15 | 15 |00:00:00.01 | 4 |
|* 8 | INDEX RANGE SCAN | MYTABLE_DETAILS_FK_I | 15 | 1 | 15 |00:00:00.01 | 18 |
| 9 | TABLE ACCESS BY INDEX ROWID | MYTABLE_DETAILS | 15 | 1 | 15 |00:00:00.01 | 15 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=15)
7 - access("T"."STATUS"=3)
8 - access("T"."ID"="TD"."MYTABLE_ID")
36 rijen zijn geselecteerd.
This query doesn't take 25+ seconds, but only 0.17 seconds, as it only fetches 15 rows. The my_slow_function call is executed only 15 times. This happens because of the COUNT STOPKEY [filter(ROWNUM<=15)] line. This basically tells the database to execute the steps below (lines 3-9), but stop once it has retrieved 15 rows. And as you can see from the A-Rows column, it works perfectly here. The execution plan starts with an INDEX RANGE SCAN DESCENDING of myindex (status,mydate). All statuses 3 are retrieved and since the index itself is sorted, it just needs to 4 consistent gets (see Buffers column line 7) to identify those 15 rows. Then the columns of mytable are retrieved and with two (!) NESTED LOOPS the mytable_details are joined. So this is an effective optimization technique, which prevents having to execute the function call 2500 times.Lots of people have been written about pagination in the past. If you want to read more about the technique itself or its side effects, here you can read a few of the better articles:
- On ROWNUM and Limiting Results, by Tom Kyte in Oracle Magazine
- Partial Results - fetch next page (offset method) by Markus Winand
- Getting first rows of a large sorted result set quickly - NOSORT issues, by Randolf Geist
APEX
Now let's have a look at what APEX does with our query. In APEX you enter your query via the GUI into the APEX repository and on runtime, APEX constructs and executes dynamic SQL around the query you entered. The exact SQL constructed depends on numerous APEX settings. By SQL tracing your APEX application you can see what exact query APEX constructed and executed.
We'll start by looking at an LOV query, then classic reports and finally interactive reports. At a client site, I tested it on version 4.2.4 and privately, the basis of this post, I use 5.0.3 (in a prebuilt App Development VM). I saw no differences between the two versions for this story.
APEX List of Values
This is the one with the least amount of variations. In an LOV query, we see APEX uses two variants of the pagination query. One for the very first set of rows, with just an upper bound bind variable:
select a.* from (
select t.name || ' | ' ||
to_char(t.mydate,'dd-mm-yyyy hh24:mi:ss') || ' | ' ||
td.name || ' | ' ||
my_slow_function description
, t.id
from mytable t
inner join mytable_details td on (t.id = td.mytable_id)
where t.status = 3
order by t.mydate desc
)a
where ROWNUM <= :p$_max_rows
and one for the subsequent sets, also containing an offset:
select * from (select a.*, ROWNUM as apx$rownum from (select t.name || ' | ' ||
to_char(t.mydate,'dd-mm-yyyy hh24:mi:ss') || ' | ' ||
td.name || ' | ' ||
my_slow_function description
, t.id
from mytable t
inner join mytable_details td on (t.id = td.mytable_id)
where t.status = 3
order by t.mydate desc
)a
where ROWNUM <= :p$_max_rows) where apx$rownum >= :p$_first_row
So, APEX LOV queries use the pagination optimization technique and are efficient. The user experience is that the first set of rows is taking 0.15+ seconds, the next set 0.3+ seconds, then 0.45+, et cetera. Below is tkprof snippet from clicking on the "Next" button on the page containing rows 31-45, which needs to retrieve the rows 46-60:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.03 0.64 8096 8953 4 16 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.03 0.64 8096 8953 4 16
We see the elapsed time of the fetch taking 0.6+ seconds, as expected. Also worth noting is that APEX uses a single fetch to retrieve 16 rows. A nice bulk fetch. It fetches 16, not 15 rows, presumably because APEX needs to know whether to display the "Next" button when rendering the new page. If only 15 rows are present, the 16th row would be missing, and the "Next" button wouldn't be displayed.
APEX Classic reports
The queries issued from classic reports are very different from LOV's, since they don't use the pagination techique under the covers. If they did, the "Download" functionality wouldn't retrieve all rows, but just the rows currently being displayed. Which is probably not what you want. Also, user defined sorting would break, since only the retrieved rows -the small set- would be sorted. A classic report query therefore sets out to retrieve all rows, using the hash join plan in my example, but it just doesn't fetch all rows from the cursor, only the number of rows displayed, plus 2. Our query in a classic report, with an extra search box as indicated in the wizard, looks like this:
select * from (
select t.id
, t.name
, t.mydate
, td.name detail_name
, my_slow_function
from mytable t
inner join mytable_details td on (t.id = td.mytable_id)
where t.status = 3
order by t.mydate desc
) where (
instr(upper("NAME"),upper(nvl(:P20_REPORT_SEARCH,"NAME"))) > 0
)
And executing this query takes the full 25 seconds. A tkprof snippet from pressing "Next" on the page with rows 31-45, to get to rows 46-60:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 62 0.47 25.18 8096 8953 4 62 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 64 0.47 25.18 8096 8953 4 62
Three things to note here:
- Even though we need only 15 rows, the first 45 rows are also fetched, because no pagination (offset) is used.
- A mysterious extra fetch is done. For 60 rows, we expect 61 fetches: 60 rows and one more to know whether APEX should render a "Next" button. So why is the number of fetches
+2 ? - Classic reports don't bulk fetch like LOV's do. We need 62 single row fetches here instead of a single bulk fetch.
- Use an additional functional default filter, if applicable. For example, only select rows where mydate is more recent than a user provided value, default sysdate minus five days. This will limit the number of rows retrieved and will possibly flip the execution plan to use a nested loop.
- Write your own pagination into the query, without using an offset. Internally, APEX keeps track of where you are in your pagination navigation. If you hover over the "Next" button, you see "javascript:apex.widget.report.paginate('
', {min:16,max:15,fetched:15});". And if you follow the numbers in the second argument around javascript, you can see the first two end up in Oracle package variables wwv_flow.g_flow_current_min_row and wwv_flow.g_flow_current_max_rows. You can't use Oracle package variables in a SQL query, but by writing a wrapper package around these variables, you can use them, like this:
select * from (
select * from (
select t.id
, t.name
, t.mydate
, td.name detail_name
, my_slow_function
from mytable t
inner join mytable_details td on (t.id = td.mytable_id)
where t.status = 3
order by t.mydate desc
) where (
instr(upper("NAME"),upper(nvl(:P20_REPORT_SEARCH,"NAME"))) > 0
)) x where rownum <= pagination_hack.min_row + pagination_hack.max_rows
For example, for the second batch of 15 rows, by adding the 16 and 15, the predicate is "rownum <= 31". This is just enough to fetch all visible rows and let APEX know that a "Next" button is needed. APEX takes care of discarding the first set of 15 rows. Note (again) that this practice is not recommended, since it'll break your download functionality and gives unexpected sorting when you click on a sortable column. And you cannot sensibly use a pagination type with a Z.
The best way of addressing performance issues on classic reports is the same as for interactive reports. So hang on.
APEX Interactive reports (IR)
Interactive reports are great for the end user for giving them LOTS of options, to sort, filter, aggregate and more. APEX handles the user defined settings by dynamically adjusting the query fired to the database. Very different from a classic report, for which the query stays the same. This also makes it non trivial to demonstrate how the query arriving at the database will look like. Below is what I can come up with, after tracing lots of variants. It's obviously still far from complete and leaves many options untold, but it gives you an idea how an IR generated query looks like:
select null as apxws_row_pk,
[ all visible columns ,]
[ in case Maximum Row Count specified or Row Ranges X to Y of Z: count(*) over () as apxws_row_cnt]
from ( select *
from ( select [ all columns (b.*), including LOV columns (Ln.descriptor)]
from ( select *
from ( [ your APEX-report query ] )
) b,
[ outer join LOV tables ]
) r
where [ your IR filters ]
) r
[ in case Max Row Count specified: where rownum <= to_number(:APXWS_MAX_ROW_CNT) ]
order by [ IR user sort ]
Basically, the query is a non-pagination query that sets out to retrieve all rows. You can trim the result set by specifying the Maximum Row Count property.
There are two settings and a characteristic of the query execution plan important to the performance of IR queries:
- Pagination Type
- Maximum Row Count
- Whether the base query can be fetched from directly
The three possible options for an IR pagination type are None, Row Ranges X to Y of Z, and Row Ranges X to Y. And if you specify "Row Ranges X to Y of Z", APEX needs to calculate the Z and it does so by adding the analytic function "count(*) over ()" to the outermost block. To calculate this sum, all rows must be visited and the query will therefore never be able to fetch directly. If your IR query will retrieve lots of rows and performance is important, you should choose the Pagination type "Row Ranges X to Y", without the Z.
Maximum Row Count
If your base query retrieves too many rows, you might want to reduce this number by specifying the Maximum Row Count. If you set it to -let's say- 1000, an end user probably won't hit the "Next" button so many times that he/she will hit that number. There are some issues with this. First one is that the download functionality will be deemed broken, as only 1000 rows will appear in the file. A second one is that the user might be unpleasantly surprised when he starts sorting the 1000 rows and he expects rows at the top that are now being filtered. But if these disadvantages don't mean much to you, you can use this property for tuning purposes.
If you specify a non null value for Maximum Row Count, APEX will add a "where rownum <= to_number(:APXWS_MAX_ROW_CNT)" predicate to your query. This sounds nice, as it will introduce a COUNT STOPKEY in the query execution plan, and get us pagination-query-type performance. However, specifying the Maximum Row Count also introduces the "count(*) over ()" expression, regardless of the Pagination type! Which effectively kills the possibility to fetch directly as the database now still needs to visit all rows. By unwrapping some APEX packages for learning purposes, I learned that the "count(*) over ()" expression is generated when the Maximum Row Count is specified OR pagination type is "Row Ranges X to Y of Z". This doesn't make sense to me and I'd love to hear why this is the case. I'd expect the "count(*) over ()" only being generated when calculating the Z in pagination type "Row Ranges X to Y of Z". Don't try this at work, but in my development VM I hacked the APEX package and changed the OR into an AND and witnessed a spectacular performance improvement because the optimizer could now succesfully transform the execution plan to a plan similar to a pagination query.
Whether the base query can be fetched from directly
Here I'm talking about making it possible for the database to immediately start returning rows, right after issuing the query. If we have a simple query retrieving rows from just one table, this is possible. If we join a second table with a nested loop join, it's still possible. If we join the second table using a hash join, then it's not possible. The hash join needs to be complete before we start returning rows. There are all kinds of operations which prohibit the database from immediately returning rows, too many to list here. Aggregating, grouping and analytic functions generally prohibit the immediate returning of rows. For sorting you might need an index on the column(s) in the order by clause. Et cetera.
There is however a general good idea you can apply here. In our example we tell the database to give us 2500 rows and the CBO generates an execution plan to make that happen as fast as possible. But we didn't tell the CBO our intention of only wanting the first 15 rows. The purpose of the FIRST_ROWS(n) hint is to tell exactly that to the database. In our example, adding a FIRST_ROWS(10) hint immediately switches the plan from using a hash join to a nested loop join after an index range scan descending to identify the first 15 rows quickly. A FIRST_ROWS(n) hint is suboptimal for the download functionality, but that's a disadvantage which is usually outweighed by a much quicker response time for regular IR querying. Sometimes however, a FIRST_ROWS(n) hint is not enough. A customer I visited, had the database parameter OPTIMIZER_MODE set to FIRST_ROWS_10, but nevertheless didn't saw their queries switching to direct fetch plans. I reluctantly had to add optimizer hints to the queries, forcing nested loop joins and sometimes even an index_desc hint, which made most queries better. The trouble with adding hints, especially with interactive reports, is that they are not always the right fix for every query that can be generated.
Conclusion
For both classic reports and interactive reports, the recommended solution is the same: make your query able to fetch directly, preferably without hints or with the FIRST_ROWS(n) hint. If that doesn't work use other hints (like USE_NL or INDEX_DESC) to force the execution plan you want. Needless to say, a sound data model helps tremendously with writing straightforward queries allowing direct fetching.
Leave the Maximum Row Count property null, so classic reports won't fetch all the way to this number and interactive reports won't introduce the analytic function count(*) over ().
Don't use a Pagination Type with a Z, so classic reports won't fetch all rows and interactive reports again won't introduce count(*) over ().
Recommendations for the APEX development team
Maybe some of them have already been evaluated and rejected. Or maybe I'm unaware yet of certain disadvantages. If so, I'd appreciate it if you would mention this in the comments section.
1) Use bulk fetch
In classic reports and interactive reports, there are as much fetch operations as their are rows being retrieved, which indicates single row fetching. In a report you'll always retrieve 10 to 100 rows, so it makes sense to use bulk fetching here, which lowers the number of context switches between PL/SQL and SQL. When I tweeted this finding recently, Joel Kallman tweeted back with "IRs don't use array fetch, probably should. Legacy decision. Will evaluate using array fetch for IG in 5.1.", where IG stands for the new Interactive Grid. It's still worth implementing this for classic and interactive reports as well, in my opinion.
2) In interactive reports, use "count(*) over () apxws_row_cnt" if and only if Pagination Type is set to "Row Ranges X to Y of Z"
As it is now, this analytic function is also generated when Maximum Row Count is specified, needlessly preventing pagination optimizations. If you cannot make your query fetch immediately, the Maximum Row Count property, and giving up a useful download functionality, is a good option to lessen the performance impact. However, this works best when the analytic function is not there.
3) Default top level hint FIRST_ROWS(10), and checkbox to not generate this hint
It makes sense for APEX to always include a FIRST_ROWS(10) hint into the query in interactive reports and also in classic reports when Maximum Row Count is null and the Pagination Type doesn't contain a Z. This clearly states APEX' intent to the cost based optimizer of only fetching the first few rows and will generally lead to better execution plans. To still give the developer full control over the generated queries, APEX may need an extra checkbox to not include the hint in a rare case, even though I can't think of reason to not want this hint.
4) Default top level hint ALL_ROWS behind download functionality and classic reports with a pagination type containing a Z, and checkbox to not generate this hint.
The counterpart of recommendation 3, is that the download functionality in both the classic reports as the interactive report will be better with an ALL_ROWS hint, clearly stating the intent of wanting to fetch all rows to the cost based optimizer. The hint is also useful for a classic report with a pagination type containing a Z, because such a report will also fetch all rows.
Update 29 december 2016
In Apex 5.1 (5.1.0.00.45 to be more precise), all above conclusions and recommendations still stand.
Update 1 June 2018
In APEX 18.1, the inner workings of classic reports and interactive reports have changed, and the story above no longer applies. It applies up until version 5.1.4. Also, bulk fetch has been implemented in 18.1. Recommendations 2, 3 & 4 still stand and would be nice to have.