Skip to content

PostgreSQL explain plan operations

PostgreSQL explain plan operations published on Комментариев к записи PostgreSQL explain plan operations нет

to be continued

> — what does «Bitmap Heap Scan» phase do?

A plain indexscan fetches one tuple-pointer at a time from the index,
and immediately visits that tuple in the table. A bitmap scan fetches
all the tuple-pointers from the index in one go, sorts them using an
in-memory «bitmap» data structure, and then visits the table tuples in
physical tuple-location order. The bitmap scan improves locality of
reference to the table at the cost of more bookkeeping overhead to
manage the «bitmap» data structure — and at the cost that the data
is no longer retrieved in index order, which doesn’t matter for your
query but would matter if you said ORDER BY.

> — what is «Recheck condition» and why is it needed?

If the bitmap gets too large we convert it to «lossy» style, in which we
only remember which pages contain matching tuples instead of remembering
each tuple individually. When that happens, the table-visiting phase
has to examine each tuple on the page and recheck the scan condition to
see which tuples to return.

> — why are proposed «width» fields in the plan different between the two
> plans?

Updated statistics about average column widths, presumably.

> (actually, a nice explanation what exactly are those widths would also
> be nice :) )

Sum of the average widths of the columns being fetched from the table.

> — I thought «Bitmap Index Scan» was only used when there are two or more
> applicable indexes in the plan, so I don’t understand why is it used
> now?

True, we can combine multiple bitmaps via AND/OR operations to merge
results from multiple indexes before visiting the table … but it’s
still potentially worthwhile even for one index. A rule of thumb is
that plain indexscan wins for fetching a small number of tuples, bitmap
scan wins for a somewhat larger number of tuples, and seqscan wins if
you’re fetching a large percentage of the whole table.

regards, tom lane

https://www.postgresql.org/message-id/12553.1135634231@sss.pgh.pa.us

Oracle, find query witch runs more than &1 sec

Oracle, find query witch runs more than &1 sec published on Комментариев к записи Oracle, find query witch runs more than &1 sec нет
with stats as (
select /*+ materialize */
 * from (
select  parsing_schema_name,t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  s.parsing_schema_name,min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
 round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.begin_interval_time > trunc(sysdate-4,'iw')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > &1
group by  sql_id, plan_hash_value,parsing_schema_name
 )t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs*avg_sec desc ) tt where
 rownum <= 50 )
 select * from stats s 
 where parsing_schema_name not like 'SYS%'
  and s.sql_text not like '%/* DS_SVC */%'
  and s.sql_text not like '%_job_proc%'
  and s.sql_text not like '%SQL Analyze%'
  and lower(s.sql_text) not like '%dbms_feature%'
  and s.sql_text not like '%SYS_DBA_SEGS%'
  and lower(s.sql_text) not like '%v$sys%'
  and lower(s.sql_text) not like '%dba_audit_trail%'
  and lower(s.sql_text) not like '%no_index(mytab)%';

Postgresql how-to find top query for last 15 seconds

Postgresql how-to find top query for last 15 seconds published on Комментариев к записи Postgresql how-to find top query for last 15 seconds нет
DROP TABLE IF EXISTS pg_stat_statements_temp; 
  create table pg_stat_statements_temp as
  SELECT  d.datname,u.usename, s.queryid,
round(s.total_time::numeric, 2) AS total_time,
s.calls,
round(s.mean_time::numeric, 2) AS mean,
now() as cur_date
FROM    pg_stat_statements s
    join pg_database d on s.dbid=d.oid
    join pg_user u on s.userid=u.usesysid;
 
    select pg_sleep(15);



 with stat as (SELECT  d.datname,u.usename, s.queryid,s.query,
round(s.total_time::numeric, 2) AS total_time,
s.calls,
round(s.mean_time::numeric, 2) AS mean
FROM    pg_stat_statements s
    join pg_database d on s.dbid=d.oid
    join pg_user u on s.userid=u.usesysid  )
    select  s.datname,s.usename,s.queryid,regexp_replace(s.query, E'[\n\r]+', ' ', 'g' )::varchar(80) AS short_query,
    s.total_time-t.total_time as time,
    s.calls-t.calls as calls,
    s.mean -t.mean as mean, 
    round( 
      (100 * (s.total_time-t.total_time) /sum(s.total_time-t.total_time) OVER ()
            )::numeric, 2)     as cpu,
    now()-t.cur_date as diff_time, 
CASE WHEN s.calls-t.calls >0  THEN 
     (s.calls-t.calls )/(extract ( second from now()-t.cur_date) + extract ( minutes from now()-t.cur_date)*60 + extract ( minutes from now()-t.cur_date)*60*60 ) 
    else '0' 
    end as tps
     from stat s
    	join pg_stat_statements_temp t on s.datname=t.datname and s.usename=t.usename and s.queryid=t.queryid
    	order by cpu desc limit 20;

oracle12c how-to get detailed info about gather stat job

oracle12c how-to get detailed info about gather stat job published on Комментариев к записи oracle12c how-to get detailed info about gather stat job нет

from oracle 12c it is possible to find out detailed information about gather stats job
in em you may find it at

or use dbms_stats.report_* to get info
detailed at https://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL95102

I use it this way:

variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_stats_operations(
    since=>SYSTIMESTAMP-3,
    until=>SYSTIMESTAMP,
    detail_level=>'ALL',
    format=>'TEXT');
    end;
   /

print mystatsreport
variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_single_stats_operation ( opid => 11898,detail_level => 'TYPICAL', format =>'TEXT' );
    end;
   /
print mystatsreport
variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_single_stats_operation ( opid => 11898,detail_level => 'TYPICAL', format =>'TEXT' );
    end;
   /
   print mystatsreport

postgresql top 10 tables by io operations

postgresql top 10 tables by io operations published on Комментариев к записи postgresql top 10 tables by io operations нет

helps to keep focus on «hot» tables
top 10 tables by sum of io operatoins:

SELECT
st.schemaname||'.'||st.relname,
round (100*( 
		sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) 
) 
	/ 
(
		sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) over () 
),2) as pct_io_ops
 from pg_stat_user_tables st
JOIN pg_class c ON c.oid=st.relid 
LEFT JOIN pg_tablespace t ON t.oid=c.reltablespace
WHERE
coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)>100
group by st.schemaname||'.'||st.relname,st.n_tup_ins,st.n_tup_upd,st.n_tup_hot_upd,st.n_tup_del
order by pct_io_ops desc 
limit 10;

top 10 tables by index scans:

   SELECT 
        pg_stat_all_tables.schemaname||'.'||relid::regclass AS table, 
        round (100* (sum(pg_stat_user_indexes.idx_scan))
                      / (sum(pg_stat_user_indexes.idx_scan) over () )
                       ,2) pct_indx_scans
    FROM 
        pg_stat_user_indexes 
        JOIN pg_index USING (indexrelid)
        join pg_stat_all_tables using (relid)
        group by pg_stat_all_tables.schemaname||'.'||relid::regclass,pg_stat_user_indexes.idx_scan
        order by 2 desc 
        limit 10;

generate ash report from sqlplus

generate ash report from sqlplus published on Комментариев к записи generate ash report from sqlplus нет

plan to make a script with time range as input values

19:22:41 (1)system@xxxxx> select * from table (dbms_workload_repository.ash_global_report_html( 4281370344,1,timestamp'2016-05-05 13:50:00',timestamp'2016-05-05 14:05:00') );

open in browser:
add for mac os x

host open %SQLPATH%\tmp\output_&_connect_identifier..html

add for linux:

host firefox %SQLPATH%\tmp\output_&_connect_identifier..html --linux

find query running more than 5 sec

find query running more than 5 sec published on Комментариев к записи find query running more than 5 sec нет
 select t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
 round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > 5
group by  sql_id, plan_hash_value
 )t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs desc;

Screen Shot 2016-03-28 at 10.16.48

Calculating TPS from ASH via SQL_EXEC_ID

Calculating TPS from ASH via SQL_EXEC_ID published on Комментариев к записи Calculating TPS from ASH via SQL_EXEC_ID нет

save to my blog ( original )

to calculate tps by sql_id we can use sql_exec_id , ash and exel

SELECT TO_CHAR (sample_time, 'HH24:MI'),inst_id,
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_MINUTE
  FROM gv$active_Session_history
 WHERE    sql_id = 'b6asbgkghps5h' and sample_time > sysdate-5/24
group by TO_CHAR (sample_time, 'HH24:MI'),inst_id
order by 1 asc;

Screen Shot 2016-02-08 at 15.50.27

or to split results it frame by 10 minutes:

select date#,"'1'" as first_node,"'2'" as second_node from (
SELECT TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60) as date#,instance_number,
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_10_MINUTE
  FROM gv$active_Session_history
 WHERE    sql_id = '77qx41mkwcm92' 
group by TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60),instance_number
order by 1 asc )
pivot 
(
   sum(EXECUTIONS_PER_10_MINUTE)
   for instance_number in ('1'  ,'2' )
) order by date# asc;

what happens when table does not fit to keep pool

what happens when table does not fit to keep pool published on Комментариев к записи what happens when table does not fit to keep pool нет

create big table for tests:

create table bigtab (
    id      number(12,6),
    v1      varchar2(10),
    padding varchar2(50)
)
nologging   -- just to save a bit of time
/

insert /*+ append ordered full(s1) use_nl(s2) */
into bigtab
select
        3000 + trunc((rownum-1)/500,6),
        to_char(rownum),
        rpad('x',50,'x')
from
        all_objects s1,      -- you'll need the privilege
        all_objects s2
where
        rownum <= 2000000
/
commit;

check size:

21:07:01 (1)[PDBORCL]c##bushmelev_aa@orcl> @seg_space bigtab
old   2: where segment_name=upper ('&1')
new   2: where segment_name=upper ('bigtab')

OWNER           | SEGMENT_NAME                             |     MB_SIZE
--------------- | ---------------------------------------- | -----------
C##BUSHMELEV_AA | BIGTAB                                   |       152.0

Elapsed: 00:00:07.42
23:28:41 (1)[PDBORCL]c##bushmelev_aa@orcl> l
  1  select owner,segment_name,sum(bytes)/1024/1024 as MB_SIZE from dba_extents
  2  where segment_name=upper ('&1')
  3* group by segment_name,owner

ps: describe of x$kcbwds ds, x$kcbwbpd pd you may find at oracle x$ tables

usefull links:
http://enkitec.tv/2012/05/19/oracle-full-table-scans-direct-path-reads-object-level-checkpoints-ora-8103s/
Direct path read and fast full index scans

lets_check:

 select ksppinm as hidden_parameter  , ksppstvl as value
from x$ksppi join x$ksppcv using (indx)
 where ksppinm like '\_small_table_threshold%' escape '\' order by ksppinm;

HIDDEN_PARAMETER          | VALUE
------------------------- | ----------
_small_table_threshold    | 2203

00:25:34 (1)[PDBORCL]sys@orcl> select 2203*8192/1024/1024 from dual;

2203*8192/1024/1024
-------------------
         17.2109375

so our 17 mb table should do DPR on full table scan

now lets full scan bigtable ( there is no indexes on table, so i use count (*) for minimize network influence on results ) an check sga info and session stats :

00:13:23 (1)[PDBORCL]c##bushmelev_aa@orcl> select count (*) from bigtab;

  COUNT(*)
----------
   2000000

using:
Script to Identify Objects and Amount of Blocks in the Buffer Pools — Default, Keep, Recycle, nK Cache (Doc ID 373472.1)

get info about sement in sga:

select BP_ID, decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') subcache,
bh.object_name, round (sum(bh.blocks)* (select to_number(value) from v$parameter where name='db_block_size') /(1024*1024),2) used_mb
from x$kcbwds ds, x$kcbwbpd pd,
(select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS from obj$ o, x$bh x where o.dataobj#=x.obj and x.state!=0 and o.owner#!=0 and upper(o.name)=upper('&mytable') group by set_ds, o.name) bh
where ds.set_id>=pd.bp_lo_sid and ds.set_id<=pd.bp_hi_sid and pd.bp_size!=0 and ds.addr=bh.set_ds group by pd.BP_ID, decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') , bh.object_name; 00:50:04 (1)[PDBORCL]sys@orcl> @db_seg_cache2
Enter value for mytable: bigtab

     BP_ID | SUBCACHE   | OBJECT_NAME     |    USED_MB
---------- | ---------- | --------------- | ----------
         3 | DEFAULT    | BIGTAB          |        .01

Screen Shot 2016-01-03 at 00.29.56

now put full table (152mb) to keep_pool:

alter system set db_keep_cache_size=200m;
alter table bigtab storage (buffer_pool keep );

now fullscan works from cache:

Screen Shot 2016-01-03 at 00.37.23

now check buffer pools :

select BP_ID, decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') subcache,
bh.object_name, round (sum(bh.blocks)* (select to_number(value) from v$parameter where name='db_block_size') /(1024*1024),2) used_mb
from x$kcbwds ds, x$kcbwbpd pd,
(select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS from obj$ o, x$bh x where o.dataobj#=x.obj and x.state!=0 and o.owner#!=0 and upper(o.name)=upper('&mytable') group by set_ds, o.name) bh
where ds.set_id>=pd.bp_lo_sid and ds.set_id<=pd.bp_hi_sid and pd.bp_size!=0 and ds.addr=bh.set_ds
group by pd.BP_ID, decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') , bh.object_name;

     BP_ID | SUBCACHE   | OBJECT_NAME     |    USED_MB
---------- | ---------- | --------------- | ----------
         3 | DEFAULT    | BIGTAB          |        .01
         1 | KEEP       | BIGTAB          |     148.64

change keep pool to 140mb and repeat test:

alter system set db_keep_cache_size=120m;

00:39:38 (1)[PDBORCL]sys@orcl> /
Enter value for mytable: bigtab

     BP_ID | SUBCACHE   | OBJECT_NAME     |    USED_MB
---------- | ---------- | --------------- | ----------
         3 | DEFAULT    | BIGTAB          |        .01
         1 | KEEP       | BIGTAB          |     133.27

even when 87% of table are in current mode in keep pool session do DPR

01:01:19 (1)[PDBORCL]sys@orcl> @db_seg_cache c##bushmelev_aa bigtab

AREA         | STATUS                         | DISTINCT_BLOCK_COUNT                     | BLOCK_COUNT
------------ | ------------------------------ | ---------------------------------------- | ------------------------------------------------------------------------------------------------------------------------
BUFFER CACHE | free                           | 3                                        | 3
BUFFER CACHE | xcur                           | 17060                                    | 17060
BUFFER CACHE | summary                        | 17063                                    | 17063
DATABASE     | db blocks                      | 19456                                    |
SGA          | BUFFER CACHE of MAX SGA SIZE   | 754,974,720 of 1,426,063,360             | (Resizeable)

Elapsed: 00:00:00.11
01:01:20 (1)[PDBORCL]sys@orcl> l
  1  with OBJLIST as
  2   (select DATA_OBJECT_ID
  3      from dba_objects
  4     where (owner, object_name) in ((upper(nvl('&&1', 'user')), upper('&&2')))
  5       and DATA_OBJECT_ID is not null)
  6  select 'BUFFER CACHE' as AREA,
  7         nvl(status,'summary') as STATUS,
  8         to_char(count(distinct(file# || '#' || block#))) as DISTINCT_BLOCK_COUNT,
  9         to_char(count(*)) as BLOCK_COUNT
 10    from V$BH, OBJLIST
 11   where objd = OBJLIST.DATA_OBJECT_ID
 12   group by rollup(status)
 13  union all
 14  select 'DATABASE',
 15         'db blocks',
 16         to_char(blocks),
 17         '' as BH_COUNT
 18  from dba_segments where (owner, segment_name) in ((upper(nvl('&&1', 'user')), upper('&&2')))
 19  union all
 20  select 'SGA',
 21         'BUFFER CACHE of MAX SGA SIZE',
 22         trim(to_char(s1.bytes, '999,999,999,999,999')) ||
 23         ' of '||
 24         trim(to_char(s2.bytes, '999,999,999,999,999')),
 25         '(' || decode(s1.resizeable, 'Yes', 'Resizeable', 'Fixed') || ')'
 26* from v$sgainfo s1, v$sgainfo s2 where s1.name = 'Buffer Cache Size' and s2.name = 'Maximum SGA Size'

and FTS goes by DRP:

Screen Shot 2016-01-03 at 00.41.24

what_happens_when_table_does_not_fet_to_keep_pool

Primary Sidebar

Яндекс.Метрика