Skip to Content
author's profile photo Former Member
Former Member

Question about Tracing database and Database Grant

Hello All,

I am currently doing analysis for database performance for our system and I am trying to use Tracing Database for that.

Our current situation is,

- I have created tracing database with the help of the use who don't have dba rights but still it is working fine.

I have 4 question.

1. I have given below rights to my user then only system allows me to create Tracing database with that user.

- I would like to know what is the impact of each grant. So basically, I want to know use of each grant in detail. Please help me with some document which I can refer for it.

GRANT SET ANY USER DEFINED OPTION TO "dba";

GRANT SET ANY SYSTEM OPTION TO "dba";

GRANT SET ANY SECURITY OPTION TO "dba";

GRANT SET ANY PUBLIC OPTION TO "dba";

GRANT ALTER DATABASE TO "dba";

GRANT PROFILE TO dba;

2. What will be the impact if I will run Tracing database for longer time.

3. Will it slow down system if there are more than 50 users are using database at the same time when tracing database is running.

4. How to analyze the tracing database and what all information I can get with the help of tracing database. Basically, I need some document which I can refer for the same.

Thanks in advance...

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Mar 26, 2015 at 03:22 PM


    Hello,

    1. Permissions to start a tracing session (Q1):

    To answer your question first, you can find descriptions for each system privileges here ,

    However, for the user to start a tracing session, you wouldn't have to grant the above system privileges you mentioned.

    2. Impact on the Database (Q2 + Q3):

    Running a tracing session for the database would effect the performance depending on which diagnostic tracing level you have selected for the tracing session:

    • Level 0: Nearly no Impact on performance( it keeps the tracing session running, but not sending any tracing data to the tracing table)
    • Level 1: negligible impact on performance.
    • Level 2: medium impact on performance—up to, but not more than, a 20% overhead
    • Level 3: greatest impact on performance—greater than 20% overhead

    The higher the level, more information would be captured in the tracing tables.

    And yes, if there are more than 50 users using database at the same time when tracing database is running, it will relatively slow down the system as it's capturing more information and sending the data to the tracing tables.

    3. Analyze a Tracing session (Q4):

    To analyze the tracing session in Application Profiling Wizard, the user need to meet the same prerequisites as the Database Tracing Wizard)

    Here is a list of the related tutorials on the DCX:

    Tutorial: Diagnosing deadlocks

    Tutorial: Diagnosing slow statements

    Tutorial: Diagnosing index fragmentation

    Tutorial: Diagnosing table fragmentation

    Tutorial: Baselining with procedure profiling

    SQL Anywhere's wiki page: Performance and Tuning also have detailed documentation regarding Set up/Analyze the tracing database.

    Hope the above information helps

    Best Wishes,

    Sherry Jin

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.