CODEWITHSUNDEEP
sqloracle-databasebenchmarking
user590444
user590444

Reputation: 4322

Oracle sql benchmark

I have to benchmark a query - currently I need to know how adding parameter to select result set(FIELD_DATE1) will affect sql execution time. There is administration restrictions in db so I can not use debug. So I wrote a query:

SELECT COUNT(*), MIN(XXXT), MAX(XXXT) 
  FROM ( select distinct ID AS XXXID, sys_extract_utc(systimestamp) AS XXXT
              , FIELD_DATE1 AS XXXUT  
           from XXXTABLE 
          where FIELD_DATE1 > '20-AUG-06 02.23.40.010000000 PM' );

Will output of query show real times of query execution

Upvotes: 0

Views: 1232

Answers (2)

davidmontoyago
davidmontoyago

Reputation: 1833

There is a lot to learn when it comes to benchmarking in Oracle. I recommend you to begin with the items below even though It worries me that you might not have enough restrictions in db since some of these features could require extra permissions:

  • Explain Plan: For every SQL statement, oracle has to create an execution plan, the execution plan defines how to information will be read/written. I.e.: the indexes to use, the join method, the sorts, etc. The Explain plan will give you information about how good your query is and how it is using the indexes. Learning the concept of a query cost for this is key, so take a look to it.

  • TKPROF: it's an Oracle tool that allows you to read oracle trace files. When you enable timed statistics in oracle you can trace your sql statements, the result of this traces are put in files; You can read these files with TKPROF. Among the information TKPROF will let you see is:

  • count = number of times OCI procedure was executed
  • cpu = cpu time in seconds executing
  • elapsed = elapsed time in seconds executing
  • disk = number of physical reads of buffers from disk
  • query = number of buffers gotten for consistent read
  • current = number of buffers gotten in current mode (usually for update)
  • rows = number of rows processed by the fetch or execute call

See: Using SQL Trace and TKPROF

Upvotes: 3

Dave Costa
Dave Costa

Reputation: 48121

It's possible in this query that SYSTIMESTAMP would be evaluated once, and the same value associated with every row, or that it would be evaluated once for each row, or something in-between. It also possible that all the rows would be fetched from table, then SYSTIMESTAMP evaluated for each one, so you wouldn't be getting an accurate account of the time taken by the whole query. Generally, you can't rely on order of evaluation within SQL, or assume that a function will be evaluated once for each row where it appears.

Generally the way I would measure execution time would be to use the client tool to report on it. If you're executing the query in SQLPlus, you can SET TIMING ON to have it report the execution time for every statement. Other interactive tools probably have similar features.

Upvotes: 2

Related Questions