AWR (Automatic Workload Repository):
What is AWR?
AWR reports collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information.
What is default interval period between two awr report ?
By default, AWR automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 7 days.
GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSITORY
The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).
How to Manage & change interval period for awr report ?
There is two to modify or changes in AWR report.
1. Manually ---> through DBMS_WORKLOAD_REPOSITORY plsql package
2. GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSIT
How to Interpreting with AWR report ?
1. Load Profile
Here we know about Logical Read, Physical Read, Soft Parse, Hard Parse
2. Instance Efficiency Percentages
If your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.
3. Top 5 Timed Events
It is very important section in AWR report.
through this we can know most five wait event is effecting database performance.
4. SQL Statistics
It this section we will know about Execution time, cpu used, logical & physical read for top most sql statement.
5.Advisory Statistics
In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.
10.22.2008
11. Find the value of Bind variable
Select NAME,POSITION,DATATYPE_STRING,VALUE_STRING from v$sql_bind_capture
where sql_id='d9kf91muzy2wq';
where sql_id='d9kf91muzy2wq';
10. Find connected session,login_time in a given time
SELECT
a.sid,a.serial#,a.username,a.osuser,a.machine
,a.service_name
,to_char(a.logon_time, 'hh24:mi dd/mm/yy') login_time
FROM
v$session a,v$process b
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr
AND a.type='USER'
AND a.logon_time > to_date ('2008-10-18 15:00:00','YYYY-MM-DD HH24:MI:SS')
AND a.logon_time < to_date ('2008-10-19 15:00:00','YYYY-MM-DD HH24:MI:SS')
Order by a.sid,a.username;
a.sid,a.serial#,a.username,a.osuser,a.machine
,a.service_name
,to_char(a.logon_time, 'hh24:mi dd/mm/yy') login_time
FROM
v$session a,v$process b
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr
AND a.type='USER'
AND a.logon_time > to_date ('2008-10-18 15:00:00','YYYY-MM-DD HH24:MI:SS')
AND a.logon_time < to_date ('2008-10-19 15:00:00','YYYY-MM-DD HH24:MI:SS')
Order by a.sid,a.username;
9. Find the SID
Set linesize 200
column USERNAME format a10
column MACHINE format a20
column OSUSER format a10
column SERVICE_NAME format a35
SELECT
a.sid,a.serial#,b.spid,b.pid,a.username,a.osuser,a.machine
FROM
v$session a,v$process b
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr
Order by a.sid,a.username;
column USERNAME format a10
column MACHINE format a20
column OSUSER format a10
column SERVICE_NAME format a35
SELECT
a.sid,a.serial#,b.spid,b.pid,a.username,a.osuser,a.machine
FROM
v$session a,v$process b
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr
Order by a.sid,a.username;
8. Time related DBA queries/scripts
1. Which SQL taking more CPU time
SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('10/16/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('10/18/2008','MM/DD/YYYY')) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC;
SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('10/16/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('10/18/2008','MM/DD/YYYY')) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC;
7. Wait related DBA queries..
SELECT distinct wait_class#, wait_class FROM v$event_name ORDER BY wait_class#;
SELECT wait_class_id, wait_class#, wait_class, total_waits, time_waited
FROM v$system_wait_class
order by time_waited desc;
6. What object is currently causing the highest resource waits?
SQL> column OBJECT_NAME format a30
SQL> column EVENT format a30
select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc;
SQL> column EVENT format a30
select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc;
5. What SQL is currently using the most resources?
select active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
order by 4 desc;
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
order by 4 desc;
4. What user is waiting the most?
Select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;
3. Last 30 minutes result those resources that are in high demand on your system.
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2;
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2;
2. Identify heavy SQL (Get the SQL with heavy DISK_READS)
Select sql_text ,executions ,disk_reads ,buffer_gets
from v$sqlarea
where decode(executions ,0,disk_reads,disk_reads/executions)
> (select avg(decode(executions,0,disk_reads,disk_reads/executions))
+ stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea)
and parsing_user_id !=3D;
from v$sqlarea
where decode(executions ,0,disk_reads,disk_reads/executions)
> (select avg(decode(executions,0,disk_reads,disk_reads/executions))
+ stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea)
and parsing_user_id !=3D;
1. Identify heavy SQL (Get the SQL with heavy BUFFER_GETS)
Select sql_text ,executions ,disk_reads ,buffer_gets
from v$sqlarea
where decode(executions,0,buffer_gets,buffer_gets/executions)
> (select avg(decode(executions,0,buffer_gets,buffer_gets/executions))
+ stddev(decode(executions,0,buffer_gets,buffer_gets/executions))
from v$sqlarea) and parsing_user_id !=3D;
where decode(executions,0,buffer_gets,buffer_gets/executions)
> (select avg(decode(executions,0,buffer_gets,buffer_gets/executions))
+ stddev(decode(executions,0,buffer_gets,buffer_gets/executions))
from v$sqlarea) and parsing_user_id !=3D;
10.20.2008
Difference between Host variable and Bind Variable
HOST VARIABLES:
BIND VARIABLES:
- Host variables are the key to communication between your host program and Oracle.
- Typically, a precompiler program inputs data from a host variable to Oracle, and Oracle outputs data to a host variable in the program.
- Oracle stores input data in database columns, and stores output data in program host variables.
BIND VARIABLES:
- A host variable can be any arbitrary C expression that resolves to a scalar type. But, a host variable must also be an lvalue. Bind variables are used in SQL and PL/SQL statements for holding data or result sets.
- They are commonly used in SQL statements to optimize statement perrformance.
- A statement with a bind variable may be re-executed multiple times without needing to be re-parsed.
- Their values can be set and referenced in PL/SQL blocks.
- They can be referenced in SQL statements bind variable references should be prefixed with a colon
Execute to parse ratio
Execute to parse ratio:
Execute to parse ratio is a measure of how many times you execute a sql statement versus parse it.
This 'ratio' will go towards 100 as the number of executes goes up and up and up, while the number of parses remains the same.
This will go to zero as the number of parses and executes are equal.
This will go negative if you parse more than you execute.
That percentage is computed as: round(100*(1-:prse/:exe),2)
Your developers have all of the control here - we cannot change this ratio without touching the way the application interacts with the database.
When Execute to Parse values is too low:
That means, someone is parsing statements highly, but not executing properly. They are just chewing up your CPU, latching the shared pool, killing your performance.
If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1%.
How could parse be bigger than execution? Or, Why Execute to Parse values become too low?
(http://www.dba-oracle.com/m_parse_to_execute_ratio.htm)
There may be tools that parse a statement but never execute it. Or they parse it to discover what inputs/outputs it takes and then later on parse it again to execute it (so the parse/execute is 2:1).
It is commonly referred to as "inefficient coding techniques". They parse like there is no tomorrow -- works great in a single user environment -- starts to stink with two users (or more).
Not using bind variable in query can cause such low value.
Some applications (generic apps typically) parse a "select * from T" to describe a table, never execute the cursor -- their parses exceed their executes.
Oracle does what it is told to do. Oracle is told by you to PARSE. If you do not tell Oracle to execute the statement, it won't. That is how you have parse but no execute.
How I can identify those SQL's, Which PARSE but never EXECUTE:
v$sql -- look at the parse and execute counts.
Example:
select PARSE_CALLS,EXECUTIONS,SQL_TEXT
from v$sql
where executions = 0 and parse_calls > 0
order by parse_calls asc;
Select PARSE_CALLS, EXECUTIONS, SQL_TEXT
from v$sql
where executions < PARSE_CALLS
order by parse_calls;
How they can be eliminated or efficiently coded ?
By only parsing a statment ONCE per session,
not once per execution
not once to "describe a table"
not once to "describe the ith column in a table"
When Parse is very high and execution is very low, Check in coding --
cursor-sharing=force must be on?
**Make cursor-sharing=similar. That will reduce hard parse.
(http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314)
SQL> show parameter cursor_sharing
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.
Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter. Cursor_sharing=similar allows for bind variable "peeking", and in my opinion, it's too buggy to use until Oracle 11i, when you get adaptive cursor sharing. (http://www.dba-oracle.com/t_cursor_sharing_similar.htm)
If you have a routine that will be executed many times to generate a page, you should definitely parse once per page and execute it over and over, closing the cursor if necessary before your connection is returned to the connection pool.
It is 100% controlled by the client code and cannot be affected by the server settings at all. It is 100% a function of the number of times the client parses a sql statement and how many times they execute it.
How a Statement given for parsing cannot Execute?
Example:
dbms_sql.parse ( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
That just parsed the query, described it (to find the number of columns, data types, names, sizes, etc).
It never executed it -- If you closed the cursor, you would have a PARSE without an execute.
cursor sharing and parse:
Cursor sharing cannot reduce the NUMBER of parse calls (only your coders can do that! really - that is the ONLY way to reduce parse calls, they developers must call PARSE less often!)
cursor sharing can reduce the NUMBER OF HARD PARSES, that is all.
Hard parse:
A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small or when you have non-reusable SQL statements without host variables and bind variables.
http://www.ooug.org/2005slides/0720/ToolsIuse/trace_no.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314
Execute to parse ratio is a measure of how many times you execute a sql statement versus parse it.
This 'ratio' will go towards 100 as the number of executes goes up and up and up, while the number of parses remains the same.
This will go to zero as the number of parses and executes are equal.
This will go negative if you parse more than you execute.
That percentage is computed as: round(100*(1-:prse/:exe),2)
Your developers have all of the control here - we cannot change this ratio without touching the way the application interacts with the database.
When Execute to Parse values is too low:
That means, someone is parsing statements highly, but not executing properly. They are just chewing up your CPU, latching the shared pool, killing your performance.
If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1%.
How could parse be bigger than execution? Or, Why Execute to Parse values become too low?
It is possible that the application is not using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like excessive parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.
(http://www.dba-oracle.com/m_parse_to_execute_ratio.htm)
There may be tools that parse a statement but never execute it. Or they parse it to discover what inputs/outputs it takes and then later on parse it again to execute it (so the parse/execute is 2:1).
It is commonly referred to as "inefficient coding techniques". They parse like there is no tomorrow -- works great in a single user environment -- starts to stink with two users (or more).
Not using bind variable in query can cause such low value.
Some applications (generic apps typically) parse a "select * from T" to describe a table, never execute the cursor -- their parses exceed their executes.
Oracle does what it is told to do. Oracle is told by you to PARSE. If you do not tell Oracle to execute the statement, it won't. That is how you have parse but no execute.
How I can identify those SQL's, Which PARSE but never EXECUTE:
v$sql -- look at the parse and execute counts.
Example:
select PARSE_CALLS,EXECUTIONS,SQL_TEXT
from v$sql
where executions = 0 and parse_calls > 0
order by parse_calls asc;
Select PARSE_CALLS, EXECUTIONS, SQL_TEXT
from v$sql
where executions < PARSE_CALLS
order by parse_calls;
How they can be eliminated or efficiently coded ?
By only parsing a statment ONCE per session,
not once per execution
not once to "describe a table"
not once to "describe the ith column in a table"
When Parse is very high and execution is very low, Check in coding --
cursor-sharing=force must be on?
SQL> show parameter cursor_sharing
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.
Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter. Cursor_sharing=similar allows for bind variable "peeking", and in my opinion, it's too buggy to use until Oracle 11i, when you get adaptive cursor sharing. (http://www.dba-oracle.com/t_cursor_sharing_similar.htm)
If you have a routine that will be executed many times to generate a page, you should definitely parse once per page and execute it over and over, closing the cursor if necessary before your connection is returned to the connection pool.
It is 100% controlled by the client code and cannot be affected by the server settings at all. It is 100% a function of the number of times the client parses a sql statement and how many times they execute it.
How a Statement given for parsing cannot Execute?
Example:
dbms_sql.parse ( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
That just parsed the query, described it (to find the number of columns, data types, names, sizes, etc).
It never executed it -- If you closed the cursor, you would have a PARSE without an execute.
cursor sharing and parse:
Cursor sharing cannot reduce the NUMBER of parse calls (only your coders can do that! really - that is the ONLY way to reduce parse calls, they developers must call PARSE less often!)
cursor sharing can reduce the NUMBER OF HARD PARSES, that is all.
Hard parse:
A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small or when you have non-reusable SQL statements without host variables and bind variables.
http://www.ooug.org/2005slides/0720/ToolsIuse/trace_no.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314
10.19.2008
Oracle DBA scripts - Partition related
set pages 999 lines 100
col table_name format a40
col high_value format a20
col tablespace_name format a20
1. List partitioned tables
select table_name, partitioning_type type, partition_count partitions
from dba_part_tables
where owner = '&owner'
order by 1;
2. List a tables partitions
select partition_name , tablespace_name , high_value
from dba_tab_partitions
where table_owner = '&owner' and table_name = '&table_name'
order by partition_position;
col table_name format a40
col high_value format a20
col tablespace_name format a20
1. List partitioned tables
select table_name, partitioning_type type, partition_count partitions
from dba_part_tables
where owner = '&owner'
order by 1;
2. List a tables partitions
select partition_name , tablespace_name , high_value
from dba_tab_partitions
where table_owner = '&owner' and table_name = '&table_name'
order by partition_position;
Subscribe to:
Posts (Atom)