Oracle SQL

    Tag Archives: dynamic sampling

    Little script for finding tables for which dynamic sampling was used

    Posted on October 7, 2014 by Sayan Malakshinov Posted in CBO, oracle, statistics 3 Comments

    You can always download latest version here: http://github.com/xtender/xt_scripts/blob/master/dynamic_sampling_used_for.sql
    Current source code:

    col owner         for a30;
    col tab_name      for a30;
    col top_sql_id    for a13;
    col temporary     for a9;
    col last_analyzed for a30;
    col partitioned   for a11;
    col nested        for a6;
    col IOT_TYPE      for a15;
    with tabs as (
          select 
             to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,1))  owner
            ,to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,2))  tab_name
            ,count(*)                                                                    cnt
            ,sum(executions)                                                             execs
            ,round(sum(elapsed_time/1e6),3)                                              elapsed
            ,max(sql_id) keep(dense_rank first order by elapsed_time desc)               top_sql_id
          from v$sqlarea a
          where a.sql_text like 'SELECT /* OPT_DYN_SAMP */%'
          group by
             to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,1))
            ,to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,2))
    )
    select tabs.* 
          ,t.temporary
          ,t.last_analyzed
          ,t.partitioned
          ,t.nested
          ,t.IOT_TYPE
    from tabs
        ,dba_tables t
    where 
         tabs.owner    = t.owner(+)
     and tabs.tab_name = t.table_name(+)
    order by elapsed desc
    /
    col owner         clear;
    col tab_name      clear;
    col top_sql_id    clear;
    col temporary     clear;
    col last_analyzed clear;
    col partitioned   clear;
    col nested        clear;
    col IOT_TYPE      clear;
    

    ps. Or if you want to find queries that used dynamic sampling, you can use query like that:

    select s.*
    from v$sql s
    where 
      s.sql_id in (select p.sql_id 
                   from v$sql_plan p
                   where p.id=1
                     and p.other_xml like '%dynamic_sampling%'
                  )
    
    dynamic sampling dynamic_sampling

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL