A reader, April 12, 2021 - 9:03 am UTC
Thanks for the response.
I'll check and let you know,
Geraldo, April 12, 2021 - 2:29 pm UTC
I have looked v$sql_plan and v$sqlarea dictionary views and I realized that a single query is repeating multiple times (hard parsing) just changing the value in the OFFSET clause.
FROM schema.table t1 LEFT JOIN schema.table2 t2
ON t1.id = t2.id LEFT JOIN schema.table3 t3
ON t1.id = t3.id
WHERE t1.state IS NOT NULL
AND t1.recep_date >= TO_TIMESTAMP(:recep_date, 'dd-mm-yyyy hh24:mi:ss')
AND t1.recep_date < TO_TIMESTAMP(:recep_date, 'dd-mm-yyyy hh24:mi:ss')
ORDER BY t1.recep_date DESC
OFFSET 20 ROWS --> The are hundreds of this query in v$sqlarea view. Each SQL_ID has a different value in OFFSET clause.
FETCH NEXT 10 ROWS ONLY
I think this implementation is filling up the shared pool.
What can I do?
A reader, April 13, 2021 - 4:35 pm UTC
Thanks for the recommendation.
I will check the link.
April 14, 2021 - 2:50 am UTC
glad we could help
A reader, April 22, 2021 - 8:01 pm UTC
I just read the post above about seek method
and it is really better.
But what happens if you have an id
column (a no ordered sequence in a RAC database) on schema.table t1
and you want to choose that column to be use in the seek method and replace recep_date
column for id
column? This sequence is not ordered and if it is, it may cause performance issues on a RAC env.
I ask this because maybe the data could look like this using ORDER BY id DESC:
35300289 01/01/21 16:20:15 --> this record was inserted first on the table but by id it is listed first
35300288 01/01/21 16:20:17
April 27, 2021 - 6:00 am UTC
Any kind of mechanism where you are getting a subset of rows (pagination or otherwise) should generally have a *deterministic* mechanism of carving up the data.
In the case you mentioned, I would order by date (because that is the most intuitive to users) and then by ID as a tie breaker, *unless* you really are sure that ID is the definitive ordering identifier.
(A sequence can be ordered in RAC as well with less overhead than what people think. Its not *no* overhead, but its not a big one either because you can *still* cache them)
A reader, April 27, 2021 - 2:49 pm UTC
But in this case, I can not use seek method although I order rows by date and id DESC because id values are not ordered. So I can not use id > max_id_value condition.
Will I have to order (sequence order) id values to use seek method?
May 03, 2021 - 4:37 am UTC
It shouldnt matter that the ID's are not ordered, more that you are ordering them so that you are presenting a logical subset to the user.
The same holds for your date column eg if person 1 does:
- get date/time 10:34:00am
- spend 5 seconds doing work
- commit row
and someone else does
- get date/time 10:34:02am
- spend 1 second doing work
- commit row
then you can *see* a 10:34:02 row in the database *before* you see a 10:34:00 row. What you are seeking to control here is that you don't present an inconsistent pagination where is why seek method is useful