cancel
Showing results for 
Search instead for 
Did you mean: 

Question about Tracing database and Database Grant

Former Member
0 Kudos

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...

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos


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

Former Member
0 Kudos

Thank you very very much for this information. It will really help us...

Thanks again.

Answers (0)