Note: Burleson Oracle Consulting is the leader in Oracle Emergency Support and is available to assist with any Oracle database problem, anywhere in the world, anytime.

 

Adding Missing Oracle Indexes An Oracle Financial application shop in New York called and said that their performance degraded as more data was entered into the tables. A quick check of v$sql_plan using my plan9i.sql script looked like this: Full table scans and counts OWNER NAME NUM_ROWS C K BLOCKS NBR_FTS ---------- ------------------------ -------- - - -------- ------- APPLSYS FND_CONC_RELEASE_DISJS 14,293 N 4,293 498,864 APPLSYS FND_CONC_RELEASE_PERIODS 384,173 N 67,915 134,864 DONALD PERSON_LOGON_ID 18,263,390 N 634,272 96,212 DONALD SITE_AMDMNT 2,371,232 N 51,020 50,719 DONALD CLIN_PTCL_VIS_MAP 23,123,384 N 986,395 11,273 Here we see a huge number of large-table, full-table scans. A quick look into v$sql revealed that the rows returned by each query was small, and a common WHERE clause for many queries looked like this: WHERE customer_status = ‘:v1’ and customer_age > :v2; A quick creation of a concatenated index on customer_status and customer_age resulted in a 50x performance improvement and reduced disk I/O by over 600 percent. In another memorable case on an 8.1.6 database, my access.sql script revealed suspect large-table, full-table scans: Full table scans and counts OWNER NAME NUM_ROWS C K BLOCKS NBR_FTS ---------- -------------------- ------------ - - -------- -------- APPLSYS FND_CONC_RELEASE_DISJS 1,293,292 N K 65,282 498,864 APPLSYS FND_CONC_RELEASE_PERIODS 4,373,362 N K 62,282 122,764 APPLSYS FND_CONC_RELEASE_STATES 974.193 N K 9,204 98,122 APPLSYS FND_CONC_PP_ACTIONS 715,021 N 6,309 52,036 APPLSYS FND_CONC_REL_CONJ_MEMBER 95,292 N K 4,409 23,122 The DBA had created an index on the order_date column and was surprised that their order_date index was not being used, primarily because their boss was too cheap to pay for him to attend an Oracle8i new features class. Creating the function-based index on to_char(order_date,’MON-DD’)  resulted in an immediate 5x performance improvement. If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress. It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: http://www.rampant-books.com/book_ 2005_1_awr_proactive_tuning.htm