A high level overview. Expectation should be for all OLTP operations to take less than 1 second, and all batch processing jobs and background scheduled report jobs to take less than 1 hour each. This is deliverable on even large databases with moderate hardware, with some effort put in. 1) Is the issue even a […]
Read More What to do if a database performance issue is reportedList most recent rman database backups on target database sqlplus with: set pages 9999 lines 132 ALTER SESSION SET nls_date_format = ‘Dy DD-Mon-YYYY HH24:MI:SS’ ; SELECT b.checkpoint_time , b.incremental_level , COUNT(*) FROM v$backup_datafile b JOIN v$datafile f ON f.file# = b.file# JOIN v$tablespace ts ON f.ts# = ts.ts# WHERE ts.included_in_database_backup = ‘YES’ AND b.checkpoint_time > […]
Read More Rman list most recent backupsBind variable peeking is still a cause of plan instability, even after 11gR1’s introduction of adaptive cursor sharing. In my case adaptive cursor sharing wasn’t helpful for a daily batch job, because the sql was aged out of cache by the time the next scheduled run came around. Making everything worse was having parameter cursor_sharing […]
Read More Oracle hint cursor_sharing_exact using sql patchThere are two ways of selecting the earliest and latest rows for each customer. 1) Keep group: SELECT t1.customer_ref , MAX ( t1.order_amount ) KEEP ( DENSE_RANK FIRST ORDER BY t1.order_date ASC ) AS earliest_order_amount , MAX ( t1.order_amount ) KEEP ( DENSE_RANK FIRST ORDER BY t1.order_date DESC ) AS latest_order_amount FROM t1 WHERE t1.order_amount […]
Read More Oracle select earliest and latest rows using keep group or first_value analyticsMicrsoft SQL Server databases should be kept in full recovery mode (the equivalent of Oracle’s archivelog mode) except for the master, tempdb, ReportServerTempDB databases. To check all databases in a SQL Server instance, run the below script in a SQL command window. Optionally uncomment the EXEC line to have this script switch databases to full […]
Read More Windows PowerShell run script on all SQL Servers recovery modeThe equivalent of the grep command in oracle is like: SELECT * FROM dba_source s WHERE LOWER ( s.text ) LIKE ‘%within group%’ ; That works fast enough for querying dba_source, which is a small dataset. But for even medium sized tables, you really want a fast indexed query, which the “LIKE ‘%” syntax ordinarily […]
Read More Oracle Full Text simple example for like % grep wildcardSee real time top session info in either RAC or standalone database, including tracefile name qualified by instance name, with: — ALTER SESSION SET nls_date_format = ‘Dy DD-Mon-YYYY HH24:MI:SS’ ; SELECT DISTINCT s.inst_id , s.sid , s.serial# , s.username , s.osuser , s.program || ‘ ‘ || s.module AS program , s.status , ROUND ( […]
Read More Oracle top session info in RAC with tracefile name1) Update – thanks to Joaquin Gonzalez for pointing out a preferable safer method than logon trigger for 10046 tracing a particular module: EXEC DBMS_MONITOR.serv_mod_act_stat_enable ( service_name => ‘myservice’ , module_name => ‘myModule.exe’ , binds => TRUE ) You can verify service_name is what you expect by checking the column of the same name in […]
Read More 10046 trace a module using logon triggerMtPuTTY multi tabbed putty is an improved variant of putty ssh client, easier to use if working on several servers at the same time. However it has an annoying tendency to display long directory paths in each tab title. You can switch that behaviour off with Tools > Settings > Show Display name. Microsoft Remote […]
Read More mtputty directory path titleMost core application tables benefit from columns like these: ALTER TABLE mytable ADD ( date_created DATE DEFAULT SYSDATE , user_created_by VARCHAR2(30) DEFAULT USER , date_changed DATE , user_changed_by VARCHAR2(30) ) ; The default values deal with inserts, for updates a trigger is needed: CREATE OR REPLACE TRIGGER tu_mytable BEFORE UPDATE ON mytable FOR EACH ROW […]
Read More Oracle date changed user changed columns and batch performance