Tools

Yes, starting with TOAD 7.3. However, the Disk I/O graph will be blank.

Timestamp is assigned to a statement at the moment it is about to be sent to SQL Monitor for output, which occurs after the execution has completed. Speaking of the "execution time". Normally statement processing consists of the parsing time (can occur once), the execution time (amount of time it took the statement to execute), and the fetching time (amount of time it took the user application to fetch records returned - usually cannot be accurately determined, who knows how one application performs a fetch, it may fetch one record, and another record two hours later). The "execution time" value is a second of the above three, a number of milliseconds that it took for OCIStmtExecute() to return, without taking into account fetching time - for the reason I mentioned above. As to Explain Plan, SQL Monitor has a built-in execution plan display for quite some time. Go to its "Options" screen, the second tab, where you can turn it on. Carefully read the information on that tab to be aware of how SQL Monitor may potentially affect the normal user application flow.

The tablespace map uses a simplified fragmentation formula. The key points in question:

• What is the 'sum of the blocks for that segment'?

SELECT sum(blocks)
FROM dba_extents
WHERE tablespace_name = :tblspace
  AND (segment_name = :segName)

Sums all the blocks - by segment name - for the segments in a tablespace. Of course this query is not run explicity, it would be far too inefficient. The query is executed without the segment_name distinction; the rest is done in memory.

• What is the 'largest block in that segment'?

Run the query. Look down through the values returned in the "BLOCKS" column. Find the biggest value. That is the largest block for that segment. We've had a number of these things from users coming through support saying, "Gee, is this really true? Is my tablespace fragmented, really???" and then I end up sending out an email like this.

• for the partition, or is it taking all the extents for the whole table across all partitions?

It's not by partition. Or table. Nowhere does partition or table appear in the map blocks. Its by segment name.

In Oracle, there are many different kinds of fragmentation - and just as many opinions on which ones really matter or how they should be calculated. One type of fragmentation, which has many names but I'll refer to as internal fragmentation attempts to measure the relative fragmentation of a segment within a tablespace. That is often call internal fragmentation due to its measure of the fragmentation of the object within the tablespace - which is what we are providing. With locally managed tablespaces, uniform extents and no disadvantage to zillions of extents as in past days - this might not be as relevant a measurement as it once was.

Another type of fragmentation attempts to measure how fragmented the tablespace itself is (sort of like how fragmented is my hard disk on my PC). This is often referred to as external fragmentation - and is NOT what we are trying to show on the tablespace map screen (although lots of people assume or prefer that we did so). While this may sound like a good measurement - there is a wide and varied opinion on how to best calculate this value. Plus for large tablespaces with tons of objects in them, it could take inordinate amounts of time to return.

We are providing feedback on the segment and not the tablespace. So the short answer is: There are several kinds of fragmentations that can be calculated and we're doing one that is probably not the one you were expecting.

• Obtain the fix for [BUG:1529107] by upgrading to Oracle9i or applying the 8.1.7.2.0 (or greater) patchset.

OR

• If you are unable to apply the bug fix, you can workaround the problem by modifying the package body of DBMS_LOGMNR_D.BUILD as follows: Please note: this workaround involves making a small change to an Oracle supplied script. You should only make changes to these scripts when directed to do so by Oracle Support
• Edit the file "dbmslmd.sql" located in the "$ORACLE_HOME/rdbms/admin" directory. Change the line:

TYPE col_desc_array IS VARRAY(513) OF col_description;

to

TYPE col_desc_array IS VARRAY(700) OF col_description;

Save the file.

• Run the newly modified script:

SQLPLUS> Connect internal
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

• Recompile the package body DBMS_LOGMNR_D:

SQLPLUS> alter package DBMS_LOGMNR_D compile body;

Explanation: The error occurs due to [BUG:1529107], which is fixed in Oracle9i and for which a fix exists starting in the 8.1.7.2.0 patchset. The number of objects handled by the DBMS_LOGMNR_D.BUILD procedure goes beyond the currently defined limit for VARRAY.

If you have echo or prompt commands in your .profile, you will get this message. What happens is that the rexec expects to get back the time, but the echo/prompt output comes back first. There are two ways to correct this.

1. remove such commands from the .profile file. some users may not want to do this.
2. correct the .profile to not do the echo/prompt when being run from a non-login event. I think the check is: if [ `tty` != 'dev/console' ]

There was a release of SQLab Xpert in June 2002 that supports Oracle 9.0.1. To get this upgrade, go to Quest's website and download a new version of TOAD Xpert. Another workaround:

1. connect TOAD as DBA (e.g. SYSTEM)
2. create new user SQLAB with
   • roles (with grant, default and admin all checked)
   • connect
   • resource
   • dba
   • select_catalog_role
   • execute_catalog_role
   • privs (with grant and admin checked)
   • execute any procedure
   • select any dictionary
   • select any table
3. open SQL Edit window, enter query, execute it
4. press the SQLAB toolbar icon to launch SQLAB
5. when prompted to create server side objects, say yes
6. use existing user (not new user) and select SQLAB user from #2
7. near end of install it shows users, press show unauthorized users button
8. select all the users you want authorized and continue
9. that's it (all done), it will now work just fine

The Kill/Trace window has been removed from this release because its functionality is now replaced by the Session Browser. The Session Browser displays parallel slave info, allows multiselect, shows more detailed Locks information, and lets you create your own filters for use in the window. Some hints to make the Session Browser look similar to Kill/Trace:

1. Press "Flip form layout" to make the tabs appear at the bottom of the window instead of on the right hand side.
2. Under the "Group by" button, select "<none>" to eliminate the treeview.
3. Press "Visible columns" to get a list of the columns that you can display inthe list of sessions. If the Session Browser is not appearing on your DBA menu, right-click over the main toolbar and choose "Customize". On the Commands tab, select DBA, and drag Session Browser from the list of commands onto your menu.

For the TOAD beta (and TOAD 8.0), the Privileges window that appeared under the Database menu in previous releases of TOAD has been removed. For customers with the DBA module, all of the information was already duplicated and presented in more detail on the Users, Roles, and Sys Privs tabs of the schema browser. For customers without the DBA module, we have now enabled limited functionality in the Roles and Sys Privs tabs to substitute for the loss of the Privileges window. If the Roles and Sys Privs tabs are not appearing in your schema browser, find the "Browser style" button on the top right hand side of the schema browser and choose "Configure Object Types" from the dropdown.

If the entry is red, then either the redo log size does not match the size of the other redo logs or there is a missing file. The count of the members should be the same across all the groups.

In the file compare options, go to the miscellaneous tab and set "line termination" to "flexible"

Check the following:

• You must have SELECT access on the Space Manager tables and on DBA_TABLESPACES
• TOAD schema must have the privileges to create and alter jobs, create and drop its own tables and procedures, and must have SELECT access on:
  • DBA_TABLESPACES
  • DBA_DATA_FILES
  • DBA_FREE_SPACE
  • V_$FILESTAT
• In addition, the init.ora file must have

  JOB_QUEUE_PROCESSES = 1

  or more as TOAD currently uses the built in Oracle job processes to schedule the data collection. So the user must have sufficient privs to schedule a job as well.

Also, remember that Space Manager cannot collect stats more than once a day, regardless of the formula specified in the setup.

On the Toad list, Norm asked how do I actually use SQL Monitor to start tracing an application before I have started running said application. It connects automatically and I want to catch it and see what it is doing.

John Dorlon replied :

• Start SQL Monitor
• Click the 'lightning bolt' button in the toolbar.
• Navigate to the application's exe file.
• Enter any parameters.
• OK

The application will be started and traced for you. As an alternative, you can use CTRL+L or File→Launch Process.

On the Toad list, Rob Z asked: Yesterday we did a [database] compare. Then I tried to copy the sync script to an editor to 'tune' it. Bad luck: no copy possible! Not with control-C, not with menu 'EDIT'→COPY or 'EDIT"→Load in external editor. Also all the icons were grayed out. Do I miss some role/privilege to do this?

Jeff replied: the Sync Script is only available for use if you have the DB Admin module. It's definitely a teaser feature, sorry :(

So there you have it, the sync script is only available if you have the module formerly known as DBA, now known as the DB Admin module.