Data types in Oracle

https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1821

Most of time we have ASCII as character set . Where 7 bits are used to represent a character.

Unicode(Character set ) UTF 8(Encoding) -- Is the one that can be used for storing chinese, English.. And is a multi byte dataset. Specifying the length in character is better in case of multibyte character sets.

Unicode(Character set) -- Allows you to store data in any language in oracle.  There are two data sets 1) the default one of the data base and 2) The column can have unicode character set. This is made possible  by Nchar and Nvarchar.So suppose you are trying to save data in oracle using Hindi, French , chinese  use  Nchar,Nvarchar,Nclob data types

A Short note on Character set and Encoding

A character set is a list of characters with unique numbers (these numbers are sometimes referred to as "code points"). For example, in the Unicode character set, the number for A is 41. The other character set is ASCII

An Encoding on the other hand, is an algorithm that translates a list of numbers to binary so it can be stored on disk. For example UTF-8 would translate the number sequence 1, 2, 3, 4 like this:

Char --- Data type can have the length specified in either in Bytes or in Characters . 

Char ( 2 char) or Char ( 2 bytes) . But for Ascii it does not make much difference as 7 bits are used to represent a character . The default whether a byte or char is taken depends on the database NLS_Length_Semantics

Char is a fixed length data types and rest is padded by blank

Varchar does not pad blanks and only uses space depending on the number of characters.  For all purposes use Varchar2 .. Varchar is like the old one

Long --- Its there for backward compatiblity and for all development use LOB ( that is CLOB in case of characters)

BLOB and CLOB and NCLOB -- Clob is used for Character Large object , as the name suggest its allows you to store  ASCI character data (or character set of your DB) data . The NCLOB allows you to save unicode data irrespective of the character set of the database. The BLOB is used to store binary data. When we say that the data is Binary it means the data will not be interpreted by oracle to resemble any character from a character set.

Numeric Data types

when you specify a data type as number without scale and precision then the precision is taken as zero that is , It cant store decimal points .

Important point is you can store numbers of any length the only restriction is on the precision and maximum precision is 38

RAW and Long RAW -- They are used for Binary data that is not to be interpreted . Nowadays Long RAW is only there for backward compatibility and its better to use BLOB

Date and timestamp

Point to remember are Both date and Timestamp both contain time part.  The precision of time noted by Date is upto seconds and that noted down by timestamp is upto milli seconds. If you are  doing any comparision using dates in the where  clause be sure to do them in the exact format specified by your NLS setting otherwise you need to use to_date

Things not there in Oracle …..FLOAT, DECIMAL,BYTE, SMALL INT, INT  All these things are not there in Oracle. The NUMBER is the all that is there . It covers everything in oracle. So even if you write int while creating a table it is taken as number in the create statement. Oracle allows you to specify int while creating a table

Very good article on the topic

http://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php

Join Mechanism in Oracle

Hi All,

Consider tables as files . Fact table is Big file worth 50GB and Dimension is small file worth 1GB. Now what are the mechanism that can be used to find the data that is matching Big and small file. Whenever CPU has to read data the file has to be in RAM. For now do not consider oracle specifics like PGA, SGA, Buffer cache. Just simple RAM concept

1)  Read small file worth 1 GB first in RAM and then search 50 GB file for matching row. Now 1GB file has 10 rows and only full scan is allowed so you end up reading full 50 GB table to find one row (consider 10 rows you want are at end) . So you cannot read full 50 GB in ram in one go so you read 1GB at time if row not found you clear ram and take next 1gb. So for 10 rows you did 500gb read that is BAD idea.
2) You read 50 GB table 1gb at a time compare with 1gb smaller table . 50gb fact has 1000 rows so you end up doing 1TB i/o that is BAD. This is your Nested loop. This works if you have a index on the 1gb table which allows you to pin point to correct row without doing a full table scan.and even there is a index on 50GB table and you are not selecting the entire 50GB
3) So now look at some of the efficient algorithm for finding rows
4) Sort Merge joins --- you sort both the tables. Its like a nested loop. You take on sorted output probe the second sorted ouput till you find a row that does not match. So record 1 from source1 can only find one match in source 2 no need to look further as the table is sorted and you wont find any more matches


HASH JOINS

To illustrate a hash table, assume that the database hashes hr.departments in a join of departments and employees. The join key column is department_id. The first 5 rows of departments are as follows:

SQL> select * from departments where rownum < 6;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500

The database applies the hash function to each department_id in the table, generating a hash value for each. For this illustration, the hash table has 5 slots (it could have more or less). Because n is 5, the possible hash values range from 1 to 5. The hash functions might generate the following values for the department IDs:

f(10) = 4
f(20) = 1
f(30) = 4
f(40) = 2
f(50) = 5

Note that the hash function happens to generate the same hash value of 4 for departments 10 and 30. This is known as a hash collision. In this case, the database puts the records for departments 10 and 30 in the same slot, using a linked list. Conceptually, the hash table looks as follows:

1    20,Marketing,201,1800
2    40,Human Resources,203,2400
3
4    10,Administration,200,1700 -> 30,Purchasing,114,1700
5    50,Shipping,121,1500

Hash Join: Basic Steps

A hash join of two row sources uses the following basic steps:

The database performs a full scan of the smaller data set, and then applies a hash function to the join key in each row to build a hash table in the PGA.

The database probes the second data set, using whichever access mechanism has the lowest cost.

Typically, the database performs a full scan of both the smaller and larger data set. The algorithm in pseudocode might look as follows:

For each row retrieved from the larger data set, the database does the following:

Applies the same hash function to the join column or columns to calculate the number of the relevant slot in the hash table.

For example, to probe the hash table for department ID 30, the database applies the hash function to 30, which generates the hash value 4.

Probes the hash table to determine whether rows exists in the slot.

If no rows exist, then the database processes the next row in the larger data set. If rows exist, then the database proceeds to the next step.

Checks the join column or columns for a match. If a match occurs, then the database either reports the rows or passes them to the next step in the plan, and then processes the next row in the larger data set.

If multiple rows exist in the hash table slot, the database walks through the linked list of rows, checking each one. For example, if department 30 hashes to slot 4, then the database checks each row until it finds 30.

PX Deq Credit: send blkd causing Parallel queries to hang in Oracle

The main blocker in parallel queries in the Query coordinator, Suppose you want to sum on 500 million rows, Then even if you run in parallel , Each query coordinator will do the sum 
and then give result to query coordinator to further sum it. 

Now consider you have grouping on 2 columns, Then it has to sort the data (group) so the 
query coordinator has lot of work to do in case of 500 million. You will see in parallel plans
p->s which means parallel to sequential 

What this Send blocked error means is Query coordinator already has lot of work so it cant accept any more data from Slave processes and Slaves are waiting for Query coordinator to become free 

Solution - Try to write PL/SQL code using Table functions and DBMS_PARALLEL approach it allows you to linearly scale up

If you really want to make use of parallel , then best to go for procedure and parallelise
procedure using dbms_parallel . This is truly parallel in database

( Assumption -In most of cases you will never be slowed by read speed, 1 billion rows is not a
issue, if you are doing in TB then have to think)

Table Functions  (Very important for datawarehouse implementation and Next is dbms_parallel) 

http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm

How to make pl/sql go in parallel 

http://www.oracle.com/au/products/database/o30plsql-086044.html



We were facing issue with one of our Fact table population queries getting hanged in Datawarehouse. After long day of analysis below is the summary

1) Checked temp space , it was not increasing. Usually almost all parallel queries bypass buffer cache and they use temp space for most of the sorting , grouping , hash join,. PGA size is small though your PGA can be in GBs but a single process can use max 5% of it. So content gets spilled to temp space. So when temp space is not changing means nothing much is happening in query. It was stucked

2) The sorting , Hashing operation were not taking much part of temp space whatever their size was it was constant for more than 30 minutes

3) The v$session and V$session_wait both tables shows waits on below particular parallel events

Query coordinator ( QC)

Session are waiting on 3 parallel events  ( V$session, V$session_wait)

PX Deq: Execution Msg – Means slave process has finished execution waiting for message from QC to die
PX Deq: Table Q Normal – Means Consumers slaves waiting for producer slaves for data
PX Deq Credit: send blkd – Means slaves are not able to send data to QC they are blocked because QC has not finished processing earlier data. Reason being its parallel buffer pool is full

Root cause of issue – Too many parallel processing sending message to QC and QC not having enough buffer pool to accept those messages

Solution – Increasing buffer pool size for parallel (parallel_automatic_tuning) or reducing degree of parallelism. Can be done by removing default parallelism on tables and setting it manually . 

Very nice article on issue which helped me 

Select * From V$tempseg_Usage--------- Temp usage for query is low

select ----------------------------------------  none of the sorts are using high temp space

   a.username,

   sum(srt.blocks * 8 / 1024) "Used Space in MB",

   sum(srt.blocks * 8 / 1024)/1024 "Used Space in GB"

from

   v$session    a,

   v$sort_usage srt

where

   a.saddr = srt.session_addr

   and sid in ( select  sid FROM v$SESSION WHERE OSUSER= )

   Group By A.Username

SELECT p1 file#, p2 block#, p3 class#,EVENT-------------------- Session is waiting for 3 parallel events only

 FROM v$session_wait where sid in (

 Select  Sid From V$session

Where Osuser=''

AND USERNAME =

and blocking_session is null)

The parallel_automatic_tuning parameter sets the pool of buffer messaging. If the parameter is enabled, the buffer will be in the large pool or will be on shared pool whether the parameter is disabled. So if it is expected that your database run many parallel queries, consider fit correctly the pool size.