Life with Oracle

 MATERIALIZED VIEW - ARCHITECTURE

CLOUD CONCEPTS   Today, we will discuss about the Oracle cloud Concepts . Like all the Cloud Technologies, Oracle cloud which is called as OCI offers variety of services with better SLA's and performance.OCI is considered as most robust cloud technology because of its high security and high availability. Lets discuss few cloud basic terms before jumping in to OCI Architecture. On Demand self service -- In cloud, the services are provisioned automatically without human interaction with service provider. Broad network access   -- The cloud services are accessed by broad network which follow standard mechanisms. Resource pooling            -- In cloud, all the services of provider are pooled at one place and this are allocated to multiple customers on their requirement. Rapid Elasticity              -- In cloud , services are scaled up and down automatically and sometimes without any impact to the running env...

  INITTrans  Whenever a DML transaction wants to modify a block , it should inform the block by sending 'interested to modify' message to the block .Technically speaking, a dml transaction will always send message to the block , that it is interested to modify the block . This message is stored in ITL slots ,which is preallocated in the block header. Transactions whose entry is there in this ITL slots can only modify the block.This ITL slots are controlled by INITRANS and MAXTRANS values, the inittrans controls the minimum number of slots that will be allocated in the block header. Honestly speaking, the number of ITL slots is equal to number of DML transactions that can happen parallely at one time. Each ITL slot occupies 24 bytes of space in the block. Inittrans values is 1 for table and 2 for index by default. MAXTrans Maxtrans is the maximum number of ITL slots that can be allocated in the block . When the block runs out of this ITL slots and there is enough free space in ...

HASH JOIN : Hash Joins is one of the method of joining two tables based on the join condition mentioned in the query. It is important to mention the smaller table  in leading position in the sql query when joining two tables. The optimizer always picks the leading table/first table as per the order mentioned in the query.If the execution plan does not pick the smaller table as the leading table then there will be more load/activity at the joining step then the plan which might impact the overall elapsed time of the query. We can explicitly ask the optimizer to pick a particular table as leading table using a oracle hint Ex: /*+ leading(table name)*/  How Hash Join works?  1) First Optimizer scan the smaller table and creates a hash table  by applying hash function on the column to be joined.   2) Now the optimizer scans the second table applies a hash function(applies on the column to be joined) to get the hash value for each record in the second table. ...

       Many think that processing a activity or work parallely will be more effective then performing it sequentially(serially),Even we do apply for most of the cases.Since, in parallel processing , the work is divided among the multiple resources and each resource will take care of their allocated work and finally  results from all of them are merged as one final result. Oracle has also introduced Parallel processing technique which is called as Parallel servers or Parallel query servers, where work is divided among multiples resources , resources here mean threads which are capable of doing working dependently. But when it comes to implementing parallel processing techniques in machines , it is not as easy as what we can do as human, Since the machines need to be guided so that each parallel thread should coordinate with other whenever required.   Lets discuss in depth about Parallel processing technique i.e Parallel servers implemented in Oracle Database...