How to install and use statspack utility

STEP 1: Create required tablespaces for statspack utility.

Statspack utility requires two tablespaces to run:

 (a) PERFSTAT tablespace

 (b) TEMP tablespace

Command to get list of available tablespaces:

SQL> SELECT *

  2>   FROM DBA_TABLESPACES;

Other "less preferred" commands to get tablespaces list:

SELECT * FROM DBA_DATA_FILES;

SELECT * FROM DBA_TEMP_FILES;

SELECT * FROM v$datafile;

SELECT * FROM v$tempfile;

Command to create PERFSTAT tablespace (login as User:SYS to execute):

SQL> CREATE TABLESPACE PERFSTAT

  2      DATAFILE 'd:\oracle\oradata\oracle\perf01.dbf' SIZE 100M 

  3  /

or,

SQL> CREATE TABLESPACE PERFSTAT

  2      DATAFILE 'd:\oracle\oradata\oracle\perfstat.dbf' SIZE 100M AUTOEXTEND OFF

  3      EXTENT MANAGEMENT LOCAL AUTOALLOCATE

  4      LOGGING

  5      ONLINE

  6      SEGMENT SPACE MANAGEMENT AUTO

  7  /

STEP 2: Locate and Execute script spcreate.sql as user:SYS

Standard Location of script: $ORACLE_HOME/RDBMS/admin/spcreate.sql

Example: D:\oracle\ora92\rdbms\admin\spcreate.sql

While being executed, script will ask following questions:

a) Password for newly created user perfstat

b) Tablespace name

c) Temporary tablespace name

STEP 3: Login as user:perfstat now and generate diagnosis data by executing "statspack.snap" twice

Details:

Execute the following command twice with a few minutes of interval in between(lets say at 9 am and then at 9:10 am)

SQL> exec statspack.snap; 

STEP 4: Note down SNAP_IDs of the snapshots created above

In our case, since we have just generated statspack package, IDs will be 1 and 2.

You can confirm snapshot IDs using following command:

SQL> SELECT SNAP_ID, SNAP_TIME 

  2>  FROM  STATS$SNAPSHOT;

  3> /

STEP 5: Generate statspack report by executing "spreport.sql"

STANDARD location of script to run report : $ORACLE_HOME/RDBMS/admin/spreport.sql

Example: D:\oracle\ora92\rdbms\admin\spreport.sql

While executing, script will ask you to specify 

a) Name of the new report being created.

b) Two snapshot IDs to compare 

STEP 6:

The report thus created can be opened in any text editor and viewed (In our example, since the two SNAP_IDs were for snapshots taken at 9 am and 9 :15 am, the report will show compare performance data generated at 9 am with that generated at 9:15 am.

 

-Param

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

Want to know more? Visit:

http://www.akadia.com/services/ora_statspack_survival_guide.html

http://ist.uwaterloo.ca/~baumbach/ORACLEnotes/ST_statspack.html

http://www.troygeek.com/articles/Statspack101-Installing/

http://www.coderanch.com/t/79947/Oracle-OAS/why-datafile-TEMP-tablespace-not

Daily Diary with Random thoughts!

Everyday I visit so many web pages and come accross so much information pages that I end up thinking of starting my on version of "digg". I am dying to have a method of recording it all forever and save it for rainy days.
But you know, this lethargic nature of mine will kill me.

At last I am trying to start from today: writing(documenting) whatever I find everyday and treat this part of blog as my "daily-diary" - a place where I can write whatever I want to and record it for future dig!
So here's my first attempt.

1. Tried to brush up my knowldege of flashback version queris in Oracle 10G a bit today.
Fav link is again "Arup Nanda's top 20 features for DBAs"
http://www.oracle.com/technology/pub/articles/10gdba/week1_10gdba.html

A summary for quick reference in future:
TOPIC: FLASHBACK VERSIONS QUERY
Parameters:
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 900
Example:
select num, name, type, value from v$parameter where upper(name) like '%UNDO%'
Pseudocolumns used:
versions_starttime, versions_endtime, versions_xid, versions_operation, versions_startscn, versions_endscn
where conditions allowed:
versions between timestamp minvalue and maxvalue
versions between scn minvalue and maxvalue
Tables available:
FLASHBACK_TRANSACTION_QUERY
(Let's see if I get time to try these things practically at home tonight)

2. Also visited arup's own blog on blogspot and read his blog on "effective communication" here:
http://arup.blogspot.com/2008/10/about-clear-communications.html

3. Stumbled upon a great website today:
http://www.oracle-developer.net/
Visited an article related to bulk load(collections) and liked it:
Topic: "Introduction to bulk pl/sql enhancements in 9i"
http://www.oracle-developer.net/display.php?id=201
(explains with example how to use cast() and multiset() functions)

4. Somebody asked me about CAMEO today and I was like, dumbstruck! She linked "cameo" with "oracle database knowledge", "restructuring of existing computing environment rerquired due to merger of a newly acquired organisation in existing setup" and what not!

Google says: CAMEO means "Consolidated Access Management and Enterprise Optimization"
Will I ever get time to (1) google on such topics (2) and understand a bit of them the same day?
I don't think so.

INDEX BY TABLES (Collections)

INDEX BY TABLE:

Here is a Program which uses all collection functions available to index by tables. Such programs come very handy when you have very less time to revise something without even going through the documention:
create or replace procedure proc1
as
type name_type is table of varchar2(20) index by binary_integer;
names name_type;
i number;
begin
-- Index by table values can be used as part of select query
select ename into names(1) from emp where empno = 7654;
dbms_output.put_line(names(1));

-- Index by table values can be populated using direct assignments
names(2) := 'Param';
names(3) := 'jeet';
names(4) := 'singh';

dbms_output.put_line('names.count: 'names.count); --function:count
dbms_output.put_line('names.first:'names.first); --function:first
dbms_output.put_line('names.last:'names.last); --function:last

dbms_output.put_line('Displaying values using for loop...');
for i in names.first..names.last --functions:first,last
loop
dbms_output.put_line('names('i'): 'names(i));
end loop;

dbms_output.put_line('Displaying values using while, ascending order..');
i:= names.first;
while i <= names.last
loop
dbms_output.put_line('names('i'): 'names(i));
i := names.next(i); --function:next
end loop;
dbms_output.new_line;
dbms_output.put_line('Deleting element #2...');
names.delete(2); --function:delete
if names.exists(2)
then
dbms_output.put_line('Element #2 exists'); --function:exists
else
dbms_output.put_line('Element #2 does not exist');
end if;
dbms_output.put_line('Displaying values using while loop, descending order...');
i := names.last;
while i >= names.first
loop
dbms_output.put_line('names('i'): 'names(i));
i := names.prior(i); --function:prior
end loop;
dbms_output.new_line;
exception
when others then
dbms_output.put_line('Exception occurred, sqlcode:'sqlcode', sqlerrm:'sqlerrm);
end proc1;

=====================
Summary of Collection functions used above:
count : returns count of elements in a collection (here, index by tables).
exists: returns TRUE or FaLSE on the basis of whether the specified element exists in a collection
first : retrurns index of first element in a collection
last : return index of last element in a collection
prior : returns index of previous element in a collection
next : returns index of next element in a collection
delete : deletes specified element in a collection
Collection functions not available to index-by-tables:
limit: returns maximum number of elementrs for a collcection
extend : adds elements to a collection

NOTE:
"Index by table" is the oldest collection type introduced in Oracle 7.
"Index by tables" save values in the form of key-value (index-value) pairs.
SYNTAX:
Type is TABLE of index by BINARY_INTEGER;
EXAMPLE:
Type name_list_type is TABLE OF varchar2(20) index by BINARY_INEGER;
can only be of type binary_integer.
There is virtually no limit of storing values in an index-by-table. The limit is restricted by storage limit of binary_integer type which is -2GB to +2GB (which is treated as virtually unlimited).
Elements in an index by table need not be in order, but they can be mapped to a C++ or java array ( for that element need to be in order)
Limitations:
Index by table cannot be used as a table column type.

How good you understand PL/SQL Loops?

Let's see how good you know about Pl/SQL loops.
Here, I'll show you a code and you need to guess the expected output. Simple?
Be Honest and dont scroll down the question to see answer beforehand!
=============================
Q1. In the following code, what will be the output of line marked as "(Q)" below.
declare
begin
for i in 1..5
loop
dbms_output.put_line('i: 'i);
end loop;
dbms_output.put_line('After loop, i: 'i); -- (Q)
end;

ANSWER: The above code will show an error !
dbms_output.put_line('After loop, i: 'i); -- (Q)
*
ERROR at line 8:
ORA-06550: line 8, column 44:
PLS-00201: identifier 'I' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored

EXPLAINATION: There is no variable 'i' defined in the above code block. Variable i used by for-loop here is actually an 'on the fly' variable which is local to the loop. It has no existance outsiode the for loop. Hence the error.

=============================
Q2. In the following code, what will be the output of line marked as "(Q)" below.
declare
i number:= 0;
begin
for i in 1..5
loop
dbms_output.put_line('i: 'i);
end loop;
dbms_output.put_line('After loop, i: 'i); -- (Q)
end;

ANSWER: Output is "After loop, i: 0"

EXPLAINATION: As explained above i used by for loop is an "on the fly" variable whih loses its existance once the loop ends. The line marked as '(Q)' is printing value of another variable 'i' which is declared in the declare section.

=============================
Q3. In the following code, what will be the output of line marked as "(Q)" below.
declare
begin
for i in 1..5
loop
dbms_output.put_line('i: 'i);
i := i + 1;
end loop;
end;

ANSWER: The above code will show an error !
i := i + 1;
*
ERROR at line 7:
ORA-06550: line 7, column 5:
PLS-00363: expression 'I' cannot be used as an assignment target
ORA-06550: line 7, column 5:
PL/SQL: Statement ignored

EXPLAINATION: Variable 'i' is a loop variable here and hence its value cannot be modifed by us.
=============================
Q4. In the following code, what will be the output of lines marked as "(Q1)" and "Q2" below.
declare
i number:= 0;
n_start number := 1;
n_stop number := 5;
begin
for i in n_start..n_stop
loop
n_stop := 3;
dbms_output.put_line('i: 'i); -- (Q1)
end loop;
dbms_output.put_line('n_stop: 'n_stop); -- (Q2)
end;

ANSWER: Line marked as (Q1) will print all values from 1 to 5. Line marked with (Q2) will print "3".

EXPLAINATION: Loop is supposed to run from "p_start" value to "p_stop" value. So apparantly, if p_stop value changes from 5 to 3, loop should print values from 1 to 3 only. But in reality this doesnot happen. Once the loop starts running, run-range is freezed! So no matter you change n_stop while the loop is running, this willnot affect the loop range.

Object Types in Oracle

A step by step guide  to "Usage of objects in Oracle"

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

This article answers the following questions:

1. When was ORDBMS introduced?

2. How do we create objects in Oracle?

3. How to use Objects as oracle table column type?

4. How and where to use object constructors?

5. How to query from a table containing object types?

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

A little background first...

With Oracle 8i, oracle introduces the concept of object relational DBMS  or ORDBMS.  Starting 8i, Oracle allows you to incorporate object oriented concepts in a DBMS too. 

Terms like object, constructor, attribute  etc  are now part of a oracle's vocabulary  too.

Let us  practically learn how to create objects in Oracle and how to use them as part of an oracle table.

We start with the basic "create table" command here. Execute the following:

SQL> create table OLD_EMP

  2  (

  3   id number(2),

  4   f_name varchar2(20),

  5   m_name varchar2(20),

  6   l_name varchar2(20),

  7   add_line1 varchar2(20),

  8   add_line2 varchar2(20),

  9   city varchar2(20),

 10   state varchar2(20),

 11   pincode varchar2(10),

 12   homephone varchar(20),

 13   cellphone varchar2(20)

 14  );

Table created.

Now that we have created table OLD_EMP, let us put a row in it:

SQL> insert into old_emp 

  2  values ( 19, 

  3  'Paramjeet', 'Singh', 'Sasan', 

  4  '1001, CASTLE', 'BAVDHAN', 'PUNE', 'MAHARASHTRA', '411021',

  5  '020-112233', '+91 985 XX5 XXX5'

  6  );

1 row created.

So this is the basic method of creating a table in Oracle. Correct. But I want you to revisit the table definition 

create table OLD_EMP

(

 id number(2),

 f_name varchar2(20),

 m_name varchar2(20),

 l_name varchar2(20),

 add_line1 varchar2(20),

 add_line2 varchar2(20),

 city varchar2(20),

 state varchar2(20),

 pincode varchar2(10),

 homephone varchar(20),

 cellphone varchar2(20)

);

As you can see above (shown in different colors), we can easily divide the above data in such a way that related data can be kept together separately as independent objects). Correct?

Ok so lets' do that. We will now create three separet object types to save the the above data in three groups. Use following commands to create required objects:

SQL> create or replace type full_name_type as OBJECT

  2  (

  3   f_name  varchar2(20),

  4   m_name  varchar2(20),

  5   l_name  varchar2(20)

  6  );

  7  /

Type created.

SQL> create or replace type address_type as OBJECT

  2  (

  3   add_line1  varchar2(20),

  4   add_line2  varchar2(20),

  5   city   varchar2(20),

  6   state   varchar2(20),

  7   pincode  varchar2(10)

  8  );

  9  /

Type created.

SQL> create or replace type contact_type as OBJECT

  2  (

  3   homephone  varchar(20),

  4   cellphone  varchar2(20)

  5  );

  6  /

Type created.

Now that we've created all three objects required, we can create an object relational table "NEW_EMP" as shown below:

SQL> create table new_emp

  2  (

  3   id number(2),

  4   name   full_name_type,

  5   address address_type,

  6   contact_no contact_type

  7  );

Table created.

Looks so simple and clear now. Isn't it?

Q. But how do we insert values in this new table now?

Old style of inserting values will not work for such a table because it has only four publicly visible columns now.

So now we need to insert values using constructors. Execute the following command:

 

SQL> insert into new_emp 

  2  values ( 19, 

  3  full_name_type('Paramjeet', 'Singh', 'Sasan'),

  4  address_type('1001, CASTLE', 'BAVDHAN', 'PUNE', 'MAHARASHTRA', '411021'),

  5  contact_type('020-112233', '+91 985 XX5 XXX5')

  6  );

1 row created.

Notice the way constuctors are used (shown in bold) here to insert values in the objects which are based on new objects types (or, datatypes) introduced in table:NEW_EMP.

Now, let us try selecting from both the tables to see how differntly data is inserted and selected.

SQL> select * from old_emp;

        ID F_NAME               M_NAME               L_NAME

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

ADD_LINE1            ADD_LINE2            CITY

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

STATE                PINCODE    HOMEPHONE            CELLPHONE

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

        19 Paramjeet            Singh                Sasan

1001, CASTLE         BAVDHAN              PUNE

MAHARASHTRA          411021     020-112233           +91 985 XX5 XXX5

SQL> select * from new_emp;

        ID

----------

NAME(F_NAME, M_NAME, L_NAME)

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

ADDRESS(ADD_LINE1, ADD_LINE2, CITY, STATE, PINCODE)

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

CONTACT_NO(HOMEPHONE, CELLPHONE)

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

        19

FULL_NAME_TYPE('Paramjeet', 'Singh', 'Sasan')

ADDRESS_TYPE('1001, CASTLE', 'BAVDHAN', 'PUNE', 'MAHARASHTRA', '411021')

CONTACT_TYPE('020-112233', '+91 985 XX5 XXX5')

As we can see, in case of table:NEW_EMP, the whole address of employee "paramjeet" is treated as a single object in table NEW_EMP;

Q. What if we want to select on the basis of an object's attribute (like, on the basis of name.f_name or address.city)?

The above task can easily be performed on OLD_EMP using a simple "select.. where"  query.  Let's see...

SQL> select f_name from old_emp where l_name = 'Sasan';

F_NAME

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

Paramjeet

Q. But will the same query work for NEW_EMP too? 

Let's see that too...

SQL> select f_name from new_emp where l_name = 'Sasan';

select f_name from new_emp where l_name = 'Sasan'

                                 *

ERROR at line 1:

ORA-00904: "L_NAME": invalid identifier

OOPs, the query is not working the age old way. Do you know why ? Because,  l_name is an attribute of object name, it is hidden and not visible to outside  World.  So we should use  "object_name.attribute_name" style to access that, the way we do in Object oriented languages like C++ and java.

Try This:

SQL> select name.f_name from new_emp where name.l_name = 'Sasan';

select name.f_name from new_emp where name.l_name = 'Sasan'

                                      *

ERROR at line 1:

ORA-00904: "NAME"."L_NAME": invalid identifier

OOPS, this is not working either! Why ? Now an oracle restriction comes in: In such cases we have to use table alias name to access object's attribute value.

So the style to try now is "table_alias.object_name.attribute_name". 

SQL> select e.name.f_name from new_emp e where e.name.l_name = 'Sasan';

NAME.F_NAME

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

Paramjeet

:)

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

Interested in learning more about Object type in oracle? Visit the following link:

http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/10_objs.htm

Cheers!

-Param

Difference of one second between sysdate and current_date results

The following issue has been troubling me since yestarday morning.

Issue: I expect sydate result to match exactly with current_date result, but 50% of the times I get a difference of one second between the two results and I am not able to figure out why.

Following are the time zone settings I have:
SQL> select dbtimezone, sessiontimezone from dual;
DBTIME SESSIONTIMEZONE
------ ---------------
+00:00 +05:30
My Database server machine is set to Indian time zone +5:30

Though sysdate is not timzone aware but current_date is, I dont think this should stop the two functions from displaying me the same date and time.
When I try to fire the same following command a multiple times quickly (so as to fire it 2-3 times within a second), bot the commands donot print the same time always:

SQL> select to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'), to_char(current_date, 'dd-mon-yyyy hh:mi:ss') from dual;

SQL> /
TO_CHAR(SYSDATE,'DD- TO_CHAR(CURRENT_DATE
-------------------- --------------------
10-nov-2008 03:45:45 10-nov-2008 03:45:45

SQL> /
TO_CHAR(SYSDATE,'DD- TO_CHAR(CURRENT_DATE
-------------------- --------------------
10-nov-2008 03:45:45 10-nov-2008 03:45:46

SQL> /
TO_CHAR(SYSDATE,'DD- TO_CHAR(CURRENT_DATE
-------------------- --------------------
10-nov-2008 03:45:46 10-nov-2008 03:45:46

NOTE:
(1) Just to reaffirm my query I have tried my query on three different database servers, but everywhere I am faced the same issue.
(2) Timestamp commands do not have any issues, They give me accuracy in seconds correct to 6 decimal places, which is expected.
SQL> select systimestamp, current_timestamp from dual;
SYSTIMESTAMP CURRENT_TIMESTAMP

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

10-NOV-08 03.49.55.358227 PM +05:30 10-NOV-08 03.49.55.358257 PM +05:30

Searched google for possible answer but to no avail.

Now thinking of asking Tom.....

Are triggers trustworthy?

Why do DBAs suggest avoiding trigger usage in production Databases?

Somebody asked me this question recently and forced me to reconsider my regular "short" answer I always give. Below is the list of advantages and disadvantages of using triggers.

Advantages:

1. Triggers can be used to apply complex table constraints automatically, which we cannot apply using check constraints.
For example, check constraints cannot refer to other tables, but triggers can do that.

2. Triggers are event based so unlike procedures and functions they can be more productive in many cases where you want to automate a validation/verification process.

3. Database Triggers are executed whenver table data gets modified and they can evaluate both "before" and "after" state of data undergoing modification, and take decision effectively.

4. "Instead of" triggers are used to make complex (readonly) views MODIFIEABLE. We have to make architectural changes if we dont want to use "instead of" triggers.

5. Triggers can be used to implement cascading modificaton effect, a more complex form which may not be possible using cascade constraints (like "on cascade delete" etc). For example, If table A undergoes a specific data change, a few tables need to be automatically updated based on listed where conditions. Oracle procides "Cascade constraint" can only take care of tables linked with foreign key constraints.

Disadvantages:

1. Triggers are fired whenever Data undergoes modification. This can slow down the data modification speed if trigger is fired for every row data change again n again.

2. In cases where mass insertions are going on and the table has an "on insert" trigger, insertions will be pathetically slow.

3. Though Oracle provides us the facility of disabling triggers temporarily to counter act above two problems, this can result in too much of tracking (user has to keep track of what triggers are currently enabled/disabled) and possible human errors.

4. Since Oracle allows multiple triggers on the same table and on the same invocation level too, this can create confusion while understanding the code. For example, it is very difficult to understand the process flow if the table has multiple triggers written on it for various conditions. In such a case developer has to always keep a complex diagram depicting prcess flow with numberless arrows drawn here n there.

5. Sometimes multiple triggers create an infinite, uncontrolled cascading effect.
For example: When Table-A is updated Trigger-A is fired. Body of Trigger-A contains a statement to update Table-B. Table-B also has a simialr trigger-B containing code to modify
table-C. Now, similar is the case with Table-C too, it also has Trigger-C.
Such a case will result in cascaded call of multiple triggers which is difficult to understand and handle. The condition becomes worse when you find Trigger-C actulally containing code to modify Table-A leading to an INFINITE LOOP!

6. Oracle cannot guarantee order of invocation of two triggers defined on the same level with different functionalities.

For example:
Trigger A says: If join_date is > sysdate, donot allow this.
Trigger B says: If Join date is NULL, replace it with sysdate +1

Now if both the triggers are on the same level of invocation (like, both are defined as "before insert on same table"), Oracle cannot guarantee which of the two will be fired first. This leads to inconsistent results everytime!

The following statement proves this:

SQL> insert into EMP(empno, ename) values (1,'PARAMJEET SINGH');

Case 1: If trigger A fires before trigger B
Result: Trigger A says nothing as join_date is NULL. But now trigger B fires and puts join_date as sysdate+1 in this new row
successfully!

Case 2: If trigger B fires before trigger A
Result: Trigger B puts join_date as sysdate+1 in this new row successfully. But then Trigger A fires and throws error making the previous insertion UNSUCCESSFUL!

UPDATES:

Jan 15, 2009:

Find a related article today and thought of sharing with you all:

"The Trouble with Triggers" by Tom Kyte

link: http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html

============END OF THE DOCUMENT=================