Skip to main content

Posts

Recent posts

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 o…

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…

Retrive Exadata Version and Configuration Information

Option 1: Use DBMS_LOB package to get cell configuration information, as column CONFVAL is CLOB
spool confval.txt and search for text make in text file you will see get value in tag <makeModel>  </makeModel>

set pagesize 0
SELECT dbms_lob.substr(CONFVAL,4000,1) from V$CELL_CONFIG;
spool off

This option is good if you don't  have privilege to access storage node and can't execute command Cellcli. You would be able to find out interleaving attribute about Celldisk also as follows.

<interleaving>none</interleaving>

Explore about interleaving attribute of Celldisk at

https://uhesse.com/2011/05/18/exadata-part-vii-meaning-of-the-various-disk-layers/amp/
http://basededonnyes.blogspot.com/2012/01/creating-interleaved-grid-disks.html?m=1


Option 2:
From Tanel Podder Blog. I have modified some format to display Make Model properly.
COL cv_cellname       HEAD CELL_NAME        FOR A30
COL cv_cell_path      HEAD CELL_PATH        FOR A30
COL cv_cellversion    HEAD CE…

Oracle ADRCI - Automatic Diagnostic Repository Command Interpreter

adrci - Automatic Diagnostic Repository Command Interpreter

$ adrci
adrci> show homes
-- Set home for Oracle Databsae
adrci> set home
-- List incidents
adrci> show incidents
-- Pack incident for incident numbver
adrci> ips pack incident <incident_no> in /tmp
-- Show incident information in brief
adrci> show incident -mode brief -p "incident_id=<incident_no>"
-- Pack incident for problem key (Get problem key by commnad show incidents)
adrci> ips create package problemkey "ORA 600 [qmtGetBaseType]"
-- Pack incident by time (Get time record by commnd show incidents)
adrci> IPS CREATE PACKAGE TIME '2017-08-23 11:31:44.296000 -07:00' to '2017-08-22 08:32:16.394000 -07:00'

Ref.: http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm#BGBICJED


Drop Tuning Set of Oracle Database

Tuning Set Name : Test_TOP_SQL_1503437315950

select name from dba_sqlset;
select * from dba_sqlset where name like 'Test%';
Dropping SQL Task
select name from dba_sqlset;
execute dbms_sqltune.drop_sqlset('Test_TOP_SQL_1503437315950');
You could get an error, if it is reference to advisory task
Get advisory task name
select description, created, owner  from DBA_SQLSET_REFERENCES  where sqlset_name = 'Test_TOP_SQL_1503437315950';
task: SQL_TUNING_1503437363054
select owner,description, created,last_modified from DBA_ADVISOR_TASKS where task_name = 'SQL_TUNING_1503437363054';
execute dbms_sqltune.drop_tuning_task('SQL_TUNING_1503437363054');
execute DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'Test_TOP_SQL_1503437315950');
If you can't drop SQL Set then check whether record exist in work load repository or not
SELECT count(*)  FROM wri$_sqlset_definitions a, wri$_sqlset_references b  WHERE a.name = 'Test_TOP_SQL_1503437315950'  …