Re: reduce wait times

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 17 Feb 2004 19:02:25 -0800
Message-ID: <1ac7c7b3.0402171902.1c598692@posting.google.com>

"gp" <gieppetto_at_tiscali.it> wrote in message news:<c0r11t$h2t$1_at_grillo.cs.interbusiness.it>...
> My oracle version is 8.1.6.0
>
> sort_area_size=4M
> disk sort percent 0,02
> rows per sort 66
> sorts (disk) 293
> sorts (memory) 1301136
> sorts (rows) 86708973
>
>
> Hash_area_size=128M
> the query :
> SELECT s.sid, u.segtype, blocks FROM v$sort_usage u, v$session s WHERE
> u.session_addr = s.saddr;
>
> return no rows.
>

a few parameters of interest:

compatible                    8.1.6
optimizer_mode                RULE

I don't think that the optimizer could throw a hash join at a query if it wanted to with those settings. I'm not going to look, as I'm too far along with CBO to look back to RULE at this point.

is there any chance that you can do the following:

-1. read the concepts guide, administrators guide, etc so that you are prepared for upgrading from 8.1.6 to 8.1.7. plan it out in detail. 0. download 8.1.7 software and 8.1.7.4.1, 8.1.7.4.13 patchsets

   (as well as 8.1.7 for P IV OUI, if needed) 1. run the full routine to get a cold backup, full export, configuration files backup, full system backup, etc. use compress=N for export. unload DDL to create user accounts with existing passwords. get the backup sets onto another system and to tape. read the logs of your backups, etc.
2. uninstall 8.1.6.0.0, remove HKLM\Software\Oracle from reg. reboot. remove c:\program files\oracle.

3. install 8.1.7.0.0
4. apply patchset 8.1.7.4.1
5. apply patchset 8.1.7.4.13
6. create new database
7. create required tablespaces (as LMTs)
8. create app owner accounts
9. grant roles, sys_privs, quotas to app owner accounts

10. pre-create any objects for which you wish to change storage settings

11. import objects/data from dump files
12. compile invalid objects
13. gather stats using DBMS_STATS for all app_owner schemas,

cascade=>TRUE
14. install perfstat
15. adjust initialization parameters to sensible values (ditch RULE, try CHOOSE).

start tuning all over again, on something that we can assist you with.

Pd Received on Tue Feb 17 2004 - 21:02:25 CST