Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    84

    Unanswered: Monitoring Indexes and FTS

    Hi,
    How to enable monitoring on
    1.Usage of Indexes in teh database
    2.Full Table Scans taking place in teh database.
    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: Monitoring Indexes and FTS

    Originally posted by preetikate
    Hi,
    How to enable monitoring on
    1.Usage of Indexes in teh database
    2.Full Table Scans taking place in teh database.
    Thanks
    The following works on 9i

    ALTER INDEX <index_name> MONITORING USAGE; (to start monitoring)

    ALTER INDEX <index_name> NOMONITORING USAGE; (to stop Monitoring)

    You can look at into the V$OBJECT_USAGE to view the monitoring information about index.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    if you trace the sessions that the application uses and then
    TKPROF the trace files you can get a lot of great data.

    I use this as sysdba:
    PHP Code:
    select 'Execute sys.dbms_system.set_ev('||sid||','||serial#||',10046, 12, '''');'
    from v$session
    where username 
    'USERNAME_HERE'
    to turn off tracing:
    PHP Code:
    select 
    'Execute sys.dbms_system.set_sql_trace_in_session('||sid||','||serial#||',FALSE);'
    from v$session
    where username 
    'USERNAME_HERE'
    obviously you need to cut/paste the output.
    I analyze all the queries in the tkprof file and see what is using indexes and what is not. What is efficient and what reads from disk.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jan 2004
    Posts
    84
    Thanks for the response!
    Will setting trace not generate huge trace file for the session?

  5. #5
    Join Date
    Jan 2004
    Posts
    84

    Re: Monitoring Indexes and FTS

    Thanks!
    Is there any overhead on enabling INDEX Monitoring the way you have suggested?

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by preetikate
    Thanks for the response!
    Will setting trace not generate huge trace file for the session?
    It will generate tracefiles for each session.
    I suggest tracing for 10-30 minutes and then turn off tracing for each session.

    tkprof the traces and see what you see.
    then you can delete the trace files.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: Monitoring Indexes and FTS

    Originally posted by preetikate
    Thanks!
    Is there any overhead on enabling INDEX Monitoring the way you have suggested?
    No, Thereis no overhead of doing this because whenever Index is used in a given period of time, the V$OBJECT_USAGE is updated and it resides under the SYS schema, So end users will not face any kind of performance problem because of that.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •