Hi,
in a PLSQL package I generate and execute dynamic SQL Statements. I wrote a litte procedure, which returns the output of dbms_xplan.display into a log table:
FUNCTION logPlan(p_statament VARCHAR2) RETURN CLOB IS
PRAGMA autonomous_transaction;
TYPE vtable IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
l_plan vtable;
l_bigPlan CLOB;
BEGIN
dbms_lob.CREATETEMPORARY(l_bigPlan,false);
execute immediate'explain plan for '||p_statament;
select plan_table_output bulk collect into l_plan from table(dbms_xplan.display(format=>'ADAPTIVE'));
for i in l_plan.first .. l_plan.last loop
l_plan(i):=l_plan(i)||CHR(10);
dbms_lob.writeappend(l_bigPlan,length(l_plan(i)),l_plan(i));
END LOOP;
commit;
return l_bigPlan;
END;
As I'm doing some performancetuning I noticed that the output of explain plan inside the PLSQL Package differs from the output from explain plan done in toad in the same session.
Also when I execute the generated SQL in Toad it runs within 350ms, the same statemente with the same binds exeuted in the packages runs over 20seconds.
OPTIMIZER_DYNAMIC_SAMPLING ist set to 11, OPTIMIZER_ADAPTIVE_FEATURES=true
"Bad" Plan in PLSQL:
Plan hash value: 3564500612
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 7 (15)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 1 | 101 | 7 (15)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 101 | 6 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 101 | 6 (0)| 00:00:01 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 70 | 5 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 48 | 4 (0)| 00:00:01 | KEY | KEY |
|* 6 | TABLE ACCESS BY INDEX ROWID| TBLPARTNERSUCHE | 1 | 48 | 4 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TBLPARTNERSUCHE_NAME1_IX | 14 | | 1 (0)| 00:00:01 | KEY | KEY |
| 8 | BUFFER SORT | | 7 | 154 | 1 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | TBL3008PCBREFERENZ_IXU | 7 | 154 | 1 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | TBLVERTRAGSSUCHE_ORGA_PERS_IX | 1 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | TBLVERTRAGSSUCHE | 1 | 31 | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-->Removed from author to make it more readable
"Good" Plan in Sql Context in Toad (same session):
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77167 | 7611K| | 39523 (1)| 00:00:02 | | |
| 1 | HASH UNIQUE | | 77167 | 7611K| 9512K| 39523 (1)| 00:00:02 | | |
|- * 2 | HASH JOIN | | 77167 | 7611K| | 37732 (1)| 00:00:02 | | |
| 3 | NESTED LOOPS | | 77167 | 7611K| | 37732 (1)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 77167 | 7611K| | 37732 (1)| 00:00:02 | | |
|- 5 | STATISTICS COLLECTOR | | | | | | | | |
|- * 6 | HASH JOIN | | 62413 | 3230K| | 248 (1)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 62413 | 3230K| | 248 (1)| 00:00:01 | | |
|- 8 | STATISTICS COLLECTOR | | | | | | | | |
| * 9 | INDEX RANGE SCAN | TBL3008PCBREFERENZ_IXU | 7 | 154 | | 1 (0)| 00:00:01 | | |
| * 10 | TABLE ACCESS BY INDEX ROWID BATCHED| TBLVERTRAGSSUCHE | 8636 | 261K| | 35 (0)| 00:00:01 | | |
| * 11 | INDEX RANGE SCAN | TBLVERTRAGSSUCHE_ORGA_PERS_IX | 255 | | | 1 (0)| 00:00:01 | | |
|- * 12 | TABLE ACCESS FULL | TBLVERTRAGSSUCHE | 8636 | 261K| | 35 (0)| 00:00:01 | | |
| 13 | PARTITION RANGE SINGLE | | 1 | | | 1 (0)| 00:00:01 | KEY | KEY |
| * 14 | INDEX RANGE SCAN | TBLPARTNERSUCHE_NAME1_IX | 1 | | | 1 (0)| 00:00:01 | KEY | KEY |
| * 15 | TABLE ACCESS BY INDEX ROWID | TBLPARTNERSUCHE | 1 | 48 | | 1 (0)| 00:00:01 | | |
|- 16 | PARTITION RANGE SINGLE | | 1 | 48 | | 1 (0)| 00:00:01 | KEY | KEY |
|- * 17 | TABLE ACCESS BY INDEX ROWID BATCHED | TBLPARTNERSUCHE | 1 | 48 | | 1 (0)| 00:00:01 | | |
|- * 18 | INDEX RANGE SCAN | TBLPARTNERSUCHE_NAME1_IX | 1 | | | 1 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- -->Removed from author to make it more readable
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- this is an adaptive plan (rows marked '-' are inactive)
49 rows selected.
Also strange, that the plsql Plan does not show that dynamic statistics are used. It seems as if the OPTIMIZER_DYNAMIC_SAMPLING=11 ist ignored.
In short terms: In the same session, the same Statement has different results when using explain plan within a plsql Package (execute immediate 'explain plan for||variable_that_hols_the_statement;) and within "pure" sql.
Also it seems as if the CBO ignores the OPTIMIZER_DYNAMIC_SAMPLING=11 setting within plsql (because there there are no notes in the output of dbmx_xplan.
Testcase where I can reproduce it:
create or replace function explain_plan(p_statament varchar2) return clob is
PRAGMA autonomous_transaction;
TYPE vtable IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
l_plan vtable;
l_bigPlan CLOB;
BEGIN
dbms_lob.CREATETEMPORARY(l_bigPlan,false);
execute immediate'explain plan for '||p_statament;
select plan_table_output bulk collect into l_plan from table(dbms_xplan.display(format=>'ADAPTIVE'));
for i in l_plan.first .. l_plan.last loop
l_plan(i):=l_plan(i)||CHR(10);
dbms_lob.writeappend(l_bigPlan,length(l_plan(i)),l_plan(i));
END LOOP;
commit;
return l_bigPlan;
END;
/
alter session set OPTIMIZER_DYNAMIC_SAMPLING=11;
explain plan for <i>write a statement in which the CBO uses dynamic statistics</i>
select * from table(dbms_xplan.display(format=>'ADAPTIVE'));
Now the same with the plsql function:
select explain_plan('same statement from above') from dual
The output differs. First uses dynamic statistics, second doesn't
PS: Hope the plan is readable after submitting...
It is not a PL/SQL thing per se. It appears to be an artifact of running the PL/SQL function from within the context of SQL. Consider the following example
SQL> set timing off
SQL> drop table T purge;
Table dropped.
SQL> drop table T1 purge;
Table dropped.
SQL>
SQL> create table T as select * from dba_objects;
Table created.
SQL> create table T1 as select * from dba_objects;
Table created.
SQL>
SQL> exec dbms_stats.delete_table_stats('','T');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_table_stats('','T1');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> create or replace function explain_plan(p_statament varchar2) return clob is
2 PRAGMA autonomous_transaction;
3 TYPE vtable IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
4 l_plan vtable;
5 l_bigPlan CLOB;
6 BEGIN
7 dbms_lob.CREATETEMPORARY(l_bigPlan,false);
8 execute immediate'explain plan for '||p_statament;
9 select plan_table_output bulk collect into l_plan from table(dbms_xplan.display(format=>'ADAPTIVE'));
10 for i in l_plan.first .. l_plan.last loop
11 l_plan(i):=l_plan(i)||CHR(10);
12 dbms_lob.writeappend(l_bigPlan,length(l_plan(i)),l_plan(i));
13 END LOOP;
14 commit;
15 return l_bigPlan;
16 END;
17 /
Function created.
SQL>
SQL> set long 500000
SQL> set longchunksize 500000
SQL> set lines 200
SQL>
SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING=11;
Session altered.
SQL>
SQL> alter system flush shared_pool;
System altered.
--
-- explain plan direct
-- take note: hash join estimated rows = 89361
--
SQL> explain plan for select count(*) from t1, t where t.object_id = t1.object_id;
Explained.
SQL> select * from table(dbms_xplan.display(format=>'ADAPTIVE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 949044725
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 1221 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
|* 2 | HASH JOIN | | 89361 | 2268K| 3296K| 1221 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 134K| 1713K| | 450 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 134K| 1713K| | 450 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
20 rows selected.
SQL>
SQL> alter system flush shared_pool;
System altered.
--
-- explain plan from within PLSQL *executed from SQL*
-- take note: hash join estimated rows = 134K
-- no reference to dynamic sampling
--
SQL> select explain_plan('select count(*) from t1, t where t.object_id = t1.object_id') from dual;
EXPLAIN_PLAN('SELECTCOUNT(*)FROMT1,TWHERET.OBJECT_ID=T1.OBJECT_ID')
--------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 949044725
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 1221 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
|* 2 | HASH JOIN | | 134K| 3426K| 3296K| 1221 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 134K| 1713K| | 450 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 134K| 1713K| | 450 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
SQL>
SQL> alter system flush shared_pool;
System altered.
--
-- explain plan from within PLSQL *NOT* executed from SQL
-- take note: hash join estimated rows = 89361
--
SQL> variable c clob
SQL> exec :c := explain_plan('select count(*) from t1, t where t.object_id = t1.object_id')
PL/SQL procedure successfully completed.
SQL> print c
C
--------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 949044725
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 1221 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
|* 2 | HASH JOIN | | 89361 | 2268K| 3296K| 1221 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 134K| 1713K| | 450 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 134K| 1713K| | 450 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
SQL>
SQL>
I will ask around internally, but it would appear some things are different/locked down within a PL/SQL function when its being executed from SQL (which would sort of make sense, because by the time you are executing the plsql function, you would expect all of the dynamic sampling etc to be already done).
Moral of the story: "select plsql_function from dual", probably isnt a good idea if that function is doing dynamic SQL etc.
Hope this helps.