/
demo2.sql
106 lines (85 loc) · 3.2 KB
/
demo2.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
--------------------------------------------------------------------------------
--
-- File name: demo2.sql
--
-- Purpose: Advanced Oracle Troubleshooting Seminar demo script
-- Makes a single fetch to generate lots of LIOs by
-- nested looping over full table scans.
-- Requires lotslios.sql script from TPT scripts.
--
-- Author: Tanel Poder ( http://www.tanelpoder.com )
-- Copyright: (c) Tanel Poder
--
--------------------------------------------------------------------------------
prompt Starting Demo2...
-- @@lotslios 1000000000000
--------------------------------------------------------------------------------
--
-- File name: demos/bind_peeking_nested_loops.sql
-- Purpose: this script demos how a "wrong" bind variable value
-- can cause an execution plan to be compiled which is
-- very inefficient for the next execution with different bind variable
-- values (with large number of matching rows)
-- the second execution of the query takes very long time to complete
-- despite adaptive bind variable peeking, which would kick in during the
-- next (3rd) execution
--
-- This problem happens even on Oracle 11.2 despite adaptive bind peeking
-- and cardinality feedback (due design, not a bug)
--
-- Author: Tanel Poder (tanel@e2sn.com)
-- Copyright: (c) http://tech.e2sn.com
--
--------------------------------------------------------------------------------
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
CREATE TABLE t1 AS SELECT * FROM dba_objects;
CREATE TABLE t2 AS SELECT * FROM dba_objects;
CREATE TABLE t3 AS SELECT * FROM dba_objects;
CREATE INDEX i1 ON t1(owner);
CREATE INDEX i2 ON t2(owner);
CREATE INDEX i3 ON t3(owner);
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'T1',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'T2',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'T3',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1;
VAR v VARCHAR2(100)
EXEC :v:='SCOTT'
SET TIMING ON
--PROMPT Running query first time, this should be fast (and should use nested loops execution plan)
SELECT
MIN(t1.created), MAX(t1.created)
FROM
t1
, t2
, t3
WHERE
t1.object_id = t2.object_id
AND t2.object_id = t3.object_id
AND t1.owner = :v
AND t2.owner = :v
AND t3.owner = :v
/
SET TIMING OFF
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST ADVANCED'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null));
EXEC :v:='SYS'
SET TIMING ON
PROMPT Running the "report"...
--PROMPT Now running the same query with different bind variables (this query should take very long time)
SELECT
MIN(t1.created), MAX(t1.created)
FROM
t1
, t2
, t3
WHERE
t1.object_id = t2.object_id
AND t2.object_id = t3.object_id
AND t1.owner = :v
AND t2.owner = :v
AND t3.owner = :v
/