How to get CPU time for SQL Query

Here’s a wonderful SQL script to get CPU time for your SQL query. CPU time means %CPU usage time for fetching records. It’s an optimized script. Here you must use two keywords – Explain plan and DBMS_XPLAN. The below example shows you how to use it.

Who will get the benefit? ETL and SQL developers usually verify the CPU usage. Based on the output statistics, they must tune it accordingly.

SQL Script with Explain Plan

EXPLAIN PLAN FOR  
SELECT prod_category, AVG(amount_sold)  
FROM   sh.sales s, sh.products p  
WHERE  p.prod_id = s.prod_id  
GROUP BY prod_category;
SELECT plan_table_output  
FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'typical'));

The Output of Plan Table

Statement processed.


Result Set 2
PLAN_TABLE_OUTPUT
Plan hash value: 1197568639

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 255 | 654 (8)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 5 | 255 | 654 (8)| 00:00:01 | | |
|* 2 | HASH JOIN | | 72 | 3672 | 653 (8)| 00:00:01 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 2160 | 651 (8)| 00:00:01 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 651 (8)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 613 (2)| 00:00:01 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 613 (2)| 00:00:01 | 1 | 28 |
| 7 | VIEW | index$_join$_002 | 72 | 1512 | 2 (0)| 00:00:01 | | |
|* 8 | HASH JOIN | | | | | | | |
| 9 | INDEX FAST FULL SCAN| PRODUCTS_PK | 72 | 1512 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX | 72 | 1512 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("P"."PROD_ID"="ITEM_1")
8 - access(ROWID=ROWID)
Download CSV
23 rows selected.

Analysis of Plan Table output

Based on the cost parameter, you cannot judge the performance of SQL queries in Oracle. According to dba-oracle.com;

For example, consider a million-row table that takes 30 seconds for a full scan.  Now consider a SQL that returns 10,000 rows in sorted order via an index where clustering_factor approaches num_rows, and two possible execution plans:

  • OPTION 1 – Do an index scan – More I/O (and cost) to traverse the index top pull the rows in sorted order, but the query starts delivering rows immediately.
  • OPTION 2 – Do a full table scan and back-end sort – this may involve far less work (and less cost), but we will not see any results for 30 seconds

So, the optimizer cannot always assume that the least-cost plan is the “best” plan when optimizing for fast response time.

Summary

  • The CPU time is not a matter to tune SQL query
  • Optimizer cannot assume least-cost is the best plan
  • You can verify if the query will take Table scan, or Index scan
  • Explain plan is a execution plan of SQL query. Usually, the Datawarehouse engineers (Informatica/Power-builder) check before hand.

Books

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.