Database - Oracle - chapelhill.homeip.net

musicincurableΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 9 μήνες)

117 εμφανίσεις

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 C
TIM
E


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


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

from 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_looku
p
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 ( BIGIN
T(d
ov.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) <= 0
299

) 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.u
ri
) 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.el
em_
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
.el
em_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.objec
t_i
d = 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 A
ND
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
alu
e) <= 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.
pos
tcode' 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

BI
GINT(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
.el
em_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 ( (

BI
GINT(sov.elem_value) >= 5000 AND
BIGINT(sov.elem_value) <= 5999 ) ) AND s.uri = sov.uri ) OR EXISTS ( SELECT 1 FROM shared_object_lookup sol
, d
efault_object_values dov WHERE dov.elem_name =
'AGLS.Availability.postcode' AND ( ( BIGINT(dov.elem_value) >= 7000 AND BIGINT(dov.elem_value) <= 7999 ) ) AND dov.ob
jec
t_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

BI
GINT(sov.elem_value) <= 7999 ) ) AND s.uri =
sov.uri ) OR EXISTS ( SELECT 1 FROM shared_object_lookup sol, default_object_values dov WHERE dov.
ele
m_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_v
alu
e) <= 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 ( (

BI
GINT(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.u
ri
) 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.el
em_
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.el
em_
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(so
v.e
lem_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.el
em_
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'

AN
D 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 B
Y 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.


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

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