Creating a SQL Plan Baseline for a Problem Query

Background A few days after upgrading our most important database from 10g to 11g we started to notice some processes against the database (which were running on a 10 minute schedule) were not returning. We were alerted via a Grid Control CPU load warning email.  This was killing the database performance, shooting CPU usage up [...]

Oracle Recovery Strategy

Background This is how we recover databases using the backup strategy that we use.  This method is best used for recovery to a separate host. It can save many hours of downtime by moving the bulk of the data over to the new host and then simply applying transactions from the logs (archive and online) [...]

Oracle Backup Strategy

Background Obviously backing up data is crucial for any database. However, there are a million different strategies for all recovery needs as well as for desire of resources dedicated to backing up and recovery. This is what we use. This method works for 10g and 11g databases. Method Since I am essentially a team of [...]

ID Card Project

Background We manage the Oracle database which is used for all id card printing at the university.  A custom front-end was created to enter in data that shows on the card (name, student/staff/faculty, etc.) and more custom code exists from a consultant that deals with card printing.  This goes into our database.  More custom code [...]

Useful SQL Queries and Oracle Code

Use a Hint select /*+ index(person_sa I_PS0PERSON_SA) */ nyuidn, emplid from idm.masterids, mv_ps_person_sa person_sa where nyuidn=campus_id; Find Execution Plan in database –Optional to test different CBO settings alter session set optimizer_index_cost_adj = 1; EXPLAIN PLAN FOR select TR.rid from ( select s.rid, ROWNUM rnum from (select distinct a.rid from atlassparse.cn_css a , atlassparse.cn_css a0 where [...]