postgresql bloat monitoring script
took from https://wiki.postgresql.org/wiki/Show_database_bloat
mass move tables by dbms_redifinition
++ redefinition —ALTER USER «XXXXXX» QUOTA UNLIMITED ON XXXXXX; ps: thx to Vladimir Mukin for material
quickest way to get statiscics info
select sname, spare4 from sys.optstat_hist_control$; •gather system stats: – EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; – exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; – exec DBMS_STATS.GATHER_SYSTEM_STATS(‘interval’, interval=>60) ; • Switch on incremental statistics for partitioned tables – DBMS_STATS.SET_GLOBAL_PREFS(‘INCREMENTAL’,’TRUE’);
oracle export data without blob
find top space occupants: so if we skip blob field at export, we will save 85% of space database size: do export with remap blob field with funtion that return empty blob: create package\function which return empty blob Export: ps: dump size 52gb found at: http://dba.stackexchange.com/questions/25540/export-table-without-blob-column
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 … Читать далее
work with whitespace in bash scripts
example: psql -U username -qAt -c «select ftp_file_path from ftp_file_headers where 1=1 and ftp_file_type=’tm’;» | while read folder; do echo $folder ; done
SYS_OP_C2C full table scan
on prod system we have query that use FTS instead of IRS this is because developers put in bind variable nvarchar2 ( you may find bind type in v$sql_bind_capture ) instead of varchar2, they say that they can’t change code solved by
select from v$restore_point without catalog role
and you can grant select on this funtion to every one, without FLASHBACK ANY TABLE\SELECT_CATALOG_ROLE\SELECT ANY grants: