Databases - Huh?

1
Databases - Huh?

• Prepared by David Groves
• March 2001

2
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

3

• Types of databases
• Filesystem
• Relational (SQL based)
• Object Relational
• Object
• Ad-Hoc

• Vendors
• Oracle (80)
• IBM DB2
• Ingress
• Sybase
• MySQL, Postgresql ...

4
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)

5
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)

6
At the UNIX level

• animallt1276gt ps -aux grep oracle
• oracle 573 0.1 25.9287408260840 ? S
  Feb 01 3200 ora_d000_DAP
• oracle 565 0.0 25.8286624259552 ? S
  Feb 01 817 ora_ckpt_DAP
• oracle 559 0.0 25.9287080260200 ? S
  Feb 01 005 ora_pmon_DAP
• oracle 561 0.0 25.8287096259600 ? S
  Feb 01 010 ora_dbw0_DAP
• oracle 563 0.0 25.8286584259560 ? S
  Feb 01 033 ora_lgwr_DAP
• oracle 567 0.0 25.9286464260944 ? S
  Feb 01 018 ora_smon_DAP
• oracle 569 0.0 25.9286360260200 ? S
  Feb 01 004 ora_reco_DAP
• oracle 571 0.0 26.3294232264344 ? S
  Feb 01 2748 ora_s000_DAP
• oracle 575 0.0 25.8286448259400 ? S
  Feb 01 001 ora_arc0_DAP
• oracle 578 0.0 0.310752 2720 ?
  S Feb 01 109 /opt/oracle/app/or
• oracle 1693 0.0 25.8286800259384 ? S
  Mar 05 000 oracleDAP (DESCRIP
• animallt1281gt ipcs -am
• IPC status from ltrunning systemgt as of Wed Mar 7
  133737 EST 2001
• T ID KEY MODE OWNER
  GROUP CREATOR CGROUP NATTCH SEGSZ CPID
  LPID ATIME DTIME CTIME
• Shared Memory

7
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

8
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

9
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

10
Examples - Stored Procedure

• procedure bv_p_load_stats(p_load_file 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_file,
• x_load_no,

11
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,

12
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) gt 0200 AND
  BIGINT(dov.elem_value) lt 0299 ) OR (
  BIGINT(dov.elem_value) gt 2600 AND
  BIGINT(dov.elem_value) lt 2619 ) OR (
  BIGINT(dov.elem_value) gt 2900 AND
  BIGINT(dov.elem_value) lt 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) gt 0200 AND
  BIGINT(sov.elem_value) lt 0299 ) OR (
  BIGINT(sov.elem_value) gt 2600 AND
  BIGINT(sov.elem_value) lt 2619 ) OR (
  BIGINT(sov.elem_value) gt 2900 AND
  BIGINT(sov.elem_value) lt 2920 ) ) AND s.uri
  sov.uri ) OR EXISTS ( SELECT 1
  FROM shared_object_lookup sol, default_object_valu
  es dov WHERE dov.elem_name
  'AGLS.Availability.postcode' AND ( (
  BIGINT(dov.elem_value) gt 0800 AND
  BIGINT(dov.elem_value) lt 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) gt 0800 AND
  BIGINT(sov.elem_value) lt 0899 ) ) AND s.uri
  sov.uri ) OR EXISTS ( SELECT 1
  FROM shared_object_lookup sol, default_object_valu
  es dov WHERE dov.elem_name
  'AGLS.Availability.postcode' AND ( (
  BIGINT(dov.elem_value) gt 1000 AND
  BIGINT(dov.elem_value) lt 2599 ) OR (
  BIGINT(dov.elem_value) gt 2620 AND
  BIGINT(dov.elem_value) lt 2899 ) OR (
  BIGINT(dov.elem_value) gt 2921 AND
  BIGINT(dov.elem_value) lt 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) gt 1000 AND
  BIGINT(sov.elem_value) lt 2599 ) OR (
  BIGINT(sov.elem_value) gt 2620 AND
  BIGINT(sov.elem_value) lt 2899 ) OR (
  BIGINT(sov.elem_value) gt 2921 AND
  BIGINT(sov.elem_value) lt 2999 ) ) AND s.uri
  sov.uri ) OR EXISTS ( SELECT 1
  FROM shared_object_lookup sol, default_object_valu
  es dov WHERE dov.elem_name
  'AGLS.Availability.postcode' AND ( (
  BIGINT(dov.elem_value) gt 4000 AND
  BIGINT(dov.elem_value) lt 4999 ) OR (
  BIGINT(dov.elem_value) gt 9000 AND
  BIGINT(dov.elem_value) lt 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) gt 4000 AND
  BIGINT(sov.elem_value) lt 4999 ) OR (
  BIGINT(sov.elem_value) gt 9000 AND
  BIGINT(sov.elem_value) lt 9799 ) ) AND s.uri
  sov.uri ) OR EXISTS ( SELECT 1
  FROM shared_object_lookup sol, default_object_valu
  es dov WHERE dov.elem_name
  'AGLS.Availability.postcode' AND ( (
  BIGINT(dov.elem_value) gt 5000 AND
  BIGINT(dov.elem_value) lt 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) gt 5000 AND
  BIGINT(sov.elem_value) lt 5999 ) ) AND s.uri
  sov.uri ) OR EXISTS ( SELECT 1
  FROM shared_object_lookup sol, default_object_valu
  es dov WHERE dov.elem_name
  'AGLS.Availability.postcode' AND ( (
  BIGINT(dov.elem_value) gt 7000 AND
  BIGINT(dov.elem_value) lt 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) gt 7000 AND
  BIGINT(sov.elem_value) lt 7999 ) ) AND s.uri
  sov.uri ) OR EXISTS ( SELECT 1
  FROM shared_object_lookup sol, default_object_valu
  es dov WHERE dov.elem_name
  'AGLS.Availability.postcode' AND ( (
  BIGINT(dov.elem_value) gt 3000 AND
  BIGINT(dov.elem_value) lt 3999 ) OR (
  BIGINT(dov.elem_value) gt 8000 AND
  BIGINT(dov.elem_value) lt 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) gt 3000 AND
  BIGINT(sov.elem_value) lt 3999 ) OR (
  BIGINT(sov.elem_value) gt 8000 AND
  BIGINT(sov.elem_value) lt 8999 ) ) AND s.uri
  sov.uri ) OR EXISTS ( SELECT 1
  FROM shared_object_lookup sol, default_object_valu
  es dov WHERE dov.elem_name
  'AGLS.Availability.postcode' AND ( (
  BIGINT(dov.elem_value) gt 6000 AND
  BIGINT(dov.elem_value) lt 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) gt 6000 AND
  BIGINT(sov.elem_value) lt 6999 ) ) AND s.uri
  sov.uri ) OR EXISTS ( SELECT 1
  FROM shared_object_lookup sol, default_object_valu
  es dov WHERE dov.elem_name
  'AGLS.Availability.postcode' AND ( (
  BIGINT(dov.elem_value) gt 0 AND
  BIGINT(dov.elem_value) lt 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) gt 0 AND
  BIGINT(sov.elem_value) lt 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.Title' 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

13
Golden Rule

• KEEP IT SIMPLE IF AT ALL POSSIBLE!

14
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. Select
a.col1 from table1 a, table2 b where a.uk
b.uk and ...
Query 2 - fails to run in 2 days and machine load
average above 5. Select a.col1 from table1
a where exists (select 1 from table2 b
where a.uk b.uk) and ...
15
Oracle Storage Hierarchy

• Blocks typically 2K, 4K, 8K, 16K in size.
• Table or index an initial extent plus zero or
  more next extents.

16
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.

17
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

18
Whats 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.

19
Where can I learn more?

• http//www.oracle.com
• http//technet.oracle.com/doc/server815.htm
• Oracle magazine - free! http//www.oracle.com/oram
  ag/index.html
• Many books - OReilly are best
• Your friendly DBA