Greetings,
As in last question, i am getting these stats from awr. I tried to dig it up more for wait class concurrecy wait class.
Event Waits Time(s) Avg wait(ms) % DB time Wait Class
---------------------------------------------------------------------------------------------
SQL*Net more data from client 35,972 9,805 273 65.90 Network
cursor: pin S wait on X 159 5,688 35770 38.23 Concurrency
DB CPU 1,629 10.95
enq: TX - row lock contention 9 136 15133 0.92 Application
direct path write temp 63,834 82 1 0.55 User I/O
I found that, parse cpu to parse elapsed % is just 1.65% which is very poor.
Instance efficiency percentage
-------------------------------
Buffer Nowait %: 100.00
Redo NoWait %: 100.00
Buffer Hit %: 95.60
In-memory Sort %: 100.00
Library Hit %: 99.93
Soft Parse %: 95.40
Execute to Parse %:99.37
Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 1.65
% Non-Parse CPU: 92.25
I thought the reason to be shared pool contention. But found out the size of the pool was not a problem.
Shared Pool Statistics
----------------------------------------------
Begin End
Memory Usage %: 54.31 55.64
% SQL with executions>1: 98.61 98.44
% Memory for SQL w/exec>1: 90.01 82.19Am i analyzing wrong? Client has reported of very slow query response.
What can you suggest me by the statistics? And where should i dig for finding out the exact bottleneck?
Thank you in advance.
As we said in answer to your last question, you need to:
Trace the client session that is slow! i.e. the thing that the client is reporting is slow.
Stop looking at system stats!
You can trace a database session by calling:
exec DBMS_monitor.session_trace_enable ( <sessionid>, <serial num>, true, true );
Then dig up the trace file from the database server and parse it with TKPROF.
You can read more about how to do this at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof Once you have the formatted trace file, come back with your findings and we'll see what we can do to help.