公司的ORACLE数据库为何变慢了

tomdk

硬件配置：DELL POWEREDGE 2400 CPU 1G，MEM：512,10G硬盘,3个
软件配置：WIN2000 AD + ORACLE 8.0.5 + TUXEDO
运行时间:2年

数据库结构:C盘放ORACLE程序文件,D盘存放数据文件,E盘为备份文件

目前的情况:应用程序在调用XXX表(该表有24万条数据)时,会占用CPU 100%的资源,但内存仍有90M的空闲可用.
请高手指点,如何在ORACLE数据库中查出占用CPU资源过高的SQL语句,谢谢!
急啊,再不搞定,要挨P了.

づ★sl战神

这个。。。。。。。。。。。
我只知道查进程的。。。。。
写出来你看看，
或许对你有点帮助吧。。。。。。。。。
你要是知道答案了记得告诉我一下啊
谢谢你了！

づ★sl战神

typedef struct _THREAD_INFO
{
LARGE_INTEGER CreateTime;
DWORD dwUnknown1;
DWORD dwStartAddress;
DWORD StartEIP;
DWORD dwOwnerPID;
DWORD dwThreadId;
DWORD dwCurrentPriority;
DWORD dwBasePriority;
DWORD dwContextSwitches;
DWORD Unknown;
DWORD WaitReason;

}THREADINFO, *PTHREADINFO;

typedef struct _UNICODE_STRING
{
USHORT Length;
USHORT MaxLength;
PWSTR Buffer;

} UNICODE_STRING;

typedef struct _PROCESS_INFO
{
DWORD dwOffset;
DWORD dwThreadsCount;
DWORD dwUnused1[6];
LARGE_INTEGER CreateTime;
LARGE_INTEGER UserTime;
LARGE_INTEGER KernelTime;
UNICODE_STRING ProcessName;

DWORD dwBasePriority;
DWORD dwProcessID;
DWORD dwParentProcessId;
DWORD dwHandleCount;
DWORD dwUnused3[2];

DWORD dwVirtualBytesPeak;
DWORD dwVirtualBytes;
ULONG dwPageFaults;
DWORD dwWorkingSetPeak;
DWORD dwWorkingSet;
DWORD dwQuotaPeakPagedPoolUsage;
DWORD dwQuotaPagedPoolUsage;
DWORD dwQuotaPeakNonPagedPoolUsage;
DWORD dwQuotaNonPagedPoolUsage;
DWORD dwPageFileUsage;
DWORD dwPageFileUsagePeak;

DWORD dCommitCharge;
THREADINFO ThreadSysInfo[1];

} PROCESSINFO, *PPROCESSINFO;


//每秒钟查询一次
void __fastcall TForm1::Timer1Timer(TObject *Sender)
{
Button2Click(NULL);
}
//---------------------------------------------------------------------------

void __fastcall TForm1::Button2Click(TObject *Sender)
{
PVOID pProcInfo = NULL;
DWORD dwInfoSize = 0x20000;
PPROCESSINFO pProcessInfo;
DWORD dwWorkingSet;
long ( __stdcall *NtQuerySystemInformation )( DWORD, PVOID, DWORD, DWORD );


static __int64 LastTotalProcessCPUUsage = 0;
static __int64 LastCurrentProcessCPUUsage = 0;

int CurrentDelta;
int TotalDelta;

__int64 TotalProcessCPUUsage = 0;
__int64 CurrentProcessCPUUsage = 0;

/////////////////////////////////

pProcInfo = (PVOID)(new byte[dwInfoSize]);

NtQuerySystemInformation = (long(__stdcall*)(DWORD,PVOID,DWORD,DWORD))
GetProcAddress( GetModuleHandle( "ntdll.dll" ),"NtQuerySystemInformation" );

NtQuerySystemInformation(5,pProcInfo,dwInfoSize,0);

pProcessInfo = (PPROCESSINFO)pProcInfo;

do
{
TotalProcessCPUUsage += (__int64)pProcessInfo->;KernelTime.QuadPart + (__int64)pProcessInfo->;UserTime.QuadPart;

if(pProcessInfo->;dwProcessID == GetCurrentProcessId())
{
dwWorkingSet = pProcessInfo->;dwWorkingSet;
CurrentProcessCPUUsage += (__int64)pProcessInfo->;KernelTime.QuadPart + (__int64)pProcessInfo->;UserTime.QuadPart;
}

/////////
if(pProcessInfo->;dwOffset == 0)
{
break;
}

pProcessInfo = (PPROCESSINFO)((byte*)pProcessInfo + pProcessInfo->;dwOffset);
}
while(true);

TotalDelta = TotalProcessCPUUsage - LastTotalProcessCPUUsage;
CurrentDelta = CurrentProcessCPUUsage - LastCurrentProcessCPUUsage;

if(TotalDelta != 0)
this->;Caption = "CPU = " + IntToStr(100 * CurrentDelta / TotalDelta) +
"Memory = "+ IntToStr(dwWorkingSet / 1024) " K";

LastTotalProcessCPUUsage = TotalProcessCPUUsage;
LastCurrentProcessCPUUsage = CurrentProcessCPUUsage;

delete[] pProcInfo;
}

づ★sl战神

对
忘了
不好意思
在一个特定时间段内计算特定进程的总时间和所有进程的总时间，它们的比值就是那个特定进程的确CPU占有率，这里我采用1秒种。

tomdk

多谢指点~~~不明白的是，我应该在哪里使用你提供的这段程序啊？
在SQL/PLUS中可直接运行吗？
别笑我笨，我没写过程序的

pearaep

select address,hash_value,buffer_gets,executions,buffer_gets/executions "Gets/Exec",sql_text
  from v$sqlarea
where buffer_gets>;50000
   and executions>;0
order by 3;