Here’s a script to get CPU utilisation of SQL query.
CPU utilisation 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 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
Srinimf 