Skip navigation.

XTended Oracle SQL

Syndicate content
XTended Oracle SQL
Updated: 19 hours 26 min ago

Just a couple of screenshots of sqlplus+rlwrap+cygwin+console

Thu, 2014-10-16 14:06

I previously wrote that I peeped the idea about showing the session information in terminal title from Timur Akhmadeev’s screenshots, and Timur wrote:

I’m using (a bit modified) Tanel Poder’s login.sql available in his TPT scripts library:

Tanel’s i.sql
My title.sql and on_login.sql

Colored prompt is the one of many features of rlwrap.

Connected as simple user:
Connected as sysdba:

SQL*Plus on OEL through putty:

@inc/title “*** Test ***”

Categories: Development

Little script for finding tables for which dynamic sampling was used

Tue, 2014-10-07 14:42

You can always download latest version here:
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 (
         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.* 
from tabs
    ,dba_tables t
     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
  s.sql_id in (select p.sql_id 
               from v$sql_plan p
                 and p.other_xml like '%dynamic_sampling%'
Categories: Development