Skip to Main Content
  • Questions
  • Issue with shared pool and query performance

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: April 07, 2021 - 12:11 pm UTC

Last updated: May 03, 2021 - 4:37 am UTC

Version: 18.6

Viewed 100+ times

You Asked

Hello, Ask TOM Team.

I have an application (is like a report tool) that reads data from a 18c Oracle Database. One day the results return quick but a couple days after it gets slow. When I execute "ALTER SYSTEM FLUSH SHARED_POOL" command the the results return quick again.

What could be the problem?

Thanks in advanced.

Regards,

and we said...

Not much to go on there, but perhaps check out V$SQL_PLAN over time for differing child cursors for the problematic SQL.

Its possible that plans are changing over time due to (say) statistics feedback and then flushing the shared pool resets things.

Or you could have a parsing issue, and a heavily populated shared pool causes a lengthy parse etc.

Lots of *potential* causes. If you have AWR, check out DBA_HIST_SQL_PLAN to see if plans are changing.

Rating

  (5 ratings)

Comments

Review

A reader, April 12, 2021 - 9:03 am UTC

Thanks for the response.

I'll check and let you know,

Regards,

Follow Up

Geraldo, April 12, 2021 - 2:29 pm UTC

Hello.
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.

SELECT t1.id,
t1.guid,
t1.customer,
t1.doc,
t1.state,
t2.message_code,
t2.message,
t1.issue_date,
t1.recep_date
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?

Regards,


Chris Saxon
April 13, 2021 - 4:30 pm UTC

Don't use OFFSET ;)

https://use-the-index-luke.com/no-offset

If you want to continue using it, you can bind the offset value: e.g.:

offset :x rows
fetch next 10 rows only

Follow Up

A reader, April 13, 2021 - 4:35 pm UTC

Thanks for the recommendation.

I will check the link.

Regards,
Connor McDonald
April 14, 2021 - 2:50 am UTC

glad we could help

Follow Up

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:

ID                RECEP_DATE
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 


Connor McDonald
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)


Follow Up

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?

Regards,
Connor McDonald
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

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.