CODEWITHSUNDEEP
sql-serveroracle-databasesql-execution-plan
Fang Shuwei
Fang Shuwei

Reputation: 41

How to fetch query execution statistics using Oracle DB?

I am new to database. I try to run a simple query on SQL Server 2014 and Oracle 12c.

This is the execution plan I get using SQL Server. It contains information about I/O cost and CPU cost in seconds.

enter image description here

However I can't find the same information using Oracle. The CPU cost shown in the execution plan is not based on execution time. enter image description here

I want to do some comparison between the two databases. How I can obtain the same information in Oracle as in SQL Server? Besides, how I can know the cache hit ratio?

Thank you.

Upvotes: 1

Views: 1846

Answers (3)

David Aldridge
David Aldridge

Reputation: 52346

The cost estimate is in fact based on time.

It is a non-dimensionalised measurement that expresses the estimated time for the query to complete in terms of the equivalent number of logical reads, so if a logical read is expected to take 0.001 seconds then a cost of 12 is 0.012 seconds.

Although it is commonly stated that the cost between different queries cannot be compared, this was only definitively true in earlier versions. The difficulty in comparing query costs relates to how long single block and multiblock reads, writes and CPU operations take. This can depend on such a multitude of factors (other activity on the system, and activity immediately prior that affects the likelihood of blocks being cached by the instance or the i/o subsystem) that it is highly unlikely that you really expect to derive a time from a cost.

Cache hit ratios have been discredited for quite some time as a measurement of system efficiency. It is possible to improve the cache hit ratio to an arbitrary number by simply running particular types of highly inefficient queries.

Upvotes: 1

thatjeffsmith
thatjeffsmith

Reputation: 22427

You're close, very close.

Run with AutoTrace.

Autotrace Output in SQL Developer

I talk more about the feature here, or you can of course read up on the docs or the Help.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36807

Use the Oracle Database 12c: EM Express Performance Hub to get both estimates and actual values for queries and their operations. Regular explain plans are helpful, but they just show you what Oracle thinks will happen, not necessarily what will happen.

Specifically, use either the SQL Details (aggregate) or the SQL Monitor Details (last execution) information.

Upvotes: 0

Related Questions