Dedicated server Vs Shared Server:

Dedicated server:

--> The user process and the server process are separate all together.
--> In a dedicated server environment each user process has its own server process.
--> To take the advantage of distributed processing the user process and server process can run on different machines.
--> The dedicated server exists even when a user process is not making any database requests, at this particular point of time the server process remains idle.
--> The ratio between the user process and server process in a dedicated server environment will be one – to – one.
If the user and server process are on the same machine then the interface in use will be IPC (Inter Process Communication) mechanism.

Shared server:

--> Generally we go to shared server in order to increase the server efficiency.
--> In an OLTP (On Line Transaction Processing) environment, at the time of order entry the server process will be in the idle state for almost 90 % of the time. Due to this we are not able to actually utilize the system resources which are getting wasted.
--> Unlike dedicated server processes, a shared server process can process can process any request which comes, instead waiting for a particular request.
--> In a shared server the client (User) process hits the dispatcher which in turn assigns the request to any one of the server processes which is free at that particular point of time.
--> The PMON process registers the location and load of the dispatcher with the listener, enabling the listener to forward the requests to least utilized dispatcher.
--> A dispatched can support multiple client connection concurrently.

Benefits of Shared server:

--> Reduces the number of server process which are idle.
--> Reduces the umber of processes against an instance.
--> Load balancing is achieved.
--> The number of possible users can be increased.

Note:

A dedicated server can be used at –
--> The time of connecting as a SYSDBA to start up / shutdown / at the time of performing recovery.
--> When batch jobs are executed as there will be minimal / no idle time as such.


Subscribe to Oracle Bullets by Email

Gist of Materialized views:

People might be aware of Snapshots in ORACLE, now in recent versions of ORACLE the place of snapshots has been occupied by MATERIALIZED VIEWS. These materialized views have many uses:

1. We can copy the data from one instance to other with the same format, and we can query against the materialized view directly.
2. We can copy the aggregated (Summarized) data from one instance to other instance and we can fire queries against the materialized view directly.
3. We can create a summarized (aggregated) version of a table, the interesting point here is that if we query the base table, then the query is automatically be rewritten to use the materialized view.

Let’s have examples for each of the above uses of the materialized views.

1. Here all the code is in our local database LOCALDB. We have a remote database RMDB, where there is a table named employee. However, one time per day our materialized view wakes up, dips into the RMDB and grabs the data and brings the same to LOCALDB.

Because of this we can fire the required queries to the materialized view even though we don’t have access to the RMDB.

In our LOCALDB:

Create materialized view emp_mview_ex1
Refresh complete
Start with sysdate
Next sysdate+1 as
Select emp_name, dept_name from employee@RMDB_dblink;

Now we can query the Materialized view as such

Select * from emp_mview_ex1;

2. Now let’s discuss about the summary (aggregated) table example. Even her all the code is in the local database LOCALDB. We have a employee table in the RMDB, now this data is summarized daily – getting the number of employees per department, as well as the average salary in that particular department.

Even, here we can fire queries against our local database LOCALDB with no access to the RMDB, aside from the refreshing the materialized view daily.

In our LOCALDB:

Create materialized view emp_mview_ex2
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name,count(*), avg(salary) from employee@RMDB_dblink; group by dept_name;

Now we can query the Materialized view,

Select * from emp_mview_ex2;

3. Now the interesting use of the materialized view query rewrite. A materialized view is built with the help of a base table and data is summarized, The ORACLE optimizer is smart enough in such a way that it can realize that when a query is fired on a base table, the data is already been aggregated in a materialized view, and will go with the data in the materialized view.

In our LOCALDB:

Create materialized view emp_mview_ex3
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name, count(*) from employee group by dept_name;

Now we query the LOCALDB with the base table n the from clause

Select dept_name, count(*) from employee group by dept_name;

Even though the query is fired against the base table, at the time of execution the optimizer will switch to the existing materialized view with the information already aggregated (summarized) and it will use the emp_mview_ex3 instead.

To make optimizer to think of using the existing materialized views there are some init.ora parameters to be set they are:

1. query_rewrite_enabled needs to be set to a value TRUE and we should have the ‘query rewrite’ system privilege.
2. If the materialized view has to be refreshed based on a schedule the it is going to use dbms_job to do so, so job_queue_processes needs to be some value > 0.


Subscribe to Oracle Bullets by Email

Generating random numbers and strings in Oracle is easier

For testing purposes at times we may need some test data, for this we might need to generate some random numbers or strings. People may write their own Random number generation scripts, but even ORACLE is providing a package for generating the random numbers and strings as well. This may be useful to in creating the auto generated passwords in a web page.

The fact is that Oracle provides us with a random number generation package. When this package is used to generate the random numbers it is faster than writing our own random generation scripts in PL/SQL. This Random generator package is not only used to generate the numbers and strings but this can be used to generate the alpha numeric strings also with the desired size (number of characters).

Bullet:

DBMS_RANDOM package

The DBMS_RANDOM package which is provided by ORACLE will generate random data in character, numeric or alphanumeric formats. The size of the random values which are to be generated can also be specified.
This package can be created using the script dbmsrand.sql available in the /RDBMS/ADMIN directory.

The following are the functions that are present in the package which can be used to serve the purpose of generating random numbers and strings.
RANDOM – This function is used to generate the random numbers.

VALUE – This function is used to generate random numbers from the range provided. The range will be taken as 0-1 if nothing is provided.

STRING – This function is used to generate the strings either in upper case or lower case or alphanumeric format.

• The first argument of the functions takes the following values which will define the string type to be generated.
• U - Upper case
• L - Lower case
• A - Alphanumeric
• X - Alphanumeric with upper case alphabets.
• P - Printable characters only.

If we use any character other than these characters the output will be in Upper case.
The second argument will be the size of the random number / string to be generated.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.

INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.

SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.

TERMINATE - Close the process of random number generation.

Examples:

Below are some of the examples of using the package.

Example 1: To generate a random number (positive or negative)
Select dbms_random.random from dual;

RANDOM
_____________
1254544778

Example 2: To generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;

VALUE
_____________
0.98121484036063

Example 3: To generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;

NUM
_____________
979.825208337552

Example 4: To generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

NUM
_____________
886832250758.021

Example 5: To generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
TSKZJBAGRQREGUEBRXIO

Example 6: To generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
thfrkllkuhxqeybkcykg

Example 7: To generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
xWPvLfHYuPWDNFEIHtUI

Example 8: To generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
CWWULRKBQLAMCLGYWC4A

Example 9: To generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;

STR
___________________
dsL&B}l?mMUIr?'T1r6{


Subscribe to Oracle Bullets by Email

Running out of Disk Space:

A Query which is badly written may even fill up the disk drive even if the disk is large enough. We need to get back the system onto its feet as quickly as possible.
Temporary Tablespace:
Temporary tablespace generally it holds SQL Execution workspace, intermediate indexes, sub-query information, sorts etc… Generally it is released automatically, but there are situations where this may lead to problems. Issues such as fragmentation, DBMS bugs, badly written queries may cause temporary space to grow till you run out of disk space.

To housekeep the temporary space in oracle is a common activity. The temporary space is flexible and powerful but it comes with some administration overhead.

Bullet:

Let us assume our database is in /u01/oradata/Chaitanya/ and our temporary space is named as temp01.dbf. We have some free space in u02 to use some space for a specified period of time. A SYSDBA user can do this.

Find the name of the current default temporary tablespace

SELECT property_value FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
-----------------------------
TEMP


Create a new temporary tablespace to allow the system to operate while we are making the changes:

CREATE temporary tablespace t1
Tempfile ‘/u02/Chaitanya_temp/t01.dbf’
Size 512M reuse autoextend on next 10M maxsize unlimited
Extent management local uniform size 256K;

Bring the new temporary tablespace online

ALTER DATABASE
Tempfile ‘u02/Chaitanya_temp/t01.dbf’
ONLINE ;

Change the New temporary tablespce as the default temporary tablespace

Alter database default temporary tablespace t1;

Drop the old temporary tablespace which is using the disk space

Drop tablespace temp including the contents and datafiles;

We are running with the new temporary tablespace but what we really wanted to do is recreate the old space in old partition and use the same. While we are doing so, we can restrict the size of the new space as 0.5 GB / 1 Gb, and allow it to grow till some extent let it be 4 GB.

Create temporary tablespace temp
Tempfile ‘u01/oradata/chaitanya/temp01.dbf’
Size 512M reuse autoextend on next 10M maxsize 4096M
Extent management local uniform size 256K;

Now we have to switch every thing back to using this final temp tablespace, and delete the temporary tablespce which we have created for the temporary use (/u02/Chaitanya_temp/t1)

Alter database
Tempfile ‘u01/oradata/Chaitanya/temp01.dbf’
ONLINE;

Alter database default temporary tablespace temp;

Drop tablespace t1 including contents and datafiles;

Please post your comments on this Bullet.


Subscribe to Oracle Bullets by Email