Database - Oracle

							Databases - Huh?


          Prepared by : David Groves
                         March 2001
                                   Databases

   What is a database?
     –   Filesystem
     –   A table
     –   Application Server
     –   Operating System

   What does it give me?
     –   Consistent view of data
     –   Concurrency
     –   Transactions/Isolation
     –   Standard API
     –   Performance
     –   Backup/Recovery
     –   Headache
   Types of databases
     –   Filesystem
     –   Relational (SQL based)
     –   Object Relational
     –   Object
     –   Ad-Hoc

   Vendors
     –   Oracle (80+%)
     –   IBM DB2
     –   Ingress
     –   Sybase
     –   MySQL, Postgresql ...
                                          Oracle
   Oracle
     – Largest database vendor
     – Current Version 8i (8.1.7)
     – 9i due out mid-year
     – Runs on (most) UNIX versions,
       WinX, Linux
     – Popular version in Telstra 7.3.4
       (SOE - may have changed)
                                     Oracle Architecture
   A large shared memory segment
    Buffered Data
    Redundant Structures
    Data Dictionary (Metadata)
    Parsed SQL
   A number of server processes
   A number of per-client processes
   A number of files (configuration, Data)
                                                                                   At the UNIX level
   animal<1276> ps -aux | grep oracle

    oracle 573 0.1 25.9287408260840 ?     S Feb 01 32:00 ora_d000_DAP
    oracle 565 0.0 25.8286624259552 ?     S Feb 01 8:17 ora_ckpt_DAP
    oracle 559 0.0 25.9287080260200 ?     S Feb 01 0:05 ora_pmon_DAP
    oracle 561 0.0 25.8287096259600 ?     S Feb 01 0:10 ora_dbw0_DAP
    oracle 563 0.0 25.8286584259560 ?     S Feb 01 0:33 ora_lgwr_DAP
    oracle 567 0.0 25.9286464260944 ?     S Feb 01 0:18 ora_smon_DAP
    oracle 569 0.0 25.9286360260200 ?     S Feb 01 0:04 ora_reco_DAP
    oracle 571 0.0 26.3294232264344 ?     S Feb 01 27:48 ora_s000_DAP
    oracle 575 0.0 25.8286448259400 ?     S Feb 01 0:01 ora_arc0_DAP
    oracle 578 0.0 0.310752 2720     ?     S Feb 01 1:09 /opt/oracle/app/or
    oracle 1693 0.0 25.8286800259384 ?    S Mar 05 0:00 oracleDAP (DESCRIP

   animal<1281> ipcs -am

    IPC status from <running system> as of Wed Mar 7 13:37:37 EST 2001
    T      ID    KEY      MODE      OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
    Shared Memory:
    m       0 0x500005de --rw-r--r-- root root root root        1      4 239 239 8:22:19 8:22:19 8:22:19
    m     6916 0x6403b41f --rw-rw-rw- db2admin db2admin db2admin db2admin     0 16842752 19971 20105 14:44:49 14:46:15 13:31:55




   168 Mb shared memory - not large!
                                                            Relational model
 Entities (Objects) and Relationships

 Normalised data

 Maps directly into   database tables (tools to do this)
A   minimum code solution, but not always efficient
                                                                                     Database Objects
   Tables
    Store Data
    Rows and Columns

   Indexes
    Two uses -
    - Enforce business rules. Restrict rows via Unique indexes.
    - Performance. Fast lookup of data.

   Constraints
    Check Constraints - check gender in (‘M’,’F’)
    Foreign key constraints - check parent child relationships between tables are valid.

   Views
    A new way of looking at the data in tables.
    No new data.
    Can be used to simplify code, enforce security
    Facade pattern!

   Sequences
   Triggers
    When something is done at the table/view level. Nice for auditing.
    - on Insert
    - on Change
    - on Delete
    - others
                                                                                            SQL - PL/SQL
   Stored Code
    Procedures
    Functions
    Packages (aka classes)
    Can be written in PL/SQL, Java
    Advantages of co-locality with data


   SQL
    Structured Query Language - “Select * from dual”
    Can be very powerful
    Can be very complex
    There is a huge discrepancy between different code that produces the same result. “His code runs in 2 seconds and mine in 2
    days”!?
    You WILL need to tune your code.
    To write efficient SQL, you do need to know what your are doing, you need to know the structure and size of the data.
    Many designs work well in development and UAT, but fail miserably in production.


   PL/SQL
    Procedural (3GL) code that knows about the database
    - NULLS
    - Cursors
    - Named exceptions
    Embed SQL in PL/SQL
                                                              Examples - Stored Procedure
procedure bv_p_load_stats(p_load_f ile         in varchar2,
                     p_bills_loaded in number,
                     p_bills_rejected in number) is
  x_load_no number := 1;
 begin
  /*
   * Insert the load stats.
   */
  loop
    begin
      insert into bv_load_stats (
         LOAD_FILE,
         LOAD_NO,
         LOAD_DATE,
         BILLS_LOADED,
         BILLS_REJECTED
      ) values (
         p_load_f ile,
         x_load_no,
         sysdate,
         p_bills_loaded,
         p_bills_rejected
      );
      commit;
      return;
    exception
      when dup_val_on_index then
         x_load_no := x_load_no + 1;
    end;
  end loop;
 end;
                                          Examples - View
create or replace view bv_v_bill_adj (
        BILL_ID,
        ITEM_NO,
        ACCOUNT_ID,
        SERVICE_ID,
        DATE_ADJ,
        DESCRIPTION,
        COST,
        CURRENCY_ID,
        INVOICE_DATE)
as select
        a.BILL_ID,
        a.ITEM_NO,
        a.ACCOUNT_ID,
        a.SERVICE_ID,
        a.DATE_ADJ,
        b.DESCRIPTION,
        a.COST,
        a.CURRENCY_ID,
        a.INVOICE_DATE
f rom BV_ADJ_TYPE b, BV_BILL_ADJ a
where a.adj_type_id = b.adj_type_id (+)
/
                                                                          Examples - Access Point
SELECT uri, elem_name, elem_value FROM service_metadata s WHERE ( EXISTS (             SELECT 1       FROM shared_object_lookup sol, default_object_values dov        WHERE dov.elem_name
     = 'AGLS.Availability.postcode'      AND ( ( BIGINT(dov.elem_value) >= 0200 AND BIGINT(dov.elem_value) <= 0299 ) OR ( BIGINT(dov.elem_value) >= 2600 AND BIGINT(dov.elem_value) <=
     2619 ) OR ( BIGINT(dov.elem_value) >= 2900 AND BIGINT(dov.elem_value) <= 2920 ) ) AND dov.object_id = sol.object_id AND s.uri = sol.uri UNION SELECT 1 FROM specific_object_values
     sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( ( BIGINT(sov.elem_value) >= 0200 AND BIGINT(sov.elem_value) <= 0299 ) OR ( BIGINT(sov.elem_value) >= 2600 AND
     BIGINT(sov.elem_value) <= 2619 ) OR ( BIGINT(sov.elem_value) >= 2900 AND BIGINT(sov.elem_value) <= 2920 ) ) AND s.uri = sov.uri ) OR EXISTS (           SELECT 1         FROM
     shared_object_lookup sol, default_object_values dov        WHERE dov.elem_name = 'AGLS.Availability.postcode'        AND ( ( BIGINT(dov.elem_value) >= 0800 AND BIGINT(dov.elem_value)
     <= 0899 ) ) AND dov.object_id = sol.object_id AND s.uri = sol.uri UNION SELECT 1 FROM specific_object_values sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( (
     BIGINT(sov.elem_value) >= 0800 AND BIGINT(sov.elem_value) <= 0899 ) ) AND s.uri = sov.uri ) OR EXISTS (         SELECT 1         FROM shared_object_lookup sol, default_object_values
     dov         WHERE dov.elem_name = 'AGLS.Availability.postcode'        AND ( ( BIGINT(dov.elem_value) >= 1000 AND BIGINT(dov.elem_value) <= 2599 ) OR ( BIGINT(dov.elem_value) >= 2620
     AND BIGINT(dov.elem_value) <= 2899 ) OR ( BIGINT(dov.elem_value) >= 2921 AND BIGINT(dov.elem_value) <= 2999 ) ) AND dov.object_id = sol.object_id AND s.uri = sol.uri UNION SELECT
     1 FROM specific_object_values sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( ( BIGINT(sov.elem_value) >= 1000 AND BIGINT(sov.elem_value) <= 2599 ) OR (
     BIGINT(sov.elem_value) >= 2620 AND BIGINT(sov.elem_value) <= 2899 ) OR ( BIGINT(sov.elem_value) >= 2921 AND BIGINT(sov.elem_v alue) <= 2999 ) ) AND s.uri = sov.uri ) OR EXISTS (
     SELECT 1          FROM shared_object_lookup sol, default_object_values dov       WHERE dov.elem_name = 'AGLS.Availability.postcode'         AND ( ( BIGINT(dov.elem_value) >= 4000 AND
     BIGINT(dov.elem_value) <= 4999 ) OR ( BIGINT(dov.elem_value) >= 9000 AND BIGINT(dov.elem_value) <= 9799 ) ) AND dov.object_id = sol.object_id AND s.uri = sol.uri UNION SELECT 1
     FROM specific_object_values sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( ( BIGINT(sov.elem_value) >= 4000 AND BIGINT(sov.elem_value) <= 4999 ) OR (
     BIGINT(sov.elem_value) >= 9000 AND BIGINT(sov.elem_value) <= 9799 ) ) AND s.uri = sov.uri ) OR EXISTS (         SELECT 1         FROM shared_object_lookup sol, default_object_values
     dov         WHERE dov.elem_name = 'AGLS.Availability.postcode'        AND ( ( BIGINT(dov.elem_value) >= 5000 AND BIGINT(dov.elem_value) <= 5999 ) ) AND dov.object_id = sol.object_id
     AND s.uri = sol.uri UNION SELECT 1 FROM specific_object_values sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( ( BIGINT(sov.elem_value) >= 5000 AND
     BIGINT(sov.elem_value) <= 5999 ) ) AND s.uri = sov.uri ) OR EXISTS (       SELECT 1          FROM shared_object_lookup sol , default_object_values dov    WHERE dov.elem_name =
     'AGLS.Availability.postcode'      AND ( ( BIGINT(dov.elem_value) >= 7000 AND BIGINT(dov.elem_value) <= 7999 ) ) AND dov.object_id = sol.object_id AND s.uri = sol.uri UNION SELECT 1
     FROM specific_object_values sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( ( BIGINT(sov.elem_value) >= 7000 AND BIGINT(sov.elem_value) <= 7999 ) ) AND s.uri =
     sov.uri ) OR EXISTS (        SELECT 1        FROM shared_object_lookup sol, default_object_values dov       WHERE dov.elem_name = 'AGLS.Availability.postcode'          AND ( (
     BIGINT(dov.elem_value) >= 3000 AND BIGINT(dov.elem_value) <= 3999 ) OR ( BIGINT(dov.elem_value) >= 8000 AND BIGINT(dov.elem_value) <= 8999 ) ) AND dov.object_id = sol.object_id
     AND s.uri = sol.uri UNION SELECT 1 FROM specific_object_values sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( ( BIGINT(sov.elem_value) >= 3000 AND
     BIGINT(sov.elem_value) <= 3999 ) OR ( BIGINT(sov.elem_value) >= 8000 AND BIGINT(sov.elem_value) <= 8999 ) ) AND s.uri = sov.uri ) OR EXISTS (           SELECT 1         FROM
     shared_object_lookup sol, default_object_values dov        WHERE dov.elem_name = 'AGLS.Availability.postcode'        AND ( ( BIGINT(dov.elem_value) >= 6000 AND BIGINT(dov.elem_value)
     <= 6999 ) ) AND dov.object_id = sol.object_id AND s.uri = sol.uri UNION SELECT 1 FROM specific_object_values sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( (
     BIGINT(sov.elem_value) >= 6000 AND BIGINT(sov.elem_value) <= 6999 ) ) AND s.uri = sov.uri ) OR EXISTS (         SELECT 1         FROM shared_object_lookup sol, default_object_values
     dov         WHERE dov.elem_name = 'AGLS.Availability.postcode'        AND ( ( BIGINT(dov.elem_value) >= 0 AND BIGINT(dov.elem_value) <= 0 ) ) AND dov.object_id = sol.object_id AND s.uri
     = sol.uri UNION SELECT 1 FROM specific_object_values sov WHERE sov.elem_name = 'AGLS.Availability.postcode' AND ( ( BIGINT(sov.elem_value) >= 0 AND BIGINT(sov.elem_value) <= 0 )
     ) AND s.uri = sov.uri ) ) AND ( (EXISTS (SELECT 1 FROM service_metadata s2 WHERE ((s2.elem_name = 'DC.Title' AND LOWER(s2.elem_value) LIKE '%health%' AND s.uri = s2.uri) or
     (s2.elem_name = 'DC.Subject' AND LOWER(s2.elem_value) LIKE '%health%' AND s.uri = s2.uri) or (s2.elem_name = 'DC.Description' AND LOWER(s2.elem_value) LIKE '%health%' AND s.uri =
     s2.uri) )) ) AND EXISTS (SELECT 1 FROM status s2 WHERE s2.status != 'deleted' and s2.uri = s.uri ) )AND (s.elem_name = 'DC.Ti tle' or s.elem_name = 'DC.Subject' or s.elem_name =
     'DC.Description' or s.elem_name = 'DC.Identifier' or s.elem_name = 'DC.Type.category' or s.elem_name = 'AP.Indexes' ) ORDER BY s.uri
                            Golden Rule




KEEP IT SIMPLE IF AT ALL POSSIBLE!
                                                                                                      Optomizers
   Determine how the database engine runs a query
   Rule Based - Traditional, uses a weighted list of rules as to how the resolve a query.

   Cost Based - Uses statistics and weighting to determine how. Statistics must be gathered periodically.


   Tuning
   explain
   tkprof
   You MUST know the consequences of writing a query in different ways.
   NO substitute for serious testing (or development if possible) on production level volumes.
   This is not an OO environment, you cannot (generally) make more objects to improve performance. This strategy is almost
    guaranteed to fail.

    Query 1 - runs in 10 minutes.                           Query 2 - fails to run in 2 days and machine load average above 5.

    Select a.col1                                           Select a.col1
    from                                                    from
    table1 a, table2 b                                      table1 a
    where a.uk = b.uk                                       where exists (select 1 from table2 b
    and ...                                                               where a.uk = b.uk)
                                                            and ...
                                 Oracle Storage Hierarchy




   Blocks typically 2K, 4K, 8K, 16K in size.

   Table or index = an initial extent plus zero or more next extents.
                       Create table Statement with Storage
CREATE TABLE BV_LOAD_STATS
 (LOAD_FILE            VARCHAR2(30)          NOT NULL
 ,LOAD_NO              NUMBER                NOT NULL
 ,LOAD_DATE            DATE                  NOT NULL
 ,BILLS_LOADED         NUMBER                NOT NULL
 ,BILLS_REJECTED       NUMBER                NOT NULL
 )
PCTFREE 5 PCTUSED 40 TABLESPACE MYDATA
STORAGE (INITIAL 50K NEXT 50K MAXEXTENTS 99 PCTINCREASE 0)
CACHE
/


   Max table size = 50 + (99 * 50K) = 5000K

   Deployment sizings will almost always be different to
    development.
                                                 Create Scripts
   A good Idea to create Tables, Indexes, Constraints separately.
   Easier to manage

   Guarentees of creation


   Creation Order
   Tables

   Primary Keys/Indexes

   Load data

   Foreign Keys/Triggers

   Sequences/Packages/Procedures/Functions

   Views

   Synonyms
   Grants
                                                   What’s New
   Java running database memory area

   Stored procedure/Functions can be written in Java

   EJB/Servlets/JSP/CORBA/WebServer in database

   Extensible indexing - write your own

   XML capabilities

   Lots more.
                                 Where can I learn more?
   http://www.oracle.com

   http://technet.oracle.com/doc/server815.htm

   Oracle magazine - free!
    http://www.oracle.com/oramag/index.html

   Many books - O’Reilly are best

   Your friendly DBA