ITPub博客

1. Monitor the Global System event for Latch free, up to now the wait time is hung about 655 secSQL> select event,time_waited from v$system_event where event='latch free';EVENT  &nbs...

一、什么情况下，操作信息会出现在V$SESSION_LONGOPS同时满足以下几个条件，操作信息才会出现在V$SESSION_LONGOPS中。1、操作是以下几种操作之一# Table scan;# Index Fast Full Scan;# Hash join;# Sort/Merge;# So...

到Oracle 10g， 通过查询time model statistics可以找出应用程序在哪一类数据库操作中耗费了最多的时间， 通过v$sys_time_model和v$sess_time_model这两个视图来实现。 下面这个SQL挺有用留下来做remark了。 WI...

For optimal performance, you’ll want to keep the library cache reload ratio [sum(reloads) /sum(pins)] at zero and the library cache hit ratio above 95 percent. If the reload ratio is not zero, then t...

The data dictionary cache is a key area to tune because the dictionary is accessed so frequently, especially by the internals of Oracle. At startup, the data dictionary cache contains no data. But as ...

Here is a query that will show you the time between log switches. It can be handy in determining if you have a problem:select b.recid,to_char(b.first_time,'dd-mon-yy hh:mi:ss') start_time, a.recid,to_...

Use ASSM instead of FREELISTS, FREELIST GROUPS, and PCTUSED.In version 9i of the database, Oracle introduced Automatic Segment Space Management, as an alternative to using free lists, in locally manag...

It is recommended that you regularly monitor your database to find segments that are growing to extreme numbers of extents (over a thousand) and then manage those segments appropriately.select seg...

You can look at the chained and migrated rows of a table or cluster using the ANALYZE statement with the LIST CHAINED ROWS clause. The results of this statement are stored in a specified table cre...

Get the snapshot with level=>6-------------------------------------------exec statspack.snap(i_snap_level => 6);exec statspack.snap(i_snap_level => 6);Generate the report -----------------------------...

1. Create a SQL Tuning Task-----------------------------------------------DECLAREalan_task_name VARCHAR2(30);alan_sqltext CLOB;BEGINalan_sqltext := 'SELECT /*+ ORDERED */ * ' ||'FROM employees e, ...

The size of the redo log files can influence performance, because the behavior. of the DBWR and ARCH process depend on the redo log sizes. Generally, largerredo log files provide better performance. U...

Here summarize totally 10 MISTAKES we will encouter during the Performance Testing or Database process Monitor Process:1. Bad Connection Management-----------------------------------------------The ap...

To assist in the rollout process, build a list of tasks that—if performed correctly—will increase the chance of optimal performance in production and—if there is aproblem—enable rapid debugging of...

有的时候，使用错误的索引会导致Oracle数据库的效率明显下降，通过一些方法或者是技巧可以有效的避免这个问题:这个例子中,如果我想使用idx_a而不是idx_b.SQL> create table test  2  (a int,b int,c int,d...

读了BT大师的一篇文章,发现statspack在解决性能问题的时候还可以这样用:http://blog.itpub.net/post/330/2230今天早上一来，数据库load就比往常高了许多。想想数据库唯一的变化是昨天早上我曾经重新分析过数据库对...

Problem:========You are experiencing performance problems.  Your applications have lots ofCOMMITs and ROLLBACKs.V$SYSTEM_EVENT.TOTAL_WAITS / TOTAL_TIMEOUTS / TIME_WAITED / AVERAGE_WAIThas high nu...

STATSPACK report for--数据库DB信息Database    DB Id    Instance     Inst Num Startup Time    Release     RAC~~~~~~~...

         Oracle在buffer cache内存管理中利用hash table来提高进程检索数据的速度，一般来说oracle利用hash算法将buffer cache分割成不同的hash bucket,每hash bucket都会...

ADDM能发现定位的问题包括：·操作系统内存页入页出问题·由于Oracle负载和非Oracle负载导致的CPU瓶颈问题·导致不同资源负载的Top SQL语句和对象——CPU消耗、IO带宽占用、潜在IO问题、RAC内部通讯繁忙 ·按照PLSQL...