====== Database_Versions ====== ===== Why can Toad sometimes seem to run slower on Oracle 10g? ===== The short answer is that this is an Oracle 10g DBA best practices issue - and __not a Toad problem__ per se. Toad just happens to expose the symptons since it makes such frequent access to the data dictionary. Oracle versions prior to 10g offered two optimizers: cost and rule - with rule being the default. And very importantly, regardless of which optimizer your database applications used - Oracle internally was using the rule based optimizer for all data dictionary access. Now when using a tool like Toad where we shield users from knowing lots of data dictionary views - we hide that all behind our GUI. But Toad makes numerous SELECT's against the data dictionary - so this issue is more noticeable in Toad than many other applications. Now that does not mean it's a Toad bug, but simply Toad has exposed an important performance issue that needs addressed for optimal performance of all your database apps. With Oracle 10g, now data dictionary queries (and in fact all non-hinted queries) explicitly default to using the cost based optimizer - which means you now need accurate and fresh statistics for best performance. Of course Oracle 10g ships with an automated job to supposedly do this for you - but it's been our experience that this automated process leaves lots to be desired in terms of reliable and reasonable performance expectations. So we strongly recommend that DBA's make data dictionary stats collection part of their job - and schedule their own manual processes to collect those stats. The following two commands are usually sufficient: * exec dbms_stats.gather_dictionary_stats; * exec dbms_stats.gather_fixed_objects_stats; These two commands should probably be run at least once per week for best results. And of the two commands, the first is for **V$** tables and the more critical of the two - since Toad makes far less calls to the **X$** tables handled by the second command. Please consider making these calls either a scheduled PL/SQL job or possibly implementing them via on instance start-up database level trigger. Now to give you an example of how much difference this process can make - we've clocked screens in Toad (regardless of Toad version) that run in a flash on pre-10g, but that take large amounts of time on a default install of 10g (sometimes as long as 45 minutes). But once the proper statistics are collected, performance behavior returns to at least the pre-10g expectations - and sometimes even improves. But regardless - failure to collect data dictionary stats is one of the most common issues we hear about after people upgrade their Oracle to 10g. And again, while you may have first experienced this performance problem using Toad - it nonetheless exists quite globally. So using these commands will speed up most if not all your database activities - regardless of what tool you're using. So this is not a Toad specific recommendation or work-around. It's just a good 10g best-practice recommendation for any DBA. ---- One point of note, queries on 10G (version 10.1.0.2 only) against DBA_FREE_SPACE can take //forever// to run. This is caused by the new 10g Recycle Bin. If you purge this you will find that the queries take much less time. The commands you need are : ''PURGE RECYCLEBIN;'' or if you are a DBA enabled user : ''PURGE DBA_RECYCLEBIN;'' This problem is actually bug 3492682 and is fixed in the 10.1.0.3 patch set and in the 10.2.1.0 base release of the Oracle software. It is not a problem in versions prior to 10.1.0.3. --- //[[Norman@Dunbar-it.co.uk|Norman Dunbar]] 2006/09/19 12:07// ---- ===== What version Oracle SQL*Net client should be used with Toad? ===== That's easy - generally speaking the latest and greatest will work best. For example, let's assume you have Oracle 8i, 9i and 10g databases you need to connect to. While you could use an 8i or 9i client install on your Windows PC - some key newer features in Oracle 10g do not function properly unless you use a 10g client. And generally speaking - most newer clients are generally backwards compatible with most users' needs. There is only one key exception - 9i and 10g clients will **NOT** permit access to Oracle 7.x databases. So if you need to connect to database versions from 7.x through 10g, then you'll need two Oracle clients: one 10g for latest and greatest, and one 9i or 8i for connecting to older 7.x databases. ---- You may also find that later versions of 10g client will not permit you to connect to anything older than an 8.1.7.4 database. --- //[[Norman@Dunbar-it.co.uk|Norman Dunbar]] 2006/09/19 12:15// ---- ===== What version Oracle database servers does Toad support? ===== Remember first that Toad can connect to any database on your network for which you have access privileges. So it does not matter to Toad if that database server is running Windows, Linux, Unix, or any other server operating system. But as to database versions, Toad supports a wide range: from Oracle 7.3 all the way up to 10g. However we generally only QA against the last major point release per major release (else the QA combinations would overwhelm us). So generally speaking, it's safe to assume the following database versions are supported: * 7.X - last point release being 7.3.4.5 * 8i - last point release being 8.1.7.4 * 9i R2 - last point release being 9.2.0.8 * 10g R1 - last point release being 10.1.0.5 * 10g R2 - last point release being 10.2.0.3 Furthermore, Toad does not care whether the database server is running a 32-bit or 64-bit operating system, nor does Toad care whether the Oracle database is 32-bit or 64-bit. Toad functions equally well with all the various hardware platforms for running Oracle database server engines. Remember too - that it's always best whenever possible to have your SQL*Net client at the latest and greatest as well. So for example, don't use an 8.1.7.0 client to talk to an 8.1.7.4 database. Rather use an 8.1.7.4 or 9i or 10g client. However this is a common problem. Many Toad errors or issues can often be quickly remedied by following this advice. Here's a chart from Oracle metalink on client version support: {{oracle client versions.jpg}} ===== How quickly does Toad offer new Oracle version support? ===== Toad is one of the most aggressive tools when it comes to supporting new Oracle releases - both in our standard product features, and the optional DBA module. Generally speaking as a premier Oracle partner, we actively participate in all new Oracle version betas as soon as possible. Our goal is to support basic database connectivity and full backwards compatibility of existing Toad features ASAP - often at nearly the same time frame as the new Oracle release. We often refer to this as new Oracle version tolerance. So our goal is to be tolerant ASAP - and hopefully at the same time as the Oracle release (depending of course on our own release schedule). Next we strive to add new Oracle version feature support - again trying to shoot for nearly the same time. However, sometimes we cannot 100% know for sure how a new Oracle feature will actually work until the actual Oracle release date (i.e. Oracle features sometimes evolve during the Oracle beta program - and thus we have a moving target). But it's safe to assume that no matter what the scenario - Toad will be one of the leading tools to implement any new Oracle version tolerance and new feature support. We take great pride in this fact. ===== Does Toad support Oracle 10g Express Edition? ===== Yes. Since Oracle 10g Express Edition or XE is simply a watered down version of Oracle 10g, Toad works just fine with it. Of course Toad has to live by that database's limitations. So for example since Oracle Express does not support partitioned objects, Toad must function within those limits. Thus the Toad features for partitioning are still active for this database version, but Toad will leave it up to Oracle to give you the error message. But other than these Oracle Express imposed limits, there are no problems with using Toad against Oracle Express. ===== Does Toad support Oracle RDB, Times Ten, or Berkeley DB? ===== The answer is **NO** to all of these. Essentially, these are all radically different database engine technologies acquired by Oracle over the years. Toad does not currently support connecting to these different database server engines. However we fully expect the Times Ten memory resident technology to see more integration with the standard RDBMS server engine - so we remain vigilant for that platform. ===== Under 10g I cannot see the SQL running when I look in V$SESSION ===== Up until 10g it was possible to extract the SQL_HASH_VALUE from V$SESSION for a particular SID and then interrogate V$SQL to find out the statement that was running. This applied even if the statement was standalone or buried deep within a PL/SQL stored procedure and/or anonymous block. Toad's session browser used this to display the current statement and the explain plan for said statement. From 10g onwards, the behaviour of Oracle has changed and this no longer applies. All you get now is the hash value for the top level PL/SQL. \\ \\ From metalink, note **406452.1** : **Applies to:** Oracle Server - Enterprise Edition - Version: 10.2.0.2. This problem can occur on any platform. \\ \\ **Symptoms:** v$sql and other v$sql* views (eg: v$sqlarea) display values that are apparently wrong: * No sql information in v$sql and other views (v$sqlarea), ie sql_id, sql_address, sql_hash_value are null * The information in the view, ie sql_text does not follow the sql trace file, it remains the same, even if the trace file shows that the currently running query has changed. \\ **Changes:** This situation is particular only to the 10g release. For systems migrated from inferior releases it may be even more obvious, especially when user defined tools rely on these columns. \\ \\ **Cause:** The situation is caused by an architectural change in the 10g release: * From 10.2 onwards the v$sql columns (sql_id, sql_address and so on) will only show the top level user cursor, if any. * When using a remote procedure call, such as a rpc call from a client (eg forms client) requests that a plsql object is executed on the server, there is no top level sql and as such we don't see the sql_id in v$session. Several bugs had this problem as subject, all have been closed as non-bugs, with the above explanation: * Bug 5146994 - CANNOT FIND RECURSIVE SQL IN V$SQLAREA FOR A RUNNING SNAPSHOT REFRESH JOB * Bug 5724661 Abstract: WRONG VALUES FOR SQL_ID IN V$SESSION \\ **Solution:** It must be understood that this is the intended 10g behaviour for these particular columns. If the application relies on the previous 9.2 behaviour to monitor the query execution, its logic is to be changed to reflect this new behaviour. Currently there is no v$ view that would indicate the currently running command except for the top level sql; as such an Enhancement Request has been created: * 5823030 PLEASE ADD ADDITIONAL COLUMNS IN V$SQL VIEWS TO POINT TO THE CURRENT SQL \\ **References:** Bug 5724661 - WRONG VALUES FOR SQL_ID IN V$SESSION :-(