CODEWITHSUNDEEP
oracle-databaseplsql
Jal
Jal

Reputation: 51

Explain plan statement not executing select statement into SPOOL

I have created a explain plan statement but when I execute it, it's not displaying the results from the select query. I'm fairly new to oracle and when I've checked the spool file I can't seem to find any errors or where I'm going wrong. I'm not sure if this is the issue 2 - access("ITEM_1"="P"."PROD_ID") or there is a error in my select statement.

spool &data_dir.EXP_query_on_dwu.txt

alter session set query_rewrite_integrity = TRUSTED;
alter session set query_rewrite_enabled = TRUE;

set timing on

EXPLAIN PLAN FOR
SELECT p.prod_id, p.prod_name, p.prod_category, 
sum(s.quantity_sold) sum_quantity, sum(s.amount_sold) sum_sales 
FROM sales s
, products p
WHERE s.prod_id = p.prod_id
GROUP BY p.prod_id, p.prod_name, p.prod_category;

REM Now Let us Display the Output of the Explain Plan
set linesize 250
set pagesize 999
set markup html preformat on
select * from table(dbms_xplan.display());
set linesize 80

spool off

SQL> 
SQL> alter session set query_rewrite_integrity = TRUSTED;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set query_rewrite_enabled = TRUE;

Session altered.

Elapsed: 00:00:00.00
SQL> 
SQL> set timing on
SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT p.prod_id, p.prod_name, p.prod_category,
  3  sum(s.quantity_sold) sum_quantity, sum(s.amount_sold) sum_sales
  4  FROM sales s
  5  , products p
  6  WHERE s.prod_id = p.prod_id
  7  GROUP BY p.prod_id, p.prod_name, p.prod_category;

Explained.

Elapsed: 00:00:00.01
SQL> 
SQL> REM Now Let us Display the Output of the Explain Plan
SQL> set linesize 250
SQL> set pagesize 999
SQL> set markup html preformat on
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 504757596                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                          
----------------------------------------------------------------------------------------------------                                                                                                                                                      
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                      
----------------------------------------------------------------------------------------------------                                                                                                                                                      
|   0 | SELECT STATEMENT        |          |  5022 |   338K|  3406   (1)| 00:00:01 |       |       |                                                                                                                                                      
|   1 |  HASH GROUP BY          |          |  5022 |   338K|  3406   (1)| 00:00:01 |       |       |                                                                                                                                                      
|*  2 |   HASH JOIN             |          |  5022 |   338K|  3405   (1)| 00:00:01 |       |       |                                                                                                                                                      
|   3 |    VIEW                 | VW_GBC_5 |  5022 |   152K|  3303   (1)| 00:00:01 |       |       |                                                                                                                                                      
|   4 |     HASH GROUP BY       |          |  5022 | 60264 |  3303   (1)| 00:00:01 |       |       |                                                                                                                                                      
|   5 |      PARTITION RANGE ALL|          |  1016K|    11M|  3278   (1)| 00:00:01 |     1 |    17 |                                                                                                                                                      
|   6 |       TABLE ACCESS FULL | SALES    |  1016K|    11M|  3278   (1)| 00:00:01 |     1 |    17 |                                                                                                                                                      
|   7 |    TABLE ACCESS FULL    | PRODUCTS | 10000 |   371K|   102   (0)| 00:00:01 |       |       |                                                                                                                                                      
----------------------------------------------------------------------------------------------------                                                                                                                                                      
                                                                                                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                                                                                                       
---------------------------------------------------                                                                                                                                                                                                       
                                                                                                                                                                                                                                                          
   2 - access("ITEM_1"="P"."PROD_ID")                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                          
Note                                                                                                                                                                                                                                                      
-----                                                                                                                                                                                                                                                     
   - this is an adaptive plan                                                                                                                                                                                                                             

23 rows selected.

Elapsed: 00:00:00.08
SQL> set linesize 80
SQL> 
SQL> spool off

Upvotes: 0

Views: 66

Answers (1)

Sergey Afinogenov
Sergey Afinogenov

Reputation: 2202

I think thats because you don't have execution in your script, only creation and displaying plan. Add your query in the script without EXPLAIN PLAN FOR.

spool &data_dir.EXP_query_on_dwu.txt

alter session set query_rewrite_integrity = TRUSTED;
alter session set query_rewrite_enabled = TRUE;

set timing on

EXPLAIN PLAN FOR
SELECT p.prod_id, p.prod_name, p.prod_category, 
sum(s.quantity_sold) sum_quantity, sum(s.amount_sold) sum_sales 
FROM sales s
, products p
WHERE s.prod_id = p.prod_id
GROUP BY p.prod_id, p.prod_name, p.prod_category;

set linesize 250
set pagesize 999
set markup html preformat on

REM Now Let us Display the Output of the Explain Plan
select * from table(dbms_xplan.display());

REM Now Let us Display the Data
SELECT p.prod_id, p.prod_name, p.prod_category, 
sum(s.quantity_sold) sum_quantity, sum(s.amount_sold) sum_sales 
FROM sales s
, products p
WHERE s.prod_id = p.prod_id
GROUP BY p.prod_id, p.prod_name, p.prod_category;

set linesize 80
spool off

Upvotes: 1

Related Questions