General process for Query Tuning

Create a plan table

SQL>@?/rdbms/admin/utlxplan.sql

Autotrace

To switch it on:

SQL>column plan_plus_exp format a100

SQL>set autotrace on explain
# Displays the execution plan only.
SQL>set autotrace traceonly explain
# dont run the query
SQL>set autotrace on
# Shows the execution plan as well as statistics of the statement.
SQL>set autotrace on statistics
# Displays the statistics only.
SQL>set autotrace traceonly
# Displays the execution plan and the statistics


To switch it off:

SQL>set autotrace off

Explain plan

SQL>explain plan for
select ...

or...
SQL>explain plan set statement_id = 'bad1' for
select...

Then to see the output...
SQL>set lines 100 pages 999
SQL>@?/rdbms/admin/utlxpls

Put something unique in the like clause

SQL>select hash_value, sql_text
from v$sqlarea
where sql_text like '%TIMINGLINKS%FOLDERREF%'
/

Grab the sql associated with a hash

SQL>select sql_text
from v$sqlarea
where hash_value = '&hash'
/


Look at a query's stats in the sql area

SQL>select executions
, cpu_time
, disk_reads
, buffer_gets
, rows_processed
, buffer_gets / executions
from v$sqlarea
where hash_value = '&hash'
/

Cool Scripts

Cool script to know the details of a Particular user.

+++++++++++++++++++++++++++++++++++++++++++++++
-- user_conf.sql

set lines 100 pages 999
set verify off
set feedback off

undefine user

accept userid prompt 'Enter username:'

select username
, default_tablespace
, temporary_tablespace
from dba_users
where username = '&userid'
/

select tablespace_name
, decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas
where username = upper('&&userid')
/

select granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option')

"ROLE"
from dba_role_privs
where grantee = upper('&&userid')
/

select privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option')

"PRIV"
from dba_sys_privs
where grantee = upper('&&userid')
/

undefine user
set verify on
set feedback on

+++++++++++++++++++++++++++++++++++++++++++++++

Cool script to CLONE a Particular user.

+++++++++++++++++++++++++++++++++++++++++++++++
-- user_clone.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

select username
from dba_users
order by username
/

undefine user

accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '

select username
, created
from dba_users
where lower(username) = lower('&newuser')
/

accept poo prompt 'Continue? (ctrl-c to exit)'

--spool /tmp/user_clone_tmp.sql
spool D:\temp\testing\user_clone_tmp.sql

select 'create user ' || '&newuser' ||
' identified by ' || '&passwd' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace || ';' "user"
from dba_users
where username = '&userid'
/

select 'alter user &newuser quota '||
decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M') ||
' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = '&&userid'
/

select 'grant ' ||granted_role || ' to &newuser' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from dba_role_privs
where grantee = '&&userid'
/

select 'grant ' || privilege || ' to &newuser' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from dba_sys_privs
where grantee = '&&userid'
/

spool off

undefine user

set verify on
set feedback on
set heading on

@D:\temp\testing\user_clone_tmp.sql

host del D:\temp\testing\user_clone_tmp.sql

--@/tmp/user_clone_tmp.sql

--!rm /tmp/user_clone_tmp.sql

NOTE: Username should be Entered in Captitals.

+++++++++++++++++++++++++++++++++++++++++++++++

Save the scripts as .sql and run at the command prompt.

How to Create and Use OMF

OMF indicates Oracle Managed Files. With the use of Oracle-managed files the administration of an Oracle Database can be simplified. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle Database. You specify operations in terms of database objects rather than filenames.

Enable the Creation of OMFs
--------------------------------
The following initialization parameters allow the database server to use the Oracle-managed files feature.

1)DB_CREATE_FILE_DEST: Defines the location of the default file system directory where the database creates datafiles or tempfiles when no file specification is given in the creation operation. It is also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

2)DB_CREATE_ONLINE_LOG_DEST_n:Defines the location of the default file system directory for redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.

3)DB_RECOVERY_FILE_DEST:Defines the location of the default file system directory where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.

An Example of using OMF :
---------------------------
1)Setting the parameter for the session:

SQL> alter session set db_create_file_dest='/oradata';
Session altered.

2)Create Tablespace using OMF:

SQL> create tablespace omf_tbs;
Tablespace created.

3)Check the data file Location:

SQL> select file_name from dba_data_files where tablespace_name='OMF_TBS';
FILE_NAME
--------------------------------------------------------------------------------
/oradata/EDR/datafile/o1_mf_omf_tbs_4067w4op_.dbf

Here EDR is the Database Name.

The dafault location for datafile is Your settings for parameter/Database Name/datafile/Unique Name.dbf

Oracle11g: Where’s My Alert Log?

Just a short blog entry about Oracle 11g.
One of the first things that caught me by surprise with 11g,
when I first started in the beta program, was that the default
location for the alert log has moved. It is still placed
under the traditional OFA structure, but not /u01/app/oracle/admin.
There is a new directory called diag that resides
in /u01/app/oracle as seen on one of my systems:


[oracle@oracle11gr1 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl
[oracle@oracle11gr1 orcl]$ ll
total 64
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 alert
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 cdump
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 hm
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 incident
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 incpkg
drwxr-x--- 2 oracle oinstall 4096 Jan 24 01:38 ir
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 lck
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 metadata
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 stage
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 sweep
drwxr-x--- 2 oracle oinstall 24576 Apr 19 15:33 trace

[oracle@oracle11gr1 orcl]$ cd trace
[oracle@oracle11gr1 trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 392 Apr 19 15:33 alert_orcl.log


In this case, my database is called orcl and the instance is orcl.

View : v$diag_info

If Alert log file is lost

One of my friend asked me whether if in any case the
alert log file is lost what happens?

It is automatically created whenever there needs to
be a new entry into the alert log.

Lets see a practical explanation

[oracle@oracle11gr1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Apr 19 15:35:05 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn /as sysdba
Connected.
SQL>
SQL>!

[oracle@oracle11gr1]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 144278 Apr 19 15:26 alert_orcl.log

[oracle@oracle11gr1 trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ mv alert_orcl.log alert_orcl_bak.log

[oracle@oracle11gr1 trace]$ll alert_orcl.log
ls: alert_orcl.log: No such file or directory

[oracle@oracle11gr1 trace]$exit

SQL>
SQL> CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 2 3

Tablespace created.

SQL>
SQL>
SQL> !
[oracle@oracle11gr1 ~]$
[oracle@oracle11gr1 ~]$
[oracle@oracle11gr1 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 392 Apr 19 15:33 alert_orcl.log

[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ more alert_orcl.log
Sat Apr 19 15:33:15 2008
CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

[oracle@oracle11gr1 trace]$

Capture all SQL run between two points in time

There are situations where it is useful to capture the SQL
that a particular user is running in the database. Usually
you would simply enable session tracing for that user, but
there are two potential problems with that approach.

The first is that many web based applications maintain a
pool of persistent database connections which are shared
amongst multiple users. The second is that some applications
connect, run some SQL and disconnect very quickly, making it
tricky to enable session tracing at all (you could of course
use a logon trigger to enable session tracing in this case).

A quick and dirty solution to the problem is to capture all
SQL statements that are run between two points in time.

The following procedure will create two tables, each containing
a snapshot of the database at a particular point. The tables
will then be queried to produce a list of all SQL run during
that period.

If possible, you should do this on a quiet development system -
otherwise you risk getting way too much data back.

1. Take the first snapshot
Run the following sql to create the first snapshot:

create table sql_exec_before as
select executions
, hash_value
from v$sqlarea
/

2. Get the user to perform their task within the application

3. Take the second snapshot

select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/

4. Check the results
Now that you have captured the SQL it is time to query the results.

This first query will list all query hashes that have been executed:
select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/

This one will display the hash and the SQL itself:

set pages 999 lines 100
break on hash_value
select hash_value
, sql_text
from v$sqltext
where hash_value in (
select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
)
order by
hash_value
, piece
/

5. Tidy up

Don't forget to remove the snapshot tables once you've finished:

drop table sql_exec_before
/

drop table sql_exec_after
/


Source : www.shutdownabort.com

How to find Client IP address by Quering Oracle Database ?

We can use PLSQL package UTL_INADDR.

There are two procedures'

1. GET_HOST_NAME or
2. GET_HOST_ADDRESS

for finding out local machine HOST name or IP address.

UTL_INADDR

We can use below query to findout CLIENT IP ADDRESS.

SQL> col machine for a20
SQL> col UTL_INADDR.GET_HOST_ADDRESS(MACHINE) for a40

SQL> select sid,machine,UTL_INADDR.GET_HOST_ADDRESS (machine)
from v$session
where type = 'USER' and username is not null
order by sid;

SID MACHINE UTL_INADDR.GET_HOST_ADDRESS(MACHINE)
---------- -------------------- ----------------------------------------
152 edrserver 10.21.1.249

=====================================================
If you are getting below error message then modify above code little bit.

ERROR at line 1:ORA-29257: host xxxxxxxxxxxxxxxxxx unknown

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

==================Modified code======================

SQL> select sid, machine,
UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1)) ip
from v$session
where type='USER' and username is not null
order by sid;

SID MACHINE IP
---------- -------------------- ------------------------------
152 edrserver 10.21.1.249