by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Cost란 무엇인가?




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.





문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.