Bikle.com

Ajax Demo:

Plain HTML
(no Ajax here):

of URLs:

perftune1.html Dan Bikle 05-25-2001 -------------------- Performance Tuning an AdManager Database This paper describes my efforts to enhance the performance of a specific Oracle database. This particular database is the major piece of a back-end tier in an application named AdManager (software sold by http://www.engage.com). Older versions (V2,V3,V4) of AdManager are ofter referred to as Accipiter. This Accipiter software is used to serve advertisements on websites. The main symptoms which brought this performance problem to my attention were the long durations of the canned reports which come with Accipiter. When I am offered performance tuning engagements, I follow the plan listed below. High-Level Plan -Obtain an inventory of the major objects (of a static nature) inside and around the poorly performing database. Static Inventory Details of This Database -Obtain an inventory of the major objects and activities inside and around the poorly performing database which could be considered "non-static". Non-Static-Inventory -Document activities which contend with each other for resources. Contentious-Activities -Obtain performance measurements of various activities. Performance-Measurements-1 -Use information gained from the above activities, knowledge about Oracle mechanisms, and Oracle DBA best practices to suggest changes to the database, the application, and user behavior which will lead to better performance. Performance-Suggestions -Implement (and document) changes to a test system. Documented-Changes -Obtain reproduceable performance measurements of various activities. Performance-Measurements-2 -Decide where to go next. Decisions In a nutshell, that is the plan. I will now dive into the details of how I implemented the above plan to tune this specific database. Details With the above plan of action layed out before us, I move to the first task: StaticInventory Obtain an inventory of the major objects (of a static nature) inside and around the poorly performing database. Host Inventory (Hardware, OS [version and patches], Disks, RAM) HostInventory.txt Database Inventory (Software information, Data File Layout, Init.ora parameters, Net8 parameters, Segments) dbSoftware.txt dbFileLayout.txt dbInit-ora.txt dbNet8params.txt dbSegments.txt Application Information ApplicationInformation.html Non-Static-Inventory A good place to start with the Non-Static-Inventory is the process listing: ProcessListing.txt Next, I obtain some session information from Oracle: OracleSessInfo.txt Some other glimpses of machine activity are linked below: vmstat.txt iostat.txt chckRam.txt qryThruPut.txt Contentious-Activities When I look at the information I've gathered so far it's easy to pinpoint a number of active Accipiter processes. Next, I start an Accipiter report which ramps up the host's activity level. The script which does this is linked below: rptbld-sh.txt Some information about the host's activity (while the Accipiter report is running) is linked below: ProcessListing2.txt OracleSessInfo2.txt vmstat2.txt iostat2.txt chckRam2.txt qryThruPut2.txt Performance-Measurements-1 Here is a copy of the sql statement associated with the report: sql_text.txt Here is a copy of output from AUTOTRACE: autotrace.txt Here is a copy of EXPLAIN PLAN output: explainPlan4qry.txt Eventually the report errored out. It appears it exhausted available memory of the host: reportOut.txt At this time I finished a bstat-estat report and obtained a copy of the resulting report: bstat-estat-rpt.txt Next, I attempted to gain a look at how the SQL from the canned report and my script appeared in the V$SQL view: sql_text2.txt Performance-Suggestions My initial performance tuning suggestion is to create some indexes: perfSuggestions.html Documented-Changes I implemented the above suggestions (creation of some indexes) on the CA test system. When indexes are created in a database, it is important that the DBA then run the SQL ANALYZE command against all the affected tables so that the optimizer within the Oracle kernel can properly work its magic. A screen dump of this process is linked below: crIdxAnlz.txt Performance-Measurements-2 Obtaining a consistent performance measurement after running the above script is simple; I just complete an EXPLAIN PLAN as I had done before I had created the indexes: explainPlanCA.txt The link below compares the results of the explain plans before and after the addition of the indexes: compareExplain.txt Decisions Since the addition of indexes made no improvement to the EXPLAIN PLAN, I look for other ideas to enhance the performance of the query. When I reinspect the file below I make a note the two largest tables which are getting full table scans. perfSuggestions.html Two fully scanned tables: CREATIVE CPNCRT I decide to explore the idea of adding even more indexes (or enhancing existing indexes) to the above tables such that index scans replace full table scans in the EXPLAIN PLAN. To do this, I need to pinpoint columns in the associated query which belong to the two above tables. After I compare descriptions of the above tables to the query, I see that these columns are in the query: CREATIVE.crtid CREATIVE.adurl CREATIVE.admap CREATIVE.addesc CREATIVE.adhtml CPNCRT.ccid CPNCRT.campid CPNCRT.crtid After a bit of thought, I revise my CREATE INDEX script. The new CREATE INDEX script is displayed below: crIdx2.sql After creating the above indexes on the CA test system, I re-ran EXPLAIN PLAN: explainPlanCA2.txt I do see that some of the indexes are being used in the new EXPLAIN PLAN. A comparison of the two explain plans is displayed below: compareExplain2.txt It is easy to see that full table scans of the tables CPNCRT, CAMPAIGN, and CREATIVE have been replaced by index scans which are a more efficient type of scan. Next, I began work on reducing the size of the largest table in the query: AREASUM. According to the link below (search on AREASUM to find the acutal line in the file) , I can see that the AREASUM is consuming over 3GB of space in the DATA03 tablespace: DatabaseInventory.txt One of the main users of the system asked the question, "What kind of a performance boost will we get if we archive data out of the AREASUM table which is older than a year?" My efforts to answer this question are chronicled below: archiveAreasum.html Since the above effort yielded such positive results, I decided to rerun the script which emulates a users sending a report request to the report engine. The results of that effort are displayed in the screen dump below: rptbld601.html While working with the rptbld.exe module, I came to the opinion that it is a seriously defective piece of software and we should look at ways of relying on other methods to display data which resides in the database. Based on what I've found out about the behavior of the Accipiter software on the CA test system (am-bikle2) I request that I be allowed to write up a detailed plan which contains the following steps: -Pick a time during the xy-bikle1 service-window to commence work. -Disconnect Accipiter from Oracle on xy-bikle1 for 15 minutes to quiesce the AREASUM table. -Rename the AREASUM table to AREASUMold. -Create a new partitioned AREASUM table (with partitioned indexes) which is empty. -Reconnect Accipiter to Oracle so that we may continue serving ads. -Test that Accipiter ad serving is unaffected by the new-empty AREASUM table and that the new-empty AREASUM is filling with data from the Ad Engines. -Slowly copy data from the last year from AREASUMold into AREASUM in a way which does not overload the xy-bikle1 Oracle-transaction-backup mechanism (we must avoid this overload or else Oracle will stop functioning and the whole system will lock up). -Test the Site-Area report in a way which does not trigger the Accipiter rptbld.exe run-out-of-memory bug. Once I have written up the details associated with the above steps, and then successfully implemented them, I suggest some longer range goals: -Partition/Archive other large Accipiter tables which have been accumulating data since 1998. -Develop some simple SQL*Plus reports which give us the data we need without relying upon the bug infested Accipiter reporting mechanism. -Evaluate the usefulness of upgrading Accipiter from 4.0.2.12 to AdManager 5.2.