Oracle SQL | Yearly Archives: 2013

Controlling “direct path reads” decision with INDEX_STATS/table_stats

Posted on March 7, 2013 by Sayan Malakshinov Posted in adaptive serial direct path reads, CBO, oracle 2,277 Page views 3 Comments

Since 11.2.0.2 direct path read decision on full scans(FTS/IFFS) can be based on the statistics.

And if my test is correct, it appears that we can control this behavior on query level with changing number of blocks through index_stats/table_stats outlines:

UPD: I did a little change of the test case for avoiding impact of hard parse on main test.

Test case

[sourcecode language=”sql” highlight=”45,56″]
drop table xt_iffs purge;
spool &_spools/iffs_test.sql;
@param_ _direct_read_decision_statistics_driven
@param_ _small_table_threshold
SELECT name,block_size,buffers FROM v$buffer_pool;

create table xt_iffs as select level a,mod(level,100) b,lpad(1,100,1) c from dual connect by level<=1e5;
create index ix_iffs on xt_iffs(a);
exec dbms_stats.set_table_stats(”,’XT_IFFS’,numrows => 1e6,numblks => 5e5,avgrlen => 800);
exec dbms_stats.set_index_stats(”,’IX_IFFS’,numrows => 1e6,numlblks => 1e4);
set termout off echo off feed off timing off;
——————- 1 run for avoiding hard parse in main test
alter system flush buffer_cache;
select/*+ index_ffs(t IX_IFFS) */ sum(a) from xt_iffs;

alter system flush buffer_cache;
select/*+
BEGIN_OUTLINE_DATA
INDEX_FFS(T IX_IFFS)
INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=5, rows=10)
END_OUTLINE_DATA
*/
sum(a)
from xt_iffs t;

alter system flush buffer_cache;
select/*+
BEGIN_OUTLINE_DATA
INDEX_FFS(T IX_IFFS)
INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=1, rows=50)
END_OUTLINE_DATA
*/
sum(a)
from xt_iffs t;
——————- Main test with statistics: ———————————————-
exec xt_runstats.init(p_latches => false);
alter system flush buffer_cache;
select/*+ index_ffs(t IX_IFFS) */ sum(a) from xt_iffs;
exec xt_runstats.snap;

alter system flush buffer_cache;
select/*+
BEGIN_OUTLINE_DATA
INDEX_FFS(T IX_IFFS)
INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=5, rows=10)
END_OUTLINE_DATA
*/
sum(a)
from xt_iffs t;
exec xt_runstats.snap;

set termout on echo on serverout on;

exec xt_runstats.print(p_stats_mask => ‘reads|direct’,p_sta_diff_pct => 0);
spool off;
[/sourcecode]

[collapse]

Result

[sourcecode language=”sql” highlight=”50,69″]

NAME VALUE DEFLT TYPE DESCRIPTION
—————————————- ———— ———— ———— ————————————————————
_direct_read_decision_statistics_driven TRUE TRUE boolean enable direct read decision based on optimizer statistics

Elapsed: 00:00:00.20

NAME VALUE DEFLT TYPE DESCRIPTION
—————————————- ———— ———— ———— ————————————————————
_small_table_threshold 166 TRUE number lower threshold level of table size for direct reads

Elapsed: 00:00:00.21

NAME BLOCK_SIZE BUFFERS
—————————————- ———- ———-
DEFAULT 8192 491

Elapsed: 00:00:00.19

Table created.

Elapsed: 00:00:00.29

Index created.

Elapsed: 00:00:00.46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
SQL>
SQL> exec xt_runstats.print(p_stats_mask => ‘reads|direct’,p_sta_diff_pct => 0);
################ Results: ##################
Run # 01 ran in 48 hsecs
Run # 02 ran in 47 hsecs
Run # 03 ran in 48 hsecs
############################################################################################
Statistics | Run # 1 | Run # 2 | Run # 3
############################################################################################
session logical reads………………. | 229 | 227 | 229
session logical reads in local numa grou | 0 | 0 | 0
session logical reads in remote numa gro | 0 | 0 | 0
db block gets direct……………….. | 0 | 0 | 0
consistent gets direct……………… | 0 | 223 | 0
physical reads…………………….. | 224 | 224 | 224
physical reads cache……………….. | 224 | 1 | 224
physical reads direct………………. | 0 | 223 | 0
physical writes direct……………… | 0 | 0 | 0
physical reads direct temporary tablespa | 0 | 0 | 0
physical writes direct temporary tablesp | 0 | 0 | 0
recovery array reads……………….. | 0 | 0 | 0
physical reads cache prefetch……….. | 203 | 0 | 203
physical reads prefetch warmup………. | 0 | 0 | 0
physical reads retry corrupt………… | 0 | 0 | 0
physical reads direct (lob)…………. | 0 | 0 | 0
physical writes direct (lob)………… | 0 | 0 | 0
cold recycle reads…………………. | 0 | 0 | 0
physical reads for flashback new…….. | 0 | 0 | 0
flashback direct read optimizations for | 0 | 0 | 0
redo size for direct writes…………. | 0 | 0 | 0
cell physical IO bytes sent directly to | 0 | 0 | 0
transaction tables consistent reads – un | 0 | 0 | 0
data blocks consistent reads – undo reco | 0 | 0 | 0
table scans (direct read)…………… | 0 | 0 | 0
lob reads…………………………. | 0 | 0 | 0
index fast full scans (direct read)….. | 0 | 1 | 0
securefile direct read bytes………… | 0 | 0 | 0
securefile direct write bytes……….. | 0 | 0 | 0
securefile direct read ops………….. | 0 | 0 | 0
securefile direct write ops…………. | 0 | 0 | 0
############################################################################################
[/sourcecode]

[collapse]

Workaround for deadlock with select for update order by on 11.2.0.2-11.2.0.3

Posted on February 16, 2013 by Sayan Malakshinov Posted in bug, oracle 4,301 Page views 2 Comments

There is well-known bug with “for update order by” on 11.2, when rows locks not in specified order, although the ordering occurs.
I already wrote on my russian blog about the appearance of “buffer sort” in plans with “for update” even if sort order was not specified. And this behavior can be disabled for example by specifying /*+ opt_param( ‘optimizer_features_enable’ ‘11.1.0.7’ ) */.
But if we want to solve problem with deadlock, we need to force index full scan/index range scan ascending with “buffer sort” usage.

UPD: The patch is already available for 11.2.0.2 and 11.2.0.3: Patch 13371104: LOCK ORDER HAS CHANGED BETWEEN 10.2.0.5 AND 11.2.0.2.
Read more: Simple example

Why between to_date(‘1582-10-15′,’yyyy-mm-dd’) and to_date(‘1582-10-04′,’yyyy-mm-dd’) only one day

Posted on February 15, 2013 by Sayan Malakshinov Posted in curious, oracle 2,476 Page views Leave a comment

You may wonder why between these dates only one day:

SQL> select date'1582-10-15'-date'1582-10-04' from dual;

DATE'1582-10-15'-DATE'1582-10-04'
---------------------------------
                                1

SQL> select date'1582-10-05'                   "dt_1"
  2        ,date'1582-10-05' + 1               "dt_1 + 1"
  3        ,date'1582-10-05' - 1               "dt_1 - 1"
  4        ,to_date('1582-10-05','yyyy-mm-dd') "dt_1 and to_date"
  5  from dual;

dt_1               dt_1 + 1           dt_1 - 1           dt_1 and to_date
------------------ ------------------ ------------------ ------------------
October   05, 1582 October   16, 1582 October   04, 1582 October   15, 1582

Over the last 2 months, I gave link to answer several times, so i decided to post it here: http://www.orafaq.com/papers/dates_o.doc

BTW, yet another trick:

SQL> select date'0000-02-29','to_char:'||date'0000-02-29' from dual;

DATE'0000-02-29'    'TO_CHAR:'||DATE'0000-02-29'
------------------- ---------------------------
29.02.0000 00:00:00 to_char:00.00.0000 00:00:00

SQL> select to_date('0000-02-29','yyyy-mm-dd') error from dual;
select to_date('0000-02-29','yyyy-mm-dd') error from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

About unnecessary work with predicate “field=:bind_variable” where bind_variable is null

Posted on February 14, 2013 by Sayan Malakshinov Posted in CBO, oracle, query optimizing 1,962 Page views Leave a comment

Although we know that in the case when we do select from a table and one of the predicates is “field=:bind_variable” and :bind_variable is null, we should not get the rows, oracle not always “think” the same and don’t add upper filter predicate “:bind_variable is not null”, so it can do unnecessary work. It is completely depends from a plan, which will be chosen. Of course this applies to other predicates like >, < or != too. For example, there will not be any reads only if it is an access predicate on index range/unique scan or full table scan on 11.2.0.3 and with gathered stats. If it is FTS on previous versions, then only segment header is read. In others cases oracle will do useless scans. So if bind variable can be null and you want to be sure that oracle will not do futile work in such cases, just add predicate ":bind_variable is not null". This note is just aggregated info from recent question from our forum where i participated

Update:The explanation about segment header reads you can listen from Enkitec.tv by Tanel Poder

Full test code you can download as file – test.sql.

Little example #1

[sourcecode language=”sql”]
set timing off feed off

create table XT_NULLS_TEST(a not null, b not null,c)
as
select
level a
, mod(level,100) b
, lpad(1,100,1) c
from dual
connect by level<=1e5;

create index IX_NULLS_TEST on XT_NULLS_TEST(a);

exec dbms_stats.gather_table_stats(”,’XT_NULLS_TEST’);

——————- Main test with statistics: ———————————————-
— Variable with null:
var v_null number;

exec xt_runstats.init(p_latches => false);

— INDEX RANGE SCAN:
select/*+ INDEX(XT_NULLS_TEST IX_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap(‘IRS’);

— FULL TABLE SCAN:
select/*+ FULL(XT_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap(‘FTS’);

— INDEX FAST FULL SCAN:
select/*+ INDEX_FFS(XT_NULLS_TEST IX_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap(‘IFFS’);

–Results
set serveroutput on
exec xt_runstats.print(p_stats_mask => ‘reads|buff.*gets|consistent gets’,p_sta_diff_pct => 1);

drop table xt_nulls_test purge;
[/sourcecode]
Results:
[sourcecode language=”sql”]
SQL> @test.sql.txt

CNT
———-
0

CNT
———-
0
################ Results: ##################
Run # 01 ran in 0 hsecs
Run # 02 ran in 0 hsecs
Run # 03 ran in 1 hsecs
############################################################################################
Statistics | IRS | FTS | IFFS
############################################################################################
session logical reads………………. | 0 | 1 | 230
consistent gets……………………. | 0 | 1 | 230
consistent gets from cache………….. | 0 | 1 | 230
consistent gets from cache (fastpath)… | 0 | 1 | 230
############################################################################################
[/sourcecode]

[collapse]

Example #2

DDL

[sourcecode language=”sql”]
SQL> alter session set optimizer_dynamic_sampling=0;
SQL> alter session set statistics_level=all;
SQL> create table xt_test as select 1 i from dual;

Table created.
[/sourcecode]

[collapse]

NULL 1

[sourcecode language=”sql”]
SQL> — NULL 1:
SQL> var a number;

SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(”,”,’ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
————————————————————————————————-
SQL_ID 4rjbsjvwbq5m0, child number 0
————————————-
select * from xt_test where i=:a

Plan hash value: 3713359643

—————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
—————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 |
|* 1 | TABLE ACCESS FULL| XT_TEST | 1 | 3 | 0 |00:00:00.01 | 1 |
—————————————————————————————

Predicate Information (identified by operation id):
—————————————————
1 – filter("I"=:A)
[/sourcecode]

[collapse]

Not NULL 1

[sourcecode language=”sql”]
SQL> — NOT NULL 1:
SQL> exec :a := 0;

PL/SQL procedure successfully completed.

SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(”,”,’ALLSTATS LAST’));

Plan hash value: 3713359643

————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 | 1 |
|* 1 | TABLE ACCESS FULL| XT_TEST | 1 | 3 | 0 |00:00:00.01 | 3 | 1 |
————————————————————————————————

Predicate Information (identified by operation id):
—————————————————
1 – filter("I"=:A)
[/sourcecode]

[collapse]

Null 2

[sourcecode language=”sql”]
SQL> alter table xt_test add primary key(i);

Table altered.

SQL> exec :a := null;

PL/SQL procedure successfully completed.

SQL> — NULL 2:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(”,”,’ALLSTATS LAST’));

Plan hash value: 136758570

———————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 |
|* 1 | INDEX UNIQUE SCAN| SYS_C00161305 | 1 | 1 | 0 |00:00:00.01 |
———————————————————————————–

Predicate Information (identified by operation id):
—————————————————
1 – access("I"=:A)
[/sourcecode]

[collapse]

Not NULL 2

[sourcecode language=”sql”]
SQL> exec :a := 0;

PL/SQL procedure successfully completed.

SQL> — NOT NULL 2:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(”,”,’ALLSTATS LAST’));

Plan hash value: 136758570

———————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 |
|* 1 | INDEX UNIQUE SCAN| SYS_C00161305 | 1 | 1 | 0 |00:00:00.01 | 1 |
———————————————————————————————

Predicate Information (identified by operation id):
—————————————————
1 – access("I"=:A)
[/sourcecode]

[collapse]

Deterministic function vs scalar subquery caching. Part 2

Posted on February 11, 2013 by Sayan Malakshinov Posted in deterministic functions, oracle, query optimizing, scalar subquery caching 2,806 Page views 3 Comments

In previous part i already point out that:

Both mechanisms are based on hash functions.
Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.

Today’s topic:
4. Deterministic functions does not keeps last result as scalar subquery caching
5. Caching of deterministic functions results turns off after a certain number of attempts to get the value from the cache. But SSC always returns results from cache if values already cached.
Continue reading→

Deterministic function vs scalar subquery caching. Part 1

Posted on February 10, 2013 by Sayan Malakshinov Posted in deterministic functions, oracle, scalar subquery caching 8,569 Page views 8 Comments

I recently did a comparison caching mechanisms of scalar subquery caching(SSC) and deterministic functions in 11.2. Unfortunately, I do not have enough time to do a full analysis, so I will post it in parts.

Today’s topics:
1. Both mechanisms are based on hash functions.(You can read great articles about hash tables and hash collisions for scalar subquery caching by Tom Kyte and Jonathan Lewis(“Cost-Based Oracle fundamentals” chapter 9))
2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
3. Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.

UPD: Part 2
Continue reading→

Materialization in subquery factoring without hint “materialize” can be considered only when exists at least one predicate

Posted on February 9, 2013 by Sayan Malakshinov Posted in CBO, oracle, query optimizing, undocumented 3,469 Page views 6 Comments

I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Simple example:

Spoiler

[sourcecode language=”sql”]

SQL> create table tt1 as select 1 id from dual;

Table created.

SQL> exec dbms_stats.gather_table_stats(”,’TT1′);

PL/SQL procedure successfully completed.

SQL> explain plan for
2 with gen as (select * from tt1)
3 select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————-
Plan hash value: 486748850

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
—————————————————————————–

SQL> explain plan for
2 with gen as (select * from tt1 where 1=1)
3 select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————-
Plan hash value: 2673059801

———————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 26 | 7 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6610_6641830 | | | | |
| 3 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_6641830 | 1 | 3 | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_6641830 | 1 | 3 | 2 (0)| 00:00:01 |
———————————————————————————————————
[/sourcecode]

[collapse]

Update: I did some additional tests and found:

with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
with “xmltable” behavior is very strange – decision about materialization depends on ‘columns …’ clause: when it exists – materialization occurs, if not – doesn’t.
with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence

Tests with table() were like this:

[sourcecode language=”sql”]
with t as (select * from table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
with t as (select * from t10,table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
[/sourcecode]

[collapse]

Tests with xmltable() were like this:

[sourcecode language=”sql”]
with t as (select * from t10,xmltable(:a ) ttt where 1=1)
select count(*)
from t, t t1;
with t as (select * from t10,xmltable(:a columns n int) ttt where 1=0)
select count(*)
from t, t t1;
with t as (select/*+ no_merge */ * from table(cast(:a as sys.ku$_vcnt)),xmltable(:a) where 1=0 )
select count(*) from t,t t2;
[/sourcecode]

[collapse]

Test with 2 from 1000 columns

[sourcecode language=”sql”]
declare
c varchar2(32767):=’create table t_1000_cols as select ‘;
c2 varchar2(32767);
begin
for i in 1..1000 loop
c2:=c2||’,lpad(1,4000,1) c’||i;
end loop;
c:=c||ltrim(c2,’,’)||’ from dual connect by level<=100′;
execute immediate c;
end;
/
exec dbms_stats.gather_table_stats(”,’T_1000_COLS’);
alter session set tracefile_identifier = mat1000;
alter session set events=’10053 trace name context forever, level 1′;

with t as (select c1,c2 from t_1000_cols)
select count(*)
from t, t t2;

with t as (select c1,c2 from t_1000_cols where 1=1)
select count(*)
from t, t t2;
[/sourcecode]

[collapse]

Bug in documentation about dbms_stats.gather_schema_stats or in the dbms_stats itself

Posted on January 26, 2013 by Sayan Malakshinov Posted in bug, CBO, documentation, oracle, statistics, undocumented 2,232 Page views 2 Comments

Recently I had to gather pending stats with Object Filter List by several objects, so I copied example from documentation and was surprised: instead of gather stats by specified filter list, oracle started to gather stats for all tables in current_schema! And ‘filter list’ applies only with dbms_stats.GATHER_DATABASE_STATS

UPD: Jared Still gave link to registered bug id, which i couldn’t find in MOS before:

Bug 12754926 – DBMS_STATS.gather_schema_stats does not work with an obj_filter_list parameter specified [ID 12754926.8]
Modified:04-Jan-2012 Type:PATCH Status:PUBLISHED
This issue is fixed in 11.2.0.4 (Future Patch Set)

Little example

[sourcecode language=”sql”]
SQL> exec dbms_stats.delete_schema_stats(‘HR’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31
SQL> begin
2 for r in (select table_name
3 from dba_tables t
4 where t.owner=’XTENDER’
5 and table_name like ‘TMP%’
6 )loop
7 dbms_stats.delete_table_stats(‘XTENDER’,r.TABLE_NAME);
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> select
2 owner
3 ,table_name
4 ,num_rows
5 ,last_analyzed
6 from dba_tab_statistics t
7 where owner=’XTENDER’ and table_name like ‘TMP%’
8 or owner=’HR’;

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
—————————— —————————— ———- ——————-
HR COUNTRIES
HR DEPARTMENTS
HR EMPLOYEES
HR JOBS
HR JOB_HISTORY
HR LOCATIONS
HR REGIONS
XTENDER TMP
XTENDER TMP1
XTENDER TMP_CONTENT
XTENDER TMP_DATA
XTENDER TMP_DOCUMENT
XTENDER TMP_EXCH

13 rows selected.

Elapsed: 00:00:00.11
SQL> col dt new_value dt
SQL> select sysdate dt from dual;

DT
——————-
2013-01-27 00:30:21

1 row selected.

Elapsed: 00:00:00.00
SQL> DECLARE
2 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
3 BEGIN
4 filter_lst.extend(2);
5 filter_lst(1).ownname := ‘XTENDER’;
6 filter_lst(1).objname := ‘TMP%’;
7 filter_lst(2).ownname := ‘HR’;
8 DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst,
9 options => ‘gather’);
10 END;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:16.89
SQL> select owner,table_name
2 from dba_tables t
3 where t.LAST_ANALYZED>timestamp’&dt’+0;

OWNER TABLE_NAME
—————————— ——————————
XTENDER TT
XTENDER TR_LOG
XTENDER IOT1
…[skipped 171 rows]…
XTENDER DEPARTMENTS

175 rows selected.

Elapsed: 00:00:01.04
[/sourcecode]

[collapse]

PS. Also there is a typo in the example from oracle documentation: ‘gather_stale’ instead of ‘gather stale’ – underline instead of blank space.

Just another version of Tom Kyte’s runstats (runstats_pkg)

Posted on January 21, 2013 by Sayan Malakshinov Posted in oracle, runstats, statistics 2,436 Page views Leave a comment

I want to share my modifications of Tom Kyte’s runstats package, which include:

Any number of runs sets for analyzing
Standalone: No need to create other objects
Ability to specify session SID for statistics gathering
Ability to specify what to gather: latches, stats or both
Separate mask filters for output by statname and latchname
Ability to specify difference percentage for output separately for latches and statistics
More accurate with some statistics because of avoiding global temporary table usage, but less in several others because of collections usage

Link to package: https://github.com/xtender/xt_runstats

Output example:

SQL> begin
  2    -- little example which shows difference between "fast dual" and "full table scan dual":
  3    -- http://docs.oracle.com/cd/E11882_01/server.112/e17118/queries009.htm#SQLRF20036
  4    xt_runstats.init(p_latches => false);
  5    -- 1:
  6    for r in (select * from dual connect by level<=1e3) loop
  7      null;
  8    end loop;
  9    xt_runstats.snap;
 10
 11    -- 2:
 12    for r in (select 'X' dummy from dual connect by level<=1e3) loop
 13      null;
 14    end loop;
 15    xt_runstats.snap;
 16    xt_runstats.print(p_stats_mask => '%gets%');
 17  end;
 18  /
################     Results:      ##################
Run #  01 ran in 0 hsecs
Run #  02 ran in 0 hsecs
###########################################################################
Statistics                               | Run # 1        | Run # 2
###########################################################################
consistent gets......................... |              3 |              0
consistent gets from cache.............. |              3 |              0
consistent gets from cache (fastpath)... |              3 |              0
no work - consistent read gets.......... |              1 |              0
###########################################################################
-

Some usage examples:
1. for own session:

      begin
        xt_runstats.init();
        [some_code_1] 
        xt_runstats.snap();
        [some_code_2]
        xt_runstats.snap();
        ... 
        [some_code_N]
        xt_runstats.snap();
        -- result output:
        xt_runstats.print();
      end;

2. for session with sid = N

      begin xt_runstats.init(N); end;
      ...[after a while]
      begin xt_runstats.snap; end;
      ...[one more if needed...]
      begin xt_runstats.snap; end;
        -- result output:
      begin xt_runstats.print(); end;

3. Latches only:

xt_runstats.init(p_stats=>false);

4. Print stats with name like ‘%gets%’:

xt_runstats.print(p_stats_mask=>'%gets%');

5. Print latches which differ by 30% or more and stats differ by 15% or more:

xt_runstats.print( p_lat_diff_pct=>30, p_sta_diff_pct => 15);