Some slight modifications to the codeigniter profiler to dynamically display session vars, and to add EXPLAIN to all queries.

Profiler.php

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * CodeIgniter
 *
 * An open source application development framework for PHP 4.3.2 or newer
 *
 * @package     CodeIgniter
 * @author      ExpressionEngine Dev Team
 * @copyright   Copyright (c) 2008 - 2010, EllisLab, Inc.
 * @license     http://codeigniter.com/user_guide/license.html
 * @link        http://codeigniter.com
 * @since       Version 1.0
 * @filesource
 */

// ------------------------------------------------------------------------

/**
 * CodeIgniter Profiler Class
 *
 * This class enables you to display benchmark, query, and other data
 * in order to help with debugging and optimization.
 *
 * Note: At some point it would be good to move all the HTML in this class
 * into a set of template files in order to allow customization.
 *
 * @package     CodeIgniter
 * @subpackage  Libraries
 * @category    Libraries
 * @author      ExpressionEngine Dev Team
 * @link        http://codeigniter.com/user_guide/general/profiling.html
 */
class CI_Profiler {

    var $CI;

    function CI_Profiler()
    {
        $this->CI =& get_instance();
        $this->CI->load->language('profiler');
    }

    // --------------------------------------------------------------------

    /**
     * Auto Profiler
     *
     * This function cycles through the entire array of mark points and
     * matches any two points that are named identically (ending in "_start"
     * and "_end" respectively).  It then compiles the execution times for
     * all points and returns it as an array
     *
     * @access  private
     * @return  array
     */
    function _compile_benchmarks()
    {
        $profile = array();
        foreach ($this->CI->benchmark->marker as $key => $val)
        {
            // We match the "end" marker so that the list ends
            // up in the order that it was defined
            if (preg_match("/(.+?)_end/i", $key, $match))
            {
                if (isset($this->CI->benchmark->marker[$match[1].'_end']) AND isset($this->CI->benchmark->marker[$match[1].'_start']))
                {
                    $profile[$match[1]] = $this->CI->benchmark->elapsed_time($match[1].'_start', $key);
                }
            }
        }

        // Build a table containing the profile data.
        // Note: At some point we should turn this into a template that can
        // be modified.  We also might want to make this data available to be logged

        $output  = "\n\n";
        $output .= '<div class="width2">';

        $output .= "\n\n<table class='display stylized'>\n";
        $output .= '<caption style="margin-top: 20px">'.$this->CI->lang->line('profiler_benchmarks').'</caption>';
        $output .= "\n";


        foreach ($profile as $key => $val)
        {
            $key = ucwords(str_replace(array('_', '-'), ' ', $key));
            $output .= "<tr><td>".$key."</td><td>".$val."</td></tr>\n";
        }

        $output .= "</table>\n";
        $output .= "</div>";
        return $output;
    }

    // --------------------------------------------------------------------

    /**
     * Compile Queries
     *
     * @access  private
     * @return  string
     */
    function _compile_queries()
    {
        $dbs = array();

        // Let's determine which databases are currently connected to
        foreach (get_object_vars($this->CI) as $CI_object)
        {
            if (is_object($CI_object) && is_subclass_of(get_class($CI_object), 'CI_DB') )
            {
                $dbs[] = $CI_object;
            }
        }

        if (count($dbs) == 0)
        {
            $output  = "\n\n";
            $output .= "\n\n<table class='display stylized'>\n";
            $output .= '<caption style="margin-top: 20px">'.$this->CI->lang->line('profiler_queries').'</caption>';
            $output .= "\n";
            $output .="<tr><td>".$this->CI->lang->line('profiler_no_db')."</td></tr>\n";
            $output .= "</table>\n";
            return $output;
        }

        $output  = "\n\n";

        foreach ($dbs as $db)
        {

            $output .= "\n\n<table class='display stylized'>\n";
            $output .= '<caption style="margin-top: 20px">'.$this->CI->lang->line('profiler_database').': '.$db->database.''.$this->CI->lang->line('profiler_queries').': '.count($this->CI->db->queries).'</caption>';
            $output .= "\n";

            if (count($db->queries) == 0)
            {
                $output .= "<tr><td'>".$this->CI->lang->line('profiler_no_queries')."</td></tr>\n";
                $output .= "</table>";
            }
            else
            {
                foreach ($db->queries as $key => $val)
                {
                    $time = number_format($db->query_times[$key], 4);
                    $output .= "\n\n<table class='display stylized' style='margin-top: 50px; table-layout: fixed'>\n";
                    $output .= '<tr>';
                    $output .= '<th>Time</th>';
                    $output .= '<th colspan="8">Query</th>';
                    $output .= '</tr>';

                    $output .= '<tr>';
                    $output .= '<td>' . $time . '</td>';

                    $formatted_val = str_replace('<', '&lt;', $val);
                    $formatted_val = str_replace('>', '&gt;', $val);

                    $output .= '<td colspan="8" style="word-wrap: break-word;">' . $formatted_val . '</td>';
                    $output .= '</tr>';
                    if (preg_match("/\SELECT\b/i", $val)){
                        $explain = $this->explainQuery($val);
                        $output .= '<tr>';
                        $output .= '<th>Select Type</th>';
                        $output .= '<th>Table</th>';
                        $output .= '<th>Type</th>';
                        $output .= '<th>Possible Keys</th>';
                        $output .= '<th>Key Used</th>';
                        $output .= '<th>Key Size</th>';
                        $output .= '<th>Ref</th>';
                        $output .= '<th>Rows</th>';
                        $output .= '<th>Extra</th>';
                        $output .= '</tr>';

                        foreach ($explain as $var){
                            $output .= '<tr>';
                            $output .= '<td>' . $var['select_type'] . '</td>';
                            $output .= '<td>' . $var['table'] . '</td>';
                            $output .= '<td>' . $var['type'] . '</td>';
                            $output .= '<td style="word-wrap: break-word;">' . $var['possible_keys'] . '</td>';
                            $output .= '<td style="word-wrap: break-word;">' . $var['key'] . '</td>';
                            $output .= '<td>' . $var['key_len'] . '</td>';
                            $output .= '<td>' . $var['ref'] . '</td>';
                            $output .= '<td>' . $var['rows'] . '</td>';
                            $output .= '<td style="word-wrap: break-word;">' . $var['extra'] . '</td>';
                            $output .= '</tr>';
                        }
                        $output .= '</table>';

                    }
                }
            }

        }

        return $output;
    }


    // --------------------------------------------------------------------

    /**
     * Compile $_GET Data
     *
     * @access  private
     * @return  string
     */
    function _compile_get()
    {
        $output  = "\n\n";
        $output .= '<div class="width2">';

        $output .= "\n\n<table class='display stylized'>\n";
        $output .= '<caption style="margin-top: 20px">'.$this->CI->lang->line('profiler_get_data').'</caption>';
        $output .= "\n";

        if (count($_GET) == 0)
        {
            $output .= "<tr><td>".$this->CI->lang->line('profiler_no_get')."</td></tr>";
        }
        else
        {

            foreach ($_GET as $key => $val)
            {
                if ( ! is_numeric($key))
                {
                    $key = "'".$key."'";
                }

                $output .= "<tr><td>&#36;_GET[".$key."] </td><td'>";
                if (is_array($val))
                {
                    $output .= "<pre>" . htmlspecialchars(stripslashes(print_r($val, true))) . "</pre>";
                }
                else
                {
                    $output .= htmlspecialchars(stripslashes($val));
                }
                $output .= "</td></tr>\n";
            }
        }
        $output .= "</table>\n";
        $output .= "</div>";
        return $output;
    }

    // --------------------------------------------------------------------

    /**
     * Compile $_POST Data
     *
     * @access  private
     * @return  string
     */
    function _compile_post()
    {
        $output  = "\n\n";
        $output .= '<div class="width2">';

        $output .= "\n\n<table class='display stylized'>\n";
        $output .= "\n";
        $output .= '<caption style="margin-top: 20px">'.$this->CI->lang->line('profiler_post_data').'</caption>';
        $output .= "\n";

        if (count($_POST) == 0)
        {
            $output .= "<tr><td>".$this->CI->lang->line('profiler_no_post')."</td></tr>";
        }
        else
        {
            foreach ($_POST as $key => $val)
            {
                if ( ! is_numeric($key))
                {
                    $key = "'".$key."'";
                }

                $output .= "<tr><td>&#36;_POST[".$key."] </td><td>";
                if (is_array($val))
                {
                    $output .= "<pre>" . htmlspecialchars(stripslashes(print_r($val, true))) . "</pre>";
                }
                else
                {
                    $output .= htmlspecialchars(stripslashes($val));
                }
                $output .= "</td></tr>\n";
            }
        }
        $output .= "</table>";
        $output .= "</div>";

        return $output;
    }

    // --------------------------------------------------------------------

    /**
     * Show query string
     *
     * @access  private
     * @return  string
     */
    function _compile_uri_string()
    {
        $output  = "\n\n";
        $output .= '<div class="width2">';

        $output .= '<table class="display stylized">';
        $output .= "\n";
        $output .= '<caption style="margin-top: 20px">'.$this->CI->lang->line('profiler_uri_string').'</caption>';
        $output .= "\n";

        if ($this->CI->uri->uri_string == '')
        {
            $output .= "<tr><td>".$this->CI->lang->line('profiler_no_uri')."</td></tr>";
        }
        else
        {
            $output .= "<tr><td>".$this->CI->uri->uri_string."</td></tr>";
        }

        $output .= "</table>";
        $output .= "</div>";

        return $output;
    }

    function _compile_session_data()
    {
        $CI =& get_instance();
        $CI->load->library('session');

        $output  = "\n\n";
        $output .= '<div class="width2">';

        $output .= '<table class="display stylized">';
        $output .= "\n";
        $output .= '<caption style="margin-top: 20px">SESSION DATA</caption>';
        $output .= "\n";
        foreach ($_SESSION as $key => $var){
            $output .= "<tr><td>". $key. "</td><td>". $var ."</td></tr>";
        }
        $output .= "</table>";
        $output .= "</div>";

        return $output;
    }

    // --------------------------------------------------------------------

    /**
     * Show the controller and function that were called
     *
     * @access  private
     * @return  string
     */
    function _compile_controller_info()
    {
        $output  = "\n\n";
        $output .= '<div class="width2">';

        $output .= '<table class="display stylized">';
        $output .= "\n";
        $output .= '<caption style="margin-top: 20px">'.$this->CI->lang->line('profiler_controller_info').'</caption>';
        $output .= "\n";

        $output .= "<tr><td>".$this->CI->router->fetch_class()."/".$this->CI->router->fetch_method()."</td></tr>";


        $output .= "</table>";
        $output .= "</div>";

        return $output;
    }
    // --------------------------------------------------------------------

    /**
     * Compile memory usage
     *
     * Display total used memory
     *
     * @access  public
     * @return  string
     */
    function _compile_memory_usage()
    {
        $output  = "\n\n";
        $output .= '<div class="width2">';
        $output .= '<table class="display stylized">';
        $output .= "\n";
        $output .= '<caption style="margin-top: 20px">'.$this->CI->lang->line('profiler_memory_usage').'</caption>';
        $output .= "\n";

        if (function_exists('memory_get_usage') && ($usage = memory_get_usage()) != '')
        {
            $output .= "<tr><td>Current</td><td>".number_format($usage).' bytes</td></tr>';
        }
        else
        {
            $output .= "<tr><td>".$this->CI->lang->line('profiler_no_memory_usage')."</td></tr>";
        }

        if (function_exists('memory_get_peak_usage') && ($usage = memory_get_peak_usage(true)) != '')
        {
            $output .= "<tr><td>Peak</td><td>".number_format($usage).' bytes</td></tr>';
        }
        else
        {
            $output .= "<tr><td>".$this->CI->lang->line('profiler_no_memory_usage')."</td></tr>";
        }

        $output .= "</table>";
        $output .= '</div>';


        return $output;
    }

    // --------------------------------------------------------------------

    /**
     * Run the Profiler
     *
     * @access  private
     * @return  string
     */
    function run()
    {
        $output = "<div id='codeigniter_profiler' style='clear:both; width: 960px; margin-left: auto; margin-right: auto;'>";

        $output .= $this->_compile_queries();
        $output .= $this->_compile_session_data();
        $output .= $this->_compile_memory_usage();
        $output .= $this->_compile_benchmarks();
        $output .= $this->_compile_uri_string();
        $output .= $this->_compile_controller_info();
        $output .= $this->_compile_get();
        $output .= $this->_compile_post();

        $output .= '</div>';

        return $output;
    }

    private function explainQuery($explain_query)
    {
        $CI =& get_instance();
        $CI->load->database();

        $query = "EXPLAIN $explain_query;";

        $result = $CI->db->query($query);
        if (!$result) return;

        if ($result->num_rows() > 0){
            foreach ($result->result() as $row){
                $data[] = array(
                    'select_type'    => $row->select_type,
                    'table'          => $row->table,
                    'type'           => $row->type,
                    'possible_keys'  => $row->possible_keys,
                    'key'            => $row->key,
                    'key_len'        => $row->key_len,
                    'ref'            => $row->ref,
                    'rows'           => $row->rows,
                    'extra'          => $row->Extra
                );
           }
        }
        $result->free_result();
        return $data;
    }

}

// END CI_Profiler class

/* End of file Profiler.php */
/* Location: ./system/libraries/Profiler.php */