Oracle runing sql

currently running sql


col UNAM format a20 word heading 'User'
col STMT format a56 word heading 'Statement'
col RUNT format a08 word heading 'Run Time'
col ltim format a20 word heading 'Logon Time'
col etim format a20 word heading 'Connect Time'
col PROG format a30 word heading 'Program|Client Terminal Details'
col SID format a10 word heading 'SID/|Serial#'
col DR format 999999999 heading 'Disk Reads'
col BG format 999999999 heading 'Buffer Gets'
col thread format 99999 heading 'ThreadID
col sqltext format A64 wrap heading 'Last SQL'

break on unam on sid on status

select S.USERNAME||'('||s.sid||')-'||s.osuser UNAM
-- ,s.program||'-'||s.terminal||'('||s.machine||')' PROG
,s.sid||'/'||s.serial# sid
,s.status "Status",p.spid
,sql_text sqltext
from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address
and p.addr=s.paddr(+)
and t.hash_value = s.sql_hash_value
order by s.sid,t.piece
/


User Serial# Status SPID Last SQL
-------------------- ---------- -------- ------------------------ ----------------------------------------------------------------
SYS(18)-oracle 18/1367 INACTIVE 1212 SELECT DBMS_HM.get_run_report('test_ANUJ2') from dual
SYS(49)-oracle 49/207 ACTIVE 16349 select S.USERNAME||'('||s.sid||')-'||s.osuser UNAM
-- ,

16349 s.program||'-'||s.terminal||'('||s.machine||')' PROG
,s.sid||'/'

16349 ||s.serial# sid
,s.status "Status",p.spid
,sql_text sqltext
from

16349 v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.add

16349 ress =s.sql_address
and p.addr=s.paddr(+)
and t.hash_value = s.s

16349 ql_hash_value
order by s.sid,t.piece


7 rows selected.

Oracle USER DETAILS

prompt
prompt user details :

clear breaks
clear computes
clear columns

select username,profile,default_tablespace,temporary_tablespace from dba_users;

select * from nls_database_parameters;

select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

select owner,object_type,count(1) from dba_objects group by owner,object_type
order by owner ;

SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;

Orcale PROBLEMATIC QUERY

HEAVY LOAD SQL
load sql
problem sql


column load format a6 justify right
column executes format 9999999
break on load on executes skip 1
select
substr(to_char(s.pct, '99.00'), 2) || '%' load,
s.executions executes,
p.sql_text
from
(
select
address,
disk_reads,
executions,
pct,
rank() over (order by disk_reads desc) ranking
from
(
select
address,
disk_reads,
executions,
100 * ratio_to_report(disk_reads) over () pct
from
sys.v_$sql
where
command_type != 47 ---->> = PL/SQL EXECUTE
and PARSING_SCHEMA_NAME not in
('SYS',
'SYSTEM',
'ORACLE_OCM',
'SYSMAN',
'WMSYS',
'XDB',
'ANONYMOUS',
'CTXSYS',
'MGMT_VIEW',
'OPS$ROOT',
'OPS$ORACLE',
'DBSNMP',
'OPS$DMADMIN',
'JUDEK',
'MDDATA',
'TSMSYS',
'DIP',
'ORDSYS',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'OUTLN',
'MDSYS')
) ------
where
disk_reads > 50 * executions
) s,
sys.v_$sqltext p
where
s.ranking <= 2 and
p.address = s.address
order by 1, s.address, p.piece
/

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from audit_actions order by action
2 /

ACTION NAME
---------- ----------------------------
0 UNKNOWN
1 CREATE TABLE
2 INSERT
3 SELECT
4 CREATE CLUSTER
5 ALTER CLUSTER
6 UPDATE
7 DELETE
8 DROP CLUSTER
9 CREATE INDEX
10 DROP INDEX
11 ALTER INDEX
12 DROP TABLE
13 CREATE SEQUENCE
14 ALTER SEQUENCE
15 ALTER TABLE
16 DROP SEQUENCE
17 GRANT OBJECT
18 REVOKE OBJECT
19 CREATE SYNONYM
20 DROP SYNONYM
21 CREATE VIEW
22 DROP VIEW
23 VALIDATE INDEX
24 CREATE PROCEDURE
25 ALTER PROCEDURE
26 LOCK
27 NO-OP
28 RENAME
29 COMMENT
30 AUDIT OBJECT
31 NOAUDIT OBJECT
32 CREATE DATABASE LINK
33 DROP DATABASE LINK
34 CREATE DATABASE
35 ALTER DATABASE
36 CREATE ROLLBACK SEG
37 ALTER ROLLBACK SEG
38 DROP ROLLBACK SEG
39 CREATE TABLESPACE
40 ALTER TABLESPACE
41 DROP TABLESPACE
42 ALTER SESSION
43 ALTER USER
44 COMMIT
45 ROLLBACK
46 SAVEPOINT
47 PL/SQL EXECUTE
48 SET TRANSACTION
49 ALTER SYSTEM
50 EXPLAIN
51 CREATE USER
52 CREATE ROLE
53 DROP USER
54 DROP ROLE
55 SET ROLE
56 CREATE SCHEMA
57 CREATE CONTROL FILE
59 CREATE TRIGGER
60 ALTER TRIGGER
61 DROP TRIGGER
62 ANALYZE TABLE
63 ANALYZE INDEX
64 ANALYZE CLUSTER
65 CREATE PROFILE
66 DROP PROFILE
67 ALTER PROFILE
68 DROP PROCEDURE
70 ALTER RESOURCE COST
71 CREATE MATERIALIZED VIEW LOG
72 ALTER MATERIALIZED VIEW LOG
73 DROP MATERIALIZED VIEW LOG
74 CREATE MATERIALIZED VIEW
75 ALTER MATERIALIZED VIEW
76 DROP MATERIALIZED VIEW
77 CREATE TYPE
78 DROP TYPE
79 ALTER ROLE
80 ALTER TYPE
81 CREATE TYPE BODY
82 ALTER TYPE BODY
83 DROP TYPE BODY
84 DROP LIBRARY
85 TRUNCATE TABLE
86 TRUNCATE CLUSTER
91 CREATE FUNCTION
92 ALTER FUNCTION
93 DROP FUNCTION
94 CREATE PACKAGE
95 ALTER PACKAGE
96 DROP PACKAGE
97 CREATE PACKAGE BODY
98 ALTER PACKAGE BODY
99 DROP PACKAGE BODY
100 LOGON
101 LOGOFF
102 LOGOFF BY CLEANUP
103 SESSION REC
104 SYSTEM AUDIT
105 SYSTEM NOAUDIT
106 AUDIT DEFAULT
107 NOAUDIT DEFAULT
108 SYSTEM GRANT
109 SYSTEM REVOKE
110 CREATE PUBLIC SYNONYM
111 DROP PUBLIC SYNONYM
112 CREATE PUBLIC DATABASE LINK
113 DROP PUBLIC DATABASE LINK
114 GRANT ROLE
115 REVOKE ROLE
116 EXECUTE PROCEDURE
117 USER COMMENT
118 ENABLE TRIGGER
119 DISABLE TRIGGER
120 ENABLE ALL TRIGGERS
121 DISABLE ALL TRIGGERS
122 NETWORK ERROR
123 EXECUTE TYPE
128 FLASHBACK
129 CREATE SESSION
130 ALTER MINING MODEL
131 SELECT MINING MODEL
133 CREATE MINING MODEL
134 ALTER PUBLIC SYNONYM
157 CREATE DIRECTORY
158 DROP DIRECTORY
159 CREATE LIBRARY
160 CREATE JAVA
161 ALTER JAVA
162 DROP JAVA
163 CREATE OPERATOR
164 CREATE INDEXTYPE
165 DROP INDEXTYPE
166 ALTER INDEXTYPE
167 DROP OPERATOR
168 ASSOCIATE STATISTICS
169 DISASSOCIATE STATISTICS
170 CALL METHOD
171 CREATE SUMMARY
172 ALTER SUMMARY
173 DROP SUMMARY
174 CREATE DIMENSION
175 ALTER DIMENSION
176 DROP DIMENSION
177 CREATE CONTEXT
178 DROP CONTEXT
179 ALTER OUTLINE
180 CREATE OUTLINE
181 DROP OUTLINE
182 UPDATE INDEXES
183 ALTER OPERATOR
192 ALTER SYNONYM
197 PURGE USER_RECYCLEBIN
198 PURGE DBA_RECYCLEBIN
199 PURGE TABLESPACE
200 PURGE TABLE
201 PURGE INDEX
202 UNDROP OBJECT
204 FLASHBACK DATABASE
205 FLASHBACK TABLE
206 CREATE RESTORE POINT
207 DROP RESTORE POINT
208 PROXY AUTHENTICATION ONLY
209 DECLARE REWRITE EQUIVALENCE
210 ALTER REWRITE EQUIVALENCE
211 DROP REWRITE EQUIVALENCE
212 CREATE EDITION
213 ALTER EDITION
214 DROP EDITION
215 DROP ASSEMBLY
216 CREATE ASSEMBLY
217 ALTER ASSEMBLY
218 CREATE FLASHBACK ARCHIVE
219 ALTER FLASHBACK ARCHIVE
220 DROP FLASHBACK ARCHIVE
225 ALTER DATABASE LINK
305 ALTER PUBLIC DATABASE LINK

177 rows selected.







SQL QUERY RESPONSIBLE FOR MOST DISK READS (PROBLEMATIC QUERY):


SELECT disk_reads, executions, disk_reads/executions, sql_text FROM v$sqlarea
WHERE disk_reads > 5000
ORDER BY disk_reads;


SQL responsible for the most buffer hits (PROBLEMATIC QUERY):

SELECT buffer_gets, executions, buffer_gets/executions, sql_text
FROM v$sqlarea
WHERE buffer_gets > 100000
ORDER BY buffer_gets;


SQL Performance Diagnostics Scripts
I use the following SQL Scripts for finding top SQLs that may cause performance degradation.

Top SQL by Disk Reads

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by disk_reads desc nulls last;


Top SQL by Buffer Gets

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by buffer_gets desc nulls last;


Top SQL by CPU

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000)
"CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets
"Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end
"Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))
"Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module
"Module"
from v$sql s
order by cpu_time desc nulls last;


Top SQL by Executions

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000)
"CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets
"Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end
"Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))
"Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module
"Module"
from v$sql s
order by executions desc nulls last;

plsql exception zero_divide

declare

the_ratio number;

lower_limit constant number:=1.72;

cursor c is
select empno,nvl(comm,0) comm from emp;

begin

-- open c /* not required */

for x in c loop

begin

the_ratio:=(x.empno/x.comm);

if the_ratio>lower_limit then
dbms_output.put_line('the ratio>lower limit:'||to_char(the_ratio));
else
dbms_output.put_line('the ratioend if;
exception
when zero_divide then
dbms_output.put_line('error zero divide');

end;
end loop;
end;

Oracle active session report ( ASH report )

How much data is in your historical view, you can info from the
DBA_HIST_ACTIVE_SESS_HISTORY view:

Manually Getting Active Session Information



col Min_time format a25
col Max_time format a25
SELECT min(sample_time) Min_time ,max(sample_time) Max_time
FROM dba_hist_active_sess_history;

MIN_TIME MAX_TIME
------------------------- -------------------------
25-SEP-11 02.00.45.233 AM 04-OCT-11 11.40.04.639 AM


SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1267852645 ORCL 1 orcl


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified: text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1267852645 1 ORCL orcl apt-amd-02

Defaults to current database

Using database id: 1267852645

Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.

Using instance number(s): 1





ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available: 25-Sep-11 15:00:33 [ 12835 mins in the past]
Latest ASH sample available: 04-Oct-11 12:55:30 [ 0 mins in the past]


Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:

-- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)

Defaults to -15 mins
Enter value for begin_time: -50
Report begin time specified: -50

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration:
Report duration specified:

Using 04-Oct-11 12:06:22 as report begin time
Using 04-Oct-11 12:56:43 as report end time