0

I am checking some AWR reports.

But I am also interested to know, why some queries are executed multiple times, even if some app job does one update or insert or select ? I know we have 3 phases, prepare, execute and fetch. Some selects are being executed over 500k times.

Can someone explain me the reason why does one statement get executed multiple times?

I can see the number of executions inside DBA_HIST_SQLSTAT and also v$sql but I am interested why is a query executed multiple times when it gets started once from an app or pl/sql package? Kind regards.

Improve this question

2 Answers 2

Reset to default
1

Some selects are being executed over 500k times.

A Person cannot run a query that many times without getting really, really bored.
A Program will run a query that many times perfectly happily.

My first guess - "1+N Query" Model.
Sometimes adopted by Object Relational Mapping tools (ORMs) and possibly unknown to naïve Developers who don't bother to check what their shiny, new, ORM is actually doing, this is where the program/ORM pulls records one-by-one, based on a list of record IDs that it ran in the previous query.

Instead of

select id, a, b, c, d 
from ... 
where [complex conditions]   // say 1000 rows

it executes

select id from ... where [complex conditions]   // 1000 IDs

then

select a, b, c, d from ... where id = ?   // for id [1]
select a, b, c, d from ... where id = ?   // for id [2]
select a, b, c, d from ... where id = ?   // for id [3]
. . .  
select a, b, c, d from ... where id = ?   // for id [1000]

Why is this a problem?

To do a thing takes a finite amount of time.
To do that thing a thousand times takes [at least] a thousand times as long.

The Program will be happy, just sitting, waiting, for the ORM library to give it back some data.
The Database will be happy, being asked to retrieve data based on a single value in a uniquely-indexed column.
The User will not be happy, waiting for all of these "little "queries to run, one after the other, to get the Data that they want and, quite likely, filling out Bug Reports while they're waiting.

Such Applications must be recoded to remove this behaviour.
This cannot be fixed at the database end, because the database is just doing what the code is asking it to do.

Improve this answer
4
  • Hi. Thanks for the fast answer. For an example I have a pl/sql package that starts a coupel update and insert statements that take long sometimes for a particular table partition. When I was investigating, I see for an example in AWR that one INSERT or UPDATE query takes a long time to finish and has more than one execution. So I understood it, when an application starts the INSERT query ORACLE DOES NOT make another execution of the UPDATE because of intern reasons (needs more result sets or etc.) but the UPDATE query get executed multiple times only from the user/app ?
    – ultimo_frogman
    Commented Jun 2, 2023 at 16:32
  • @ultimo_frogman it is possible for a cursor to be invalidated during execution and for Oracle to roll it interrupt, roll back and restart it, all internally and invisibly, so that to the user it appears as one operation. However It is unlikely this would account for 500K executions. More likely your application is just executing it more times than you think.
    – William Robertson
    Commented Jun 4, 2023 at 9:03
  • Hi. Thanks for the info. I started the question with a problem of one query and also to understand more the executions. Now I understood that the executions are dealt only trough the app (that triggers the pl/sql package). The second issue I have seen with one INSERT and one UPDATE query where I guess it is has high elapsed time and only one execution, is that it could be that it performs somehow bud on this one table partition. The same query performes good with bind variables on other table partitions. Does this mean I need to run the dbtune advisor task and attach sql profile to this plan?
    – ultimo_frogman
    Commented Jun 4, 2023 at 14:49
  • Another possibility: Are the partitions "divided" equally or might the troublesome one a big, "everything-else" (or, possibly, "Archive") partition?
    – Phill W.
    Commented Jun 5, 2023 at 6:56
0

Oracle doesn’t just execute queries over and over unless it is told to. Either your code explicitly calls the query multiple times or the code is being called more often than you expect.

Review the code that is being executed, review how that code is being called.

Improve this answer
3
  • For an example I have heard that a Update or Insert query with a low number of execution and a high elapsed time is problematic? Why? I was thinking to run dbms_tune task and attach a SQL Profile to that kind of query?
    – ultimo_frogman
    Commented Jun 2, 2023 at 16:32
  • Also thanks for your remarks. I think I have figured it out that executions of a insert/update/select query is mostly started from an application and when the application executes the same query multiple times, this gets shown in the AWR report under executions. I am also trying to figure out why one insert and one update query that runs on a particular table partition hast only one execution number and high elapsed time for a certain app bulk load period. On other partitions the query with other bind variables runs ok. Maybe I need to attach a SQL profile with tuned plan to it?
    – ultimo_frogman
    Commented Jun 2, 2023 at 16:43
  • You've pivoted to a completely different question. There are many reasons why your average execution time could be slow and it's up to you/your users whether it's problematic. If you want help on that then you should start a new question and follow dba.meta.stackexchange.com/questions/3034/…. Please stick to the subject of the original question here
    – Andrew Sayer
    Commented Jun 3, 2023 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.