如何在Oracle中收集扩展的查询执行统计信息？
问题：
您想要为特定查询收集扩展的解释计划统计信息。
解
我们可以使用GATHER_PLAN_STATISTICS提示。在运行时将其放在查询中时，此提示将生成扩展的运行时统计信息。它基本上有两个步骤。
使用collect_plan_statistics提示执行查询。
使用dbms_xplan.display_cursor显示结果。
示例
SELECT /*+ gather_plan_statistics */
city,
round(avg(fees)) avg_fees,
min(fees) min_fees,
max(fees) max_fees
FROM students e, departments d, locations l
WHERE e.department_id = d.department_id
AND l.location_id = d.location_id
GROUP BY city;输出结果
Southlake 5760 4200 9000 Seattle 10701 6900 17000
现在，我们将使用dbms_xplan显示扩展的查询统计信息。确保将SQLPlus设置SERVEROUTPUT设置为OFF，否则结果将无法正确显示。
示例
SELECT * FROM table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SQL_ID 5cf72utppm8j1, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ city, round(avg(fees)) avg_fees, min(fees) min_fees, max(fees) max_fees FROM students e, departments d, locations l WHERE e.department_id = d.department_id AND l.location_id = d.location_id GROUP BY city
输出结果
Plan hash value: 1628862737
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.01 | 22 | | | |
| 1 | HASH GROUP BY | | 1 | 23 | 7 |00:00:00.01 | 22 | 851K| 851K| 1042K (0)|
|* 2 | HASH JOIN | | 1 | 106 | 106 |00:00:00.01 | 22 | 1610K| 1610K| 1416K (0)|
|* 3 | HASH JOIN | | 1 | 27 | 27 |00:00:00.01 | 16 | 1744K| 1744K| 1589K (0)|
| 4 | VIEW | index$_join$_003 | 1 | 23 | 23 |00:00:00.01 | 8 | | | |
|* 5 | HASH JOIN | | 1 | | 23 |00:00:00.01 | 8 | 1449K| 1449K| 1625K (0)|
| 6 | INDEX FAST FULL SCAN| LOC_CITY_IX | 1 | 23 | 23 |00:00:00.01 | 4 | | | |
| 7 | INDEX FAST FULL SCAN| LOC_ID_PK | 1 | 23 | 23 |00:00:00.01 | 4 | | | |
| 8 | VIEW | index$_join$_002 | 1 | 27 | 27 |00:00:00.01 | 8 | | | |
|* 9 | HASH JOIN | | 1 | | 27 |00:00:00.01 | 8 | 1610K| 1610K| 1535K (0)|
| 10 | INDEX FAST FULL SCAN| DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 4 | | | |
| 11 | INDEX FAST FULL SCAN| DEPT_LOCATION_IX | 1 | 27 | 27 |00:00:00.01 | 4 | | | |
| 12 | TABLE ACCESS FULL | students | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
3 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
5 - access(ROWID=ROWID)
9 - access(ROWID=ROWID)注意：这是一个自适应计划
GATHER_PLAN_STATISTICS提示收集运行时统计信息；因此，需要执行查询才能收集这些统计信息。如果您已经有一个查询未达到优化标准级别的查询，则使用GATHER_PLAN_STATISTICS提示运行查询可能会很有用。这可以快速为您提供普通解释计划根本没有的信息，因为它可以显示有关查询统计信息的估计信息和实际信息。F
确实需要一些资源才能收集这些额外的运行时统计信息。
数据准备：用于该问题的数据如下所示。数据完全用于演示目的。
示例
DROP TABLE students;
COMMIT;
CREATE TABLE students
( student_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
, email VARCHAR2(40)
, phone_number VARCHAR2(20)
, join_date DATE
, class_id VARCHAR2(20)
, fees NUMBER(8,2)
, professor_id NUMBER(6)
, department_id NUMBER(4)
) ;示例
CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);
COMMIT;示例
CREATE TABLE locations
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city VARCHAR2(30)
CONSTRAINT loc_city_nn NOT NULL
, state_province VARCHAR2(25)
, country_id CHAR(2)
) ;示例
CREATE TABLE departments
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, professor_id NUMBER(6)
, location_id NUMBER(4)
) ;示例
INSERT INTO departments VALUES ( 10, 'Administration', 200, 1700); INSERT INTO departments VALUES ( 20, 'Teaching', 201, 1800); INSERT INTO departments VALUES ( 30 , 'Purchasing' , 114 , 1700 ); INSERT INTO departments VALUES ( 40 , 'Human Resources' , 203 , 2400 ); INSERT INTO departments VALUES ( 50 , 'Students' , 121 , 1500 ); INSERT INTO departments VALUES ( 60 , 'IT' , 103 , 1400 ); INSERT INTO departments VALUES ( 70 , 'Public Relations' , 204 , 2700 ); INSERT INTO departments VALUES ( 80 , 'Fee collectors' , 145 , 2500 ); INSERT INTO departments VALUES ( 90 , 'Executive' , 100 , 1700 ); INSERT INTO departments VALUES ( 100 , 'Finance' , 108 , 1700 ); INSERT INTO departments VALUES ( 110 , 'Accounting' , 205 , 1700 ); INSERT INTO departments VALUES ( 120 , 'Treasury' , NULL , 1700 ); INSERT INTO departments VALUES ( 130 , 'Corporate Tax' , NULL , 1700 ); INSERT INTO departments VALUES ( 140 , 'Control And Credit' , NULL , 1700 ); INSERT INTO departments VALUES ( 160 , 'Benefits' , NULL , 1700 ); INSERT INTO departments VALUES ( 230 , 'Helpdesk' , NULL , 1700 ); COMMIT; ---------------------locations--------------- INSERT INTO locations VALUES ( 1000 , '1297 Via Cola di Rie' , '00989' , 'Roma' , NULL , 'IT' ); INSERT INTO locations VALUES ( 1100 , '93091 Calle della Testa' , '10934' , 'Venice' , NULL , 'IT' ); INSERT INTO locations VALUES ( 1200 , '2017 Shinjuku-ku' , '1689' , 'Tokyo' , 'Tokyo Prefecture' , 'JP' ); INSERT INTO locations VALUES ( 1300 , '9450 Kamiya-cho' , '6823' , 'Hiroshima' , NULL , 'JP' ); INSERT INTO locations VALUES ( 1400 , '2014 Jabberwocky Rd' , '26192' , 'Southlake' , 'Texas' , 'US' ); INSERT INTO locations VALUES ( 1500 , '2011 Interiors Blvd' , '99236' , 'South San Francisco' , 'California' , 'US' ); INSERT INTO locations VALUES ( 1600 , '2007 Zagora St' , '50090' , 'South Brunswick' , 'New Jersey' , 'US' ); INSERT INTO locations VALUES ( 1700 , '2004 Charade Rd' , '98199' , 'Seattle' , 'Washington' , 'US' ); INSERT INTO locations VALUES ( 1800 , '147 Spadina Ave' , 'M5V 2L7' , 'Toronto' , 'Ontario' , 'CA' ); INSERT INTO locations VALUES ( 1900 , '6092 Boxwood St' , 'YSW 9T2' , 'Whitehorse' , 'Yukon' , 'CA' ); INSERT INTO locations VALUES ( 2000 , '40-5-12 Laogianggen' , '190518' , 'Beijing' , NULL , 'CN' ); INSERT INTO locations VALUES ( 2100 , '1298 Vileparle (E)' , '490231' , 'Bombay' , 'Maharashtra' , 'IN' ); INSERT INTO locations VALUES ( 2200 , '12-98 Victoria Street' , '2901' , 'Sydney' , 'New South Wales' , 'AU' ); INSERT INTO locations VALUES ( 2300 , '198 Clementi North' , '540198' , 'Singapore' , NULL , 'SG' ); INSERT INTO locations VALUES ( 2400 , '8204 Arthur St' , NULL , 'London' , NULL , 'UK' ); INSERT INTO locations VALUES ( 2500 , 'Magdalen Centre, The Oxford Science Park' , 'OX9 9ZB' , 'Oxford' , 'Oxford' , 'UK' ); INSERT INTO locations VALUES ( 2600 , '9702 Chester Road' , '09629850293' , 'Stretford' , 'Manchester' , 'UK' ); INSERT INTO locations VALUES ( 2700 , 'Schwanthalerstr. 7031' , '80925' , 'Munich' , 'Bavaria' , 'DE' ); INSERT INTO locations VALUES ( 2800 , 'Rua Frei Caneca 1360 ' , '01307-002' , 'Sao Paulo' , 'Sao Paulo' , 'BR' ); INSERT INTO locations VALUES ( 2900 , '20 Rue des Corps-Saints' , '1730' , 'Geneva' , 'Geneve' , 'CH' ); INSERT INTO locations VALUES ( 3000 , 'Murtenstrasse 921' , '3095' , 'Bern' , 'BE' , 'CH' ); INSERT INTO locations VALUES ( 3100 , 'Pieter Breughelstraat 837' , '3029SK' , 'Utrecht' , 'Utrecht' , 'NL' ); INSERT INTO locations VALUES ( 3200 , 'Mariano Escobedo 9991' , '11932' , 'Mexico City' , 'Distrito Federal,' , 'MX' ); COMMIT;