| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Slow performance/response
The query is:
SELECT /* This query provides a general retention table that can be modified with parameter input (see subquery -a- below)
or by modifying the code. */
cohort_year, unit_abbrev unit, DECODE ( classif,
'A', '1-Fresh ',
'B', '2-Soph ',
'C', '3-Junior',
'D', '4-Senior',
'Undergrd') classif,
(TRUNC ( year_retained, -2) - TRUNC ( cohort_year, -2)) / 100 + 1 year_retained, SUM (fall_retention) student_count, SUM (fall_retention) / cohort_count cohort_percent, SUM (degrees_earned) degrees_earned, degrees_to_date / cohort_count degree_percent FROM /* Begin subquery d */ /* This subquery finds the cohort total and the year to date degree totals. */ (SELECT cohort_year, classif, year_retained, degrees_earned, fall_retention, SUM (DECODE ( year_retained, cohort_year, fall_retention, 0)) OVER ( PARTITION BY unit_abbrev, cohort_year, classif) cohort_count, SUM (degrees_earned) OVER ( PARTITION BY unit_abbrev, cohort_year, classif ORDER BY year_retained RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) degrees_to_date, unit_abbrev FROM /* Begin subquery c */ /* This subquery determines fall to fall retention by matching the cohort from subquery -b- with student_analytical. */ (SELECT a.cohort_year, a.classif, DECODE ( uwsa.student_analytical.term, 'S', DECODE ( uwsa.student_analytical.year, a.cohort_year, uwsa.student_analytical.year, 199900, 199899, uwsa.student_analytical.year - 101), uwsa.student_analytical.year) year_retained, DECODE ( a.year_degree_earned, DECODE ( uwsa.student_analytical.term, 'S', DECODE ( /* degree earned */ uwsa.student_analytical.year, /* in given year? */ a.cohort_year, uwsa.student_analytical.year, 199900, 199899, uwsa.student_analytical.year - 101), uwsa.student_analytical.year), a.degrees_earned, 0) degrees_earned, DECODE ( MIN (uwsa.student_analytical.term), '1', DECODE ( SUBSTR ( MIN (uwsa.student_analytical.term || uwsa.student_analytical.classif), /* only count fall terms */ 2, /* for undergraduate */ 1), /* retention */ 'A', 1, 'B', 1, 'C', 1, 'D', 1, 0), 0) fall_retention, a.unit_abbrev FROM /* Begin subquery b */ /* This subquery consolidates information from subquery a. */ (SELECT year cohort_year, classif, DECODE ( SUBSTR ( degree_year, 1, 1), '1', 1, 0) degrees_earned, TO_NUMBER (DECODE ( SUBSTR ( degree_year, 8, 1), '0', DECODE ( SUBSTR ( degree_year, /* moves summer degrees to */ 2, /* previous year */ 6), TO_CHAR (year), SUBSTR ( degree_year, 2, 6), '199900', '199899', TO_CHAR (TO_NUMBER (SUBSTR ( degree_year, 2, 6)) - 101)), SUBSTR ( degree_year, 2, 6))) year_degree_earned, student_ssn, unit, unit_abbrev FROM /* Begin subquery a */ /* This subquery defines the student_analytical cohort, "b", and joins it with student_analytical to determine bachelor degrees. The following parameters are requested at runtime :beginning_year - cohort starting year (for example 199091) :campus - blank for all by campus, S for system wide or unit letter :population - O for Official, blank for Combined :classification - blank for no breakdown by classification, any other character breaks by classification :full_time - blank for all, F for full time, P for part time (student_analytical definition) :credits - cohort includes all students with credits >= :credits, use 12 with blank :full_time to match standard "full time" reports :entry_status - N, R (includes S), or T */ (SELECT b.year, DECODE ( :classification, ' ', ' ', b.classif) classif, MIN (DECODE ( SUBSTR ( /* This code places years where */ LTRIM (uwsa.student_analytical.deg1_earned), /* a bachelor degree was earned */ 1, /* at the front of the line */ 1), 'B', '1', '9') || TO_CHAR (uwsa.student_analytical.year) || DECODE ( uwsa.student_analytical.term, 'S', '0', '1', '1', /* corrects sort order */ 'W', '2', '2', '3', '3', '4', uwsa.student_analytical.term)) degree_year, b.student_ssn, b.unit, decode ( :campus, 'S', 'System', b.unit_abbrev) unit_abbrev FROM uwsa.student_analytical b, uwsa.student_analytical WHERE b.student_ssn = uwsa.student_analytical.student_ssn AND b.year <= uwsa.student_analytical.year AND b.unit = uwsa.student_analytical.unit AND b.year >= :beginning_year AND b.term = '1' AND b.unit <> 'R' AND (:campus IN ('S', ' ') OR b.unit = :campus) AND DECODE ( b.entr_status, 'S', 'R', b.entr_status) = :entry_status AND DECODE ( :population, 'O', b.regular_credits, b.total_credits) >= :credits AND DECODE ( :population, 'O', b.official_flag, 'Y') = 'Y' AND b.classif IN ('A', 'B', 'C', 'D') AND (:full_time NOT IN ('F', 'P') OR :full_time = 'F' AND DECODE ( :population, 'O', b.part_time_official, b.part_time) = 'N' OR :full_time = 'P' AND DECODE ( :population, 'O', b.part_time_official, b.part_time) = 'Y') GROUP BY b.year, DECODE ( :classification, ' ', ' ', b.classif), b.student_ssn, b.unit, decode ( :campus, 'S', 'System', b.unit_abbrev))) a, /* End subquery a */ /* End subquery b */ uwsa.student_analytical WHERE a.student_ssn = uwsa.student_analytical.student_ssn AND a.cohort_year <= uwsa.student_analytical.year AND a.unit = uwsa.student_analytical.unit AND (a.degrees_earned = 0 OR a.year_degree_earned >= DECODE ( uwsa.student_analytical.term, 'S', DECODE ( uwsa.student_analytical.year, a.cohort_year, uwsa.student_analytical.year, 199900, 199899, uwsa.student_analytical.year - 101), uwsa.student_analytical.year)) AND DECODE ( :population, 'O', uwsa.student_analytical.regular_flag, 'Y') = 'Y' AND (uwsa.student_analytical.classif IN ('A', 'B', 'C', 'D') OR SUBSTR ( LTRIM (uwsa.student_analytical.deg1_earned), 1, 1) = 'B') GROUP BY a.cohort_year, a.classif, DECODE ( uwsa.student_analytical.term, 'S', DECODE ( uwsa.student_analytical.year, a.cohort_year, uwsa.student_analytical.year, 199900, 199899, uwsa.student_analytical.year - 101), uwsa.student_analytical.year), DECODE ( a.year_degree_earned, DECODE ( uwsa.student_analytical.term, 'S', DECODE ( uwsa.student_analytical.year, a.cohort_year, uwsa.student_analytical.year, 199900, 199899, uwsa.student_analytical.year - 101), uwsa.student_analytical.year), a.degrees_earned, 0), a.student_ssn, a.unit_abbrev)) /* End subquery c */ /* End subquery d */ GROUP BY cohort_year, unit_abbrev, DECODE ( classif,
'A', '1-Fresh ',
'B', '2-Soph ',
'C', '3-Junior',
'D', '4-Senior',
'Undergrd'),
(TRUNC ( year_retained, -2) - TRUNC ( cohort_year, -2)) / 100 + 1, cohort_count, degrees_to_date ORDER BY unit_abbrev, cohort_year, DECODE ( classif,
'A', '1-Fresh ',
'B', '2-Soph ',
'C', '3-Junior',
'D', '4-Senior',
'Undergrd'),
(TRUNC ( year_retained, -2) - TRUNC ( cohort_year, -2)) / 100 + 1 ---------------------------------------------------
Lee
Leroy Kemnitz wrote:
> All - > > I have not done tuning a great deal so bear with me. I currently have a > table that consists of 10 million rows - warehouse. The largest table I > have. A few months ago a query took around 30 minutes to run against > this table. This was acceptable. Now the query takes almost 5 hours!!! > > The db is 9.2.0.4 running on aix 5.2. I have stats that I ran on the > db during the query run and it shows the db working but not maxed out. > The tablespace is at 75% used, not seeing waits. There is alot of disk > reads. No disk sorts. I am seeing a high 'Physical Blks per Read %' > but I am attrbiuting that to the datafiles being on one disk. I do plan > on rearranging them after the query finishes and hope this helps. > > I am looking for other ideas of what could be wrong with the table or > places to look. I will continue to search for ideas at metalink and in > the books. > > > Lee > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request_at_freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- >
-- LeRoy Kemnitz UW System Administration Database Administrator 780 Regent Street, #246 Madison, WI 53714 Phone: (608) 265 -5775 Fax: (608) 265 - 2090 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 29 2004 - 14:29:02 CDT
 |
 |