Cost란 무엇인가?

• Cost는 Query의 예상 수행 시간(Time)이다.
• Jonathan Lewis는 Cost란 Optimizer가 문장을 수행하는 데 걸릴 것이라고 예상하는 추측 시간을 의미 하며, 과거에도 그래 왔다.

필자의 의견

• Oracle 스스로 Cost가 무엇인지 명확하게 밝히지 않고 있고, 아무도 Cost를 보고 SQL문장을 튜닝하지 않기 때문에,
• 실행 계획을 이해하고 각 단계 별로 Cardinality,
• 즉 예측 로우 수(Estimated Rows)와 실제 로우 수(Actual Rows),
• 그리고 일량(Logical Reads, Physical Read, PGA 사용량 등)을분석하므로,
• Cost계산 공식을 이해할 필요는 없지만 Oracle에서 Cost가 무엇을 의미하는지 이해할 필요는 있다

오라클 레퍼런스에서의 정의

• The cost represents units of work or resource used.
• The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.
• So, the cost used by the query optimizer represents an estimate of the number of disk I/Os and the amount of CPU and memory used in performing an operation.
• The operation can be scanning a table, accessing rows from a table by using an index, joining two tables together, or sorting a row set.
• The cost of a query plan is the number of work units that are expected to be incurred when the query is executed and its result produced.
• The access path determines the number of units of work required to get data from a base table.

• The access path can be a table scan, a fast full index scan, or an index scan. During table scan or fast full index scan, multiple blocks are read from the disk in a single I/O operation.
• Therefore, the cost of a table scan or a fast full index scan depends on the number of blocks to be scanned and the multiblock read count value.
• The cost of an index scan depends on the levels in the B-tree, the number of index leaf blocks to be scanned, and the number of rows to be fetched using the rowid in the index keys.
• The cost of fetching rows using rowids depends on the index clustering factor.

• The join cost represents the combination of the individual access costs of the two row sets being joined, plus the cost of the join operation.