Éibhear Ó hAnluain
f33add7663
+ contrib/pls-mode.el: pls-mode for emacs. Used for building PL/SQL files. + docs/oracle-lisp-functions.txt: Another document file + For creating explain plans.
184 lines
7.1 KiB
EmacsLisp
Executable file
184 lines
7.1 KiB
EmacsLisp
Executable file
;; (load-file "enode-experiments.el")
|
|
|
|
;; The customisation group. Will in the ENODE group.
|
|
(defgroup enode-planner nil
|
|
"A group for customising the ENODE-PLANNER functionality."
|
|
:group 'ENODE
|
|
:version "21.2"
|
|
)
|
|
|
|
;; What table the execution plan should be made from
|
|
(defcustom enode-planner-plan-table "PLAN_TABLE"
|
|
"The table that the will be used for generating an execution plan for
|
|
queries. Defaults to PLAN_TABLE, but can, of course, be anything else."
|
|
:type 'string
|
|
:group 'enode-planner
|
|
:version "21.2"
|
|
)
|
|
|
|
(defun enode-oracle-remove-prompt-from-plan-output (plan-buffer)
|
|
"A function to clean the explain plan output of the sqlprompt string"
|
|
(let ((sql-prompt (enode-oracle-get-sql-prompt)))
|
|
(save-excursion
|
|
(set-buffer plan-buffer)
|
|
(goto-char (point-min))
|
|
(while (search-forward sql-prompt nil t)
|
|
(replace-match "" nil nil)
|
|
)
|
|
)
|
|
)
|
|
)
|
|
|
|
(defun enode-plan-buffer ()
|
|
"A function to return the buffer assigned for the explain plan output"
|
|
(get-buffer-create "*enode-explain-plan*"))
|
|
|
|
(defun get-statement-id ()
|
|
"A function to generate a unique statement id for the explain plan"
|
|
(format "ENODE_%s_%s"
|
|
(format-time-string "%Y%m%d")
|
|
(format-time-string "%H%M%S"))
|
|
)
|
|
|
|
(defun prepare-sql-buffer-for-command ()
|
|
"A function to prepare the SQLi buffer for an outputting command. It sets
|
|
properties of the SQL*Plus process to value that make an explain plan output
|
|
somewhat pretty."
|
|
(enode-oracle-sqlplus-set-param "heading" "off")
|
|
(enode-oracle-sqlplus-set-param "linesize" "1024")
|
|
(enode-oracle-sqlplus-set-param "pagesize" "0")
|
|
)
|
|
|
|
(defun reset-sql-buffer-after-command (page-size line-size)
|
|
"A function to return the SQL*Plus process' setting to 'original' values. As
|
|
well as setting PAGE-SIZE and LINE-SIZE, it also turns 'heading'
|
|
on and 'sqlnumber' on."
|
|
(enode-oracle-sqlplus-set-param "heading" "on")
|
|
(enode-oracle-sqlplus-set-param "pagesize" page-size)
|
|
(enode-oracle-sqlplus-set-param "linesize" line-size)
|
|
)
|
|
|
|
(defun get-sql-explain-plan-command (enode-plan-statement-id)
|
|
"A function to return the 'explain plan...' statement. It sets the
|
|
statement_id to ENODE-PLAN-STATEMENT-ID. It also includes the sql command from
|
|
the current buffer -- i.e. the command you want to analyse."
|
|
(format
|
|
"explain plan\nset statement_id = '%s'\ninto %s\nfor\n%s;"
|
|
enode-plan-statement-id
|
|
enode-planner-plan-table
|
|
(get-sql-command-to-explain)
|
|
)
|
|
)
|
|
|
|
(defun get-sql-command-to-explain ()
|
|
"A function to return as a string the SQL command in the current buffer.
|
|
It assumes there is only one command. It replaces empty lines with a space and
|
|
the ';' character with nothing."
|
|
(replace-regexp-in-string "^[$\n]" " "
|
|
(replace-regexp-in-string ";" "" (buffer-string)))
|
|
)
|
|
|
|
(defun get-explain-plan-output-command (enode-plan-statement-id)
|
|
"A function to return the command to generate the nice explain plan output.
|
|
It uses ENODE-PLAN-STATEMENT-ID to determine which explain plan we want to
|
|
look at."
|
|
(format "SELECT LPAD ( ' ', LEVEL - 1 ) || decode ( position, null, '', position || '-' ) || operation || decode ( options, null, '', ' ' || options ) || decode ( object_name, null, '', ' ' || object_name ) || decode ( optimizer, null, '', ' Optimiser=''' || optimizer || '''' ) || decode ( cost, null, '', ' Cost=' || cost ) || decode ( cardinality, null, '', ' Cardinality=' || cardinality ) FROM %s START WITH id = 0 AND statement_id = '%s' CONNECT BY PRIOR id = parent_id AND statement_id = '%s';"
|
|
enode-planner-plan-table
|
|
enode-plan-statement-id
|
|
enode-plan-statement-id)
|
|
)
|
|
|
|
;;;; Change this to use enode-oracle-run-sql-command
|
|
(defun enode-prepare-explain-plan (enode-plan-statement-id)
|
|
"A function to prepare an explain plan. That is, it takes the SQL command
|
|
in the current buffer and generates an explain plan in the PLAN_TABLE in it,
|
|
using ENODE-PLAN-STATEMENT-ID as the statement_id value."
|
|
(save-excursion
|
|
;; Get the full explain plan command and the SQLi buffer to run it in.
|
|
(let ((enode-sql-command
|
|
(get-sql-explain-plan-command enode-plan-statement-id))
|
|
(enode-sql-buffer sql-buffer)
|
|
)
|
|
;; Run the command.
|
|
(comint-redirect-send-command-to-process
|
|
enode-sql-command
|
|
(enode-temp-output-buffer)
|
|
enode-sql-buffer
|
|
nil
|
|
t
|
|
)
|
|
;; Allow the command to complete.
|
|
(enode-wait-for-command)
|
|
)
|
|
)
|
|
)
|
|
|
|
;;;; Change this to use enode-oracle-run-sql-command
|
|
(defun enode-output-explain-plan (enode-plan-statement-id)
|
|
"A function to display an explain plan that is already in the PLAN_TABLE. It
|
|
identifies the plan with ENODE-PLAN-STATEMENT-ID."
|
|
(save-excursion
|
|
;; Get the linesize and pagesize values the command to run
|
|
;; to generate the output and the SQLi buffer in which to run it.
|
|
(let ((enode-line-size (enode-oracle-sqlplus-get-linesize))
|
|
(enode-page-size (enode-oracle-sqlplus-get-pagesize))
|
|
(enode-plan-output-command
|
|
(get-explain-plan-output-command enode-plan-statement-id))
|
|
(enode-sql-buffer sql-buffer)
|
|
)
|
|
;; Empty the explain plan output buffer.
|
|
(enode-clean-buffer (enode-plan-buffer))
|
|
;; Tell SQL*Plus we want pretty output for this command.
|
|
(prepare-sql-buffer-for-command)
|
|
;; Run the command.
|
|
(comint-redirect-send-command-to-process
|
|
enode-plan-output-command
|
|
(enode-plan-buffer)
|
|
enode-sql-buffer
|
|
nil
|
|
t)
|
|
;; Allow the command to complete.
|
|
(enode-wait-for-command)
|
|
;; We're done with the nice output. Return SQL*Plus to it regular ugly
|
|
;; stuff.
|
|
(reset-sql-buffer-after-command
|
|
enode-page-size enode-line-size)
|
|
)
|
|
)
|
|
)
|
|
|
|
(defun get-explain-plan (&optional present-plan-command-output-p)
|
|
"A function to display an explain plan for an SQL command in the current
|
|
buffer."
|
|
;; We want to call this as a command
|
|
(interactive "P")
|
|
;; For feedback purposes
|
|
(setq enode-oracle-progress-message ".")
|
|
;; Generate a statement_id value for this plan.
|
|
(let ((plan-statement-id (get-statement-id)))
|
|
;; Generate the plan in the PLAN_TABLE
|
|
(enode-prepare-explain-plan plan-statement-id)
|
|
(if present-plan-command-output-p
|
|
(save-excursion
|
|
(display-buffer (get-buffer " *enode-comint-sql-output*"))
|
|
(read-from-minibuffer "Hit return to continue...")))
|
|
;; Send a nice representation of the plan to the plan buffer.
|
|
(enode-output-explain-plan plan-statement-id)
|
|
;; Clean up the buffer
|
|
(enode-oracle-remove-prompt-from-plan-output (enode-plan-buffer))
|
|
;; Show us the plan in another window.
|
|
(pop-to-buffer (enode-plan-buffer))
|
|
)
|
|
)
|
|
|
|
(defun get-explain-plan-for-region (beg end &optional present-plan-command-output-p)
|
|
"A function to take an SQL statement from the region and to
|
|
get an explain plan for it."
|
|
(interactive "r\nP")
|
|
(let ((plan-command-buffer (enode-stage-buffer))
|
|
(plan-command (buffer-substring beg end)))
|
|
(set-buffer plan-command-buffer)
|
|
(insert plan-command)
|
|
(get-explain-plan present-plan-command-output-p)
|
|
)
|
|
) |