Untitled

-- DYNAMIC PERFORMANCE VIEWS
-- SESSION #3

DROP TABLE child purge
;
DROP TABLE mother purge
;
DROP SEQUENCE sqMother
;
DROP SEQUENCE sqChild
;

CREATE TABLE mother
(
   ID    NUMBER(10,0)
         NOT NULL
         CONSTRAINT coPKMother
         PRIMARY KEY

 , name  VARCHAR2(100)
         NOT NULL

 , gender CHAR(01)
          NOT NULL
          CONSTRAINT coCHMotherGender
          CHECK (gender IN ('F'))

 , dateOfBirth  DATE
                NOT NULL

 , meaninglessData CHAR(1000)
)
;
CREATE TABLE child
(
   ID    NUMBER(10,0)
         NOT NULL
         CONSTRAINT coPKChild
           PRIMARY KEY

 , motherID NUMBER(10,0)
            NOT NULL
            CONSTRAINT coFKChildMother
              references mother

 , name     VARCHAR(100)
            NOT NULL

 , gender   CHAR(01)
            NOT NULL
            CONSTRAINT coCHChildGender
              CHECK  (gender IN ('M','F'))

 , dateOfBirth DATE
               NOT NULL

 , meaninglessData CHAR(1000)
                   NOT NULL
)
;
CREATE SEQUENCE sqMother
  START WITH 1
  INCREMENT BY 1
  noMaxValue
  cache 100
  ;
  CREATE SEQUENCE sqChild
  START WITH 1
  INCREMENT BY 1
  noMaxValue
  cache 100

/
DECLARE

  noOfMothers  CONSTANT int := 1000;
  noChildrenPerMother CONSTANT int := 5;

BEGIN

  EXECUTE IMMEDIATE 'truncate table child';
  EXECUTE IMMEDIATE 'truncate table mother';
  FOR m IN 1..noOfMothers LOOP

     INSERT INTO mother (id,name,gender,dateOfBirth,meaninglessData)
     VALUES (    sqMother.NEXTVAL
              , 'Mother ' || TO_CHAR(sqMother.CURRVAL)
              , 'F'
              , SYSDATE
              , 'x'
              );

     FOR c IN 1..noChildrenPerMother LOOP
       INSERT INTO child (id, motherID, name, gender, dateOfBirth, meaninglessData)
        VALUES (
                   sqChild.NEXTVAL
                ,  sqMother.CURRVAL
                ,  'Child number ' || TO_CHAR(c) || ' of mother ' || TO_CHAR(sqMother.CURRVAL)
                ,  'F'
                ,  SYSDATE
                , 'x'
               );
     END LOOP;

  END LOOP;
  COMMIT;
END;


-- 1) Use the static
-- data dictionary to obtain a list of the dynamic performance views.

SELECT VIEW_NAME FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'V_$%';

-- 2) Using the V$SESSION view, find the processes running on your instance. Can you identify
-- which process is yourself? And what might some of the others be?
SELECT PROCESS, USERNAME FROM V$SESSION;

-- 3) Use the V$SGA view to calculate how much memory your
-- instance is using for Shared Global Area. And, once you have that number, could you
-- please return it in Gigabytes as well?
SELECT NAME, VALUE FROM V$SGA;
SELECT (SUM(VALUE) / 1073741824) AS "Total memory" FROM V$SGA;

-- 4) Session memory usage is exposed on a session-by-by-session
-- level in the view V$SESSTAT, so we will need to sum this for all sessions. Join
-- V$SESSTAT with the view V$STATNAME in order to limited the search for the statistic named
-- 'session pga memory'
SELECT SUM(V$SESSTAT.VALUE) AS "Total value"
FROM V$STATNAME INNER JOIN V$SESSTAT
ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
WHERE V$STATNAME.NAME = 'session pga memory';

-- 5) SCRIPT TO CREATE AND POPULATE THE TABLES

-- 6) Write a piece of SQL that retrieves one mother (with some ID) and all of her children (so
-- we are clearly in join-territory here)
SELECT m.ID, m.NAME, c.NAME AS "Child name"
FROM MOTHER m INNER JOIN CHILD c ON (c.MOTHERID = m.ID)
WHERE m.ID = 22;

-- 7) . Expand the code from question 4
-- 7.1. Change it so it shows ALL the different resource counters for your session. You can
-- identify your own session by adding the line where sid =
-- sys_context(‘userenv’,’sid’) to the SQL.
-- 7.2. Take a peek at the results: how many different types of ressources can we track the
-- consumption of

SELECT DISTINCT V$STATNAME.NAME, SUM(V$SESSTAT.VALUE) AS "Total value"
FROM V$STATNAME INNER JOIN V$SESSTAT
ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
WHERE V$SESSTAT.SID = SYS_CONTEXT('userenv', 'sid')
GROUP BY V$STATNAME.NAME;

SELECT COUNT(DISTINCT V$STATNAME.NAME) AS "Number of resources"
FROM V$STATNAME INNER JOIN V$SESSTAT
ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
WHERE V$SESSTAT.SID = SYS_CONTEXT('userenv', 'sid');

-- 8) One of the resource counters is named 'consistent gets' This records the number of
-- times your session has requested a block of data to be read.
-- 8.1. Change the SQL to retrieve only that value
-- 8.2. Make a note of the value that you find
-- ### 86735

SELECT SUM(V$SESSTAT.VALUE) AS "Total value"
FROM V$STATNAME INNER JOIN V$SESSTAT
ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
WHERE V$SESSTAT.SID = SYS_CONTEXT('userenv', 'sid')
AND V$STATNAME.NAME = 'consistent gets';

-- 9
SELECT m.ID, m.NAME, c.NAME AS "Child name"
FROM MOTHER m INNER JOIN CHILD c ON (c.MOTHERID = m.ID)
WHERE m.ID = 22;

-- 10, 11)
--Find the IO cost of retrieving the rows:
--10.1. Re-run the SQL that retrieves the value of 'consistent gets'
--10.2. Calculate the difference between the value that you now got and the one from point
-- The difference is the number of blocks that Oracle had to read to return your data

/*
88499, 89381, DIFF 882
89381, 90263, DIFF 882
90263, 91145, DIFF 882
*/

-- 12) Above, we got the IO cost for retrieving a mother and her children. Let try to improve that.
--12.1. Create an index on the foreign key in the Child table (use the create index name on
-- tablename (columname) syntax
--12.2. Make sure that the query optimizer knows about the index. Use the execute
-- DBMS_STATS.GATHER_TABLE_STATS ('owner','tablename') syntax for both tables
--12.3. Clear out any old execution plans. Use the alter system flush shared_pool syntax
--12.4. Repeat steps 8 through 11
--12.5. Compare the IO cost without and with the index in place. Was the index a good idea?

CREATE INDEX ixMotherChild ON CHILD (MOTHERID);

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','CHILD');
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','MOTHER');

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT m.ID, m.NAME, c.NAME AS "Child name"
FROM MOTHER m INNER JOIN CHILD c ON (c.MOTHERID = m.ID)
WHERE m.ID = 22;

SELECT SUM(V$SESSTAT.VALUE) AS "Total value"
FROM V$STATNAME INNER JOIN V$SESSTAT
ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
WHERE V$SESSTAT.SID = SYS_CONTEXT('userenv', 'sid')
AND V$STATNAME.NAME = 'consistent gets';

/*
18600, 18607, DIFF - 7 (no index: 882)
*/

-- 13) Write SQL that joins the two views for your own session (check question 7 for hints),
-- showing which SQL statements that you are running/just ran.
SELECT SQL_TEXT FROM V$SESSION INNER JOIN V$SQL
ON (V$SESSION.SQL_ID = V$SQL.SQL_ID OR V$SQL.SQL_ID = V$SESSION.PREV_SQL_ID)
WHERE SID = SYS_CONTEXT('userenv','SID');

--- *** EXTRA
-- 14)
--14.1. Reset the entire thing. Go back and rerun the script from step 5.
--14.2. Explain the statement that retrieves a mother and her children. SAVE the execution
--plan that you get
--14.3. Recreate the index, update statistics and all that. In other words, repeat the code
--from steps 12.1 and 12.2
--14.4. Explain the statement again. Save the execution plan
--14.5. Compare the two execution plans, the one without the index and the one with the
--index. How does Oracle resolve the query in the two cases, and why is one much
--cheaper than the other

DROP INDEX ixMotherChild;

SET AUTOTRACE ON EXPLAIN
/
SELECT m.ID, m.NAME, c.NAME AS "Child name"
FROM MOTHER m INNER JOIN CHILD c ON (c.MOTHERID = m.ID)
WHERE m.ID = 22;
/
SET AUTOTRACE OFF
/

/*
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     5 |   240 |   240   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |            |     5 |   240 |   240   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MOTHER     |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | COPKMOTHER |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | CHILD      |     5 |   165 |   238   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
*/

CREATE INDEX ixMotherChild ON CHILD (MOTHERID);

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','CHILD');
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','MOTHER');

SET AUTOTRACE ON EXPLAIN
/
SELECT m.ID, m.NAME, c.NAME AS "Child name"
FROM MOTHER m INNER JOIN CHILD c ON (c.MOTHERID = m.ID)
WHERE m.ID = 22;
/
SET AUTOTRACE OFF
/

/*
------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |     5 |   240 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |               |     5 |   240 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | MOTHER        |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | COPKMOTHER    |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| CHILD         |     5 |   165 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | IXMOTHERCHILD |     5 |       |     1   (0)| 00:00:01 |
*/