Tuning MySQL on windows

My MySQL import of 1.2GB files was taking 1hr 30min, after 1st tunring it took 50min and finally after 30hr searching, got the import to run in 10min....phew
innodb_buffer_pool_size=1024M # Changed from 5M to 256M 1/4 of pool size innodb_log_file_size=256M # Kept the default 8M innodb_log_buffer_size=8M

impdb error

col owner_name for a30; col job_name for a30; col operation for a30; col state for a20; col job_mode for a20; SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2; SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT" FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

MySQL memory change

SHOW ENGINE INNODB STATUS; BUFFER POOL AND MEMORY ---------------------- Total memory allocated 135987200; in additional pool allocated 0 Dictionary memory allocated 1124711 Buffer pool size 8191 Free buffers 3303 Database pages 4833 Old database pages 1804 After changes vi /etc/mysql/my.cnf # Added innodb_buffer_pool_size = 512M /etc/init.d/mysql stop /etc/init.d/mysql start BUFFER POOL AND MEMORY ---------------------- Total memory allocated 543948800; in additional pool allocated 0 Dictionary memory allocated 890055 Buffer pool size 32767 Free buffers 29924 Database pages 2840 Old database pages 1068

RMAN session / rman longops

RMAN backup status, RMAN jobs, rman back list, Is rman running, rman session, rman process -- With time (RAC version) col "Sid Serial#" for a15 col "Logon time" for a20 col spid for a6 select b.sid||','||b.serial# as "Sid Serial#", a.inst_id, b.status, a.spid, to_char(b.logon_time, 'dd Mon yyyy, hh24:mi') as "Logon time", b.client_info from gv$process a, gv$session b where a.addr=b.paddr and a.inst_id = b.inst_id and lower(client_info) like '%rman%'; -- Basic and (Non-RAC) select b.sid, b.serial#, a.spid, b.client_info from v$process a, v$session b where a.addr=b.paddr and client_info like '%rman%';

Is database standby

Quick check to see if the instance is standby or primary? SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> select controlfile_type from V$database; CONTROL ------- STANDBY Useful sql select to_char(max(next_time),'dd Mon yyyy hh24:mi:ss') from v$archived_log where applied = 'YES'; TO_CHAR(MAX(NEXT_TIME),'DDMON ----------------------------- 05 Mar 2019 07:24:02 alter session set nls_date_format='dd Mon yyyy hh24:mi:ss'; select * from ( SELECT FIRST_TIME, NEXT_TIME, SEQUENCE# FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc) where rownum < 6 order by SEQUENCE#; FIRST_TIME NEXT_TIME SEQUENCE# ----------------------------- ----------------------------- ---------- 05 Feb 2019 06:39:02 05 Feb 2019 06:54:02 213003 05 Feb 2019 06:54:02 05 Feb 2019 07:09:02 213004 05 Feb 2019 07:09:02 05 Feb 2019 07:24:02 213005 05 Feb 2019 07:24:02 05 Feb 2019 07:39:02 213006 05 Feb 2019 07:39:02 05 Feb 2019 07:54:02 213007

Kill session

-- Current session alter system kill session '213,98643'; alter system kill session '777,56244,@1'; -- Kill session from another instance alter system kill session '66,64486,@2'; alter system kill session '66,64486' IMMEDIATE;

Schema Last analysed | Stale Stats

-- Summary of stale select stale_stats, count(*) from user_tab_statistics group by stale_stats; STA COUNT(*) --- ---------- 414 NO 2582 YES 199 select TABLE_NAME, STALE_STATS from user_tab_statistics where stale_stats = 'YES';