Is it possible to make Oracle queries to Zero execution time?

I have Oracle database queries which executes in 2 to 3 seconds time. Is it possible to make the queries with Zero execution time?
If so which tool we can use for it?
LVL 3
MujeeburRahmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
You can only get closer to zero. i.e. There has to be some execution time.

I don't believe the question really has an answer because if a tool existed that took all Oracle queries to near zero it would be very famous indeed, so famous you would not need to ask us.

Extracting improved performance in your case might need better queries, more indexing, more hardware - but we don't know anything about your environment or your database or the queries etc.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Concurring fully with Paul's comment above. you may even come very close to 0 seconds.
the less the query has to "execute" (index filtering; computing; joining etc), the faster it can be.

so unless you show up with the query (queries), we cannot tell you any concrete techniques.
materialized views, denormalization, computed columns are the first techniques that I can think of...
Geert GOracle dbaCommented:
check cary millsap's blog or get his method r software
http://method-r.com/software
it all starts with tracing
it'll give you an idea on where exactly time is wasted using the traces

you might want to be wary of the compulsive tuning disorder
> it's when you think everything has to have zero (or less if possible) seconds execution time

other than that ... it takes experience and analysis skills
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
To me, this would be the fastest thing you could ever run:
SQL> select 1 from dual;

         1
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> spool off

Open in new window

That takes time.  Everything takes time.  Not sure how you could do work in no time.
Geert GOracle dbaCommented:
it's a little bit faster if you create a synonym for dual
... less bytes need to be sent across the network

13:48:01 >create synonym t for dual;

Synonym created.

13:49:06 >select 0 from t;


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
         31  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        518  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

13:49:15 >/


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        518  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

13:49:17 >/


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        518  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

13:49:22 >

Open in new window


but who would want to do that ?
Geert GOracle dbaCommented:
i can understand the reasoning ...

if your capable of everybody doing everything in no time
the cost of all their work will be:
time x hourly cost = ~0 x 50euro average per hour = ~0 euro

i've not met a consultant yet who can work that fast though
slightwv (䄆 Netminder) Commented:
As others have pointed out, we really do need to know more about the query, the system, use, etc...

If the tables involved in the query aren't changed much, you might get close to what you want with Result Caching:
http://docs.oracle.com/cd/E11882_01/server.112/e41573/memory.htm#PFGRF986
johnsoneSenior Oracle DBACommented:
No network traffic.  Run on the server.  You can reduce a couple of bytes sent back and forth, but there is no way it can take no time.

Also, network traffic to get the query into Oracle wouldn't be included in execution time.  It cannot time the network round trip on something it hasn't received.  The shorter synonym (DUAL is already a synonym) may save a couple of milliseconds on the total time, but should not affect execution time.  Then you will spend the extra time doing the extra synonym translation during parse.

That is purely a memory read against a pinned table.
sdstuberCommented:
MujeeburRahman,

I'm assuming the question wording was intended as a joke.
Zero time is silly, because it's obvious that's not possible - but I'm also assuming there is a real question behind it.  

Can you rephrase?  What is your real intent and do you have an example for us to help you with?
Geert GOracle dbaCommented:
i don't think he is joking
he just posted the same question in the oracle forum too:
https://community.oracle.com/thread/3784298
sdstuberCommented:
I believe the asker is serious in wanting help.

If the question phrased as is is serious - then the answer is laughably simple - NO.

If the question needs rephrased to "can a query be made faster?" then as stated above and elsewhere is "it depends" - we need details to answer it.
Mark GeerlingsDatabase AdministratorCommented:
I agree with sdstuber, the simple answer is: no.  But also, if the question really means: "can a query be made faster?"  Then, usually: yes.

There is no one single tool or programming technique that will do this for all queries in all databases.  It all depends on: the server hardware, the database size, the query syntax, available indexes, table statistics, and other things.

Maybe adding more RAM to the server and adjusting the size of the Oracle SGA will be the only way to speed up a particular query.  There are so many things we don't know about this particular situation. so there is no way that we can recommend a particular option or approach without some details.
MujeeburRahmanAuthor Commented:
Dear All,
Thank you all for putting your valuable opinion. I am not joking. I know there are lot of experts here.  Actually a query taking 2 seconds is  performing very well. So I want to know is it possible to make that query execute with 0 or less than 1 second. Is there any concept in oracle like that?
Geert Gruwez replied that time x hourly cost = ~0 x 50euro average per hour = ~0 euro. So it means that making a query with 0 response time is not possible.
Regards
Mujeeb
Geert GOracle dbaCommented:
that's a bit of an odd approach to performance tuning

in these past years I have found the worst querries needing the most attention
typically the worst querries are the highest on this list: execution rate per minute * buffer gets per execution

if you don't have AWR (or any other monitoring tool) to give you this figure, which generally it doesn't, ...
especially not across a whole week, then it's a bit difficult to find the bad querries


1 difficult part in performance tuning is finding the querries causing the problem
consuming resources like buffer gets take time, and time multiplied by a rate gives you a number
which you can, just about, use as a cost for the query

i had a few querries with rates of 1000 hits per second
If those querries take longer than .1 sec there is a real performance problem
as John Watson indicated in the oracle thread you made, result_cache is a very good solution for an item like that

there is common solution to some common query problems
doing an operation on an indexed column will negate the use of the index

problem:
where datecolumn+3 < sysdate

solution
where datecolumn < sysdate - 3

imho, you need to look at the querries with highest consumption, taking into account the execution rate

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
>>"is it possible to make that query execute ... less than 1 second"
Yes

I am going to recommend "Explain Plans" to you. These are the first place (in my view) to go if you want to improve a query.

Here is an article on this by  Franck Pachot:
     How to get an Oracle execution plan with all important information

{+edit}
Just a word of caution: Not every query can be super fast, some will always be harder to satisfy than others. If you have a specific query to improve, then an "explain plan" of that query is a place to look for ways to improve that query.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.