3.4 PROFILING

PROFILING

Profiling shows performance-related information about the executed SQL. When profiling is enabled through SET PROFILING and SQL is executed, profiling information is collected. The collected profile information can be viewed through the show profile command.

Please refer to the following commands for profiling:

• Turn on profiling: SET PROFILING = 1;
• Turn off profiling: SET PROFILING = 0;
• Profile retrieval:
  • Detailed profile of the last executed SQL: SHOW PROFILE;
  • Execution time of profiled SQL: SHOW PROFILES;
  • Detailed profile of a specific SQL: SHOW PROFILE FOR QUERY [Profile ID]
    • The profile ID of a SQL executed through SHOW PROFILES can be obtained.
  • Detailed profile retrieval by specific element:
    • show profile cpu for query [Profile ID];
    • show profile block IO for query [Profile ID];
    • show profile memory for query [Profile ID];
    • show profile source for query [Profile ID];

The process of setting up profiling, running SQL, and checking the profiling information collected for the SQL is as follows.

SET PROFILING = 1; # Turn on profile

# SQL execute
SELECT  COUNT(*)
FROM    MYTUNDB.T_ORD_BIG T1
WHERE   T1.ORD_YMD LIKE '201701%';

SHOW PROFILE; 
# profile result
# starting 0.000077
# ... 
# statistics                   0.008959
# preparing                    0.000035
# executing                    0.205336
# ... 
# cleaning up                  0.000031

SET PROFILING = 0; # Turn off profile