Database Tracing In English

The interweb has finally run out of storage space for images. Or, I've run out of patience pressing Alt + Print Screen.

Either way, this article is...

• a text-only description

• of how to get the query plan

• in SQL Anywhere Version 11.0.1.2276

• for a SQL statement

• that lives inside a block of code...

...like an application, or a stored procedure, or a trigger, or something as simple as a batch in ISQL containing a cursor fetch loop.

It's not easy. It should be easy, because ISQL has a wonderful "Plan Viewer", but it's not; here's why:

There... is... no... plan...

For example, if you have a BEGIN block containing a FOR loop that runs just fine in ISQL, albeit slowly, when you try to run Tools - Plan Viewer on the whole block you get this message:

There is no plan for the SQL statement.
[Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'DECLARE' on line 2

If, in foolish desperation, you highlight the FOR loop and try to run the Plan Viewer on that, you'll see a message like this:

There is no plan for the SQL statement.
[Sybase][ODBC Driver][SQL Anywhere]Plan can not be generated for this type of statement

Who knew?

But wait! There is a way! It's called Database Tracing! and it comes in the box with SQL Anywhere! (take THAT, evil Borg!)

Note: If I spoke German (I don't) this article could have been "Database Tracing In German". The specific language isn't important except for the fact it is NOT "Database Tracing In Hieroglyphs". In other words, all text, no pictures. "A picture is worth a thousand words" means words are necessary to explain what you are looking at. Well, except for pronography art :)

Database Tracing In 64 Easy Steps

There's only 55 or 60 steps, some of these are [ahem] editorial comment:

1. Start Sybase Central.

2. Connect to your database. Don't forget to do that. If you forget, your whole experience will be an empty one.

3. See the "Mode" item appear in the menu bar. Q: Why wasn't it there before, disabled but visible? A: Bad GUI design.

4. Pay NO ATTENTION to the Mode item, it just leads to the Application Profiling Wizard which in turn leads to unhappiness.

5. Instead, right-click on the grey "oil drum" icon that represents your database in the left pane, and click on "Tracing..."

6. See the "Database Tracing Wizard" window appear.

7. ...or maybe not. Everything looks different the second time you do this. Q: Why is this stuff so hard to explain? A: Bad GUI design.

8. Let's assume this is your first time, life's too short to describe every branch of a tree that has a thousand forks.

9. Click on Next.

10. See the "Tracing Detail Level" window appear.

11. Check "High detail".

12. Click on Next.

13. See the "Edit Tracing Levels" window appear.

14. Uncheck everything... really, uncheck all 7 items, one by one, click click click, they're useless.

15. Click on New.

16. See the "Add Tracing Level" dialog box appear.

17. Choose these values:

    Scope: database
    Tracing type: plans_with_statistics
    Condition: none

18. Take a moment to wonder why, if those are the default values for a "New" entry, why wasn't that entry already there? A: Bad GUI design.

19. At this point, if you listen carefully, you may hear a voice say "Don't do that! It's too expensive!"

20. Disregard the voice, click on Add.

21. Click on Next.

22. See the "Create External Database" window appear.

23. Check "Do not create a new database".

24. Click on Next.

25. See the "Start Tracing" window appear.

26. Check "Save tracing in this database".

27. Check "No limit".

28. Click on Finish.

29. Wait a bit... it's either going to work... or it's not (see next section).

30. If it does work...

31. See the Sybase Central display appear as if nothing was happening... but it is, behind the scenes.

32. To confirm that, look for this message in your database's console log...

    Diagnostic tracing is being sent to 'links=tcpip{host=127.0.0.1};ENG=sss;DBN=ddd;Encryption=SIMPLE'

33. Switch over to ISQL, or your application, whatever, and run your test(s).

34. When you're done running your test(s), switch back to Sybase Central.

35. Right-click on the database icon in the left pane, and click on Tracing - Stop tracing with save.

36. See the "Stop Tracing With Save" progress window appear and disappear... cool horizontal barber pole!

37. NOW, go back and click on Mode - Application Profiling on the menu bar.

38. ...that's "Mode" and then "Application Profiling", not "Application Profiling". Q: Why does a sub-menu item have the same name as a menu item? A: Bad GUI design.

39. See the "Application Profiling Wizard" window appear.

40. Click on Cancel.

41. See the pretty blue "Application Profiling Details" pane appear at the bottom of the Sybase Central window.

42. Click on the line of text "Open Analysis File or Connect to a Tracing Database". Yes, it's a link... you didn't know that, did you? A: Bad GUI design.

43. See the "Open Analysis Or Connect To Tracing Database" dialog box appear.

44. Check "In a tracing database"... even though you don't think you have a tracing database, you do... it's YOUR database.

45. Click on Open.

46. See the "Connect to a Tracing Database" dialog box open.

47. Fill in these fields to connect to YOUR database...
    

    Identification tab - User ID: dba
    Identification tab - Password: xxx
    Database tab - Server name: sss
    Database tab - Database name: ddd

48. Click on OK.

49. See the "Tracing Database" display appear in the "Application Profiling Details" pane.

50. Don't touch the buttons! Look down, wayyyy down, see the line of upside-down tabs? You didn't notice them at all, did you? A: Bad GUI design.

51. Click on the "Database Tracing Data" tab.

52. See a second line of upside-down tabs appear, with the "Summary" tab showing.

53. Find the query you're interested in... not the BEGIN block line, but SELECT you coded in the FOR statement.

54. On that query line, right-click "Show the Detailed SQL Statements for the Selected Summary SQL Statement"... yes, we have no verbosity.

55. See the display switch to the "Details" tab.

56. Find the query you're interested in... again...

57. Take a moment to remember the "LogExpensiveQueries" feature in 9.0.2, and how you thought that was complicated.

58. Right-click "View More SQL Statement Details for the Selected Statement".

59. See the "SQL Statement Details" dialog box appear.

60. See the line of upside-down tabs at the bottom of the dialog box. You STILL didn't notice them appear? Yeah, neither did I. We're just dumb, I guess.

61. Click on the "Query Information" tab.

62. See the plan! Woohoo!

63. It's just like the ISQL Plan Viewer except crappier.

64. Why "crappier"? Try saving the plan to a file, you'll see. Same thing in 12, thanks for asking.

But... you do get to see the query plan! So that's good news.

ATTACH... TRACING... could... not... connect...

Here is Database Tracing's most popular common error message:

An error has occurred - tracing was not attached to the database.
ATTACH TRACING could not connect to the tracing database
[Sybase][ODBC Driver][SQL Anywhere]ATTACH TRACING could not connect to the tracing database
SQLCODE: -1097
SQLSTATE: HY000
SQL Statement: ATTACH TRACING TO LOCAL DATABASE

You can get that error...

• if you specify the dbsrv11 -x none command line option (TCPIP is required even for a local database), or

• if you specify the dbsrv11 -sb 0 command line option, or

• if you use the SQL Anywhere 12.0.0.2589 engine on a database created with 11.0.1.2276.

The latter combination (V12 Database Tracing on a V11 database) comes with a bit more excitement later on: the engine crashes on shutdown.

In every case, the "ATTACH TRACING could not connect" message gives no clue as to the real problem, and that's a real problem in itself.