Xplan 2.0
A lot of new features have been added in version 2.0 of xplan, the sqlplus script I use to investigate about SQL statements performance (I spoke about version 1.0 in this post). Here's a brief description.
wait profile (from ASH)
For each statement, its wait profile is calculated fetching wait information from Active Session History:
----------------------------------------- |ash event |cnt |% | ----------------------------------------- |enq: HW - contention |2606|61.0| |enq: TX - row lock contention| 875|20.5| |db file sequential read | 344| 8.0| |enq: TX - index contention | 158| 3.7| |gc current grant busy | 152| 3.6| |cpu | 56| 1.3| |gc current block 2-way | 34| 0.8| |gc current block busy | 13| 0.3| |gc buffer busy | 10| 0.2| |gc cr block 2-way | 7| 0.2| |gc current grant 2-way | 5| 0.1| |read by other session | 5| 0.1| |direct path write | 3| 0.1| |gc cr block busy | 3| 0.1| |gc cr grant 2-way | 1| 0.0| |SQL*Net more data from client| 1| 0.0| |cr request retry | 1| 0.0| -----------------------------------------
By default this feature is on in 10g+ and inspects a window of ash_profile_mins=15 minutes from v$active_session_history.
Important note: you must have bought the appropriate Oracle licence (i.e. the Diagnostic Pack in 11.1) to read from that view and hence to use this feature (xplan will output a warning to remember you about that); you can disable this feature by setting ash_profile_mins=0.
Dump of dependent object definitions
If the statement references some database objects (e.g. a view, a pl/sql function) and hence depends on them, xplan will list them right below the statement text:
SELECT /*+ index(t,t_fbi) ordered use_nl(v) xplan_test_marker */ T.RR, PLSQL_FUNC(MAX(T.X)) FROM T, V WHERE UPPER(T.X) >= '0' AND T.X > :B1 AND V.RR ='x' GROUP BY T.RR ORDER BY T.RR - depends on view DELLERA.V - depends on function DELLERA.PLSQL_FUNC
and the object definition will be reported at the bottom of the xplan output:
############################################# function DELLERA.PLSQL_FUNC ### function plsql_func (p varchar2) return varchar2 is begin return p; end plsql_func; ############################################# view DELLERA.V ### view columns: #1 X(NUMBER),#2 PADDING(VARCHAR2),#3 RR(VARCHAR2) select x, padding, rr from t where x > 0
Reading other RAC instance statements
Now you can read from another instance by specifying the option inst_id (defaults to the instance you are connected). This is handy for inspecting other instances of the RAC cluster without reconnecting.
Automatic dump of AWR most-expensive statements
The experimental script xplan_awr.sql will inspect AWR (Active Workload Repository) and dump all the statements that are still in the library cache and that have exceeded some resource consumption thresholds in any of the periods marked by two consecutive AWR snapshots. Thresholds can be the percentage of total (e.g. dump if the CPU consumption is more that 10% of total CPU) or the ranking position (e.g. dump if the statement ranks more than 5th in the CPU chart - the typical "top-N" analysis). The thresholds are configurable in the topmost "params" WITH clause.
Again, you must have bought the appropriate Oracle licence to use AWR, and hence to run xplan_awr.sql.
- Friday, August 7, 2009 performance tuning, tools, xplan
- jump to comments
Martin Berger
Alberto,
What a nice improvemet for xplan! I used it quite regulary and I will use your new version with at least the same gratitude.
As you now also show related objects, you might want to include assicuated statistics, if they exist. I know, they are used rarely, but if they exist, they will sum up the informations xplan will deliver.
Martin
Alberto Dell'Era
@Martin
thanks, and I will add your request to my to-do list, absolutely.
Just to be sure – you mean the statistics added with the command ASSOCIATE STATISTICS, do you ?
Martin Berger
Alberto,
yes, I mean the statiscits added with ASSOCIATE STATISTICS. To be honest, Adrian Billington brought me to them resently: http://www.oracle-developer.net/display.php?id=426#
Alberto Dell'Era
@Martin
I have added the associated statistics to the report output:
I’ve not implemented anything for domain index and indextypes.
Hence now, the three types of associated statistics (selectivity/cost/stat type), associated to three types of objects (functions/packages/types) are reported by xplan.
I’ve also improved the report here and there (especially improved the way dependent materialized views are reported – now there’s the defining statement and the CBO stats of the container table in a simpler output).
Martin Berger
Alberto,
you are just great!
( I will have to read your scripts in detail, I’m sure there are tons of gems hidden for simply minds like me ).
please keep improving the oracle-world with your knowledge!
Martin
rich
Hello Sir,
this would be silly question but I am not able to get the output from your script, please help me to solve this.
I want to use this great script for my day to day issues of PT.
used all option but no luck ..
@xplan.sql “%select jp.cp_job_id%” “”
@xplan.sql “” “sql_id=ab4rjccnt8dvx”
@xplan.sql “” “sql_id=ab4rjccnt8dvx,hash=697579389″
getting following errors
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
NCHAR | NCHAR (n) | NVARCHAR2 (n) |
CLOB | NCLOB | REFCURSOR ] ]
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
NCHAR | NCHAR (n) | NVARCHAR2 (n) |
CLOB | NCLOB | REFCURSOR ] ]
SP2-0552: Bind variable “CURRENT_ERROR” not declared.
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
NCHAR | NCHAR (n) | NVARCHAR2 (n) |
CLOB | NCLOB | REFCURSOR ] ]
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
NCHAR | NCHAR (n) | NVARCHAR2 (n) |
CLOB | NCLOB | REFCURSOR ] ]
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
NCHAR | NCHAR (n) | NVARCHAR2 (n) |
CLOB | NCLOB | REFCURSOR ] ]
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
NCHAR | NCHAR (n) | NVARCHAR2 (n) |
CLOB | NCLOB | REFCURSOR ] ]
SP2-0552: Bind variable “INSTANCE_NAME” not
…
..
SP2-0552: Bind variable “OPT_PLAN_STATS” not declared.
SP2-0552: Bind variable “OPT_PLAN_STATS” not declared.
SP2-0552: Bind variable “OPT_PLAN_STATS” not declared.
…
..
Input truncated to 46 characters
Input truncated to 2 characters
Input truncated to 2 characters
Input truncated to 15 characters
Alberto Dell'Era
@rich
a) are you using a recent version of sqlplus (e.g. 10g, 11g) ?
b) have you extracted all the files contained in xplan.zip in the current directory ?
regards
Alberto
Rich
thanks Sir,
sorry for late reply, my problem got solved ..
Rich ..
Rich
hi Sir,
I am getting following error, while running the xplan.
I tried
SET SERVEROUTPUT ON size UNL
but no luck ..any suggestions ..
@xplan “” “sql_id=akwwmt0txutp3″
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
ORA-06512: at line 174
ORA-06512: at line 238
ORA-06512: at line 252
ORA-06512: at line 1694
ORA-06512: at line 2702
ORA-06512: at line 2856
Alberto Dell'Era
Hi Rich,
can you tell me :
- the version of the database you are connecting to
- the version of sqlplus you are using
The most recent the version of sqlplus, the better; I usually use 11.2 to connect even to older databases.
Rich
hello Sir,
sorry for delayed reply, I am using 10202 sqlplus version ..
I will try with 11.2 version ..
thanks for writing a such a beautiful script, I can’t say. how useful it was for me ..
Rich ..
» Best Oracle Peformance Tools?
[...] xplan – extend explain plan info from Alberto Dell Era [...]
Lynn Sattler
Alberto,
I’ve chatted before with you about your xplan routine.
I’ve been using it some more and I read your documentation again.
I learned by trial and error that I could get it to sort the output by execution time per execution.
Here is what I used:
@xplan “%” “parsed_by=ONEADMIN,order_by=elapsed_time/executions desc”
If you are so inclined you may want to add this sort option to your samples.
Thanks for the xplan routines, I am finding them more useful. We have the sql tune / addm big dollar utilities. I find good info in your output that does not show up with addm report.
Lynn Sattler
Toledo, Ohio USA
Alberto Dell'Era
Hi Lynn,
I’m happy to see that you find xplan useful and that you are so kind in letting me know :)
I have added your suggestion in the header of xplan.sql and I have also expanded the example description
a bit more – it is indeed possible to add full expressions in the order-by list, provided that you turn
commas into semicolons:
– order_by: (default: null)
– Order statements by the specified columns/expressions. Ties are ordered by sql_text and child_number.
– Use the semicolon instead of the comma in expressions.
– For example: “order_by=elapsed_time desc;buffer_gets”
– “order_by=elapsed_time/decode(executions;0;null;executions) desc;buffer_gets”
This is useful also in your scenario, since it happens far too often that you get an executions=0 row, and
all it takes to spoil the fun is just one row ;)
ciao
Alberto
Lynn Sattler
Alberto,
Have a new issue with xplan.
Running against v11.1 linux rh5 with multibyte characterset:
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LENGTH_SEMANTICS CHAR
( I have a hunch the issue is related to the characterset because xplan works fine against another v 11.1 db)
Issued: @xplan “” “” (have tried different options, same results)
get this:
new 147: :OPT_ASH_PROFILE_MINS := 15;
old 148: &COMM_IF_LT_10G. end if;
new 148: end if;
declare /* xplan_exec_marker */ — process options
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 28
SQL>
SQL> — print current options values
SQL> variable CURRENT_XPLAN_OPTIONS varchar2(500 char)
SQL> begin
2 select /*+ xplan_exec_marker */
3 ‘inst_id=’ || :OPT_INST_ID
4 || ‘ plan_stats=’||:OPT_PLAN_STATS
Any ideas?
Lynn Sattler
Kyle Hailey » Best Oracle Performance Tools
[...] xplan [...]
Daya
@Martin, Rich, Alberto
Where is the link to download the zip files. I couldn’t find the link to download the folder. can some body ping me the link to download the files.
Thanks,
Daya
Alberto Dell'Era
Daya,
here it is:
http://www.adellera.it/scripts_etcetera/xplan/index.html
the zip can be found by following the first link on the page