oracle快速定位数据库瓶颈
–数据库巡检或性能优化方法各异，但首要的是要发现数据库性能瓶颈，系统自带的statspack,或awr太耗时.
- 查询数据库等待事件top10,关注前前几个等待事件，关注前三个等待事件是否有因果或关联关系–oracle 9i
select t2.event,round(100t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from (
SELECT SUM(time_waited) w1 FROM v s y s t e m e v e n t W H E R E e v e n t N O T I N ( ′ s m o n t i m e r ′ , ′ p m o n t i m e r ′ , ′ r d b m s i p c m e s s a g e ′ , ′ N u l l e v e n t ′ , ′ p a r a l l e l q u e r y d e q u e u e ′ , ′ p i p e g e t ′ , ′ c l i e n t m e s s a g e ′ , ′ S Q L ∗ N e t m e s s a g e t o c l i e n t ′ , ′ S Q L ∗ N e t m e s s a g e f r o m c l i e n t ′ , ′ S Q L ∗ N e t m o r e d a t a f r o m c l i e n t ′ , ′ d i s p a t c h e r t i m e r ′ , ′ v i r t u a l c i r c u i t s t a t u s ′ , ′ l o c k m a n a g e r w a i t f o r r e m o t e m e s s a g e ′ , ′ P X I d l e W a i t ′ , ′ P X D e q : E x e c u t i o n M s g ′ , ′ P X D e q : T a b l e Q N o r m a l ′ , ′ w a k e u p t i m e m a n a g e r ′ , ′ s l a v e w a i t ′ , ′ i / o s l a v e w a i t ′ , ′ j o b q s l a v e w a i t ′ , ′ n u l l e v e n t ′ , ′ g c s r e m o t e m e s s a g e ′ , ′ g c s f o r a c t i o n ′ , ′ g e s r e m o t e m e s s a g e ′ , ′ q u e u e m e s s a g e s ′ ) ) t 1 , ( s e l e c t ∗ f r o m ( s e l e c t t . e v e n t , t . t o t a l w a i t s , t . t o t a l t i m e o u t s , t . t i m e w a i t e d , t . a v e r a g e w a i t , r o w n u m n u m f r o m ( s e l e c t e v e n t , t o t a l w a i t s , t o t a l t i m e o u t s , t i m e w a i t e d , a v e r a g e w a i t f r o m v system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') ) t1, (select * from ( select t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum num from (select event,total_waits,total_timeouts,time_waited,average_wait from v systemeventWHEREeventNOTIN(′smontimer′,′pmontimer′,′rdbmsipcmessage′,′Nullevent′,′parallelquerydequeue′,′pipeget′,′clientmessage′,′SQL∗Netmessagetoclient′,′SQL∗Netmessagefromclient′,′SQL∗Netmoredatafromclient′,′dispatchertimer′,′virtualcircuitstatus′,′lockmanagerwaitforremotemessage′,′PXIdleWait′,′PXDeq:ExecutionMsg′,′PXDeq:TableQNormal′,′wakeuptimemanager′,′slavewait′,′i/oslavewait′,′jobqslavewait′,′nullevent′,′gcsremotemessage′,′gcsforaction′,′gesremotemessage′,′queuemessages′))t1,