Inside Oracle Database

Explain oracle database architecture?

Refer below figure to understand the oracle database architecture. For detail of each component, refer my last post.

What is the difference between Oracle 9i and Oracle 10g ?

Each release of Oracle has many differences, and Oracle 10g is a major re-write of the Oracle kernel from Oracle 9i. While there are several hundred new features and other differences between 9i and 10g, here are the major differences between Oracle9i and Oracle10g:

• Major changes to SQL optimizer internals

• Oracle Grid computing

• AWR and ASH tables incorporated into Oracle Performance Pack and Diagnostic Pack options

• Automated Session History (ASH) materializes the Oracle Wait Interface over time

• Data Pump replaces imp utility with impdp

• Automatic Database Diagnostic Monitor (ADDM)

• SQLTuning Advisor

• SQLAccess Advisor

• Rolling database upgrades (using Oracle10g RAC)

• dbms_scheduler package replaces dbms_job for scheduling

What is data dictionary ?

Metadata is data about data, or data that defines other data. The Oracle data dictionary is metadata about the database.The data dictionary is full of 'Metadata', information about what is going-on inside your database. The data dictionary is presented to us in the form of a number of views. The dictionary views come in two primary forms: DBA, ALL or USER views

There are hundreds of views in the data dictionary. Few views that store data about Oracle tables are : dba_all_tables, dba_indexes, dba_tables, dba_tab_cols, dba_tab_columns.

What are the properties of a Database?

The properties of a Database are also known as ACID Properties. These are Atomicity, Consistency, Isolation and Durability.

• Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.
• The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules.
• Isolation requires that multiple transactions occurring at the same time not impact each other’s execution.
• Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors

What is normalization? what is the advantage of normalization?

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.

mostly used nromal forms are:

First normal form (1NF) : an attribute (column) of a table cannot hold multiple values. It should hold only atomic values.

Second normal form (2NF) : Table is in 1NF and no non-prime attribute is dependent on the proper subset of any candidate key of table (An attribute that is not part of any candidate key is known as non-prime attribute).

Third Normal form (3NF) : Table design is in 3NF if it is in 2NF and there is no transitive functional dependency of non-prime attribute on any super key.

Some of the benefits of normalization are: Data integrity (because there is no redundant, neglected data). Optimized queries (because normalized tables produce rapid, efficient joins). Faster index creation and sorting (because the tables have fewer columns).

What are database links used for ?

Database links are created in order to form communication between various databases. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

CREATE [PUBLIC] DATABASE LINK <Link_Name> CONNECT TO &src_user identified by &src_password using '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&src_host)(PORT=&src_port)))(CONNECT_DATA=(sid=&src_database)) )' ;

What is Context Area in PL/SQL ?

Oracle process an SQL statement in a separate memory zone called as Context Area which is part of the program global area (PGA) allocated on the server. It contains information regarding SQL Query, number of rows accessed by it, information retrieved from database tables/records and many more.

What is the process of PL/SQL compilation ?

When a user sends a PL/SQL block from a client to the database server, the PL/SQL compiler check syntax, check semantics and security, resolve names and then generate (and optimize) bytecode (or MCode). Once the SQL compiler is done, it hands the execution plan over to the SQL engine to fulfil the request. Meanwhile the PVM (PL/SQL vitual machine) interprets the PL/SQL bytecode and, once the SQL engine has come up with the results, returns the status (success/failure) to the client session.

What is Native Compilation ?

By default, PL/SQL code is compiled and stored in the form of byte code ready for execution. During the execution process, this byte code is interpreted, a process which requires time and resources.

The process of native compilation converts PL/SQL stored procedures to native code shared libraries which are linked into the kernel resulting in performance increases for the procedural code. The extent of the performance increase depends on the content of the PL/SQL. The compilation process does not affect the speed of database calls, only the procedural logic around them such as loops and calculations.