Re: Does Oracle 8i improve SQL preformance against 8?

"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message news:auuhhtc25bu9rnt0bsi4si648jh04dtliu_at_4ax.com...
> Dear all,
>
> Our users are complaining about the slowness of report generation, so
> I wonder whether Oracle 8i (8.1.7) improves SQL performance against 8
> (8.0.5)? Also whether Impromptu 6.0 improves SQL performance against
> 5.0?
>
> This is a Data Mart application:
> 1.Oracle 8.0.5 EE for Windows NT
> 2.Impromptu 5.0
> 3.About 50 million Sales records
> 4.Compaq server with 4 Intel CPU's, 1G DRAM, and 80G RAID disks
>
> Thanks, Dino
>

tell the users to stop running reports ! create the reports at night, when no one is running ad-hoc queries, and publish them to a web server or network share. the most efficient query (in terms of database resource usage) is the query that is not run. seriously.

i really doubt that an upgrade to Oracle 8.1.7 is going to do much towards resolving a performance issue, unless there is a specific bug that you have identified as a source of a performance problem.

but by all means, upgrade from 8.0.5, not necessarily as a way to improve performance, but to move up to a supported release. (8.0.5 has been desupported, 8.0.6 is going to be desupported the end of september)

new versions sometimes have nasty bugs that can play havoc with the performance of a well tuned database environment.... be sure you test thoroughly before you implement the release into your production environment.

as far as an upgrade of Impromptu 5.0 to 6.0, it depends on what has changed. typically, newer versions add features rather than improve performance, although there are exceptions. if most of the user "wait" times are due to waits for SQL query completion,

without knowing anything about your enviroment, i suspect that the performance problem is due to queries that process large numbers of rows, running against the 50,000,000 detail rows. it is likely that these queries are also performing joins to "dimension" tables.

my recommendations to improve performance:

. measure performance
. determine performance goals
. tune the application
. tune the SQL
. tune the database
. repeat

these steps are going to get you the biggest bang for your time.

measure performance, of wait time for a specific report, or whatever. you need a benchmark to start from.

determine what performance goal is to be met, and how you are going to tell if you are meeting those performance goals.

(hint: don't use "users are no longer complaining" as your tuning goal, as you don't really have any control over that...)

tune the application

make sure you're tuning the right things. it may be that database performance is not the problem. it could be that an application server is bogged down and is the bottleneck. the actual waits for SQL completion may actually be an insiginifcant delay.

is the application pulling back 50,000,000 detail rows for each report ? it isn't realistic to expect that type of workload to complete in a couple of seconds. make sure the app is pulling back only the data that it needs, and makes effective use of data that has already been retrieved, without having to go back to the database to get the same data again.

tune the SQL

capture the SQL that is being run, and run explain plans. compare execution paths, run times and resource ussage to that for alternate execution paths. make sure the queries are using CBO, and that statistics have been computed. if indexes are being used, check that they are not in need of reorganization. rewrite the SQL. include hints. if the query is performing disk sorts, consider increasing the sort area size.

in short, ensure that the execution path chosen by oracle is the optimum execution path for the result set that the user or application needs returned.

tune the database

from there, tune the design of the database objects. consider adding indexes, partitioning large tables, etc. consider reorgs for tables and indexes.

if some of the queries are pulling "summary" results, consider creating and pre-populating smaller summary tables, and have the reports pull from them rather than the detail tables.

HTH Received on Sat Jun 02 2001 - 20:21:50 CDT