-
Hi,
User is complaing system is slow.
How to start identify the problem..?
Thanks,
Harry
-
Look for your bottleneck. Typical bottlenecks are CPU, I/O, network, Inadequate Indexing, etc.
Check v$waitstat
Jeff Hunter
-
Oh, and 80% of performance problems are poorly written queries.
Jeff Hunter
-
SQL> /
CLASS COUNT TIME
------------------ ---------- ----------
data block 5158 0
sort block 0 0
save undo block 0 0
segment header 7 0
save undo header 0 0
free list 0 0
extent map 0 0
bitmap block 0 0
bitmap index block 0 0
unused 0 0
system undo header 0 0
CLASS COUNT TIME
------------------ ---------- ----------
system undo block 0 0
undo header 38 0
undo block 5 0
How can I move from here..?
Thanks
Harry
-
data block 5158 0
indicates you probably have some heavy I/O. I would check a couple things:
1. make sure your physical devices (disks) are not swamped. On solaris, use iostat -xdM
2. Make sure your top queries are using indexes. Look in v$sqltext, v$session, and v$process to find out your top offenders.
Jeff Hunter
-
Originally posted by marist89
Oh, and 80% of performance problems are poorly written queries.
I agree 100% on this one with Jeff!
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Which kind of queries will tell me that which particular User or application or query causing the problem.
I am new born DBA.
Thanks,
Harry
-
Another possible problem is:
undo header 38 0
# of rollback segments or # of extents of rbs.
u have to check it and increase # of rbs.
-
Originally posted by harrygulls
Which kind of queries will tell me that which particular User or application or query causing the problem.
I am new born DBA.
Thanks,
Harry
Code:
select b.username, a.buffer_gets reads,
a.executions exec, a.buffer_gets / decode
(a.executions, 0, 1, a.executions) rds_exec_ratio,
a.command_type, a.sql_text Statement
from v$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.buffer_gets > 100000
order by a.buffer_gets desc;
select b.username, a.disk_reads reads,
a.executions exec, a.disk_reads / decode
(a.executions, 0, 1, a.executions) rds_exec_ratio,
a.command_type, a.sql_text Statement
from v$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Thanks
But how I am gonna apply the results..?
How to analyze from these results.
Thanks
Harry
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|