Oracle Database Related Page

Oracle Database Learning:


------------------------------------------------------------------------------------------------------------
iggy-lesson-1-of-40

fyi: Nothing much to read. The first article that i had got on Optimization Of  Database thats the best.

Only the one the below article fine the "Logical Physical Design" [designing normalized database] .

Little to note and useful that the "Physical Database Design" need to know on Indexes , Partitions , Clusters ,Materialized Views.

Indexes help in restriction , projection , joins of all types and sorting.

Partitions and clusters can be used to co-late data with similar characteristics in order to limit the physical
I/O.

Materialized Views purpose is to improve the efficiency of  database operations as  joins and aggregations ahead of time.

In Depth Research for writing on Practical level.

Resource:

1.Optimization article is best to refer it tells exact practical steps, best article ever.

2. DBA related seems. Partitioning::Clustering

Partitioning:


Summary:



------------------------------------------------------------------------------------------------------------
1.Physical Database Design for Oracle Databases: Necessity & Definition

------------------------------------------------------------------------------------------------------------
Definitions of  Logical  &  Physical Database Design:
i.Database  design can be divided into "Logical Database Design" & a "Physical Database Design".
a.Logical database design is  the process of designing normalized database tables.
example:
Project ( Project number, Project description)
Part ( Part number, part name, part description, quantity-on-hand, quantity-on-order)

When a project makes use of  a part, the quantity of that part commited to the given project.
You will construct one table.
Table details in Yellow notebook.Page 2 , If  the quantity of the column changes multiple rows need to be
updated. Through the process of Data Modelling , database normalization a better solution. Three tables.
Page 3,4  have the create statements.


The above CREATE TABLE commands constructs the table in the Oracle Database. This concludes the
process of  "logical database" design. The database design does not complete here as the question of
performance is their.

The SQL queries , need indexes and other mechanism to retrieve data efficiently. They are transformed by
the query optimizer into sequence of database operations ( restriction , projection , inner join, outer join,
semi-join, anti-join, aggregation, and sorting). These operations are illustrated in the following examples:

Restriction ( rows removed ), Projection (column removed) operations:








------------------------------------------------------------------------------------------------------------
20 Oracle Lessons


------------------------------------------------------------------------------------------------------------
Oracle's MySql Blog


------------------------------------------------------------------------------------------------------------
1. Explain oracle database architecture.

Oracle Architecture in less than 10 minutes

1.What  happens when you start a Database?

Questions that pops like popcorn in mind? The song.. Popcorn popping on the apricot tree..

               Where the Oracle is situated? - Oracle Home

                Which Database? - ORACLE_SID
                 ( The same machine can have different vers. of  oracle running.)

2.what does the administrator does daily in his life from morning to night?

              MyServer> sqlplus       /nolog

              SQL*Plus:  Release 11...
              Copyright (c)  1982, 2007 ,  Oracle...

              SQL> connect    sys     as      sysdba
              Enter  password:
              Connected to an  idle instance.

Here the administrator is not  connected to the database but checking through a dummy connection  all
connection parameters.

The above to check if  the login or say user login credentials have right priveleges to start the
database engine.

3. Then

      SQL > startup

      The above program starts by reading a file

                  $ ORACLE_HOME / dbs

                  spfile$ { ORACLE_SID} . ora
  ( reads all parameters of  the Database which we come to know from ORACLE_SID)

The above  is a binary file.

INT.ORA file - text file which tells on the binary file.

4. Database Parameters:
    i. The above file having database parameters are in SGA area.
      This is where Oracle works.
      No. Of  utility processes also start here. Oracle related windows processes start.
      SGA - System Global Area.
   ii.when memory are located and processes started.
      sql> startup

      ORACLE  instance started.
       Total System Global Area            627....bytes( seems 627 mega bytes - near to a 1gb)
       Fixed Size                                    .........bytes
       Variable Size                                ........bytes
        Database Buffers                         ........bytes
        Redo Buffers                               ........bytes

5. Then Oracle will read an important file : Control Files , location is mentioned in parameter file.
     Here oracle finds names of  data files, temporary files, log files that together make up the database.

----------------------------------------------------------------------------------------------------------
summary:1

Well  as you know the system global area (sga) is  the hidden treasure area having all info.
Why not it be called that , even to start the engine the ORACLE_HOME/db/ORACLE_SID.ora file
which has  the database to start and also this has the path details to Control file ( tells data file, temporary
file, log file which makes the database) and the file is binary so understand it needs the INT.ora file.

Commands:

1.Myserver> sqlplus  /nolog

2.SQL> connect  sys as sysdba

3.SQL> startup

info.
The above program starts reading a file.

${ORACLE_HOME}\dbs

       spfile${ORACLE_SID}.ora

----------------------------------------------------------------------------------------------------------
     
Lesson 2:

1.
  Control file also checks :
          global constitency
          if something need to be restored
          if the database got properly shutdown
          or if  any unfinished transactions need to be rolled back
without control file your database is dead.

2.
SQL>  startup

ORACLE  instance started.

Total size SGa
.....
Database mounted.

The database mounted means all files are identified.

3. Then step is "Opening of  files for Read & Write operations.

shows. after Database mounted , Database opened.

SQL> exit

Disconnected from Oracle instance

4. if you want users from other machines to log on to Application Server , which is
what happens in daily life. you have to launch the listner command.

MyServer> lsnrctl start

LSNRCTL

Starting  /../bin/tnslsnr:

tnslsnr  is  the listner which waits for the incoming connections.

it shows details in configuration of  tnslsnr.ora file , the program runs in the background.

          Now everything in place.

5. Want the client program want to connect to database then it need to provide the below.

     Host Name: Name of  the host in which oracle runs.
     Port: tnslsnr is listening
     Service: the identifier of the database you want to connect to.

Several ways to provide the above info.

Directly specified.: Done in JDBC ( pure java driver)

if  client using oracle libraries then the client get the parameters from tnsnames.ora file.

The above two used.

Other methods are:

LDAP directory
Network Information Services ( as an substitutes to tnsnames.ora file).

6. But then Oracle needs you to sign on.

Username/Password

Authentication: LDAP identification
   
client -> TNSLSNR


Then TNSLSNR will run a process so that client becomes a dedicated server or

redirect to an already existing shared connection which is much

i.Server process is the client proxy in oracle side , it can access SGA , it will locate
private memory area called (PGA). In PGA it stores what is private to the process.

7. Closer look at "SGA" , it is shared between different processes.

Fixed System Area.
Variable Area
      Shared Pool - have compiled version of the queries,
        as well as Buffer Pools - that contain Data Dictionary info.
      Dedicated Memory Pools -

Data - Buffers contains data from tables ( indexed structure, work areas
Spliting locks
blocks - storage unit
when you modify a query you are writing on a block in memory
(Database Buffers)

Log
(Redo Buffer)
Finally data is writen to Log area for transaction constitency.

What one need to understand for understanding Oracle is

How Shared Pool works?
How Data Buffer works?
How Log  Buffer works?

8.Let see how things happen.

Say a client issues a query.
select empno, ename, job from emp where ename like 'S%'

i.query arrives as a text to the server process, then a Hash value is computated.

Hash value = 3046656596

Server will check if this Hash value already executed and chk Shared Pool.
If its found the analysis stops here and its called "Soft Passing".

If  the query not found its Hard Passing then it locks some shared resources.
sql syntax must be analyzed. The object need to be used must be identified.
Then the best execution plan must be determined.

Think queries as functions and pass parameters so the recursive calling to data dicitonary
is avoided.

Its loaded to the shared pool and is executed.

9.updating data
Before modifying data the data are copied to workarea,
when commit is issued the LGWR write to

----------------------------------------------------------------------------------------------------------
summary:2

The Control file as its name says , knows about database constitency. If  the database did shutdown properly or any transaction need to be rolledback.

The way oracle works that in the "Shared Pool"  the compiled queries are their  if any query is modified
its written to the "Database Buffer"  the memory block then for constitency to "Log Buffer" when commit
is issued written to the database. This how oracle works. Also when client issue a query it runs  as a
"Server process" in its PGA ( Private area) as a proxy to client.

SGA area is shared between processes.
Fixed Memory Area.
Variable Memory Area.
   Shared Pool which have compiled queries. Then the Buffer Pool where Data Dictionary is their , this one
if parameters are passed better than accessing recursively this area a optimization tip to send as parameter
and think it as a function.

I do not know on Dedicate Memory Pool.

Then the Data Buffer. Then Log Buffer.

When a query issued by client runs a Hash value is assigned.

1) All about Client :

Now the Client need to able access the application , is not it what in day today life things required.
Then obvious is  Client need to give info. about the where abouts  the Host it want to access and then
which ever one listens to it the Port and  then the Service which connects to the Database.

To listen to a client a Listner to be their.  The tnslsnr.ora file has all the parameter of  the listner named
tnslsnr.


command.
SQL > lsnrctl start

starting /bin/lsnrctl

2) The client connect through JDBC connection or if  using oracle library see the parameters in tnslsnr.ora file.

It can use LDAP for authentication , also Network Information Services (NIS , instead of tnslsnr.ora) file.








----------------------------------------------------------------------------------------------------------

PL/SQL - Oracle programming language page

Ma Gurudeva

PL/SQL Tutorial