Geek Answers Handbook

How to determine the request time

I have a very long SQL command with many JOIN and UNION statements.

I opened the Explain Plan Window , but I can’t say what Cost , Cardinality or Bytes mean.

Can someone explain these conditions? And does this decrease mean faster query time?

+4
source share
3 answers

Cost is an estimate made by the cost-based query optimizer to select an adequate implementation plan; usually, lower cost is associated with faster request time, but not always.

Cardinality is the expected number of rows returned by your query based on database statistics. Again, this is just an estimate.

Bytes are the number of bytes that the database expects to read during query execution.

Literature:

+1
source

as i can see in oracle documentation

CARDINALITY: Evaluate, using a cost-based approach, the number of rows accessing an operation.

BYTES: cost-based estimate of the number of bytes accessing the operation.

COST: Cost of operation calculated by the optimizer based on the cost approach. For operators using a rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measure; it is simply the weighted value used to compare the costs of fulfilling plans. the value of this column is a function of the CPU_COST and IO_COST columns.

So you also need to know:

* CPU_COST: * CPU cost for the operation, estimated by the optimizer on the basis of costs. For operators using a rule-based approach, this column is zero. The value of this column is proportional to the number of machine cycles required for operation.

* IO_COST: * The cost of an I / O operation estimated using the cost-based optimizer approach. For operators using a rule-based approach, this column is zero. The value of this column is proportional to the number of data blocks read by the operation.

+1
source

You need to know that Explain Plan gives you a plan that the database believes will be used, but the actual plan used may be different.

If you intend to optimize a query or compare it with other queries (to choose the best one), measuring runtime, latency, retry size and logical I / O are good places to start. I would not rely too much on "Explain the plan."

Take a look:

http://www.orafaq.com/wiki/SQL_Trace

and

http://www.orafaq.com/wiki/TKProf

0
source

More articles:


All Articles