TAF is not working on Rac One Node in case of node failure ? Well it depends
Martin Bach did a good study of Rac One Node capability and especially of what happens with session failover during a database relocation or during a node failure into this blog post.
He showed us that:
- during a database relocation (initiated manually with srvctl relocate database) the TAF works as expected.
- during a node failure the TAF did not work and you will be disconnected (for a running select or a new execution)
It’s important: I said “during a node failure“, it means no instances are available during the select.
But what’s about a session already connected to a TAF service before the node failure that :
- will be inactive during the node failure
- will launch a select after the node failure (means an instance is back)
For this one an node failure is transparent, let’s see that in action:
First check my database is a Rac One Node:
srvctl config database -d BDTO | grep -i type Type: RACOneNode
Now let’s check that the bdto_taf service i’ll connect on is a TAF one:
srvctl config service -s BDTO_TAF -d BDTO | egrep -i "taf|failover" Service name: BDTO_TAF Failover type: SELECT Failover method: BASIC TAF failover retries: 0 TAF failover delay: 0 TAF policy specification: BASIC
Well everything is in place to test.
So connect to my Rac One Node database using the TAF service:
sqlplus bdt/"test"@bdto_taf SQL*Plus: Release 11.2.0.3.0 Production on Thu May 16 14:39:36 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS'; Session altered. SQL> select sysdate||' '||host_name from v$instance; SYSDATE||''||HOST_NAME -------------------------------------------------------------------------------- 2013/05/16 14:39:47 BDTSRV1
Now let’s kill smon:
ps -ef | grep -i smon | grep -i BDTO_1
oracle 8035 1 0 14:30 ? 00:00:00 ora_smon_BDTO_1
kill -9 `ps -ef | grep -i smon | grep -i BDTO_1 | awk '{print $2}'`
Let’s wait an instance is back on a node (Important step)
In this case the instance has been relocated on the other node, let’s see it is up:
ps -ef | grep -i smon | grep -i BDTO_1 oracle 29266 1 0 14:54 ? 00:00:00 ora_smon_BDTO_1
and come back to our “idle” sqlplus session to re-launch the sql:
SQL> / SYSDATE||''||HOST_NAME -------------------------------------------------------------------------------- 16-MAY-13 BDTSRV2
Oh ! As you can see the “idle” sqlplus session has not been disconnected and we have been able to re-launch the query.
As you can see we lost our nls_date_format setting. This is expected as explained into Martin’s Book “Pro Oracle Database 11g RAC on Linux“:
“
Also, note that while TAF can reauthorize a session, it does not restore the session to its previous
state. Therefore, following an instance failure, you will need to restore any session variables, PL/SQL
package variables, and instances of user-defined types. TAF will not restore these values automatically,
so you will need to extend your applications to restore them manually.
“
Conclusion:
If you are using a TAF service to connect to a Rac One Node database (of course through the OCI), then a node failure is transparent if the session is “idle” during the time there is no instance available.
When you think of it, it is quite normal as the TAF is more or less nothing more than a OCI / SQL*net feature, so once the TAF service is back, then TAF can work as expected.
Remarks:
- If the “idle” connection has an “opened” transaction, you’ll receive the “ORA-25402: transaction must roll back” oracle error (This is not so transparent anymore
). - Do not test TAF connected as the oracle sys user as SYSDBA Sessions do not failover with SRVCTL TAF configured, see MOS [ID 1342992.1]
- If you are using srvctl to test TAF”s reaction on node failure then you should read the MOS note [ID 1324574.1] first (as the TAF may not work depending of the version and srvctl options being used)
- For a good explanation of Rac One Node, you should have a look to this Aman Sharma’s blog post.
Why i wrote this post ?
Because into Martin’s post, i put a comment on ”December 13, 2012 at 19:08″ trying to explain this behavior. I came back to this comment yesterday and i realized that my comment was not so clear ! I hope this post is clear 
ASM Preferred Read: Collect performance metrics thanks to my amsiostat utility and SLOB 2
Posted by bdrouvot in Perl Scripts on 05/05/2013
Some times ago i provided a way to see the ASM Preferred Read feature in action thanks to Kevin Closson’s SLOB kit and my asmiostat utility into this blog post. Since that time Kevin released SLOB 2.
One of its new feature is that it now supports RAC so that we don’t need to launch SLOB on each node anymore as it has been done into my previous post.
Let’s see how we can show the ASM preferred read feature in action and collect performance metrics thanks to SLOB 2.
First, let’s create two services SLOB_BDT1 and SLOB_BDT2 respectively on BDT_1 and BDT_2 instances:
$ srvctl add service -s SLOB_BDT2 -d BDTO -r BDTO_2 $ srvctl start service -s SLOB_BDT2 -d BDTO $ srvctl add service -s SLOB_BDT1 -d BDTO -r BDTO_1 $ srvctl start service -s SLOB_BDT1 -d BDTO $ srvctl status service -s SLOB_BDT1 -d BDTO Service SLOB_BDT1 is running on instance(s) BDTO_1 $ srvctl status service -s SLOB_BDT2 -d BDTO Service SLOB_BDT2 is running on instance(s) BDTO_2
Now let’s configure the slob.conf so that the SLOB’s sessions will be distributed over those 2 services with a “round-robin” manner and no updates triggered:
$ grep -i sqlnet slob.conf ADMIN_SQLNET_SERVICE=slob_bdt SQLNET_SERVICE_BASE=slob_bdt SQLNET_SERVICE_MAX=2 $ grep -i UPDATE_PCT slob.conf | head -1 UPDATE_PCT=0
Let’s configure the ASM preferred read parameters:
SQL> alter system set asm_preferred_read_failure_groups='BDT_ONLY.WIN' sid='+ASM1'; System altered. SQL> alter system set asm_preferred_read_failure_groups='BDT_ONLY.JMO' sid='+ASM2'; System altered.
As we want to see the ASM preferred read in action, we need to configure our BDT_1 and BDT_2 instances in such a way that the SLOB run will generate physical IOs.
For this purpose, I use those settings:
alter system set "_db_block_prefetch_limit"=0 scope=spfile sid='*'; alter system set "_db_block_prefetch_quota"=0 scope=spfile sid='*'; alter system set "_db_file_noncontig_mblock_read_count"=0 scope=spfile sid='*'; alter system set "cpu_count"=1 scope=spfile sid='*'; alter system set "db_cache_size"=4m scope=spfile sid='*'; alter system set "shared_pool_size"=500m scope=spfile sid='*'; alter system set "sga_target"=0 scope=spfile sid='*';
You can find a very good description, on how to use SLOB for PIO testing into this flashdba’s blog post.
Now we are ready to launch the SLOB 2 run:
$ ./runit.sh 16
and see the preferred read in action as well as its associated performance metrics thanks to my asmiostat utility that way:
$ ./real_time.pl -type=asmiostat -dg=BDT_ONLY -show=dg,inst,fg
with the following result:
Great, data have been read from their preferred read failure groups We can also see their respectives performance metrics.
Conclusion:
Thanks to Kevin’s SLOB 2 kit and my asmiostat utility we are now able to check the ASM preferred read performance metrics with a single SLOB run.
Diagnose Adaptive Cursor Sharing (ACS) per execution for non monitored sql
Posted by bdrouvot in Perl Scripts on 04/05/2013
Into this blog post: Diagnose Adaptive Cursor Sharing (ACS) per execution in 11.2 i provided a way to check if ACS came into play per execution of a given sql.
You should read the previous post to understand this one.
As you can see i retrieved for the bind variables the “peeked” and the “passed” values.
The “passed” values come from the v$sql_monitor.binds_xml column: This information could be useful but is not mandatory to check if ACS came into play (as the check rely on the “peeked” values).
So we can get rid of the “passed” values (and then of the v$sql_monitor view) to check where ACS came into play per execution for non monitored sql.
For this purpose, let’s modify the sql introduced into the previous post that way:
SQL> !cat binds_peeked_acs.sql
set linesi 200 pages 999 feed off verify off
col bind_name format a20
col end_time format a19
col start_time format a19
col peeked format a20
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
select
pee.sql_id,
ash.starting_time,
ash.end_time,
(EXTRACT(HOUR FROM ash.run_time) * 3600
+ EXTRACT(MINUTE FROM ash.run_time) * 60
+ EXTRACT(SECOND FROM ash.run_time)) run_time_sec,
pee.plan_hash_value,
pee.bind_name,
pee.bind_pos,
pee.bind_data peeked,
--first_value(pee.bind_data) over (partition by pee.sql_id,pee.bind_name,pee.bind_pos order by end_time rows 1 preceding) previous_peeked,
case when pee.bind_data = first_value(pee.bind_data) over (partition by pee.sql_id,pee.bind_name,pee.bind_pos order by end_time rows 1 preceding) then 'NO' else 'YES' end "ACS"
from
(
select
p.sql_id,
p.child_number,
p.child_address,
c.bind_name,
c.bind_pos,
p.plan_hash_value,
case
when c.bind_type = 1 then utl_raw.cast_to_varchar2(c.bind_data)
when c.bind_type = 2 then to_char(utl_raw.cast_to_number(c.bind_data))
when c.bind_type = 96 then to_char(utl_raw.cast_to_varchar2(c.bind_data))
else 'Sorry: Not printable try with DBMS_XPLAN.DISPLAY_CURSOR'
end bind_data
from
v$sql_plan p,
xmltable
(
'/*/peeked_binds/bind' passing xmltype(p.other_xml)
columns bind_name varchar2(30) path '/bind/@nam',
bind_pos number path '/bind/@pos',
bind_type number path '/bind/@dty',
bind_data raw(2000) path '/bind'
) c
where
p.other_xml is not null
) pee,
(
select
sql_id,
sql_child_number,
sql_exec_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time
from
v$active_session_history
group by sql_id,sql_child_number,sql_exec_id,sql_exec_start
) ash
where
pee.sql_id=ash.sql_id and
pee.child_number=ash.sql_child_number and
pee.sql_id like nvl('&sql_id',pee.sql_id)
order by 1,2,3,7 ;
Let’s see the result with the same test as Diagnose Adaptive Cursor Sharing (ACS) per execution in 11.2:
SQL> @binds_peeked_acs.sql Enter value for sql_id: bu9367qrhq28t SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED ACS ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- --- bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OWNER 1 BDT NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OWNER 1 BDT NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_OWNER 1 ME YES bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_OWNER 1 BDT YES bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_OWNER 1 ME YES bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_OWNER 1 ME NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_OBJECT_ID 3 1 NO
So, same result as in the previous post except that the bind variable “passed” values have been lost.
Conclusion:
We are able to check for which execution ACS came into play for non monitored sql (as we get rid of the bind variable “passed” values and as a consequence we don’t query the v$sql_monitor view anymore).
Remark:
You need to purchase the Diagnostic Pack in order to be allowed to query the “v$active_session_history” view
Diagnose Adaptive Cursor Sharing (ACS) per execution in 11.2
Posted by bdrouvot in Perl Scripts on 01/05/2013
As you know oracle introduced a new feature “Adaptive cursor sharing (ACS)” in 11g. You can find a very good explanation of what it is into this Maria Colgan’s blog post.
So, as Maria said: “A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are.“
That’s fine, but I would like to see per execution of a given sql_id, if the Adaptive Cursor Sharing feature came into play.
Let’s define “When ACS comes into play” means: ACS comes into play for a particular execution:
- if the peeked values (The ones that generate the execution plan) changed compare to the previous execution.
- if this execution is not the first one that has been executed after the initial hard parse.
For this, I adapted the query that I use to retrieve “peeked” and “passed” bind values per execution into this blog post that way:
SQL>!cat binds_peeked_passed_acs.sql
set linesi 200 pages 999 feed off verify off
col bind_name format a20
col end_time format a19
col start_time format a19
col peeked format a20
col passed format a20
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
select
pee.sql_id,
ash.starting_time,
ash.end_time,
(EXTRACT(HOUR FROM ash.run_time) * 3600
+ EXTRACT(MINUTE FROM ash.run_time) * 60
+ EXTRACT(SECOND FROM ash.run_time)) run_time_sec,
pee.plan_hash_value,
pee.bind_name,
pee.bind_pos,
pee.bind_data peeked,
--first_value(pee.bind_data) over (partition by pee.sql_id,pee.bind_name,pee.bind_pos order by end_time rows 1 preceding) previous_peeked,
run_t.bind_data passed,
case when pee.bind_data = first_value(pee.bind_data) over (partition by pee.sql_id,pee.bind_name,pee.bind_pos order by end_time rows 1 preceding) then 'NO' else 'YES' end "ACS"
from
(
select
p.sql_id,
p.sql_child_address,
p.sql_exec_id,
c.bind_name,
c.bind_pos,
c.bind_data
from
v$sql_monitor p,
xmltable
(
'/binds/bind' passing xmltype(p.binds_xml)
columns bind_name varchar2(30) path '/bind/@name',
bind_pos number path '/bind/@pos',
bind_data varchar2(30) path '/bind'
) c
where
p.binds_xml is not null
) run_t
,
(
select
p.sql_id,
p.child_number,
p.child_address,
c.bind_name,
c.bind_pos,
p.plan_hash_value,
case
when c.bind_type = 1 then utl_raw.cast_to_varchar2(c.bind_data)
when c.bind_type = 2 then to_char(utl_raw.cast_to_number(c.bind_data))
when c.bind_type = 96 then to_char(utl_raw.cast_to_varchar2(c.bind_data))
else 'Sorry: Not printable try with DBMS_XPLAN.DISPLAY_CURSOR'
end bind_data
from
v$sql_plan p,
xmltable
(
'/*/peeked_binds/bind' passing xmltype(p.other_xml)
columns bind_name varchar2(30) path '/bind/@nam',
bind_pos number path '/bind/@pos',
bind_type number path '/bind/@dty',
bind_data raw(2000) path '/bind'
) c
where
p.other_xml is not null
) pee,
(
select
sql_id,
sql_exec_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time
from
v$active_session_history
group by sql_id,sql_exec_id,sql_exec_start
) ash
where
pee.sql_id=run_t.sql_id and
pee.sql_id=ash.sql_id and
run_t.sql_exec_id=ash.sql_exec_id and
pee.child_address=run_t.sql_child_address and
pee.bind_name=run_t.bind_name and
pee.bind_pos=run_t.bind_pos and
pee.sql_id like nvl('&sql_id',pee.sql_id)
order by 1,2,3,7 ;
So, i simply added this line:
case when pee.bind_data = first_value(pee.bind_data) over (partition by pee.sql_id,pee.bind_name,pee.bind_pos order by end_time rows 1 preceding) then 'NO' else 'YES' end "ACS"
This new line:
- Will result in ‘YES’ if the value of a “peeked” bind variable changed compare to the previous execution.
- Will result in “NO” if this is the first execution after the hard parse or the value of a peeked variable did not change compare to the previous execution.
Let’s test it:
There is a data skew on the owner column which has one index on it. The data distribution is the following:
SQL> select owner,count(*) from bdt2 group by owner; OWNER COUNT(*) ------------------------------ ---------- BDT 13848830 ME 100098
Let’s query the table that way:
SQL> var my_owner varchar2(50) SQL> var my_date varchar2(30) SQL> var my_object_id number SQL> exec :my_owner :='BDT' PL/SQL procedure successfully completed. SQL> exec :my_date := '01-jan-2001' PL/SQL procedure successfully completed. SQL> exec :my_object_id :=1 PL/SQL procedure successfully completed. SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id; COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID) ---------- -------------- -------------- 13848830 2 18233 SQL> set pagesi 0 SQL> select * from table(dbms_xplan.display_cursor); SQL_ID bu9367qrhq28t, child number 0 ------------------------------------- select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id Plan hash value: 1047781245 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12726 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | |* 2 | TABLE ACCESS FULL| BDT2 | 13M| 224M| 12726 (6)| 00:01:59 | ---------------------------------------------------------------------------
So a Full Table Scan occured and the “peeked” and “passed” bind variables are:
SQL> @binds_peeked_passed_acs.sql Enter value for sql_id: SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED PASSED ACS ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- --- bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OWNER 1 BDT BDT NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OBJECT_ID 3 1 1 NO
and no ACS into play.
Now, let’s change the bind values of the owner column and check the “peeked” and “passed” values:
SQL> exec :my_owner :='ME';
PL/SQL procedure successfully completed.
SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id;
COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
100098 2 18233
SQL> @binds_peeked_passed_acs.sql
Enter value for sql_id:
SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED PASSED ACS
------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- ---
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OWNER 1 BDT BDT NO
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 NO
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OBJECT_ID 3 1 1 NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OWNER 1 BDT ME NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OBJECT_ID 3 1 1 NO
So, same “peeked” values while the “passed” ones are not the same (and still not ACS triggered) as we can check that way (See Maria Colgan’s blog post):
SQL> l
1* select child_number, executions, buffer_gets,is_bind_sensitive, is_bind_aware from v$sql where sql_id='bu9367qrhq28t'
SQL> /
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- -------------------- --------------------
0 2 360295 Y N
Now let’s run the query a second time with the ‘ME’ value for the owner column field:
SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id;
COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
100098 2 18233
And the execution plan has changed:
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID bu9367qrhq28t, child number 1
-------------------------------------
select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2
where owner=:my_owner and created > :my_date and object_id >
:my_object_id
Plan hash value: 2372635759
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1511 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BDT2 | 100K| 1661K| 1511 (1)| 00:00:15 |
|* 3 | INDEX RANGE SCAN | BDT_OWNER | 100K| | 213 (1)| 00:00:02 |
------------------------------------------------------------------------------------------
As you can see the execution plan changed. Well, let’s see the result of my sql:
SQL>@binds_peeked_passed_acs.sql Enter value for sql_id: SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED PASSED ACS ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- --- bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OWNER 1 BDT BDT NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OBJECT_ID 3 1 1 NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OWNER 1 BDT ME NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OBJECT_ID 3 1 1 NO bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_OWNER 1 ME ME YES bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_OBJECT_ID 3 1 1 NO
Great! It detected that ACS came into play for this execution. Fine but what’s new compare to checking:
SQL>select child_number, executions, buffer_gets,is_bind_sensitive, is_bind_aware from v$sql where sql_id='bu9367qrhq28t';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- -------------------- --------------------
0 2 360295 Y N
1 1 1576 Y Y
What’s new is that you can check if ACS came into play per execution. Let’s run the sql 3 times with 2 changes of the bind value and check the result:
SQL>exec :my_owner :='BDT'
PL/SQL procedure successfully completed.
SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id;
COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
13848830 2 18233
SQL> exec :my_owner :='ME';
PL/SQL procedure successfully completed.
SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id;
COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
100098 2 18233
SQL> /
COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
100098 2 18233
You don’t have more informations from v$sql (you can see that ACS came into play but you don’t know for which execution):
SQL> l
1* select child_number, executions, buffer_gets,is_bind_sensitive, is_bind_aware from v$sql where sql_id='bu9367qrhq28t'
SQL> /
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- -------------------- --------------------
0 2 360295 Y N
1 3 3152 Y Y
2 1 180104 Y Y
while you can have the details per execution that way:
QL> @binds_peeked_passed_acs.sql Enter value for sql_id: SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED PASSED ACS ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- --- bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OWNER 1 BDT BDT NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OBJECT_ID 3 1 1 NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OWNER 1 BDT ME NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OBJECT_ID 3 1 1 NO bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_OWNER 1 ME ME YES bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_OBJECT_ID 3 1 1 NO bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_OWNER 1 BDT BDT YES bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_OBJECT_ID 3 1 1 NO bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_OWNER 1 ME ME YES bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_OBJECT_ID 3 1 1 NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_OWNER 1 ME ME NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_DATE 2 01-jan-2001 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_OBJECT_ID 3 1 1 NO
Conclusion:
You know for which executions ACS came into play and furthermore which “peeked” bind variable value changed compare to the previous execution (ACS column=’YES’).
Remarks:
- You need Diagnostic and tuning licenses pack to query v$active_session_history and v$sql_monitor.
- The query rely on the fact that the sql is monitored (which means CPU + I/O wait time >= 5 seconds per default that can be changed thanks to the _sqlmon_threshold hidden parameter)
- If you are ready to get rid of the “passed” values, then you can check this post for non monitored sql: Diagnose Adaptive Cursor Sharing (ACS) per execution for non monitored sql
Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2
Posted by bdrouvot in Perl Scripts on 29/04/2013
To understand this blog post you have to know what bind variable peeking is. You can found a very good explanation into this Kerry Osborne’s blog post.
So when dealing with performance issues linked to bind variable peeking you have to know:
- The peeked values (The ones that generate the execution plan)
- The passed values (The ones that have been passed to the sql statement)
Kerry Osborne helped us to retrieve the peeked values from v$sql_plan view into this blog post, but what about the passed values ? For those ones, Tanel Poder helped us to retrieve the passed values from v$sql_monitor into this blog post (This is reliable compare to v$sql_bind_capture)
Great ! So we know how to extract the peeked and the passed values. Another interesting point is that v$sql_monitor contains also the sql_exec_id field (see this blog post for more details about this field).
Here we are: It looks like that as of 11.2 we are able to retrieve the passed and peeked values per execution (If the statement is “monitored” which means CPU + I/O wait time >= 5 seconds per default (can be changed thanks to the _sqlmon_threshold hidden parameter).
But as your are dealing with performance issues related to bind variable peeking it is likely that the sql is monitored
So let’s write the sql to do so, and let’s test it.
The sql script is the following:
SQL> !cat binds_peeked_passed.sql
set linesi 200 pages 999 feed off verify off
col bind_name format a20
col end_time format a19
col start_time format a19
col peeked format a20
col passed format a20
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
select
pee.sql_id,
ash.starting_time,
ash.end_time,
(EXTRACT(HOUR FROM ash.run_time) * 3600
+ EXTRACT(MINUTE FROM ash.run_time) * 60
+ EXTRACT(SECOND FROM ash.run_time)) run_time_sec,
pee.plan_hash_value,
pee.bind_name,
pee.bind_pos,
pee.bind_data peeked,
run_t.bind_data passed
from
(
select
p.sql_id,
p.sql_child_address,
p.sql_exec_id,
c.bind_name,
c.bind_pos,
c.bind_data
from
v$sql_monitor p,
xmltable
(
'/binds/bind' passing xmltype(p.binds_xml)
columns bind_name varchar2(30) path '/bind/@name',
bind_pos number path '/bind/@pos',
bind_data varchar2(30) path '/bind'
) c
where
p.binds_xml is not null
) run_t
,
(
select
p.sql_id,
p.child_number,
p.child_address,
c.bind_name,
c.bind_pos,
p.plan_hash_value,
case
when c.bind_type = 1 then utl_raw.cast_to_varchar2(c.bind_data)
when c.bind_type = 2 then to_char(utl_raw.cast_to_number(c.bind_data))
when c.bind_type = 96 then to_char(utl_raw.cast_to_varchar2(c.bind_data))
else 'Sorry: Not printable try with DBMS_XPLAN.DISPLAY_CURSOR'
end bind_data
from
v$sql_plan p,
xmltable
(
'/*/peeked_binds/bind' passing xmltype(p.other_xml)
columns bind_name varchar2(30) path '/bind/@nam',
bind_pos number path '/bind/@pos',
bind_type number path '/bind/@dty',
bind_data raw(2000) path '/bind'
) c
where
p.other_xml is not null
) pee,
(
select
sql_id,
sql_exec_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time
from
v$active_session_history
group by sql_id,sql_exec_id,sql_exec_start
) ash
where
pee.sql_id=run_t.sql_id and
pee.sql_id=ash.sql_id and
run_t.sql_exec_id=ash.sql_exec_id and
pee.child_address=run_t.sql_child_address and
pee.bind_name=run_t.bind_name and
pee.bind_pos=run_t.bind_pos and
pee.sql_id like nvl('&sql_id',pee.sql_id)
order by 1,2,3,7 ;
Now let’s test it:
SQL> var my_owner varchar2(50) SQL> var my_date varchar2(30) SQL> var my_object_id number SQL> exec :my_owner :='BDT' PL/SQL procedure successfully completed. SQL> exec :my_date := '01-jan-2001' PL/SQL procedure successfully completed. SQL> exec :my_object_id :=1 PL/SQL procedure successfully completed. SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id; COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID) ---------- -------------- -------------- 6974365 2 18233 SQL> @binds_peeked_passed.sql Enter value for sql_id: SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED PASSED ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_OWNER 1 BDT BDT bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_OBJECT_ID 3 1 1
As this is the first execution then peeked values = passed values. Note that i used the “MONITOR” hint to force the sql to be monitored and then get an entry into v$sql_monitor.
Let’s put new passed values:
SQL> exec :my_date := '01-jan-2002' PL/SQL procedure successfully completed. SQL> exec :my_object_id :=100 PL/SQL procedure successfully completed. SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id; COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID) ---------- -------------- -------------- 6923776 101 18233 SQL> @binds_peeked_passed.sql Enter value for sql_id: SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED PASSED ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_OWNER 1 BDT BDT bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_OBJECT_ID 3 1 1 bu9367qrhq28t 2013/04/29 11:07:21 2013/04/29 11:07:25 4.139 1047781245 :MY_OWNER 1 BDT BDT bu9367qrhq28t 2013/04/29 11:07:21 2013/04/29 11:07:25 4.139 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2002 bu9367qrhq28t 2013/04/29 11:07:21 2013/04/29 11:07:25 4.139 1047781245 :MY_OBJECT_ID 3 1 100
So as you can see, peeked values are the same and passed are not: bind variable peeking in action
Conclusion:
We are able to retrieve peeked and passed values per execution.
Remarks:
- You need Diagnostic and tuning licenses pack to query v$active_session_history and v$sql_monitor.
- The query is not able to retrieve the DATE values (if any) from the v$sql_plan (check the code): This is because I don’t want to create a new function into the database. If you want to extract the DATE datatype then you could create the display_raw function (see Kerry Osborne’s blog post for this) and modify the sql.
- If you know how to extract DATE values from RAW type without creating new function please tell me so that i can update the code
Drill down to sql_id execution details in ASH
Posted by bdrouvot in Perl Scripts on 19/04/2013
Some times ago I explained how we can link a huge PGA or TEMP consumption to a sql_id over a period of time into this blog post.
Now I need to extract this information not only per sql_id but also per execution. This is not so simple to extract from ash as the same session could execute many times the same sql_id over a period of time.
Hopefully, since 11g the sql_exec_id column has been added to the v$active_session_history and dba_hist_active_sess_history views. You can find a very useful description of this column into this blog post from Tanel Poder.
Basically, the sql_exec_id is a unique identifier of a sql_id execution on the database. That way we are now able to know if an ash entry for this sql_id is linked to a new execution (means new sql_exec_id) or is showing a long running execution (means same sql_exec_id).
We are also able to retrieve some useful metrics as avg, min, max execution time: You can see some good examples of sql_exec_id usage into Kyle Hailey’s blog post or Karl Arao’s one.
Back to my need:
Let’s suppose that I found (Thanks to the sql provided into this post) that the sql_id “btvk5dzpdmadh” is responsible of about 2Gb of pga over allocation during a period of time:
SQL_ID PGA_MB % ------------- ---------- --------- ---------- btvk5dzpdmadh 2394 100.00 **********
Now I can drill down to details to get the pga over allocation per execution that way:
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
select sql_id,
starting_time,
end_time,
(EXTRACT(HOUR FROM run_time) * 3600
+ EXTRACT(MINUTE FROM run_time) * 60
+ EXTRACT(SECOND FROM run_time)) run_time_sec,
READ_IO_BYTES,
PGA_ALLOCATED PGA_ALLOCATED_BYTES,
TEMP_ALLOCATED TEMP_ALLOCATED_BYTES
from (
select
sql_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time,
sum(DELTA_READ_IO_BYTES) READ_IO_BYTES,
sum(DELTA_PGA) PGA_ALLOCATED,
sum(DELTA_TEMP) TEMP_ALLOCATED
from
(
select sql_id,
sample_time,
sql_exec_start,
DELTA_READ_IO_BYTES,
sql_exec_id,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA,
greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP
from
dba_hist_active_sess_history
where
sample_time >= to_date ('2013/04/16 00:00:00','YYYY/MM/DD HH24:MI:SS')
and sample_time < to_date ('2013/04/16 03:10:00','YYYY/MM/DD HH24:MI:SS')
and sql_exec_start is not null
and IS_SQLID_CURRENT='Y'
)
group by sql_id,SQL_EXEC_ID,sql_exec_start
order by sql_id
)
where sql_id = 'btvk5dzpdmadh'
order by sql_id, run_time_sec desc;
It will produces this kind of output:
SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC READ_IO_BYTES PGA_ALLOCATED_BYTES TEMP_ALLOCATED_BYTES ------------- ------------------- ------------------- ------------ ------------- ------------------- -------------------- btvk5dzpdmadh 2013/04/16 00:05:01 2013/04/16 03:09:56 11095.559 2.0417E+10 240123904 2642411520 btvk5dzpdmadh 2013/04/16 00:05:01 2013/04/16 03:09:56 11095.559 2.2207E+10 181993472 2233466880 btvk5dzpdmadh 2013/04/16 00:05:01 2013/04/16 03:09:56 11095.559 2.4342E+10 192610304 2453667840 btvk5dzpdmadh 2013/04/16 00:13:43 2013/04/16 03:09:56 10573.559 1.3095E+10 212074496 1142947840 btvk5dzpdmadh 2013/04/16 00:05:01 2013/04/16 02:10:10 7509.398 1.9374E+10 200540160 2076180480 btvk5dzpdmadh 2013/04/16 00:26:32 2013/04/16 02:05:19 5927.907 6603603968 226099200 251658240 btvk5dzpdmadh 2013/04/16 01:37:19 2013/04/16 03:09:56 5557.559 7589232640 245497856 639631360 btvk5dzpdmadh 2013/04/16 00:05:22 2013/04/16 01:36:57 5495.072 4285022208 237371392 125829120 btvk5dzpdmadh 2013/04/16 02:12:54 2013/04/16 03:09:56 3422.559 7433412608 237371392 209715200 btvk5dzpdmadh 2013/04/16 02:29:17 2013/04/16 03:09:56 2439.559 2776473600 288489472 146800640 btvk5dzpdmadh 2013/04/16 02:05:43 2013/04/16 02:29:02 1399.436 1841930240 126484480 62914560 btvk5dzpdmadh 2013/04/16 00:05:01 2013/04/16 00:22:59 1078.508 1548386304 19333120 btvk5dzpdmadh 2013/04/16 00:05:01 2013/04/16 00:13:38 517.558 652337152 19202048 btvk5dzpdmadh 2013/04/16 00:23:08 2013/04/16 00:26:29 201.867 439246848 29425664 btvk5dzpdmadh 2013/04/16 02:10:57 2013/04/16 02:12:50 113.706 97910784 53673984 btvk5dzpdmadh 2013/04/16 00:05:01 2013/04/16 00:05:17 16.703 22503424 0
As you can see we retrieved:
- Start time of the execution
- End time of the execution
- The run time of the execution
- The number of READ_IO_BYTES of the execution
- The PGA and TEMP over allocation of the execution
Remarks:
- The dba_hist_active_sess_history view is no so accurate as only a subset of the rows coming from v$active_session_history are flushed into the dba_hist_active_sess_history view.
- This SQL works as of 11.2.0.1.
- You need to purchase the Diagnostic Pack in order to be allowed to query the “v$active_session_history” view.
- This query is useful to diagnose “huge” PGA or TEMP consumption. It is not so helpful to find out which execution used exactly how much PGA or TEMP (As it may used already pre-allocated PGA or TEMP space and did not need over allocation: See the columns definition in the beginning of this post)
Retrieve and visualize system statistics metrics from AWR with R
Into my last post I gave a way to Retrieve and visualize wait events metrics from AWR with R, now it’s time for the system statistics.
So, for a particular system statistic, I’ll retrieve from the dba_hist_sysstat view:
- Its VALUE between 2 snaps
- Its VALUE per second between 2 snaps
As the VALUE is cumulative, I need to compute the difference between 2 snaps that way:
SQL> !cat check_awr_stats.sql
set linesi 200
col BEGIN_INTERVAL_TIME format a28
col stat_name format a40
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
select s.begin_interval_time,sta.stat_name,sta.VALUE,
--round(((sta.VALUE)/(to_date(s.end_interval_time)-to_date(s.begin_interval_time)))/86400,2) VALUE_PER_SEC_NOT_ACCURATE,
round(((sta.VALUE)/
(
(extract(day from s.END_INTERVAL_TIME)-extract(day from s.BEGIN_INTERVAL_TIME))*86400 +
(extract(hour from s.END_INTERVAL_TIME)-extract(hour from s.BEGIN_INTERVAL_TIME))*3600 +
(extract(minute from s.END_INTERVAL_TIME)-extract(minute from s.BEGIN_INTERVAL_TIME))*60 +
(extract(second from s.END_INTERVAL_TIME)-extract(second from s.BEGIN_INTERVAL_TIME))
)
),2) VALUE_PER_SEC
from
(
select instance_number,snap_id,stat_name,
value - first_value(value) over (partition by stat_name order by snap_id rows 1 preceding) "VALUE"
from
dba_hist_sysstat
where stat_name like nvl('&stat_name',stat_name)
and instance_number = (select instance_number from v$instance)
) sta, dba_hist_snapshot s
where sta.instance_number=s.instance_number
and sta.snap_id=s.snap_id
and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-&sysdate_nb_day_begin_interval+1)
and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-&sysdate_nb_day_end_interval+1)
order by s.begin_interval_time asc;
I use the “partition by stat_name order by snap_id rows 1 preceding” to compute the difference between snaps par stat_name.
I also use Extract to get an accurate value per second, you should read this blog post to understand why.
The output is like:
SQL> @check_awr_stats.sql
Session altered.
Session altered.
Enter value for stat_name: physical reads
old 17: where stat_name like nvl('&stat_name',stat_name)
new 17: where stat_name like nvl('physical reads',stat_name)
Enter value for sysdate_nb_day_begin_interval: 7
old 22: and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-&sysdate_nb_day_begin_interval+1)
new 22: and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-7+1)
Enter value for sysdate_nb_day_end_interval: 0
old 23: and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-&sysdate_nb_day_end_interval+1)
new 23: and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-0+1)
BEGIN_INTERVAL_TIME STAT_NAME VALUE VALUE_PER_SEC
---------------------------- ---------------------------------------- ---------- -------------
2013/03/21 00:00:12 physical reads 1363483 1132.11
2013/03/21 00:20:17 physical reads 260228 216.04
2013/03/21 00:40:21 physical reads 29573 24.56
2013/03/21 01:00:25 physical reads 231492 192.18
2013/03/21 01:20:30 physical reads 494749 410.7
2013/03/21 01:40:35 physical reads 232803 193.02
2013/03/21 02:00:41 physical reads 318803 264.66
2013/03/21 02:20:45 physical reads 1253398 1039.57
2013/03/21 02:40:51 physical reads 2064294 1711.98
2013/03/21 03:00:57 physical reads 503404 439.13
2013/03/21 03:20:03 physical reads 138052 114.59
So if you use this sql, you’ll be able to see for a particular system statistic its historical behaviour. That’s fine and I used it a lot of times.
But I like also to have a graphical view of what’s going on, and that is exactly where R comes into play.
I created a R script named: graph_awr_sysstat.r (You can download it from this repository) that provides:
- A graph for the VALUE metric over the period of time
- A graph for the VALUE per second metric over the period of time
- A graph for the Histogram of VALUE over the period of time
- A graph for the Histogram of VALUE per second over the period of time
- A pdf file that contains those graphs
- A text file that contains the metrics used to build the graphs
The graphs will come from both outputs (X11 and the pdf file). In case the X11 environment does not work, the pdf file is generated anyway.
As a graphical view is better to understand, let’s have a look how it works and what the display is:
For example, let’s focus on the “physical reads” system statistic over the last 7 days that way:
./graph_awr_sysstat.r Building the thin jdbc connection string.... host ?: bdt_host port ?: 1521 service_name ?: bdt system password ?: XXXXXXXX Display which sysstat (no quotation marks) ?: physical reads Please enter nb_day_begin_interval: 7 Please enter nb_day_end_interval: 0 Loading required package: methods Loading required package: DBI Loading required package: rJava [1] 11 Please enter any key to exit:
The output will be like:
and the physical_reads pdf file will be generated as well.
As you can see, you are prompted for:
- jdbc thin “like” details to connect to the database (You can launch the R script outside the host hosting the database)
- oracle system user password
- The statistic we want to focus on
- Number of days to go back as the time frame starting point
- Number of days to go back as the time frame ending point
Remarks:
- You need to purchase the Diagnostic Pack in order to be allowed to query the AWR repository.
- If the script has been launched with X11 not working properly, you’ll get:
Loading required package: methods Loading required package: DBI Loading required package: rJava [1] "Not able to display, so only pdf generation..." Warning message: In x11(width = 15, height = 10) : unable to open connection to X11 display '' [1] 11 Please enter any key to exit:
But the script takes care of it and the pdf file will be generated anyway.
Conclusion:
We are able to display graphically AWR historical metrics for a particular statistic over a period of time with the help of a single script named: graph_awr_sysstat.r (You can download it from this repository).
If you don’t have R installed:
- you can use the sql provided at the beginning of this post to get at least a “text” view of the historical metrics.
- Install it (See “Getting Starting” from this link)
LinkedIn
