Thursday 29 December 2011

Oracle DBA – Beginner’s essential Guide

Preface:

This article is mainly aiming for giving a solid idea about Oracle for the beginners and the Job seekers.

Role of DBA:

In order to become a fully fledged DBA, you need to have an understanding of the basic concepts about oracle architecture and DBA responsibilities. This article aims to provide the fundamentals of what a DBA is and what a DBA does in their daily role. DBAs are highly paid, due to the nature of their responsibilities, risks and technical knowledge. The Database Administrator (DBA) takes care of all of the security, manage, data management and efficient running of an organizations database. Backup and recovery are also one of the essential parts of the role. The DBA must frequently make recommendations for improvement and future strategy. The DBA must liaise frequently with co-workers and other departments who rely on the database for information. During out of office hours also DBA has to support their critical database with extra vigilance in order to keep the organizations database operational. DBA always would be more careful to maintain the data integrity. . Developers rely on the database for their working platform. Therefore it not only affects the users but developers of products and applications. Another task performed by the DBA is the evaluation and comparison of DBMS’s, so that the correct product can be selected to meet the database and customer specification.

Wednesday 28 December 2011

Why Oracle

I was always wondering why people are still behind Oracle. I’ve done a basic analysis and found out some of the following. Hope the below will clarify all your doubts.

Disclaimer and Update: I'm now working on Oracle Platform. So keep that in mind when you read these posts. The topic I posted here is really based on my knowledge. Even I know, there are some advantages for SQL as well. Please rectify me if you find anything wrong which I’ve posted here. I’m happy to remove any point which seems to be incorrect.

Oracle is available on multiple platforms such as Windows, all flavors of Unix from vendors such as IBM, Sun, Digital, HP, Sequent, etc. and VAX-VMS, as well as MVS. The multi-platform nature of Oracle makes it a true enterprise solution. Oracle and Microsoft SQL Server are competitors in the enterprise database market space. Oracle database software is the market leader with a 44.3 percent market share, while Microsoft SQL has an 18.5 percent market share. Oracle is an order of magnitude more flexible and robust that SQL Server. However, market share does not tell the whole story--Microsoft has regularly introduced new features to ensure it remains competitive.

Ø DBAs can perform typical administrative functions in 41 percent less time when using Oracle Database 11g compared to Microsoft SQL Server 2008.

Ø Oracle have more sophisticated partitioning including multiple partition types (range, list, hash) and subpartitioning. SQL Server is limited to range partitioning and no subpartitioning

Ø Oracle Database 11g requires 43 percent fewer steps for the same set of standard RDBMS tasks than Microsoft SQL Server 2008 using Edison’s metric for complexity assessment.

Ø Oracle Database provides support for developing, storing, and deploying Java applications. Bringing Oracle and Java together in our development environment gives us an exceptionally flexible and robust application development and data-handling infrastructure that fronts an excellent data management system and is easily extended in enterprise resource planning. On top of that, it’s extremely Internet-friendly, an increasingly important consideration. SQL server doesn’t have no Java database engine as in Oracle.

Ø In SQL Server there is no "dead connection detection". Clients who lose their connection may still hold locks until a DBA is notified to kill their server side processes.

Ø Benefiting from increased DBA productivity due to lower complexity and higher efficiency cited above, businesses could save up to $33,520.47 per year per DBA by using Oracle Database 11g rather than Microsoft SQL Server 2008.

Why Oracle Wins

Oracle is the first successful commercial RDBMS, they have helped set the standard.

It runs on any OS you can think of, including UNIX, Windows and Mac OSX

You can run it for free. If you consider it’s supported on Linux, then it gets even cheaper. Just find some decent hardware, and you’re good to go.

You can create, populate, query, manage, backup, & destroy a database all on the command-line. No tools necessary.

Even Oracle support GUI.

You can control it over the web, if that’s your kind of thing.

There’s no shortage of jobs that require Oracle expertise. If you know Oracle, you should be able to find a job. I don’t see this changing anytime soon.

Oracle jobs generally come with a very good salary.

Advantages of Oracle over SQL Server

In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.

No range portioning of large tables and indexes. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions. For example, an invoice table can be partitioned into monthly partitions. Such partitioned tables and partitioned indexes give performance and maintenance benefits and are transparent to the application.

There are no function-based indexes in SQL Server.

There is no partitioning in SQL Server.

There are no reverse key indexes in SQL Server.

All pages (blocks) are always 8k and all extents are always 8 pages (64k). This means you have no way to specify larger extents to ensure contiguous space for large objects.

There are no bitmap indexes in SQL Server.

There is no star query optimization in SQL Server.

PL/SQL is better in error exception handling, and there is no such thing as exceptions in T-SQL (T-SQL uses @@error -- not elegant!).

In SQL Server there is no such thing as SQL*NET aliases/service names! This means applications have to hard code the actual server name into their apps, making it difficult to move databases later to load balance across servers.

No ability to read/write from external files from a stored procedure.

PL/SQL has many DBMS system packages, where T-SQL relies only on a limited number of extended and system stored procedures in the master database.

Stored procedures in SQL Server are not compiled until executed (which means overhead, late binding and finding errors at execution time only!).

We cannot declare public or private synonyms.

No "before" event triggers (only "after" event triggers) and no row triggers (only statement).

There is no such thing as independent sequence objects.

There are no packages; i.e., collections of procedures and functions.

Friday 23 December 2011

Largest Database Objects in a Tablespace

The following query shows 10 Largest database objects in a specific Tablespace

col owner format a15

col segment_name format a30

col segment_type format a15

col mb format 999,999,999

select owner

, segment_name

, segment_type

, mb

from (

select owner

, segment_name

, segment_type

, bytes / 1024 / 1024 "MB"

from dba_segments

where tablespace_name='&name'

order by bytes desc

)

where rownum < 11

TOP 10 Database Objects

The following Query shows first 10 largest database objects in a DB.

col owner format a15

col segment_name format a30

col segment_type format a15

col mb format 999,999,999

select owner

, segment_name

, segment_type

, mb

from (

select owner

, segment_name

, segment_type

, bytes / 1024 / 1024 "MB"

from dba_segments

order by bytes desc

)

where rownum < 11

Oracle Statspack Essential Guide

Why Statspack

STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK is a diagnosis tool for instance-wide performance problems; it also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem.
Always set timed_statistics to true for our instance. Setting this parameter provides timing data which is invaluable for performance tuning.

How to Install and Configure STATSPACK

The installation process is simple and straightforward. It can be outlined as:

1. Connect to the database as sysdba and create the PERFSTAT/TOOLS Tablespace if the user doesn’t exit

a) sqlplus /nolog;

b) connect / as sysdba

c) create the PERFSTAT Tablespace as below

SQL> CREATE TABLESPACE statspack
     DATAFILE ‘/u01/oracle/db01/SULFI_statspack.dbf’ SIZE 1000M REUSE
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
     SEGMENT SPACE MANAGEMENT AUTO
     PERMANENT    ONLINE;

Now that the tablespace exists, we can begin the installation process of the STATSPACK software. Note that you must have performed the following before attempting to install STATSPACK.

$ cd $ORACLE_HOME/rdbms/admin$ sqlplus “/ as sysdba”

SQL> start spcreate.sql

Set the default tablespaces (the tablespaces must exist if not must be created):

a) define default_tablespace = ‘STATSPACK’

b) define temporary_tablespace = ‘TEMP’

Choose the PERFSTAT user’s password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user’s
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users’s default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT

Pressing <return> will result in STATSPACK’s recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: STATSPACK

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user’s temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user’s Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *

Pressing <return> will result in the database’s default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

.....
.....Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

2.) Set the timed_statistics = true:

Statistics are collected and stored in trace files or displayed in V$SESSTAT and V$SYSSTAT.

ALTER SESSION SET timed_statistics = true;

or
ALTER SYSTEM SET timed_statistics = true;

STATSPACK Data Collection

The STATSPACK utility comes with two options for controlling the collection of data, level and threshold. The type of the collected data is controlled by the “level” parameter whereas the “threshold” parameter filters the SQL statement collection into the stats$sql_summary table.

· Level 0 – This level captures general statistics including system events, SGA, rollback segment, background events, wait statistics, lock statistics, row cache, session events and latch information.

· Level 5 (default) – This level include the level 0 statistics and the capture of high resource usage SQL statements.

· Level 6 – Capture of SQL plan and plan usage information for the SQL statements with high resource e usage along with information from level 5.

· Level 7 – Segment level statistics including physical and logical reads, row lock and buffer busy waits along with the information from level 6.

· Level 10 – Child latch statistics.

To see the current level of the STATSPACK, you can use the following:

SELECT FROM stats$level_description ORDER BY snap_level;*

· Set the level of the STATSPACK parameter( In this example it is 5)

Exec statspack.snap(i_snap_level=>5, i_modify_parameter => ‘true’);

3.) Create, View and Delete Snapshots

sqlplus perfstat/perfstat
SQL> exec statspack.snap;
OR

SQL> exec statspack.snap(i_snap_level => 6, i_modify_parameter => ‘true’);

SQL> select name,snap_id,to_char(snap_time,’DD.MM.YYYY:HH24:MI:SS’) “Date/Time” from stats$snapshot,v$database;

NAME SNAP_ID Date/Time

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

CITPROD 3346 21.12.2011:13:45:00

CITPROD 3347 21.12.2011:14:00:01

CITPROD 3348 21.12.2011:14:15:00

CITPROD 3349 21.12.2011:14:30:00

CITPROD 3350 21.12.2011:14:45:01

CITPROD 3351 21.12.2011:15:00:01

CITPROD 3352 21.12.2011:15:15:01

CITPROD 3353 21.12.2011:15:30:01

CITPROD 3354 21.12.2011:15:45:01

CITPROD 3355 21.12.2011:16:00:01

CITPROD 3356 21.12.2011:16:15:00

CITPROD 3357 21.12.2011:16:30:01

CITPROD 3358 21.12.2011:16:45:01

CITPROD 3359 21.12.2011:17:00:00

CITPROD 3360 21.12.2011:17:15:00

CITPROD 3361 21.12.2011:17:30:01

CITPROD 3362 21.12.2011:17:45:01

CITPROD 3363 21.12.2011:18:00:01

CITPROD 3364 21.12.2011:18:15:00

CITPROD 3365 21.12.2011:18:30:00

CITPROD 3366 21.12.2011:18:45:00

CITPROD 3367 21.12.2011:19:00:01

CITPROD 3368 21.12.2011:19:15:00

CITPROD 3369 21.12.2011:19:30:00

CITPROD 3370 21.12.2011:19:45:00

CITPROD 3371 22.12.2011:07:00:01

CITPROD 3372 22.12.2011:07:15:00

CITPROD 3373 22.12.2011:07:30:01

CITPROD 3374 22.12.2011:07:45:00

CITPROD 3375 22.12.2011:08:00:01

CITPROD 3376 22.12.2011:08:15:01

CITPROD 3377 22.12.2011:08:30:00

CITPROD 3378 22.12.2011:08:45:01

22 rows selected.

4.) Create the Report

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql

We have to provide at the prompt: BEGIN_SNAP, END_SNAP, REPORT_NAME (we can use a default value as well)

It shows as follows

Snap Snap

Instance DB Name Id Snap Started Level Comment

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

CITPROD CITPROD 3438 23 Dec 2011 10:45 5

3439 23 Dec 2011 11:00 5

3440 23 Dec 2011 11:15 5

3441 23 Dec 2011 11:30 5

3442 23 Dec 2011 11:45 5

3443 23 Dec 2011 12:00 5

3444 23 Dec 2011 12:15 5

3445 23 Dec 2011 12:30 5

3446 23 Dec 2011 12:45 5

3447 23 Dec 2011 13:00 5

3448 23 Dec 2011 13:15 5

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 3438

Begin Snapshot Id specified: 3438

Enter value for end_snap: 3439

End Snapshot Id specified: 3439

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_3438_3439. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: myreport.txt

resource_limit TRUE

rollback_segments r01, r02, r03, r04, R05, r06, r07

sessions 445

shared_pool_size 800M

sort_area_retained_size 1048576

sort_area_size 4190208

timed_statistics TRUE

transactions 368

transactions_per_rollback_seg 34

user_dump_dest /Production/u01/admin/CITPROD/udum

utl_file_dir /Production/CITPROD/rf/log, /Pr

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

End of Report

SQL>

STATSPACK Maintenance

1. View snapshot details in the current database:

SELECT name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')"Date/Time", SESSION_ID, SERIAL# FROM stats$snapshot,v$database;

2. Gather statistics on PERFSTAT schema to create reports faster:

execute dbms_stats.gather_schema_stats('PERFSTAT',DBMS_STATS.AUTO_SAMPLE_SIZE);

execute dbms_stats.gather_schema_stats('PERFSTAT');

3. Delete old snapshot:

@?/rdbms/admin/sppurge; (after that Enter the Lower and Upper Snapshot ID )

Enjoy The Spirit of Learning J.

Post your comments please

Contact me on sulfi4u@gmail.com for further clarifications. Thanks for reading.

Thursday 15 December 2011

What does 'SNIPED' status in v$session mean? . How it is getting created?.

When IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.

This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.

Try this:

In SQLNET.ORA set the line:

SQLNET.EXPIRE_TIME = 20

Every 20 minutes this will try to clean the connections.

My Real-time Practice : To know how the snipped sessions got created

=======================================================

SQL> create user dba1 identified by abc123 profile TEMP;

User created.

SQL> alter system set resource_limit=true scope=memory;

System altered.

SQL> alter profile temp limit idle_time 2;

Profile altered.

SQL> select USERNAME,ACCOUNT_STATUS,PROFILE from dba_users where PROFILE='TEMP';

USERNAME ACCOUNT_STATUS PROFILE

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

DBA1 OPEN TEMP

SQL> ! date

Fri Sep 17 17:04:07 BST 2010

SQL> select status from v$session where username ='DBA1';

STATUS

--------

INACTIVE