Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our User Agreement and Privacy Policy.

Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our Privacy Policy and User Agreement for details.

This presentation presented a challenge because many of the concepts were like the chicken and the egg. In order to explain one I needed the other. I’ve tried to keep the subjects as linear as possible but sometimes I interweave from one to another.

This presentation presented a challenge because many of the concepts were like the chicken and the egg. In order to explain one I needed the other. I’ve tried to keep the subjects as linear as possible but sometimes I interweave from one to another.

This presentation presented a challenge because many of the concepts were like the chicken and the egg. In order to explain one I needed the other. I’ve tried to keep the subjects as linear as possible but sometimes I interweave from one to another.

This presentation presented a challenge because many of the concepts were like the chicken and the egg. In order to explain one I needed the other. I’ve tried to keep the subjects as linear as possible but sometimes I interweave from one to another.

WAIT_TIME is time of last wait, &amp;gt; 0 , means on CPU TIME_WAITED is the actual wait time

Graphics – humans can process 400,000 times as much data graphically as textually Think of a map of the US and all the counties with birth rates colored from low to high. It’s a massive about of information textually but it can all be seen on one page of a map Select session_id, session_serial# , count(*) , round((count(*)*100)/(5*60),0) &amp;quot;%&amp;quot;, lpad(&amp;apos;*&amp;apos;,round((count(*)*10)/(5*60),0),&amp;apos;*&amp;apos;) &amp;quot;Bar&amp;quot; from v$active_session_history where session_state= &amp;apos;ON CPU&amp;apos; and SAMPLE_TIME &amp;gt; sysdate - (5/(24*60)) group by session_id, session_serial# order by count(*) desc /

In Oracle Support I learned more faster than I think I could have anywhere. Porting gave me my first appreciation for the shareable nature of Oracle code and also a bit of disbelief that it worked as well as it did. Oracle France gave me an opportunity to concentrate on the Oracle kernel. At Oracle France I had 3 amazing experiences. First was being sent to the Europecar site where I first met a couple of the people who would later become the founding members of the Oaktable, James Morle and Anjo Kolk. The Europecar site introduced me to a fellow name Roger Sanders who first showed me the wait interface before anyone knew what it was. Roger not only used it but read it directly from shared memory without using SQL. Soon after Europecar I began to be sent often to benchmarks at Digital Europe. These benchmarks were some of my favorite work at Oracle. The benchmarks usually consisted of installing some unknown Oracle customer application and then having a few days to make it run as fast as possible. I first started using TCL/TK and direct shared memory access (DMA) at Digital Europe and got solid hands on tuning experience testing things like striping redo and proving it was faster long before people gave up arguing that this was bad from a theoretical point of view. Finally in France, my boss, Jean Yves Caleca was by far the best boss I ever had, but on top of that he was wonderful at exploring the depths of Oracle and explaining it to others, teaching me much about the internals of block structure, UNDO, REDO and Freelsits. I came back from France wanting to do performance work and especially graphical monitoring. The kernel performance group had limited scope in that domain, so I left for a dot com where I had my first run as the sole DBA for everything, backup, recovery, performance, installation and administration. I was called away by Quest who had my favorite performance tool Spotlight. It turns out thought that scope for expanding Spotlight was limited so I jumped at the chance in 2002 to restructure Oracle OEM. The work at OEM I’m proud of but still want to do much more to make performance tuning faster, easier and more graphical.

In Oracle Support I learned more faster than I think I could have anywhere. Porting gave me my first appreciation for the shareable nature of Oracle code and also a bit of disbelief that it worked as well as it did. Oracle France gave me an opportunity to concentrate on the Oracle kernel. At Oracle France I had 3 amazing experiences. First was being sent to the Europecar site where I first met a couple of the people who would later become the founding members of the Oaktable, James Morle and Anjo Kolk. The Europecar site introduced me to a fellow name Roger Sanders who first showed me the wait interface before anyone knew what it was. Roger not only used it but read it directly from shared memory without using SQL. Soon after Europecar I began to be sent often to benchmarks at Digital Europe. These benchmarks were some of my favorite work at Oracle. The benchmarks usually consisted of installing some unknown Oracle customer application and then having a few days to make it run as fast as possible. I first started using TCL/TK and direct shared memory access (DMA) at Digital Europe and got solid hands on tuning experience testing things like striping redo and proving it was faster long before people gave up arguing that this was bad from a theoretical point of view. Finally in France, my boss, Jean Yves Caleca was by far the best boss I ever had, but on top of that he was wonderful at exploring the depths of Oracle and explaining it to others, teaching me much about the internals of block structure, UNDO, REDO and Freelsits. I came back from France wanting to do performance work and especially graphical monitoring. The kernel performance group had limited scope in that domain, so I left for a dot com where I had my first run as the sole DBA for everything, backup, recovery, performance, installation and administration. I was called away by Quest who had my favorite performance tool Spotlight. It turns out thought that scope for expanding Spotlight was limited so I jumped at the chance in 2002 to restructure Oracle OEM. The work at OEM I’m proud of but still want to do much more to make performance tuning faster, easier and more graphical.

In Oracle Support I learned more faster than I think I could have anywhere. Porting gave me my first appreciation for the shareable nature of Oracle code and also a bit of disbelief that it worked as well as it did. Oracle France gave me an opportunity to concentrate on the Oracle kernel. At Oracle France I had 3 amazing experiences. First was being sent to the Europecar site where I first met a couple of the people who would later become the founding members of the Oaktable, James Morle and Anjo Kolk. The Europecar site introduced me to a fellow name Roger Sanders who first showed me the wait interface before anyone knew what it was. Roger not only used it but read it directly from shared memory without using SQL. Soon after Europecar I began to be sent often to benchmarks at Digital Europe. These benchmarks were some of my favorite work at Oracle. The benchmarks usually consisted of installing some unknown Oracle customer application and then having a few days to make it run as fast as possible. I first started using TCL/TK and direct shared memory access (DMA) at Digital Europe and got solid hands on tuning experience testing things like striping redo and proving it was faster long before people gave up arguing that this was bad from a theoretical point of view. Finally in France, my boss, Jean Yves Caleca was by far the best boss I ever had, but on top of that he was wonderful at exploring the depths of Oracle and explaining it to others, teaching me much about the internals of block structure, UNDO, REDO and Freelsits. I came back from France wanting to do performance work and especially graphical monitoring. The kernel performance group had limited scope in that domain, so I left for a dot com where I had my first run as the sole DBA for everything, backup, recovery, performance, installation and administration. I was called away by Quest who had my favorite performance tool Spotlight. It turns out thought that scope for expanding Spotlight was limited so I jumped at the chance in 2002 to restructure Oracle OEM. The work at OEM I’m proud of but still want to do much more to make performance tuning faster, easier and more graphical.

30.
vv$$aaccttiivvee__sseessssiioonn__hhiissttoorryy
SAMPLE_ID NUMBER
SAMPLE_TIME TIMESTAMP(3)
SESSION_ID NUMBER
SESSION_SERIAL# NUMBER
USER_ID NUMBER
SERVICE_HASH NUMBER
SESSION_TYPE VARCHAR2(10)
PROGRAM VARCHAR2(64)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)
SESSION_STATE VARCHAR2(7)
WAIT_TIME NUMBER
EVENT VARCHAR2(64)
EVENT_ID NUMBER
EVENT# NUMBER
SEQ# NUMBER
P1 NUMBER
P2 NUMBER
P3 NUMBER
WAIT_TIME NUMBER
TIME_WAITED NUMBER
CURRENT_OBJ# NUMBER
CURRENT_FILE# NUMBER
CURRENT_BLOCK# NUMBER0
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_PLAN_HASH_VALUE NUMBER
SQL_OPCODE NUMBER
QC_SESSION_ID NUMBER
QC_INSTANCE_ID NUMBER
TIME_WAITED NUMBER
When
Session
State
Wait
SQL
Duration

34.
Counting is the key to ASH
AAS = DB Time/elapsed ~ = count(*)/elapsed
Select count(*)/{time period in seconds} AAS
from ASH
where {criteria}
and {time period};
AAS = load on the database

35.
AAAASS FFoorrmmuullaass
Use CPU count as yardstick:
 AAS < 1
Database is not blocked
 AAS ~= 0
Database basically idle
Problems are in the APP not DB
 AAS < # of CPUs
CPU available
Are any single sessions 100% active?
• AAS > # of CPUs
Could have performance problems
 AAS >> # of CPUS
There is a bottleneck
Ideal world –
one database
solution
track CPU at OS
AAS > 1
still want to know
if a single user is
100% active

42.
TToopp WWaaiittiinngg SSeessssiioonn
• in last 5 minutes
Select
session_id,
count(*)
from
v$active_session_history
where
session_state=‘WAITING’ and
SAMPLE_TIME > SYSDATE - (5/(24*60))
group by
session_id
order by
count(*) desc;
Select
session_id,
count(*)
from
v$active_session_history
where
session_state=‘WAITING’ and
SAMPLE_TIME > SYSDATE - (5/(24*60))
group by
session_id
order by
count(*) desc;