AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

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.cn...

Disables locking while copying the non-InnoDB files: [mysqlbackup] no-locking After issuing FLUSH TABLES WITH READ LOCK: We can see one of the following status: Flushing tables Waiting for table flush Waiting for global read lock Pending FLUSH TABLES WITH READ LOCK because connection has: executed LOCK TABLES ... WRITE; for one or more tables a long running query (including DDL, simple SELECT, etc.) Identify Queries Taking Longer Than 10 Seconds: SELECT * FROM sys.session WHERE command = 'Query' AND time > 10\G SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_COMMAND = 'Query' AND PROCESSLIST_TIME > 10; SELECT * FROM information_schema.PROCESSLIST WHERE Command = 'Query' AND TIME > 10; Flush MySQL Host: flush hosts; Flush MySQL bin logs: flush bin logs; Select current date and time: s...

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 R ef.: 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 ...

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...

adrci - Automatic Diagnostic Repository Command Interpreter $ adrci adrci> show homes Set home for Oracle Database: adrci> set home List incidents: adrci> show incidents Pack incident for incident number: 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  

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'  A...

Enable smart scan for ASM Disk group while creating disk group in Exadata SQL> CREATE DISKGROUP data HIGH REDUNDANCY DISK 'o/*/DATA*' ATTRIBUTE 'AU_SIZE' = '4M',           'content.type' = 'data',           'cell.smart_scan_capable'='TRUE',           'compatible.rdbms'='11.2.0.2',           'compatible.asm'='11.2.0.3'; Ref.: http://docs.oracle.com/cd/E80920_01/SAGUG/exadata-administering-asm.htm#SAGUG20526 Check cell smart scan is enables for ASM disk or not:  Make sure to connect ASM instance, if you connect to DB instance you won't find value. SELECT dg.name AS diskgroup, SUBSTR(a.name,1,24) AS name, SUBSTR(a.value,1,24) AS value FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a WHERE dg.group_number = a.group_number and a.NAME = 'cell.smart_scan_capable'; EXADATA - Importance of Cellinit.ora and Celli...