Q
Problem solve Get help with specific problems with your technologies, process and projects.

Fluctuating build times for static tables

The process for building a number of static tables from OLTP tables has suddenly increased two- or threefold. We conclude that the user processes have had no influence on the time changes. No known database or systems changes have been identified. No new events have been linked to the build extension. Any ideas on the fluctuating build times?

The process for building a number of static tables from OLTP tables has suddenly increased two- or threefold. We are using HP-UX 11 Oracle 9r2. The build, today, snapped back to the original time. The build has been run several times a day in whole or by single tables to gather statistics for diagnosis. The most remarkable outcome has been the fluctuation in build time per table. For example, Table A may take 15 minutes and Table B may take over an hour. The reverse may be true for the next run. The tables may both run long on a subsequent run.

Some of the diagnosis efforts include user processes and counts, Sar, Top, OS stats, DBstats, comparing historic...

stats, analyzing current events, rubuilding the analyze tables and indexes, system reboot, etc. We conclude that the user processes have had no influence on the time changes. No known database or systems changes have been identified. No new events have been linked to the build extension.

Any ideas on the fluctuating build times?

The best way to diagnose this problem is to trace the sessions that do the builds. Use the supplied package DBMS_SUPPORT to create trace files including wait information, then format the trace files with TKPROF and examine the output.

Note that DBMS_SUPPORT is not installed by default; create it by logging in as SYS and execute $ORACLE_HOME/rdbms/admin/dbmssupp.sql.

This was last published in July 2006

Dig Deeper on Oracle database performance problems and tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close