This is Oracle's best guess, based on the data currently existing in the table.įinally on line 0, we have Bytes=29. This value indicates how many rows must be processed in order to execute the query. The actual execution time and resources needed to run the query depend on many different factors, so the cost should be used to determine whether you are helping or hindering query performance when tuning a query. So, a query that has a cost of 16 would run slightly faster than this query does, and a query with a cost of 6000 would run significantly more slowly. However, this is a relative measurement, not an absolute measurement of the computing resources needed to run the query. In general, lower cost equals better performance.

This is the cost for the entire query, and is a relative indication of how much processing has to be done to execute it. Next, you will notice that line 0 indicates Cost=18. Here, the default configuration of Oracle gives the optimizer the freedom to choose which mode to use based on the characteristics of this particular database. An OLTP database, such as an airline reservation system, would likely see a high volume of transactions, while a data warehouse, such as a business process management system, might only be updated nightly, weekly, or monthly. There are two possible modes: on-line transaction processing (OLTP) and data warehousing (DW). This indicates that Oracle's cost-based optimizer is choosing the mode in which it runs on the fly. First, note the portion that says Optimizer=CHOOSE. Line 0 shows important information about the entire query.

This output shows the execution plan for the query. In SQL*Plus, you could do this as follows:ġ TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) To do this, you need to explain the execution plan of the query you are executing. Now, suppose you want to find out how a simple query that looks up an employee by employee ID executes. Consider the Employees table as shown below: Suppose that you have a database that is used by the HR and payroll systems to keep track of employee information and payroll transactions. Nothing can make a good application perform badly like poorly tuned SQL.

Oracle scratchpad