Top SQLs in Oracle Database session

Finding Top rated SQL's is the key process while working on Database performance. Sure we can find these SQL's using AWR report but still we should know which view to access to find Top rated SQL's. PFB SQL's:-

1. TOP 10 SQL STATEMENT WITH LARGE NO. OF DISK READS :-

set lin 400
column "SQL_TEXT" format a100
column EXECUTIONS format 9999999
column DISK_READS format 999999999
column USER FORMAT a10
select substr(sql_text,0,100) as "SQL_TEXT",PARSING_SCHEMA_NAME as "USER",executions ,disk_reads from v$sqlarea  where decode(executions ,0,disk_reads,disk_reads/executions)
> (select avg(decode(executions,0,disk_reads,disk_reads/executions))  + stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea) and ROWNUM < 11 order by disk_reads desc
/

2. TOP 10 SQL STATEMENT WITH FULL TABLE SCANS :-

column "SQL_TEXT" format a100
column OPERATION format a12
column OPTIONS format a12
column USER FORMAT a10
select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",p.operation,p.options from v$sqlarea t, v$sql_plan p where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL' and p.object_owner not in ('SYS','SYSTEM') and ROWNUM < 11 order by DISK_READS DESC, EXECUTIONS DESC
/

3. TOP 10 SQL STATEMENT WITH MOST CPU UTILIZATION :-

column "SQL_TEXT" format a100
column EXECUTIONS format 9999999
column CPU_TIME format 999999999
column USER FORMAT a10
select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.CPU_TIME from v$sqlarea t where ROWNUM < 11 order by CPU_TIME DESC,EXECUTIONS DESC
/

4. TOP 10 SQL STATEMENT WITH MOST BUFFER GETS :-

column SQL_TEXT format a100
column EXECUTIONS format 9999999
column BUFFER_GETS format 999999999
column USER FORMAT a10
select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.BUFFER_GETS from v$sqlarea t where ROWNUM < 11 order by BUFFER_GETS DESC,EXECUTIONS DESC
/

5. TOP 10 SQL STATEMENT WITH MOST NO. OF EXECUTIONS :-

column SQL_TEXT format a100

column EXECUTIONS format 9999999

column USER FORMAT a10

select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS from v$sqlarea t where ROWNUM < 11 order by EXECUTIONS DESC

/

6. TOP 10 SQL STATEMENT WITH MOST NO. OF SORTS :-

column SQL_TEXT format a100
column EXECUTIONS format 9999999
column SORTS format 99999999
column USER FORMAT a10
select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.SORTS from v$sqlarea t where ROWNUM < 11 order by SORTS DESC,EXECUTIONS DESC
/

7. TOP 10 SQL STATEMENT WITH MOST SHARABLE MEMORY :-

column SQL_TEXT format a100

column EXECUTIONS format 9999999

column SHARABLE_MEM format 99999999

column USER FORMAT a10

select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.SHARABLE_MEM from v$sqlarea t where ROWNUM < 11 order by SHARABLE_MEM DESC,EXECUTIONS DESC

/

8. TOP 10 SQL STATEMENT WITH MOST NO. of PARSE CALLS :-

column SQL_TEXT format a100

column EXECUTIONS format 9999999

column PARSE_CALLS format 99999999

column USER FORMAT a10

select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.PARSE_CALLS from v$sqlarea t where ROWNUM < 11 order by PARSE_CALLS DESC,EXECUTIONS DESC

/

I hope this article helped you.

Regards,
Amit Rath

How to create a user or schema in Oracle

User or schema is a description of data in the database. It consists of all database objects related to a particular user. Schema is a collection of logical structures including tables,views,procedures,functions etc. 

A user in a database owns a schema . User and Schema have the same name. Create User Command creates a user, it automatically creates the schema for that user. 

Regarding creation of schema you need a tablespace where your user can store its objects.

Create a Tablespace for a user :-

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\AMIT.RATH\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\AMIT.RATH\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\AMIT.RATH\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\AMIT.RATH\ORADATA\ORCL\USERS01.DBF
C:\APP\AMIT.RATH\ORADATA\ORCL\EXAMPLE01.DBF

SQL> create tablespace amit datafile 'C:\APP\AMIT.RATH\ORADATA\ORCL\amit01.dbf' size 10m;

Command to create a user :-

1. Create a user having tablespace as default tablespace of database and temporary tablespace as default temporary tablespace of database.

SQL> create user amit identified by amit;

User created.

2. Create a user having different tablespsace as default tablespace of database and different temporary tablespace as default temporary tablespace of database.

SQL> create user amit identified by amit default tablespace test temporary tablespace temporary quota unlimited on test;         ---------- unlimited quota on test tablespace

SQL> create user amit identified by amit default tablespace test temporary tablespace temporary quota 100m on test;               ----------- 100m quota on test tablespace

3. Create a user by assigning a profile other than default profile of database :-

SQL> create user amit identified by amit default tablespace test temporary tablespace temporary quota unlimited on test profile AMIT_PROF; 

Providing Grants to a user :-

SQL> grant connect,resource to amit;

Now your user is ready to connect to database.

I hope this article helped you.

Regards,
Amit Rath

How to generate script to kill multiple Oracle sessions

Killing Oracle session :-

TO kill a oracle session you have to be very sure which session you want o kill otherwise you may kill any other session which is useful to you.

To Find your session and machine from which you are connected

SQL> select sid,serial#,status,machine,osuser,to_char(LOGON_TIME, 'DD-MON-YYYY hh24:mi:ss') as LOGON_TIME from v$session where username='USERNAME' order by logon_time;

Alter system kill session 'sid,serial#' immediate;

Disconnecting Oracle sessions :-

Disconnecting a session is similar to kill a session . Unlike Kill session asks session to kill itself, disconnect session kill the dedicated server process equivalent to killing from OS level.
Syntax wise disconnect has a additional clause called POST_TRANSACTION, it waits for ongoing transactions to complete before disconnecting the session while IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immeiately.

Alter system disconnect session 'sid,serial#' POST_TRANSACTION;
Alter system disconnect session 'sid,serial#' IMMEDIATE;

When in our database we have multiple inactive sessions and we want to kill all of them, then we can generate a script to kill all of them.

Small Script to kill multiple oracle sessions where status is INVALID:-

SQL> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE'

Small Script to kill multiple oracle sessions of a particular user :-

SQL> select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='USERNAME' ;

Finding how much a session is executed

SQL>col OPNAME for a20
SQL>col USERNAME for a18
SQL> col START_TIME for a25
SQL> select sid,serial#,opname,sofar,totalwork,username,to_char(start_time,'dd-mon-yyyy hh24:mi:ss') as "START_TIME",time_remaining from v$session_longops where username='SYSTEM' and time_remaining!=0;

I hope this article helped you.

Regards,
Amit Rath

dbms_metadata.get_ddl package, How to get ddl's of object's in the database

DDL 's of Objects in a Schema :-

select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME') from dual;


SQL> set lin 1000
SQL> set pagesize 9999
SQL> set long 9999
SQL> select dbms_metadata.get_ddl('TABLE','AMIT') from dual;

  CREATE TABLE "OWNER"."AMIT"

   (    "A" VARCHAR2(26),

        "B" VARCHAR2(10),

        "C" VARCHAR2(10),

        "D" VARCHAR2(20),

        "E" VARCHAR2(10),

        "F" DATE,

        "G" NUMBER,

        "H" VARCHAR2(20),

        "I" VARCHAR2(20),

        "J" VARCHAR2(5),

        "K" NUMBER,

        "L" NUMBER,

        "M" NUMBER,

        "N" NUMBER,

        "O" DATE,

        "P" DATE,

        "Q" VARCHAR2(255),

        "R" VARCHAR2(255),

        "S" NUMBER,

        "T" NUMBER,

        "U" VARCHAR2(10),

        "V" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

;

DDL 's of Objects in a Any Schema :-

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','USERNAME') from dual;

select dbms_metadata.get_ddl('INDEX','INDEX_NAME','USERNAME') from dual;

select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','USERNAME') from dual;

SQL> set lin 1000

SQL> set pagesize 9999

SQL> set long 9999

SQL> select dbms_metadata.get_ddl('TABLE','AMIT','OWNER') from dual;

  CREATE TABLE "OWNER"."AMIT"
   (    "A" VARCHAR2(26),
        "B" VARCHAR2(10),
        "C" VARCHAR2(10),
        "D" VARCHAR2(20),
        "E" VARCHAR2(10),
        "F" DATE,
        "G" NUMBER,
        "H" VARCHAR2(20),
        "I" VARCHAR2(20),
        "J" VARCHAR2(5),
        "K" NUMBER,
        "L" NUMBER,
        "M" NUMBER,
        "N" NUMBER,
        "O" DATE,
        "P" DATE,
        "Q" VARCHAR2(255),
        "R" VARCHAR2(255),
        "S" NUMBER,
        "T" NUMBER,
        "U" VARCHAR2(10),
        "V" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

;

Script to Generate DDL 's of Various Objects of database :-

Script for DDL 's of All Indexes of database:-

SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || index_name|| ''' ) from dual;'  from user_indexes;


SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='INDEX';

SQL> select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='INDEX' and owner not in ('SYS','SYSTEM');

Script for DDL's of all Tables of database:-

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || table_name|| ''' ) from dual;'  from user_tables;

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TABLE';

SQL> select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TABLE' and owner not in ('SYS','SYSTEM');

Script for DDL's of All Procedures of database:-

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE';

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='PROCEDURE' and owner='OWNER_NAME';

Script for DDL's of All Functions of database :-

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION';

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='FUNCTION' and owner='OWNER_NAME';

Script for DDL's of All Triggers of database:-

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER';

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='TRIGGER' and owner='OWNER_NAME';

Script for DDL's of All Views of database:-

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW';

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW' and owner not in ('SYS','SYSTEM');

SQL> select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  from dba_OBJECTS where object_type='VIEW' and owner='OWNER_NAME';

I hope this article helped you.

Regards,
Amit Rath

How to determine size of Schema or Index or Table in Oracle Database.

Size of a User or Schema

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' group by owner;

Size of INDEX

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='INDEX_NAME' group by segment_name;
OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='INDEX_NAME' group by owner,segment_name;

List of Size of all INDEXES of a USER

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='INDEX' group by segment_name order by "SIZE in GB" desc;
 OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all indexes

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner;

Size of table

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='TABLE_NAME' group by segment_name;
OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='TABLE_NAME' group by owner,segment_name;

 List of Size of all tables of a USER

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='TABLE' group by segment_name order by "SIZE in GB" desc
 OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='TABLE' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all tables

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='TABLE' group by owner;

I hope this article helped you.

Regards,
Amit Rath