Skip to main content

Posts

Showing posts from September, 2017

Oracle Grid Infrastructure srvctl options for 12.1.0.2.0

Database:
Usage: srvctl add database -db <db_unique_name> -oraclehome <oracle_home> [-dbtype {RACONENODE | RAC | SINGLE} [-server <server_list>] [-instance <inst_name>] [-timeout <timeout>]] [-domain <domain_name>] [-spfile <spfile>] [-pwfile <password_file_path>] [-role {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY | FAR_SYNC}] [-startoption <start_options>] [-stopoption <stop_options>] [-startconcurrency <start_concurrency>] [-stopconcurrency <stop_concurrency>] [-dbname <db_name>] [-policy {AUTOMATIC | MANUAL | NORESTART}] [-serverpool "<serverpool_list>" [-pqpool <pq_server_pools>]] [-node <node_name>] [-diskgroup "<diskgroup_list>"] [-acfspath "<acfs_path_list>"] [-eval] [-fixed] [-verbose]
Usage: srvctl config database [-db <db_unique_name> [-all] | -serverpool <serverpool_name>] [-verbose]
Usage: srvctl start datab…

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 table
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 INDEXE…