1.先explain plan for 目标sql：
explain plan for WITH sales_countries AS (SELECT /*+ gather_plan_statistics */ cu.cust_id, co.country_name FROM sh.countries co, sh.customers cu WHERE cu.country_id = co.country_id), top_sales AS (SELECT p.prod_name, sc.country_name, s.channel_id, t.calendar_quarter_desc, s.amount_sold, s.quantity_sold FROM sh.sales s JOIN sh.times t ON t.time_id = s.time_id JOIN sh.customers c ON c.cust_id = s.cust_id JOIN sales_countries sc ON sc.cust_id = c.cust_id JOIN sh.products p ON p.prod_id = s.prod_id), sales_rpt AS (SELECT prod_name product, country_name country, channel_id channel, substr(calendar_quarter_desc, 6, 2) quarter, SUM(amount_sold) amount_sold, SUM(quantity_sold) quantity_sold FROM top_sales GROUP BY prod_name, country_name, channel_id, substr(calendar_quarter_desc, 6, 2)) SELECT * FROM (SELECT product, channel, quarter, country, quantity_sold FROM sales_rpt) pivot(SUM(quantity_sold) FOR(channel, quarter) IN((5, '02') AS catalog_q2, (4, '01') AS internet_q1, (4, '04') AS internet_q4, (2, '02') AS partners_q2, (9, '03') AS tele_q3)) 46 ORDER BY product, country 47 / Explained. Elapsed: 00:00:00.37
SQL>
2.用以下sql可以查询出相关表的大小：
SELECT owner, segment_name, segment_type, SUM(bytes / 1024 / 1024) "Size(Mb)" FROM dba_segments WHERE owner IN (SELECT /*+ no_unnest */ object_owner FROM plan_table) AND segment_name IN (SELECT /*+ no_unnest */ object_name FROM plan_table) GROUP BY owner, segment_type, segment_name UNION ----table in the index SELECT owner, '*' || segment_name, segment_type, SUM(bytes / 1024 / 1024) "Size(Mb)" FROM dba_segments WHERE owner IN (SELECT table_owner FROM dba_indexes WHERE owner IN (SELECT /*+ no_unnest */ object_owner FROM plan_table) AND index_name IN (SELECT /*+ no_unnest */ object_name FROM plan_table)) AND segment_name IN (SELECT /*+ no_unnest */ table_name FROM dba_indexes WHERE owner IN (SELECT /*+ no_unnest */ object_owner FROM plan_table) AND index_name IN (SELECT /*+ no_unnest */ object_name FROM plan_table)) GROUP BY owner, segment_type, segment_name ORDER BY 3, 4;
