Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Usage » Monitoring and Improving Database Performance » Application profiling tutorials » Tutorial: Diagnosing table fragmentation

 

Lesson 1: Setting up table fragmentation

This tutorial assumes you have created the test database. If you have not, see Lesson 1: Creating the test database.

Tip

You can copy and paste the SQL statements in this tutorial into Interactive SQL.

To set up table fragmentation
  1. Start Sybase Central and connect to the test database app_profiling.db with the user ID DBA and the password sql.

    If you are unfamiliar with starting Sybase Central and connecting to a database, see Connect to a local database.

  2. In the left pane, select the app_profiling - DBA database, and then choose File » Open Interactive SQL.

    Interactive SQL starts and connects to the app_profiling - DBA database.

  3. In Interactive SQL, run the following SQL statements to introduce table fragmentation:

    1. Create the table:

      CREATE TABLE "DBA"."tablefrag" (
      "id" UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT, 
      "val1" LONG VARCHAR NULL, 
      "val2" LONG VARCHAR NULL, 
      "val3" LONG VARCHAR NULL, 
      "val4" LONG VARCHAR NULL, 
      "val5" LONG VARCHAR NULL, 
      "val6" LONG VARCHAR NULL, 
      "val7" LONG VARCHAR NULL, 
      "val8" LONG VARCHAR NULL, 
      "val9" LONG VARCHAR NULL, 
      "val10" LONG VARCHAR NULL,
      PRIMARY KEY ( id ) );
    2. Create a procedure to insert values into the table:

      CREATE PROCEDURE "DBA"."proc_tablefrag"( ) 
         BEGIN 
            DECLARE I INTEGER; 
            SET I = 0; 
            WHILE I < 1000 
               LOOP 
                  INSERT INTO "DBA"."tablefrag" ( "val1" ) 
                  VALUES('a'); 
                  SET I = I + 1; 
               END LOOP; 
         END;
    3. Insert values:

      CALL proc_tablefrag( );
    4. Update the values in the table:

      UPDATE "DBA"."tablefrag" 
      SET "val1" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val2" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val3" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val4" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val5" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val6" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val7" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val8" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val9" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
          "val10" = 'abcdefghijklmnopqrstuvwxyz0123456789';
    5. Commit the changes you made to the database:

      COMMIT;
  4. Exit Interactive SQL.