Load Check

To check DB load press: w
To check CPU load press: prstat -t

AWR

AWR report from sqlplus:

oracle@jupiter:~$ cd $ORACLE_HOME
oracle@jupiter:/oracle/app$ cd rdbms/admin
oracle@jupiter:/oracle/app/rdbms/admin$ sqlplus s as sysdba

sql>@awrrpt.sql

Enter value for report_type: html
Enter value for num_days: 1
Enter value for begin_snap: 9014
Enter value for end_snap: 9017
Enter value for report_name: awr_nahar.html

SQL> host
bash-3.00$ ls
bash-3.00$ scp awr_nahar.html nahar@nahar:
-----------------------------------------------------------------------------------------
Example:
I was trying to spool but I had not spooling permission there. So -
Then I run the awrrpt.sql file from my home.

nahar@db01:~$ sqlplus s as sysdba
SQL> @/d00/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql

scp mf01.therap.net:/u/nahar/nahar-20111127.html /home/nahar/AWR/
-----------------------------------------------------------------------------------------
AWR (Automatic Workload Repository):

What is AWR?
AWR reports collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information.

What is default interval period between two awr report ?

By default, AWR automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 7 days.

GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSITORY

The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).

How to Manage & change interval period for awr report ?

There is two to modify or changes in AWR report.
1. Manually ---> through DBMS_WORKLOAD_REPOSITORY plsql package

2. GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSIT

How to Interpreting with AWR report ?

1. Load Profile
Here we know about Logical Read, Physical Read, Soft Parse, Hard Parse

2. Instance Efficiency Percentages
If your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.


3. Top 5 Timed Events
It is very important section in AWR report.
through this we can know most five wait event is effecting database performance.

4. SQL Statistics
It this section we will know about Execution time, cpu used, logical & physical read for top most sql statement.


5.Advisory Statistics
In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.

Back Deleted Datafiles

Q: How can u back deleted datafiles?

It is possible to brought back deleted datafile. To see how :

SQL> CREATE TABLESPACE test DATAFILE '/backup2/test_01.dbf' SIZE 10M;
SQL> ALTER TABLESPACE test ADD DATAFILE '/backup2/test_02.dbf' SIZE 800M ;
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ -----------------------------------------
TEST /backup2/test_01.dbf
TEST /backup2/test_02.dbf

SQL> alter tablespace test drop datafile '/backup2/test_02.dbf';
Tablespace altered.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------
TEST /backup2/test_01.dbf

SQL> ALTER TABLESPACE test ADD DATAFILE '/backup2/test_02.dbf' SIZE 800M REUSE;
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------
TEST /backup2/test_01.dbf
TEST /backup2/test_02.dbf

** there is no guaranty that the object stored on the deleted datafile are available when it brought back :)
so when such thing happen the ideal way to use backup dump or other database backup system ...

Autotrace Setting

SET AUTOTRACE TRACEONLY

Autotrace is a very useful feature that used to trace the cost of a sql query and execution plane oracle used for that query.

Any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. But you will get the following error if it is not enabled.

sql :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

To solve this do the followings:

1. Run plustrce.sql. The location of plustrce.sql is:
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
sql> @ .../plustrce.sql

2. Grant PLUSTRACE to the user : Sql> GRANT PLUSTRACE to user_name;

3. Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table , execute UTLXPLAN.sql location of the file is:
ON UNIX :-$ORACLE_HOME/rdbms/admin/utlxplan.sql
sql> @../utlxplan.sql

A User can use the AUTOTRACE options as follows:

sql> SET AUTOTRACE OFF ;
sql> SET AUTOTRACE ON EXPLAIN ;
sql> SET AUTOTRACE ON STATISTICS;
sql> SET AUTOTRACE ON;
sql> SET AUTOTRACE TRACEONLY;
- Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

Temp Tablespace

A temporary tablespace contains data that persists only for the duration of the session.

Temporary tablespaces can improve the concurrency of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. A temporary tablespace cannot contain permanent objects and therefore no need to back up. Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed.

Q: Why we need Temp tablespace?

For example, If you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

Assign temporary tablespce to a user:

SQL> CREATE USER prd DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;

SQL> ALTER USER prd TEMPORARY TABLESPACE temp;

TEMPORARY TABLESPACE Creation:

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.

TEMPFILES:

DROP: ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

If you remove all tempfiles from temp tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Then u have to add a TEMPFILE:

ADD: ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;

MONITORING:

Tempfile: V$TEMPFILE, DBA_TEMP_FILES.
Who occupies the spaceiews: v$sort_usage.
Temporary segments: v$sort_segment

DBA_FREE_SPACE doesn't record free space for temp tablespaces. Use V$TEMP_SPACE_HEADER:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

DEFAULT TEMP TABLESPACE:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Q: Find out the default temp tablespace for a database?

SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

The following restrictions apply to default temporary tablespaces:

The Default Temporary Tablespace must be of type TEMPORARY
The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

All new users that are not explicitly assigned a TEMPORARY TABLESPACE, will get the Default Temporary Tablespace as its TEMPORARY TABLESPACE.

DROP Default Temporary Tablespace:

SQL> DROP TABLESPACE temp;

drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform.

Step-1:

The first step you need to perform is create another temporary tablespace (lets call it TEMP2).
SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Step-2:

Next step is making TEMP2 the default temporary tablespace for the database.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Step-3:

Drop / recreate the TEMP tablespace to the size you want.
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Step-4:

Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Problem-1: I had a temporary tablepsace TEMP and there no space left on that tablespace.

Solution:

Create a new tablespace:

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE '/dump/TEMP_FILE/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Making the new tablespace default:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

Remove all the files other than the default file:

SQL> select file_name from dba_temp_files;

FILE_NAME
-----------------------------------------------
/oracle/temp_file/temp02_02.dbf
/oracle/temp_file/temp2_01.dbf
/dump/TEMP_FILE/temp01.dbf

SQL> ALTER DATABASE TEMPFILE '/oracle/temp_file/temp2_01.dbf' DROP INCLUDING DATAFILES;

SQL> ALTER DATABASE TEMPFILE '/oracle/temp_file/temp02_02.dbf' DROP INCLUDING DATAFILES;

ALTER DATABASE TEMPFILE '/oracle/temp_file/temp02_02.dbf' DROP INCLUDING DATAFILES
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

Reason- Some users are working with this temp file, so it is not deleting.
bash-3.00$ rm temp02_02.dbf

SQL> shutdown immediate;
SQL> startup

How to recover a dropped table?

FLASHBACK TABLE table_name TO BEFORE DROP;

Scenario: One of our developer accidentally dropped a table from local database. And we don't keep any rman backup, or regular dump for our local database. Then I had to recover the table from recyclebin.

There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN.
For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN.

The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter. When the recyclebin is enabled, any tables that you drop do not actually get deleted. Instead, when you drop a table, Oracle just renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

Example:
create table nahar_test1 (col varchar2(10), row_chng_dt date);
insert into nahar_test1 values ('Version1', sysdate);
commit;
Drop table nahar_test1;

select object_name, original_name, type
, can_undrop as "UND"
, can_purge as "PUR", droptime
from USER_RECYCLEBIN where original_name='NAHAR_TEST1';

alter session set nls_date_format='HH24:MI:SS';
select * from "BIN$j35MMYy7Q1LgRAAVFyazQA==$0";FLASHBACK TABLE nahar_test1 TO BEFORE DROP; -------------------wow!! The table Backed!!!

It's important to know that after you've dropped a table, it has only been renamed. The table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it (by flashback), or by purging (permanent delete) it from the recyclebin.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;

OBJECT_NAME ----- -----------------------------ORIGINAL_NAME ----- TYPE ---UND - PUR ----- DROPTIME
---------------------------------------------------------------------------------------------------------------------------------------------------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0----- TST -------------------TABLE ----YES -- YES ---006-09-01:16:10:12

PURGE TABLE "BIN$HGnc55/7rRPgQPeM/qQoRw==$0"; --------------Purged/Permanently deleted!

Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.

CONCLUSION:

The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order; you can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version's BIN$... name directly. Oracle drops most dependent objects along with the table, and restores them when the table is restored with FLASHBACK DROP, but does not restore their names. You can purge dependent objects separately to restore the table without them. Even after turning RECYCLEBIN OFF, you can FLASHBACK DROP objects that were already in the RECYCLEBIN.

Finding out the full table scans

There could be cases, queries are fetching too many rows by full tables scans and there are huge "consistent gets" causing the use of more cpu cycles. It might be the case that we missed an index or need to repartition tables. So, first of all we have to find out the full table scans.

SET LONG 1000000

COL object_name FORMAT A25
COL object_name FORMAT A25
COL sql_fulltext FORMAT A50

SELECT sp.object_name, dtab.num_rows, sa.sql_fulltext, sa.executions
FROM v$sql_plan sp
JOIN dba_tables dtab ON (dtab.table_name = sp.object_name)
JOIN v$sqlarea sa ON (sa.address = sp.address AND sa.hash_value =sp.hash_value)
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY sa.executions DESC;

Another faster sql version could be -

SELECT sp.object_name
,(SELECT num_rows FROM dba_tables WHERE table_name = sp.object_name AND owner = sp.object_owner) num_rows
,(SELECT sql_fulltext FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) sql_fulltext
,(SELECT executions FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) full_scans
FROM v$sql_plan sp
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY full_scans DESC;

Now we need to apply both technical and business knowledge to find out why those sqls are doing full scans and if re-indexing would be helpful or something out of the box!

Find out tables with Outdated statistics

SQL> SET SERVEROUTPUT ON

SQL>
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.'|| ObjList(i).ObjName || ''|| ObjList(i).ObjType || ''|| ObjList(i).partname);
END LOOP;
END;
/

To collect statistics of a outdated table of SCOTT schema:


exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE', cascade=>true, degree=>2, estimate_percent=>50, method_opt=>'FOR ALL COLUMNS SIZE 1');

To find schema level stats that are stale one can call-


DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT', objlist=>ObjList, options=>'LIST STALE');

Moving table(s) to a different tablespace

Q: What happens when u move a table to a different Tablespace?
Q: How can u move table to a different Tablespace which have long datatype?
Q: How can u move table to a different Tablespace with indexes?

There are a couple of ways in which a table can be moved to a different tablespace:

1) One of them is to perform export/import.
2) Another approach is to use the 'alter table' command with 'move tablespace' clause.

Practice:
CREATE TABLE TEST (
TEST_ID NUMBER(9) NOT NULL, TEST_DESC VARCHAR(10),
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID));

CREATE INDEX TEST_IND_1 ON TEST(TEST_DESC);

Above SQLs creates table and index in user's default tablespace. In our case, the default tablespace is "USERS". Run following SQL to check where these objects are created-

SELECT TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_TABLES
WHERE TABLE_NAME = 'TEST'
UNION ALL
SELECT INDEX_NAME,TABLESPACE_NAME,STATUS FROM USER_TABLES
WHERE TABLE_NAME = 'TEST';

TABLE_NAME TABLESPACE_NAME----------STATUS
----------------------- ----------------------------------------------------------
TEST -------------------- USERS -------------------------- VALID
TEST_IND_1 -------- USERS -------------------------- VALID
PK_TEST -------------- USERS -------------------------- VALID

INSERT INTO TEST VALUES(1,'Hello'); commit;

SQL> SELECT ROWID, TEST_ID FROM TEST;----------- [Retrieve value for TEST_ID and ROWID]

ROWID ----------------------------TEST_ID
-------------------------------------------------------------
AAAQ+eAAEAAAA3tAAA -------------1

SQL> ALTER TABLE TEST MOVE TABLESPACE SLMDATA; [Move TEST table to SLMDATA]

SQL> SELECT ROWID, TEST_ID FROM TEST;--------------------------[Check ROWID value again]

ROWID -------------------------- TEST_ID
--------------------------------------------------------
AAAQ+hAAHAAAAAsAAA ---------- 1

Moving table to new tablespace will make all the indexes on the table unusable.

TABLE_NAME-------------------------- TABLESPACE_NAME ----------STATUS
--------------------------------------------------- -----------------------------------------------------------------------------------------------
TEST ------------------------------------------------SLMDATA ---------------------VALID
TEST_IND_1 ------------------------------------USERS --------------------------UNUSABLE
PK_TEST ------------------------------------------USERS --------------------------UNUSABLE

As, indexes still point to older tablespace and are in 'unusable' status. Indexes in unusable state will prevent any DML activity on the table.

INSERT INTO TEST VALUES(2,'World!');
ERROR at line 1:
ORA-01502: index ‘DECIPHER.PK_TEST’ or partition of such index is in unusable State

Why this happened?
When we moved table to new tablespace, each row of the table got moved and got new ROWID. If we compare the ROWID values before and after the move, we'll realize that ROWID for the same TEST_ID is different. Indexes point to the previous location of the row and not to the current location. If we want to keep the index in the current tablespace (USERS), we just need to issue only rebuild clause. If we also want to move index to the new tablespace then we have to include tablespace clause. Following is the example of both.

ALTER INDEX PK_TEST REBUILD;
ALTER INDEX TEST_IND_1 REBUILD TABLESPACE SLMDATA;

First statement only rebuilds the primary key index without moving it to new tablespace.
Second statement rebuilds the index and also moves it to the other tablespace.

SELECT INDEX_NAME,TABLESPACE_NAME,STATUS FROM User_Indexes
WHERE TABLE_NAME = 'TEST';

INDEX_NAME TABLESPACE_NAME ---------------------STATUS
--------------------------------------------------------------------------------------------------
TEST_IND_1 SLMDATA ---------------------------------------------VALID
PK_TEST USERS --------------------------------------------------------VALID

One thing to remember is that 'MOVE TABLESPACE' does not work if table contains column with LONG or LONG RAW data type. You will run into ‘ORA-00997: illegal use of LONG datatype error. Such tables can be moved to new tablespace using exp/imp command.

Basic Checklist

1. Field length: Be aware of Database field length for corresponding UI fields. For text fields and text boxes, length must be smaller than Database field length (about 10 characters smaller for every 100 char in db). This is needed for dealing with multi-byte character set.

2. Blank space: No space before 'colon' and 'comma' - use space after comma.

3. Date: Proper formatting of date

4. Access Control/Privilege Checking: For important business information which depends on user privilege, make sure, list that user sees, are based on their access privileges

5. SQL injection: Use bind variables to avoid SQL injection

6. Double submission problems: What happens when user re-submit the request - handle properly.