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 • 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!

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 • - on Change • - on Delete • - others

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

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, a.INVOICE_DATE from BV_ADJ_TYPE b, BV_BILL_ADJ a where a.adj_type_id = b.adj_type_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) >= 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_value) <= 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.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. 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.

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