perfomance tune

what happens when table does not fit to keep pool

от

create big table for tests: check size: 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: so our 17 mb table should do DPR on full table scan now lets full scan bigtable ( there is no indexes on … Читать далее

Oracle X$ Tables

от

original at yong321.freeshell.org X$ Tables Oracle X$ Tables Updated to Oracle 12.1.0.2. The X$ tables not included here are too obvious, too obscure, or too uninteresting. Table Name Guessed Acronym Comments x$activeckpt active checkpoint Ckpt_type 2 for MR checkpoint (Ref), 3 for interval (Ref) or thread checkpoint (Ref), 7 for incremental checkpoint, 10 for object … Читать далее

awr report by hour

от

simplest way to generate awr report by hour after benchmark
export awr

@?/rdbms/admin/awrextr.sql

import awr

@?/rdbms/admin/awrload.sql

— make sure to set line size appropriately
— set linesize 152

generate awr

 set termout off
set linesize 80
set pagesize 10000
select 'spool awrrpt_dwhfrn_'|| snap_id ||'.html'|| chr(13)||
 'select output from table(dbms_workload_repository.awr_report_text('||dbid||',1,'||snap_id||','|| LEAD (snap_id, 1)  OVER (ORDER BY  dbid,snap_id ) ||',0));' || chr(13)||
 ' spool off '
 from dba_hist_snapshot where dbid !=1399642255 and extract( minute from begin_interval_time ) <2 ;