Skip to main content

Posts

Showing posts from September, 2017

MySQL Percona Tool Kit

Kill queries running longer than 60s:
pt-kill --busy-time 60 --kill
Print, do not kill, queries running longer than 60s:
pt-kill --busy-time 60 --print
Print all login processes:
pt-kill --match-state login --print --victims all
Find all tables created more than a day ago, which use the MyISAM engine, and print their names:
 pt-find --ctime +1 --engine MyISAM
Find InnoDB tables and convert them to MyISAM:
pt-find --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM"
Find empty tables in the test and junk databases, and delete them:
pt-find --empty junk test --exec-plus "DROP TABLE %s"
Find tables more than five gigabytes in total size:
pt-find --tablesize +5G
Summarize MySQL information nicely:
pt-mysql-summary --user=root
Diff MySQL configuration files and server variables:
Diff host1 config from SHOW VARIABLES against host2:
pt-config-diff h=host1 h=host2
Diff config from [mysqld] section in my.cnf against host1 config:
pt-config-diff /etc/my.cnf h=host1
Diff the [mysqld] section …

My SQL Daily SQL

Get list of tables with particular column
SELECT DISTINCT TABLE_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME NOT REGEXP "^test_.*(_archive)|(_new)|(_old)$" AND
TABLE_NAME REGEXP "^test_.*" AND
COLUMN_NAME = '<column_name>' AND
TABLE_SCHEMA ='<schema_name>'

Find number of connections:
SHOW STATUS WHERE `variable_name` = 'Threads_connected';

Find if some table is locked:
SHOW OPEN TABLES WHERE
`Table` LIKE '%[TABLE_NAME]%' AND
`Database` LIKE '[DBNAME]' AND In_use > 0;

Find all foreign keys on a table:
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = '<table_name>';

Oracle Gather Schema | Table Statistics | Extended Statistics | method_opt

Select Histograms for tables:-

SELECT column_name, num_distinct, histogram FROM   user_tab_col_statistics WHERE  table_name = '<table_name>';

Gather tables stats using DBMS_STATS for schema
exec dbms_stats.gather_schema_stats
( ownname => '<owner_name>', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt  => 'FOR ALL COLUMNS SIZE 1', cascade => true, degree => 10, granularity => 'ALL' );
exec dbms_stats.gather_schema_stats
( ownname=> 'SAMAPPL', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade=> true, granularity=>'ALL', degree => 10 ); Extended statistics Ref.:
https://blogs.oracle.com/optimizer/extended-statistics
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/perform/multistats/multicolstats.htm

method_opt options FOR ALL COLUMNS SIZE 1
FOR COLUMNS SIZE 254 <column_name> FOR ALL IND…