TKPROF and Explain Plan

1. http://www.dbspecialists.com/files/presentations/use_explain.html
 

Isolation Levels

1. On Transaction Isolation Levels By Tom Kyte
http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html

Partitioning

http://www.rampant-books.com/art_nanda_partitioning1.htm

Space Requirement for Oracle Data

The simple test below tells that 1 Character of data needs approx 1.25 Bytes of disk space in Oracle.

SQL>DEFINE n1 = 100000
SQL>DROP TABLE tsize;
Table dropped.
SQL>CREATE TABLE tsize ( v1 VARCHAR2(100), n1 NUMBER(15), d1 DATE, v2 VARCHAR2(10));
Table created.
SQL>
SQL>DECLARE
  2     lv_count NUMBER;
  3  BEGIN
  4     lv_count := &n1;
  5     FOR i IN 1..lv_count
  6     LOOP
  7        INSERT INTO tsize(v1,n1,d1,v2)
  8           VALUES ( 'This Is a String of 100 Characters Length.This Is a String of 100 Characters
 Length.This Is a String'
  9                    ,123456789012345
 10                    ,SYSDATE
 11                    ,'This  Test'
 12                  );
 13     END LOOP;
 14     COMMIT;
 15  END;
 16  /
old   4:    lv_count := &n1;
new   4:    lv_count := 100000;
PL/SQL procedure successfully completed.
SQL>
SQL>ANALYZE TABLE TSIZE COMPUTE STATISTICS;
Table analyzed.
SQL>
SQL>SELECT AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = 'TSIZE';
AVG_ROW_LEN
-----------
        133
SQL>
SQL>SELECT BYTES/1024/1024 size_in_mb FROM dba_segments WHERE segment_name = 'TSIZE';
SIZE_IN_MB
----------
        16
SQL>
SQL>SELECT bytes/(avg_row_len*&n1)  size_per_char_bytes
  2    FROM user_tables         ut
  3        ,dba_segments        ds
  4   WHERE ds.segment_name     = ut.table_name
  5     AND ut.table_name       = 'TSIZE' ;    
old   1: SELECT bytes/(avg_row_len*&n1)  size_per_char_bytes
new   1: SELECT bytes/(avg_row_len*100000)  size_per_char_bytes
SIZE_PER_CHAR_BYTES
-------------------
         1.26144481
        
/*******************************************************************************************/
SQL>DEFINE n1 = 100000
SQL>DROP TABLE tsize;
Table dropped.
SQL>CREATE TABLE tsize ( v1 VARCHAR2(100), v2 VARCHAR2(100), v3 VARCHAR2(100), v4 VARCHAR2(100));
Table created.
SQL>
SQL>BEGIN
  2     FOR i IN 1..100000
  3     LOOP
  4        INSERT INTO tsize(v1) VALUES('This Is a String of 100 Characters Length.This Is a String
of 100 Characters Length.This Is a String');
  5     END LOOP;
  6     COMMIT;
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL>SELECT BYTES/1024/1024 size_in_mb FROM dba_segments WHERE segment_name = 'TSIZE';
SIZE_IN_MB
----------
        12
SQL>
SQL>UPDATE tsize
  2     SET v2 = v1;
100000 rows updated.
SQL>
SQL>UPDATE tsize
  2     SET v3 = v1;
100000 rows updated.
SQL>
SQL>SELECT BYTES/1024/1024 size_in_mb FROM dba_segments WHERE segment_name = 'TSIZE';  
SIZE_IN_MB
----------
        36
SQL>
SQL>UPDATE tsize
  2    SET v4 = v1;
100000 rows updated.
SQL>
SQL>SELECT BYTES/1024/1024 size_in_mb FROM dba_segments WHERE segment_name = 'TSIZE';  
SIZE_IN_MB
----------
        47
SQL>
SQL>ANALYZE TABLE TSIZE COMPUTE STATISTICS;
Table analyzed.
SQL>
SQL>SELECT bytes/(avg_row_len*&n1)  size_per_char_bytes
  2     FROM user_tables         ut
  3         ,dba_segments        ds
  4    WHERE ds.segment_name     = ut.table_name
  5      AND ut.table_name       = 'TSIZE' ;      
old   1: SELECT bytes/(avg_row_len*&n1)  size_per_char_bytes
new   1: SELECT bytes/(avg_row_len*100000)  size_per_char_bytes
SIZE_PER_CHAR_BYTES
-------------------
         1.19910151
/*************************************************************************************/        

Introduction to Oracle Advanced Queuing (AQ)

1.   Introduction

http://www.oracle-developer.net/display.php?id=411

2.   Troubleshooting Advanced Queuing and Oracle Streams Propagation Issues (Doc ID 233099.1)

http://blogs.oracle.com/db/entry/oracle_support_master_note_for_troubleshooting_advanced_queuing_and_oracle_streams_propagation_issue

3.   Data Dictionary Views for Advance Queuing

 -- List of Queue Tables

SELECT * FROM dba_tables;

-- List of all the Queues

SELECT * FROM dba_queues;

-- List of Messaging Gateways

SELECT * FROM mgw_gateway

-- List of Queues in Queue Table

SELECT * FROM ppid_staging.aq$ppid_qtab_ack

-- List of Queues and its Subscribers/Consumers

select * from all_queue_subscribers

-- List of Foreign Queues

SELECT * FROM mgw_foreign_queues

--List of MQ Links Details (Host/Port/Channel)

SELECT * FROM mgw_mqseries_links

--List of Schedules, Types(Outbound/Inbound) Source,Target

SELECT * FROM mgw_schedules;

--List of Queue Schedules for AQ to AQ messaging

SELECT * FROM dba_queue_schedules

-- Registry

SELECT * FROM dba_registry

BEGIN

    dbms_mgwadm.startup;

END;

/

BEGIN

    dbms_mgwadm.shutdown;

END;

/

Hash, Nested Loop and Sort Merge Join

A good blog for Tuning Tips and other stuff.

http://oracle-online-help.blogspot.com/2007/03/nested-loops-hash-join-and-sort-merge.html

Oracle Optimizer

1. Introduction to the Optimizer

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm

2. How the Optimizer Transforms SQL Statements

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1005536

Oracle Normal Forms

Read this first (Good Explaination)

http://nishantrana.wordpress.com/2007/11/03/understanding-normalizations-in-databaserdbms/

and this too

http://www.phlonx.com/resources/nf3/

Below Nonmalization Forms are explained using a single example.

http://www.phpbuilder.com/columns/barry20000731.php3?page=4

Database Normalization And Design Techniques

Barry Wise

One of the most important factors in dynamic web page development is database definition. If your tables are not set up properly, it can cause you a lot of headaches down the road when you have to perform miraculous SQL calls in your PHP code in order to extract the data you want. By understanding data relationships and the normalization of data, you will be better prepared to begin developing your application in PHP.

Whether you work with mySQL or Oracle, you should know the methods of normalizing the table schema in your relational database system. They can help make your PHP code easier to understand, easier to expand upon, and in some cases, actually speed up your application.

Basically, the Rules of Normalization are enforced by eliminating redundancy and inconsistent dependency in your table designs. I will explain what that means by examining the five progressive steps to normalization you should be aware of in order to create a functional and efficient database. I'll also detail the types of relationships your data structure can utilize.

Let's say we want to create a table of user information, and we want to store each users' Name, Company, Company Address, and some personal bookmarks, or urls. You might start by defining a table structure like this:

Zero Form

users
name	company	company_address	url1	url2
Joe	ABC	1 Work Lane	abc.com	xyz.com
Jill	XYZ	1 Job Street	abc.com	xyz.com

We would say this table is in Zero Form because none of our rules of normalization have been applied yet. Notice the url1 and url2 fields -- what do we do when our application needs to ask for a third url? Do you want to keep adding columns to your table and hard-coding that form input field into your PHP code? Obviously not, you would want to create a functional system that could grow with new development requirements. Let's look at the rules for the First Normal Form, and then apply them to this table.

First Normal Form

1. Eliminate repeating groups in individual tables.
2. Create a separate table for each set of related data.
3. Identify each set of related data with a primary key.

Notice how we're breaking that first rule by repeating the url1 and url2 fields? And what about Rule Three, primary keys? Rule Three basically means we want to put some form of unique, auto-incrementing integer value into every one of our records. Otherwise, what would happen if we had two users named Joe and we wanted to tell them apart? When we apply the rules of the First Normal Form we come up with the following table:

users
userId	name	company	company_address	url
1	Joe	ABC	1 Work Lane	abc.com
1	Joe	ABC	1 Work Lane	xyz.com
2	Jill	XYZ	1 Job Street	abc.com
2	Jill	XYZ	1 Job Street	xyz.com

Now our table is said to be in the First Normal Form. We've solved the problem of url field limitation, but look at the headache we've now caused ourselves. Every time we input a new record into the users table, we've got to duplicate all that company and user name data. Not only will our database grow much larger than we'd ever want it to, but we could easily begin corrupting our data by misspelling some of that redundant information. Let's apply the rules of Second Normal Form:

Second Normal Form

1. Create separate tables for sets of values that apply to multiple records.
2. Relate these tables with a foreign key.

We break the url values into a separate table so we can add more in the future without having to duplicate data. We'll also want to use our primary key value to relate these fields:

users
userId	name	company	company_address
1	Joe	ABC	1 Work Lane
2	Jill	XYZ	1 Job Street

urls
urlId	relUserId	url
1	1	abc.com
2	1	xyz.com
3	2	abc.com
4	2	xyz.com

Ok, we've created separate tables and the primary key in the users table, userId, is now related to the foreign key in the urls table, relUserId. We're in much better shape. But what happens when we want to add another employee of company ABC? Or 200 employees? Now we've got company names and addresses duplicating themselves all over the place, a situation just rife for introducing errors into our data. So we'll want to look at applying the Third Normal Form:

Third Normal Form

1. Eliminate fields that do not depend on the key.

Our Company Name and Address have nothing to do with the User Id, so they should have their own Company Id:

users
userId	name	relCompId
1	Joe	1
2	Jill	2

companies
compId	company	company_address
1	ABC	1 Work Lane
2	XYZ	1 Job Street

urls
urlId	relUserId	url
1	1	abc.com
2	1	xyz.com
3	2	abc.com
4	2	xyz.com

Now we've got the primary key compId in the companies table related to the foreign key in the users table called relCompId, and we can add 200 users while still only inserting the name "ABC" once. Our users and urls tables can grow as large as they want without unnecessary duplication or corruption of data. Most developers will say the Third Normal Form is far enough, and our data schema could easily handle the load of an entire enterprise, and in most cases they would be correct.

But look at our url fields - do you notice the duplication of data? This is prefectly acceptable if we are not pre-defining these fields. If the HTML input page which our users are filling out to input this data allows a free-form text input there's nothing we can do about this, and it's just a coincedence that Joe and Jill both input the same bookmarks. But what if it's a drop-down menu which we know only allows those two urls, or maybe 20 or even more. We can take our database schema to the next level, the Fourth Form, one which many developers overlook because it depends on a very specific type of relationship, the many-to-many relationship, which we have not yet encountered in our application.

Data Relationships

Before we define the Fourth Normal Form, let's look at the three basic data relationships: one-to-one, one-to-many, and many-to-many. Look at the users table in the First Normal Form example above. For a moment let's imagine we put the url fields in a separate table, and every time we input one record into the users table we would input one row into the urls table. We would then have a one-to-one relationship: each row in the users table would have exactly one corresponding row in the urls table. For the purposes of our application this would neither be useful nor normalized.

Now look at the tables in the Second Normal Form example. Our tables allow one user to have many urls associated with his user record. This is a one-to-many relationship, the most common type, and until we reached the dilemma presented in the Third Normal Form, the only kind we needed.

The many-to-many relationship, however, is slightly more complex. Notice in our Third Normal Form example we have one user related to many urls. As mentioned, we want to change that structure to allow many users to be related to many urls, and thus we want a many-to-many relationship. Let's take a look at what that would do to our table structure before we discuss it:

users
userId	name	relCompId
1	Joe	1
2	Jill	2

companies
compId	company	company_address
1	ABC	1 Work Lane
2	XYZ	1 Job Street

urls
urlId	url
1	abc.com
2	xyz.com

url_relations
relationId	relatedUrlId	relatedUserId
1	1	1
2	1	2
3	2	1
4	2	2

In order to decrease the duplication of data (and in the process bring ourselves to the Fourth Form of Normalization), we've created a table full of nothing but primary and foriegn keysin url_relations. We've been able to remove the duplicate entries in the urls table by creating the url_relations table. We can now accurately express the relationship that both Joe and Jill are related to each one of , and both of, the urls. So let's see exactly what the Fourth Form Of Normalization entails:

Fourth Normal Form

1. In a many-to-many relationship, independent entities can not be stored in the same table.

Since it only applies to the many-to-many relationship, most developers can rightfully ignore this rule. But it does come in handy in certain situations, such as this one. We've successfully streamlined our urls table to remove duplicate entries and moved the relationships into their own table.

Just to give you a practical example, now we can select all of Joe's urls by performing the following SQL call:

SELECT name, url FROM users, urls, url_relations WHERE url_relations.relatedUserId = 1 AND users.userId = 1 AND urls.urlId = url_relations.relatedUrlId

And if we wanted to loop through everybody's User and Url information, we'd do something like this:

SELECT name, url FROM users, urls, url_relations WHERE users.userId = url_relations.relatedUserId AND urls.urlId = url_relations.relatedUrlId

Fifth Normal Form

There is one more form of normalization which is sometimes applied, but it is indeed very esoteric and is in most cases probably not required to get the most functionality out of your data structure or application. It's tenet suggests:

1. The original table must be reconstructed from the tables into which it has been broken down.

The benefit of applying this rule ensures you have not created any extraneous columns in your tables, and that all of the table structures you have created are only as large as they need to be. It's good practice to apply this rule, but unless you're dealing with a very large data schema you probably won't need it.

I hope you have found this article useful, and are able to begin applying these rules of normalization to all of your database projects. And in case you're wondering where all of this came from, the first three rules of normalization were outlined by Dr. E.F. Codd in his 1972 paper, "Further Normalization of the Data Base Relational Model". Other rules have since been theorized by later Set Theory and Relational Algebra mathematicians.

Oracle Collections URLs

1.  http://sheikyerbouti.developpez.com/collections/collections.htm

2. http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm

Oracle Architecture

1. http://ugweb.cs.ualberta.ca/~c391/manual/chapt2.html

Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint

1. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48500

Bulk Collect with Returing Clause

1. http://www.dba-oracle.com/plsql/t_plsql_dml.htm

2. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876

Useful Oracle Websites

1.    http://www.orafaq.org/               - The Underground Oracle Frequently-Asked Questions List (especially the Oracle Database Administration pages)

2.     http://www.oracle.com/               - Oracle Corporation's Web Site.

3.     http://www.orafans.com/             - ORACLE User Forum and Fans Club (especially the ORACLE Technical Papers pages).

4.     http://exforsys.com           - Very good site for Various Tutorials on All technologies and Career guidance

5.    http://www.oraclefans.com/         - Info from various sites on their pages, such as machine-based FAQ's from faqs.org in their Links page.

6.    http://www.fors.com/orasupp      - More articles by Oracle Worldwide Customer Support. Includes some of the older Oracle Technical Bulletins.

7.    http://www.dbresources.com/       - Scanning the web for Oracle articles or news?  Check out this compilation.

8.    http://my.ittoolbox.com/       - Oracle Forums and Articles

9.    http://www.oracletuning.com/      - Scripts and articles for DBA's.

10. http://think-forward.com/     - Adelante, Ltd, Computer Consultants, with scripts and tips, including a UNIX to VMS Translation Table.

11. http://searchoracle.com/     - Tips, scripts, news, white papers, ask the experts, discussion forums - lots of neat stuff!

12. http://asktom.oracle.com/    - For any oracle question very useful site

13. http://www.psoug.org/links.html   - Various Oracle Links

14. http://the-big-o.port5.com/   - Oracle Tips, Tricks, Hints, and How-To's, including Oracle Forms articles and general database articles.

15. http://education.oracle.com/ - Oracle Corporation's Education Site.

16. http://www.psoug.org/library.html          - Oracle SQL and PLSQL Syntax

17. http://www.oracle-books.com/oracle      - Rhubarb's Oracle Site (from which came the Oracle Technical Bulletins).

18. http://bijoos.com/oracle/index.htm           - Biju's Oracle Page - Scripts, Utilities, Source code generators, etc.

19. http://www.szofi.hu/index_link.html#Oracle       - Link Exchange - tons of links, Oracle and other programming languages and operating systems as well.

20. http://www.akadia.com/html/ora_scripts.html    - Various Oracle Scripts

21. http://www.vb-bookmark.com/vbOracle.html     - Oracle Bookmark with Oracle and Perl sites containing articles, tutorials, tips, tricks, guides, and samples.

22. http://techonthenet.com/oracle/index.php  - Oracle, Unix, Linux etc.

23. http://www.dbspecialists.com/presentations.html - Various useful presentations

24. http://oracle.com/pls/db92/db92.homepage         - Oracle 9i Documentation

25. http://www.tusc.com/oracle/download/categories.html  - Years of PowerPoint presentations, including lots of Oracle 9i stuff.

26. http://www.geekinterview.com/question_details/12376  -

27. http://www.orafaq.com/scripts/index.htm#GENPLSQL   - For various useful SQL and PLSQL Scripts

28. http://www.osborne.com/downloads/downloads.shtml#J-L       - For All the Codes of Java as well as other Languages

29. http://www.akhilamatrix.com/website_designing_basics.htm    - Website Designing

30. http://home.clara.net/dwotton/dba/oracle_extn_rtn.htm - Calling External routines from PL/SQL.

31. http://www.fortunecity.com/skyscraper/oracle/699/orahtml/index.html         - A bunch of articles (tuning, etc.) for Oracle DBA's (another Rhubarb collection).

32. http://otn.oracle.com/pub/articles/calish_file_commands.html    - Oracle Articles

33. http://www.akadia.com/services/ora_interpreting_explain_plan.html   - Nice articles about Performance Tuning

34. http://forums.oracle.com/forums/category.jspa?categoryID=18 - Oracle Forum

35. http://oracle-base.com/articles/Misc/EfficientSQLStatements.php                   -

36. http://4shared.com/dir/1493877/e168fc62/Oracle_E-Books.html                    -       

37. http://www.oracle.com/technology/oramag/oracle/06-nov/o66plsql.html                 - On Object Types in Collections)

38. http://www.intelligentedu.com/blogs/post/Best_New_Training_Sites/456/Fast-          - Very good link for information about PLSQL and PLSQL Tuning

39. http://searchoracle.techtarget.com/general/0,295582,sid41_gci1087688,00.html         - RDBMS

40. http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96533/toc.htm         - Oracle Performance Tuning Guide

41. http://www.w3schools.com/xml/default.asp       - XML Tutorials        

42. http://sql-plsql.blogspot.com/2007/03/sql-introduction.html        - Basics of SQL and PLSQL

43. http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065826 - Various Clause of a SELECT statement.

44. http://www.enterprisedb.com/documentation/index.html           - SQL and PLSQL Enterprise Book

45. http://www.oracle-developer.net/display.php?id=320                - Flashback version query in oracle 10g

46. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#sthref1551          (Native Dynamic SQL)

A.  SQL Plus Commands

1.    http://igor.gold.ac.uk/oracle/9i/server.920/a90842/ch13.htm#1011230        - SQL Commands