1. 先执行xplan.sql脚本
-- ----------------------------------------------------------------------------------------------
--
-- Script: xplan.sql
--
-- Author: Adrian Billington
--
--
-- Description: Creates a package named XPLAN as a wrapper over DBMS_XPLAN. Provides access to
-- the following DBMS_XPLAN pipelined functions:
--
-- 1. DISPLAY;
-- 2. DISPLAY_CURSOR;
-- 3. DISPLAY_AWR (optional - see Notes section for licence implications).
--
-- The XPLAN wrapper package has one purpose: to include an "order" column in the
-- plan output to show the order in which plan operations are performed. See the
-- following example for details.
--
-- Example: DBMS_XPLAN output (format BASIC):
-- ------------------------------------------------
-- | Id | Operation | Name |
-- ------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | MERGE JOIN | |
-- | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | SORT JOIN | |
-- | 5 | TABLE ACCESS FULL | EMP |
-- ------------------------------------------------
--
-- Equivalent XPLAN output (format BASIC):
-- --------------------------------------------------------
-- | Id | Order | Operation | Name |
-- --------------------------------------------------------
-- | 0 | 6 | SELECT STATEMENT | |
-- | 1 | 5 | MERGE JOIN | |
-- | 2 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | 1 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | 4 | SORT JOIN | |
-- | 5 | 3 | TABLE ACCESS FULL | EMP |
-- --------------------------------------------------------
--
-- Usage: SELECT * FROM TABLE(XPLAN.DISPLAY(...));
-- SELECT * FROM TABLE(XPLAN.DISPLAY_CURSOR(...));
-- SELECT * FROM TABLE(XPLAN.DISPLAY_AWR(...));
--
-- Usage for XPLAN is exactly the same as for DBMS_XPLAN. See the DBMS_XPLAN
-- documentation for all options.
--
-- Note that the only exception to this is that XPLAN.DISPLAY does not contain
-- the FILTER_PREDS parameter available in 10.2+ versions of DBMS_XPLAN.DISPLAY
-- (this parameter enables us to limit the data being returned from an Explain
-- Plan but is of quite limited use).
--
-- See the Notes section for details on the licensing implications of using
-- XPLAN.DISPLAY_AWR.
--
-- Versions: This utility will work for all versions of 10g and upwards.
--
-- Required: 1) PLAN_TABLE of at least 10.1 format
--
-- 2) Either:
-- SELECT ANY DICTIONARY
-- Or:
-- SELECT on V$DATABASE
-- SELECT on V$SQL_PLAN
-- SELECT on V$SESSION
-- SELECT on V$MYSTAT
-- SELECT on DBA_HIST_SQL_PLAN
--
-- 3) CREATE TYPE, CREATE PROCEDURE
--
-- Notes: *** IMPORTANT: PLEASE READ ***
--
-- 1) Oracle license implications
-- ---------------------------
-- The AWR functionality of XPLAN accesses a DBA_HIST% view which means
-- that it requires an Oracle Diagnostic Pack license. The XPLAN.DISPLAY_AWR
-- pipelined function is therefore disabled by default. It can be included
-- by modifying two substitution variables at the start of the script. Please
-- ensure that you are licensed to use this feature: the author accepts
-- no responsibility for any use of this functionality in an unlicensed database.
--
-- Installation: Installation requires SQL*Plus or any IDE that supports substitution
-- variables and SQL*Plus SET commands. To install, simply run the script in
-- the target schema.
--
-- Creates: 1) XPLAN_OT object type
-- 2) XPLAN_NTT collection type
-- 3) XPLAN package
--
-- Removal: 1) DROP PACKAGE xplan;
-- 3) DROP TYPE xplan_ntt;
-- 4) DROP TYPE xplan_ot;
--
--
-- ----------------------------------------------------------------------------------------------
--
-- Define the "commenting-out" substitution variables for the AWR elements of this utility. The
-- default is commented out. To include the AWR functionality, change the variables to " " (i.e.
-- a single space).
--
SET DEFINE ON
DEFINE _awr_start = "/*"
DEFINE _awr_end = "*/"
--
-- Supporting types for the pipelined functions...
--
CREATE OR REPLACE TYPE xplan_ot AS OBJECT( plan_table_output VARCHAR2(300) );
/
CREATE OR REPLACE TYPE xplan_ntt AS TABLE OF xplan_ot;
/
--
-- Xplan package...
--
CREATE OR REPLACE PACKAGE xplan AS
FUNCTION display( p_table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
p_statement_id IN VARCHAR2 DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
FUNCTION display_cursor( p_sql_id IN VARCHAR2 DEFAULT NULL,
p_cursor_child_no IN INTEGER DEFAULT 0,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
&&_awr_start
FUNCTION display_awr( p_sql_id IN VARCHAR2,
p_plan_hash_value IN INTEGER DEFAULT NULL,
p_db_id IN INTEGER DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
&&_awr_end
END xplan;
/
CREATE OR REPLACE PACKAGE BODY xplan AS
TYPE ntt_order_map_binds IS TABLE OF VARCHAR2(100);
TYPE aat_order_map IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
g_map aat_order_map;
g_hdrs PLS_INTEGER;
g_len PLS_INTEGER;
g_pad VARCHAR2(300);
----------------------------------------------------------------------------
PROCEDURE reset_state IS
BEGIN
g_hdrs := 0;
g_len := 0;
g_pad := NULL;
g_map.DELETE;
END reset_state;
----------------------------------------------------------------------------
PROCEDURE build_order_map( p_sql IN VARCHAR2,
p_binds IN ntt_order_map_binds ) IS
TYPE rt_id_data IS RECORD
( id PLS_INTEGER
, ord PLS_INTEGER );
TYPE aat_id_data IS TABLE OF rt_id_data
INDEX BY PLS_INTEGER;
aa_ids aat_id_data;
v_cursor SYS_REFCURSOR;
v_sql VARCHAR2(32767);
BEGIN
-- Build SQL template...
-- ---------------------
v_sql := 'WITH sql_plan_data AS ( ' ||
p_sql || '
)
, hierarchical_sql_plan_data AS (
SELECT id
FROM sql_plan_data
START WITH id = 0
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY id DESC
)
SELECT id
, ROW_NUMBER() OVER (ORDER BY ROWNUM DESC) AS ord
FROM hierarchical_sql_plan_data';
-- Binds will differ according to plan type...
-- -------------------------------------------
CASE p_binds.COUNT
WHEN 0
THEN
OPEN v_cursor FOR v_sql;
WHEN 1
THEN
OPEN v_cursor FOR v_sql USING p_binds(1);
WHEN 2
THEN
OPEN v_cursor FOR v_sql USING p_binds(1),
TO_NUMBER(p_binds(2));
WHEN 3
THEN
OPEN v_cursor FOR v_sql USING p_binds(1),
TO_NUMBER(p_binds(2)),
TO_NUMBER(p_binds(3));
END CASE;
-- Fetch the ID and order data...
-- ------------------------------
FETCH v_cursor BULK COLLECT INTO aa_ids;
CLOSE v_cursor;
-- Populate the order map...
-- -------------------------
FOR i IN 1 .. aa_ids.COUNT LOOP
g_map(aa_ids(i).id) := aa_ids(i).ord;
END LOOP;
-- Use the map to determine padding needed to slot in our order column...
-- ----------------------------------------------------------------------
IF g_map.COUNT > 0 THEN
g_len := LEAST(LENGTH(g_map.LAST) + 7, 8);
g_pad := LPAD('-', g_len, '-');
END IF;
END build_order_map;
----------------------------------------------------------------------------
FUNCTION prepare_row( p_curr IN VARCHAR2,
p_next IN VARCHAR2 ) RETURN xplan_ot IS
v_id PLS_INTEGER;
v_row VARCHAR2(4000);
v_hdr VARCHAR2(64) := '%|%Id%|%Operation%|%';
BEGIN
-- Intercept the plan section to include a new column for the
-- the operation order that we mapped earlier. The plan output
-- itself will be bound by the 2nd, 3rd and 4th dashed lines.
-- We need to add in additional dashes, the order column heading
-- and the order value itself...
-- -------------------------------------------------------------
IF p_curr LIKE '---%' THEN
IF p_next LIKE v_hdr THEN
g_hdrs := 1;
v_row := g_pad || p_curr;
ELSIF g_hdrs BETWEEN 1 AND 3 THEN
g_hdrs := g_hdrs + 1;
v_row := g_pad || p_curr;
ELSE
v_row := p_curr;
END IF;
ELSIF p_curr LIKE v_hdr THEN
v_row := REGEXP_REPLACE(
p_curr, '\|',
RPAD('|', GREATEST(g_len-7, 2)) || 'Order |',
1, 2
);
ELSIF REGEXP_LIKE(p_curr, '^\|[\* 0-9]+\|') THEN
v_id := REGEXP_SUBSTR(p_curr, '[0-9]+');
v_row := REGEXP_REPLACE(
p_curr, '\|',
'|' || LPAD(g_map(v_id), GREATEST(g_len-8, 6)) || ' |',
1, 2
);
ELSE
v_row := p_curr;
END IF;
RETURN xplan_ot(v_row);
END prepare_row;
----------------------------------------------------------------------------
FUNCTION display( p_table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
p_statement_id IN VARCHAR2 DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_plan_table VARCHAR2(128) := NVL(p_table_name, 'PLAN_TABLE');
v_sql VARCHAR2(512);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Prepare the inputs for the order map...
-- ---------------------------------------
v_sql := 'SELECT id, parent_id
FROM ' || v_plan_table || '
WHERE plan_id = (SELECT MAX(plan_id)
FROM ' || v_plan_table || '
WHERE id = 0 %bind%)
ORDER BY id';
IF p_statement_id IS NULL THEN
v_sql := REPLACE(v_sql, '%bind%');
ELSE
v_sql := REPLACE(v_sql, '%bind%', 'AND statement_id = :bv_statement_id');
v_binds := ntt_order_map_binds(p_statement_id);
END IF;
-- Build the order map...
-- --------------------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY(
v_plan_table, p_statement_id, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
reset_state();
RETURN;
END display;
----------------------------------------------------------------------------
FUNCTION display_cursor( p_sql_id IN VARCHAR2 DEFAULT NULL,
p_cursor_child_no IN INTEGER DEFAULT 0,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_sql_id v$sql_plan.sql_id%TYPE;
v_child_no v$sql_plan.child_number%TYPE;
v_sql VARCHAR2(256);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Set a SQL_ID if default parameters passed...
-- --------------------------------------------
IF p_sql_id IS NULL THEN
SELECT prev_sql_id, prev_child_number
INTO v_sql_id, v_child_no
FROM v$session
WHERE sid = (SELECT m.sid FROM v$mystat m WHERE ROWNUM = 1)
AND username IS NOT NULL
AND prev_hash_value <> 0;
ELSE
v_sql_id := p_sql_id;
v_child_no := p_cursor_child_no;
END IF;
-- Prepare the inputs for the order mapping...
-- -------------------------------------------
v_sql := 'SELECT id, parent_id
FROM v$sql_plan
WHERE sql_id = :bv_sql_id
AND child_number = :bv_child_no';
v_binds := ntt_order_map_binds(v_sql_id, v_child_no);
-- Build the plan order map from the SQL...
-- ----------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
v_sql_id, v_child_no, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
reset_state();
RETURN;
END display_cursor;
&_awr_start
----------------------------------------------------------------------------
FUNCTION display_awr( p_sql_id IN VARCHAR2,
p_plan_hash_value IN INTEGER DEFAULT NULL,
p_db_id IN INTEGER DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_sql VARCHAR2(256);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Prepare the SQL for the order mapping...
-- ----------------------------------------
v_sql := 'SELECT id, parent_id
FROM dba_hist_sql_plan
WHERE sql_id = :bv_sql_id
AND plan_hash_value = :bv_plan_hash_value
AND dbid = :bv_dbid';
-- Determine all plans for the sql_id...
-- -------------------------------------
FOR r_awr IN (SELECT DISTINCT
sql_id
, plan_hash_value
, dbid
FROM dba_hist_sql_plan
WHERE sql_id = p_sql_id
AND plan_hash_value = NVL(p_plan_hash_value, plan_hash_value)
AND dbid = NVL(p_db_id, (SELECT dbid FROM v$database))
ORDER BY
plan_hash_value)
LOOP
-- Prepare the binds and build the order map...
-- --------------------------------------------
v_binds := ntt_order_map_binds(r_awr.sql_id,
r_awr.plan_hash_value,
r_awr.dbid);
-- Build the plan order map from the SQL...
-- ----------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY_AWR(
r_awr.sql_id, r_awr.plan_hash_value,
r_awr.dbid, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
END LOOP;
reset_state();
RETURN;
END display_awr;
&_awr_end
END xplan;
/
UNDEFINE _awr_start
UNDEFINE _awr_end
二. 查出sql_id
SQL> select sql_text, sql_id, child_number from v$sql where sql_text like 'SELECT d.tablespace_name%';
SQL_TEXT
----------------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER
------------- ------------
SELECT d.tablespace_name, to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g, to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g, to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct FROM dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') ORDER BY 4 DESC
10mjjdnzy7r2a 0
SQL> 三. 查看直行计划：
SQL> select * from table(xplan.display_cursor('10mjjdnzy7r2a', 0, 'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 10mjjdnzy7r2a, child number 0
-------------------------------------
SELECT d.tablespace_name, to_char(nvl(a.bytes / 1024 / 1024 /
1024, 0), '99,999,990.00') size_g, to_char(nvl(f.bytes, 0) /
1024 / 1024 / 1024, '99,999,990.00') free_g,
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00')
|| '%' used_pct FROM dba_tablespaces d, (SELECT
tablespace_name, SUM(bytes) bytes FROM dba_data_files
GROUP BY tablespace_name) a, (SELECT tablespace_name,
SUM(bytes) bytes FROM dba_free_space GROUP BY
tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') ORDER BY
4 DESC
Plan hash value: 2307396872
---------------------------------------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | 66 | SELECT STATEMENT | | | | 21 (100)| |
| 1 | 65 | SORT ORDER BY | | 8 | 648 | 21 (20)| 00:00:01 |
| 2 | 64 | NESTED LOOPS | | 8 | 648 | 20 (15)| 00:00:01 |
|* 3 | 62 | HASH JOIN OUTER | | 1 | 68 | 20 (15)| 00:00:01 |
|* 4 | 26 | HASH JOIN OUTER | | 1 | 47 | 9 (12)| 00:00:01 |
|* 5 | 1 | TABLE ACCESS FULL | TS$ | 1 | 26 | 4 (0)| 00:00:01 |
| 6 | 25 | VIEW | | 2 | 42 | 5 (20)| 00:00:01 |
| 7 | 24 | HASH GROUP BY | | 2 | 42 | 5 (20)| 00:00:01 |
| 8 | 23 | VIEW | DBA_DATA_FILES | 2 | 42 | 4 (0)| 00:00:01 |
| 9 | 22 | UNION-ALL | | | | | |
| 10 | 10 | NESTED LOOPS | | 1 | 354 | 2 (0)| 00:00:01 |
| 11 | 7 | NESTED LOOPS | | 1 | 339 | 1 (0)| 00:00:01 |
| 12 | 5 | NESTED LOOPS | | 1 | 326 | 1 (0)| 00:00:01 |
|* 13 | 2 | FIXED TABLE FULL | X$KCCFN | 1 | 310 | 0 (0)| |
|* 14 | 4 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 16 | 1 (0)| 00:00:01 |
|* 15 | 3 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| |
|* 16 | 6 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 20 | 260 | 0 (0)| |
| 17 | 9 | TABLE ACCESS CLUSTER | TS$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 18 | 8 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
| 19 | 21 | NESTED LOOPS | | 1 | 399 | 2 (0)| 00:00:01 |
| 20 | 19 | NESTED LOOPS | | 1 | 386 | 2 (0)| 00:00:01 |
| 21 | 16 | NESTED LOOPS | | 1 | 377 | 1 (0)| 00:00:01 |
| 22 | 13 | NESTED LOOPS | | 1 | 362 | 0 (0)| |
|* 23 | 11 | FIXED TABLE FULL | X$KCCFN | 1 | 310 | 0 (0)| |
|* 24 | 12 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | 0 (0)| |
| 25 | 15 | TABLE ACCESS CLUSTER | TS$ | 1 | 15 | 1 (0)| 00:00:01 |
|* 26 | 14 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
|* 27 | 18 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 9 | 1 (0)| 00:00:01 |
|* 28 | 17 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| |
|* 29 | 20 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 20 | 260 | 0 (0)| |
| 30 | 61 | VIEW | | 6 | 126 | 10 (10)| 00:00:01 |
| 31 | 60 | HASH GROUP BY | | 6 | 126 | 10 (10)| 00:00:01 |
| 32 | 59 | VIEW | DBA_FREE_SPACE | 77 | 1617 | 9 (0)| 00:00:01 |
| 33 | 58 | UNION-ALL | | | | | |
| 34 | 33 | NESTED LOOPS | | 1 | 64 | 1 (0)| 00:00:01 |
| 35 | 30 | NESTED LOOPS | | 1 | 45 | 1 (0)| 00:00:01 |
| 36 | 27 | INDEX FULL SCAN | I_FILE2 | 5 | 30 | 1 (0)| 00:00:01 |
|* 37 | 29 | TABLE ACCESS CLUSTER | FET$ | 1 | 39 | 0 (0)| |
|* 38 | 28 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
|* 39 | 32 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 0 (0)| |
|* 40 | 31 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
| 41 | 38 | NESTED LOOPS | | 74 | 5180 | 4 (0)| 00:00:01 |
| 42 | 36 | NESTED LOOPS | | 74 | 4736 | 4 (0)| 00:00:01 |
|* 43 | 34 | TABLE ACCESS FULL | TS$ | 5 | 125 | 4 (0)| 00:00:01 |
|* 44 | 35 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 14 | 546 | 0 (0)| |
|* 45 | 37 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| |
| 46 | 47 | NESTED LOOPS | | 1 | 135 | 1 (0)| 00:00:01 |
| 47 | 45 | NESTED LOOPS | | 1 | 70 | 1 (0)| 00:00:01 |
| 48 | 42 | NESTED LOOPS | | 1 | 45 | 1 (0)| 00:00:01 |
| 49 | 39 | INDEX FULL SCAN | I_FILE2 | 5 | 30 | 1 (0)| 00:00:01 |
| 50 | 41 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 39 | 0 (0)| |
|* 51 | 40 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 1 | | 0 (0)| |
|* 52 | 44 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | 0 (0)| |
|* 53 | 43 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
|* 54 | 46 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 65 | 0 (0)| |
| 55 | 57 | NESTED LOOPS | | 1 | 116 | 3 (0)| 00:00:01 |
| 56 | 54 | NESTED LOOPS | | 1 | 97 | 3 (0)| 00:00:01 |
| 57 | 51 | MERGE JOIN CARTESIAN | | 1 | 45 | 3 (0)| 00:00:01 |
| 58 | 48 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 39 | 2 (0)| 00:00:01 |
| 59 | 50 | BUFFER SORT | | 5 | 30 | 1 (0)| 00:00:01 |
| 60 | 49 | INDEX FULL SCAN | I_FILE2 | 5 | 30 | 1 (0)| 00:00:01 |
| 61 | 53 | TABLE ACCESS CLUSTER | UET$ | 1 | 52 | 0 (0)| |
|* 62 | 52 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| |
|* 63 | 56 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 0 (0)| |
|* 64 | 55 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| |
|* 65 | 63 | FIXED TABLE FIXED INDEX | X$KCFISTSA (ind:1) | 14 | 182 | 0 (0)| |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
---------------------------------------------------------------------
1 - SEL$F5BB74E1
5 - SEL$F5BB74E1 / TS@SEL$2
6 - SEL$3 / A@SEL$1
7 - SEL$3
8 - SET$1 / DBA_DATA_FILES@SEL$3
9 - SET$1
10 - SEL$F5B21678
13 - SEL$F5B21678 / X$KCCFN@SEL$6
14 - SEL$F5B21678 / F@SEL$4
15 - SEL$F5B21678 / F@SEL$4
16 - SEL$F5B21678 / FE@SEL$4
17 - SEL$F5B21678 / TS@SEL$4
18 - SEL$F5B21678 / TS@SEL$4
19 - SEL$DFD66ADD
23 - SEL$DFD66ADD / X$KCCFN@SEL$9
24 - SEL$DFD66ADD / HC@SEL$7
25 - SEL$DFD66ADD / TS@SEL$7
26 - SEL$DFD66ADD / TS@SEL$7
27 - SEL$DFD66ADD / F@SEL$7
28 - SEL$DFD66ADD / F@SEL$7
29 - SEL$DFD66ADD / FE@SEL$7
30 - SEL$10 / F@SEL$1
31 - SEL$10
32 - SET$2 / DBA_FREE_SPACE@SEL$10
33 - SET$2
34 - SEL$11
36 - SEL$11 / FI@SEL$11
37 - SEL$11 / F@SEL$11
38 - SEL$11 / F@SEL$11
39 - SEL$11 / TS@SEL$11
40 - SEL$11 / TS@SEL$11
41 - SEL$12
43 - SEL$12 / TS@SEL$12
44 - SEL$12 / F@SEL$12
45 - SEL$12 / FI@SEL$12
46 - SEL$13
49 - SEL$13 / FI@SEL$13
50 - SEL$13 / RB@SEL$13
51 - SEL$13 / RB@SEL$13
52 - SEL$13 / TS@SEL$13
53 - SEL$13 / TS@SEL$13
54 - SEL$13 / U@SEL$13
55 - SEL$14
58 - SEL$14 / RB@SEL$14
60 - SEL$14 / FI@SEL$14
61 - SEL$14 / U@SEL$14
62 - SEL$14 / U@SEL$14
63 - SEL$14 / TS@SEL$14
64 - SEL$14 / TS@SEL$14
65 - SEL$F5BB74E1 / TSATTR@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5B21678")
MERGE(@"SEL$ABDE6DFF")
OUTLINE_LEAF(@"SEL$DFD66ADD")
MERGE(@"SEL$CF5359D5")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$11")
OUTLINE_LEAF(@"SEL$12")
OUTLINE_LEAF(@"SEL$13")
OUTLINE_LEAF(@"SEL$14")
OUTLINE_LEAF(@"SET$2")
OUTLINE_LEAF(@"SEL$10")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$ABDE6DFF")
MERGE(@"SEL$6")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$CF5359D5")
MERGE(@"SEL$9")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$9")
FULL(@"SEL$F5BB74E1" "TS"@"SEL$2")
NO_ACCESS(@"SEL$F5BB74E1" "A"@"SEL$1")
NO_ACCESS(@"SEL$F5BB74E1" "F"@"SEL$1")
FULL(@"SEL$F5BB74E1" "TSATTR"@"SEL$2")
LEADING(@"SEL$F5BB74E1" "TS"@"SEL$2" "A"@"SEL$1" "F"@"SEL$1" "TSATTR"@"SEL$2")
USE_HASH(@"SEL$F5BB74E1" "A"@"SEL$1")
USE_HASH(@"SEL$F5BB74E1" "F"@"SEL$1")
USE_NL(@"SEL$F5BB74E1" "TSATTR"@"SEL$2")
NO_ACCESS(@"SEL$3" "DBA_DATA_FILES"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$3")
NO_ACCESS(@"SEL$10" "DBA_FREE_SPACE"@"SEL$10")
USE_HASH_AGGREGATION(@"SEL$10")
FULL(@"SEL$14" "RB"@"SEL$14")
INDEX(@"SEL$14" "FI"@"SEL$14" ("FILE$"."TS#" "FILE$"."RELFILE#"))
INDEX(@"SEL$14" "U"@"SEL$14" "I_FILE#_BLOCK#")
INDEX(@"SEL$14" "TS"@"SEL$14" "I_TS#")
LEADING(@"SEL$14" "RB"@"SEL$14" "FI"@"SEL$14" "U"@"SEL$14" "TS"@"SEL$14")
USE_MERGE_CARTESIAN(@"SEL$14" "FI"@"SEL$14")
USE_NL(@"SEL$14" "U"@"SEL$14")
USE_NL(@"SEL$14" "TS"@"SEL$14")
INDEX(@"SEL$13" "FI"@"SEL$13" ("FILE$"."TS#" "FILE$"."RELFILE#"))
INDEX_RS_ASC(@"SEL$13" "RB"@"SEL$13" ("RECYCLEBIN$"."TS#"))
INDEX(@"SEL$13" "TS"@"SEL$13" "I_TS#")
FULL(@"SEL$13" "U"@"SEL$13")
LEADING(@"SEL$13" "FI"@"SEL$13" "RB"@"SEL$13" "TS"@"SEL$13" "U"@"SEL$13")
USE_NL(@"SEL$13" "RB"@"SEL$13")
USE_NL(@"SEL$13" "TS"@"SEL$13")
USE_NL(@"SEL$13" "U"@"SEL$13")
FULL(@"SEL$12" "TS"@"SEL$12")
FULL(@"SEL$12" "F"@"SEL$12")
INDEX(@"SEL$12" "FI"@"SEL$12" ("FILE$"."TS#" "FILE$"."RELFILE#"))
LEADING(@"SEL$12" "TS"@"SEL$12" "F"@"SEL$12" "FI"@"SEL$12")
USE_NL(@"SEL$12" "F"@"SEL$12")
USE_NL(@"SEL$12" "FI"@"SEL$12")
INDEX(@"SEL$11" "FI"@"SEL$11" ("FILE$"."TS#" "FILE$"."RELFILE#"))
INDEX(@"SEL$11" "F"@"SEL$11" "I_TS#")
INDEX(@"SEL$11" "TS"@"SEL$11" "I_TS#")
LEADING(@"SEL$11" "FI"@"SEL$11" "F"@"SEL$11" "TS"@"SEL$11")
USE_NL(@"SEL$11" "F"@"SEL$11")
USE_NL(@"SEL$11" "TS"@"SEL$11")
FULL(@"SEL$DFD66ADD" "X$KCCFN"@"SEL$9")
FULL(@"SEL$DFD66ADD" "HC"@"SEL$7")
INDEX(@"SEL$DFD66ADD" "TS"@"SEL$7" "I_TS#")
INDEX_RS_ASC(@"SEL$DFD66ADD" "F"@"SEL$7" ("FILE$"."FILE#"))
FULL(@"SEL$DFD66ADD" "FE"@"SEL$7")
LEADING(@"SEL$DFD66ADD" "X$KCCFN"@"SEL$9" "HC"@"SEL$7" "TS"@"SEL$7" "F"@"SEL$7" "FE"@"SEL$7")
USE_NL(@"SEL$DFD66ADD" "HC"@"SEL$7")
USE_NL(@"SEL$DFD66ADD" "TS"@"SEL$7")
USE_NL(@"SEL$DFD66ADD" "F"@"SEL$7")
USE_NL(@"SEL$DFD66ADD" "FE"@"SEL$7")
FULL(@"SEL$F5B21678" "X$KCCFN"@"SEL$6")
INDEX_RS_ASC(@"SEL$F5B21678" "F"@"SEL$4" ("FILE$"."FILE#"))
FULL(@"SEL$F5B21678" "FE"@"SEL$4")
INDEX(@"SEL$F5B21678" "TS"@"SEL$4" "I_TS#")
LEADING(@"SEL$F5B21678" "X$KCCFN"@"SEL$6" "F"@"SEL$4" "FE"@"SEL$4" "TS"@"SEL$4")
USE_NL(@"SEL$F5B21678" "F"@"SEL$4")
USE_NL(@"SEL$F5B21678" "FE"@"SEL$4")
USE_NL(@"SEL$F5B21678" "TS"@"SEL$4")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TS"."NAME"="F"."TABLESPACE_NAME")
4 - access("TS"."NAME"="A"."TABLESPACE_NAME")
5 - filter(("TS"."ONLINE$"<>3 AND (DECODE("TS"."BITMAPPED",0,'DICTIONARY','LOCAL')<>'LOCAL' OR
DECODE("TS"."CONTENTS$",0,DECODE(BITAND("TS"."FLAGS",16),16,'UNDO','PERMANENT'),1,'TEMPORARY')<>'TEMP
ORARY') AND BITAND("FLAGS",2048)<>2048))
13 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4))
14 - filter("F"."SPARE1" IS NULL)
15 - access("FNFNO"="F"."FILE#")
16 - filter("FE"."FENUM"="F"."FILE#")
18 - access("F"."TS#"="TS"."TS#")
23 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4))
24 - filter("FNFNO"="HC"."KTFBHCAFNO")
26 - access("HC"."KTFBHCTSN"="TS"."TS#")
27 - filter("F"."SPARE1" IS NOT NULL)
28 - access("FNFNO"="F"."FILE#")
29 - filter("FE"."FENUM"="F"."FILE#")
37 - filter("F"."FILE#"="FI"."RELFILE#")
38 - access("F"."TS#"="FI"."TS#")
39 - filter("TS"."BITMAPPED"=0)
40 - access("TS"."TS#"="F"."TS#")
43 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
44 - filter("TS"."TS#"="F"."KTFBFETSN")
45 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
51 - access("RB"."TS#"="FI"."TS#")
52 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
53 - access("TS"."TS#"="RB"."TS#")
54 - filter(("U"."KTFBUEFNO"="FI"."RELFILE#" AND "U"."KTFBUESEGTSN"="RB"."TS#" AND
"U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#"))
62 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
filter(("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#"))
63 - filter("TS"."BITMAPPED"=0)
64 - access("TS"."TS#"="U"."TS#")
65 - filter("TS"."TS#"="TSATTR"."TSID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) TO_CHAR(NVL(("A"."BYTES"-NVL("F"."BYTES",0))/"A"."BYTES"*100,0))||'%'[8],
"TS"."NAME"[VARCHAR2,30], TO_CHAR(NVL("A"."BYTES"/1024/1024/1024,0))[14],
TO_CHAR(NVL("F"."BYTES",0)/1024/1024/1024)[14]
2 - "TS"."NAME"[VARCHAR2,30], "A"."BYTES"[NUMBER,22], "F"."BYTES"[NUMBER,22]
3 - (#keys=1) "TS"."NAME"[VARCHAR2,30], "A"."BYTES"[NUMBER,22], "TS"."TS#"[NUMBER,22],
"F"."BYTES"[NUMBER,22]
4 - (#keys=1) "TS"."NAME"[VARCHAR2,30], "TS"."TS#"[NUMBER,22], "A"."BYTES"[NUMBER,22]
5 - "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30]
6 - "A"."TABLESPACE_NAME"[VARCHAR2,30], "A"."BYTES"[NUMBER,22]
7 - "TABLESPACE_NAME"[VARCHAR2,30], SUM("BYTES")[22]
8 - "TABLESPACE_NAME"[VARCHAR2,30], "BYTES"[NUMBER,22]
9 - STRDEF[30], STRDEF[22]
10 - "F"."BLOCKS"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
11 - "F"."BLOCKS"[NUMBER,22], "F"."TS#"[NUMBER,22]
12 - "F"."FILE#"[NUMBER,22], "F"."BLOCKS"[NUMBER,22], "F"."TS#"[NUMBER,22]
13 - "INST_ID"[NUMBER,22], "FNTYP"[NUMBER,22], "FNFNO"[NUMBER,22], "FNFLG"[NUMBER,22],
"FNNAM"[VARCHAR2,513]
14 - "F"."FILE#"[NUMBER,22], "F"."BLOCKS"[NUMBER,22], "F"."TS#"[NUMBER,22]
15 - "F".ROWID[ROWID,10], "F"."FILE#"[NUMBER,22]
16 - "FE"."FENUM"[NUMBER,22]
17 - "TS".ROWID[ROWID,10], "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30],
"TS"."BLOCKSIZE"[NUMBER,22]
18 - "TS".ROWID[ROWID,10]
19 - "HC"."KTFBHCSZ"[NUMBER,22], "HC"."KTFBHCCVAL"[NUMBER,22], "TS"."NAME"[VARCHAR2,30],
"TS"."BLOCKSIZE"[NUMBER,22]
20 - "HC"."KTFBHCSZ"[NUMBER,22], "HC"."KTFBHCCVAL"[NUMBER,22], "TS"."NAME"[VARCHAR2,30],
"TS"."BLOCKSIZE"[NUMBER,22], "F"."FILE#"[NUMBER,22]
21 - "FNFNO"[NUMBER,22], "HC"."KTFBHCSZ"[NUMBER,22], "HC"."KTFBHCCVAL"[NUMBER,22],
"TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
22 - "FNFNO"[NUMBER,22], "HC"."KTFBHCTSN"[NUMBER,22], "HC"."KTFBHCSZ"[NUMBER,22],
"HC"."KTFBHCCVAL"[NUMBER,22]
23 - "INST_ID"[NUMBER,22], "FNTYP"[NUMBER,22], "FNFNO"[NUMBER,22], "FNFLG"[NUMBER,22],
"FNNAM"[VARCHAR2,513]
24 - "HC"."KTFBHCAFNO"[NUMBER,22], "HC"."KTFBHCTSN"[NUMBER,22], "HC"."KTFBHCSZ"[NUMBER,22],
"HC"."KTFBHCCVAL"[NUMBER,22]
25 - "TS".ROWID[ROWID,10], "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30],
"TS"."BLOCKSIZE"[NUMBER,22]
26 - "TS".ROWID[ROWID,10]
27 - "F"."FILE#"[NUMBER,22]
28 - "F".ROWID[ROWID,10], "F"."FILE#"[NUMBER,22]
29 - "FE"."FENUM"[NUMBER,22]
30 - "F"."TABLESPACE_NAME"[VARCHAR2,30], "F"."BYTES"[NUMBER,22]
31 - "TABLESPACE_NAME"[VARCHAR2,30], SUM("BYTES")[22]
32 - "TABLESPACE_NAME"[VARCHAR2,30], "BYTES"[NUMBER,22]
33 - STRDEF[30], STRDEF[22]
34 - "F"."LENGTH"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
35 - "F"."TS#"[NUMBER,22], "F"."LENGTH"[NUMBER,22]
36 - "FI"."TS#"[NUMBER,22], "FI"."RELFILE#"[NUMBER,22]
37 - "F".ROWID[ROWID,10], "F"."TS#"[NUMBER,22], "F"."FILE#"[NUMBER,22], "F"."LENGTH"[NUMBER,22]
38 - "F".ROWID[ROWID,10]
39 - "TS".ROWID[ROWID,10], "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30],
"TS"."BLOCKSIZE"[NUMBER,22], "TS"."BITMAPPED"[NUMBER,22]
40 - "TS".ROWID[ROWID,10]
41 - "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "F"."KTFBFEBLKS"[NUMBER,22]
42 - "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "F"."KTFBFETSN"[NUMBER,22],
"F"."KTFBFEFNO"[NUMBER,22], "F"."KTFBFEBLKS"[NUMBER,22]
43 - "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
44 - "F"."KTFBFETSN"[NUMBER,22], "F"."KTFBFEFNO"[NUMBER,22], "F"."KTFBFEBLKS"[NUMBER,22]
46 - "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "U"."KTFBUEBLKS"[NUMBER,22]
47 - "FI"."RELFILE#"[NUMBER,22], "RB"."TS#"[NUMBER,22], "RB"."FILE#"[NUMBER,22],
"RB"."BLOCK#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
48 - "FI"."RELFILE#"[NUMBER,22], "RB"."TS#"[NUMBER,22], "RB"."FILE#"[NUMBER,22],
"RB"."BLOCK#"[NUMBER,22]
49 - "FI"."TS#"[NUMBER,22], "FI"."RELFILE#"[NUMBER,22]
50 - "RB"."TS#"[NUMBER,22], "RB"."FILE#"[NUMBER,22], "RB"."BLOCK#"[NUMBER,22]
51 - "RB".ROWID[ROWID,10], "RB"."TS#"[NUMBER,22]
52 - "TS".ROWID[ROWID,10], "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30],
"TS"."ONLINE$"[NUMBER,22], "TS"."CONTENTS$"[NUMBER,22], "TS"."BLOCKSIZE"[NUMBER,22],
"TS"."BITMAPPED"[NUMBER,22]
53 - "TS".ROWID[ROWID,10]
54 - "U"."KTFBUESEGTSN"[NUMBER,22], "U"."KTFBUESEGFNO"[NUMBER,22], "U"."KTFBUESEGBNO"[NUMBER,22],
"U"."KTFBUEFNO"[NUMBER,22], "U"."KTFBUEBLKS"[NUMBER,22]
55 - "U"."LENGTH"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
56 - "U"."TS#"[NUMBER,22], "U"."LENGTH"[NUMBER,22]
57 - "RB"."TS#"[NUMBER,22], "RB"."FILE#"[NUMBER,22], "RB"."BLOCK#"[NUMBER,22],
"FI"."TS#"[NUMBER,22], "FI"."RELFILE#"[NUMBER,22]
58 - "RB"."TS#"[NUMBER,22], "RB"."FILE#"[NUMBER,22], "RB"."BLOCK#"[NUMBER,22]
59 - (#keys=0) "FI"."TS#"[NUMBER,22], "FI"."RELFILE#"[NUMBER,22]
60 - "FI"."TS#"[NUMBER,22], "FI"."RELFILE#"[NUMBER,22]
61 - "U".ROWID[ROWID,10], "U"."TS#"[NUMBER,22], "U"."SEGFILE#"[NUMBER,22],
"U"."SEGBLOCK#"[NUMBER,22], "U"."LENGTH"[NUMBER,22]
62 - "U".ROWID[ROWID,10]
63 - "TS".ROWID[ROWID,10], "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30],
"TS"."BLOCKSIZE"[NUMBER,22], "TS"."BITMAPPED"[NUMBER,22]
64 - "TS".ROWID[ROWID,10]
65 - "TSATTR"."TSID"[NUMBER,22]
367 rows selected.
SQL>
2034
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
