Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to gather extended query execution stats in Oracle?
Problem:
You want to gather extended explain plan statistics for a specific query.
Solution
We can use the GATHER_PLAN_STATISTICS hint. This hint when placed within a query at runtime, will generate extended runtime statistics. It basically have two steps.
- Execute the query with the gather_plan_statistics hint.
- Use dbms_xplan.display_cursor to display the results.
Example
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;
Output
Southlake 5760 4200 9000 Seattle 10701 6900 17000
We will now use the dbms_xplan to display the extended query statistics. Ensure that the SQL Plus setting SERVEROUTPUT is set to OFF, else results will not be properly displayed.
Example
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
Output
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)
Note: this is an adaptive plan
The GATHER_PLAN_STATISTICS hint gathers runtime statistics; therefore the query needs to be executed in order to gather these statistics. If you already have a query that is performing at a substandard optimization level, it may be useful to run your query with the GATHER_PLAN_STATISTICS hint. This can quickly give you information that you simply do not have with a normal explain plan, as it shows you estimated and actual information regarding query statistics. F
It does take some resources in order to gather these extra runtime statistics.
Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.
Example
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) ) ;
Example
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;Example
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) ) ;
Example
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) ) ;
Example
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;
Updated on: 05-Dec-2020
342 Views
Advertisements