MySQL NF 5.6 - Enhanced tracing (SQL Trace 방법)

=================================================================================

* 참고

=================================================================================

http://dev.mysql.com/doc/internals/en/optimizer-tracing.html

OOW2013 - [TUT8131] Enhancing Productivity with MySQL 5.6 New Features.pptx

- https://oracleus.activeevents.com/2013/connect/sessionDetail.ww?SESSION_ID=8131

=================================================================================

use test;

drop table if exists big_table;

create table big_table

as

select a.*

  from information_schema.columns a

      ,information_schema.columns b

      ,information_schema.columns c

 limit 1000000;

select count(*) from big_table;

[출처] MySQL NF 5.6 - Enhanced tracing (SQL Trace 방법) (MySQL Power Group) |작성자 29babo

http://cafe.naver.com/mysqlpg/105

drop procedure if exists test_proc;

delimiter //

create procedure test_proc()

begin

   set @s = "";

   select 'select count(*) from big_table' into @s;

   prepare stmt from @s;

   execute stmt;

end

//

delimiter ;

call test_proc();

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

set optimizer_trace_limit        =  100;

set optimizer_trace_offset       = -100;

SET optimizer_trace_max_mem_size = 1000000;

select *               from big_table where column_name = 'CHARACTER_SET_NAME' limit 10;

select count(*) as cnt from big_table where column_name = 'CHARACTER_SET_NAME';

call test_proc();

# possibly more queries...

# When done with tracing, disable it:

SET optimizer_trace="enabled=off";

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;