How to gather extended query execution stats in Oracle?

OracleSoftware & CodingProgramming

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.

  1. Execute the query with the gather_plan_statistics hint.
  2. 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;
raja
Published on 05-Dec-2020 05:52:55
Advertisements