The length of time for each operator and the execution phases for each query are captured during the query execution.
To generate the timing diagram along with the HTML query plan, use set statistics plan_html, timing_html on.
> set statistics plan_html, timing_html on
> select title, type
from titles
where title in
(select title from titles,
titleauthor, authors
where titles.title_id = titleauthor.title_id
and titleauthor.au_id = authors.au_id
and authors.state = "CA")
and title in
(select title from titles, publishers
where titles.pub_id = publishers.pub_id
and publishers.state = "CA")
> go
The Query Timings portion shows the statistics captured during the execution of the query. The distribution of the execution time is shown in two dimensions: per operator and execution phase. The timing legend indicates the time distribution for each operator in the different query execution phases such as acquire, open, first fetch, subsequent fetches, and close.
Hovering your cursor over the colored execution phase time bar displays the actual execution time. Execution time is expressed in seconds, with up to microsecond (6-digit) precision.
In comparison, the same query is shown using the set statistics time command to view the query plan output in text format:
> set statistics timing on
> select title, type
from titles
where title in
(select title from titles,
titleauthor, authors
where titles.title_id = titleauthor.title_id
and titleauthor.au_id = authors.au_id
and authors.state = "CA")
and title in
(select title from titles, publishers
where titles.pub_id = publishers.pub_id
and publishers.state = "CA")
> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode
STEP 1
The type of query is SELECT.
11 operator(s) under root
|ROOT:EMIT Operator (VA = 11)
|
| |NESTED LOOP JOIN Operator (VA = 10) (Join Type: Inner Join)
| |
| | |NESTED LOOP JOIN Operator (VA = 8) (Join Type: Left Semi Join)
| | |
| | | |SORT Operator (VA = 4)
| | | | Using Worktable1 for internal storage.
| | | |
| | | | |N-ARY NESTED LOOP JOIN Operator (VA = 3) has 3 children.
| | | | |
| | | | | |SCAN Operator (VA = 0)
| | | | | | FROM TABLE
| | | | | | authors
| | | | | | Table Scan.
| | | | | | Forward Scan.
| | | | | | Positioning at start of table.
| | | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | | With LRU Buffer Replacement Strategy for data pages.
| | | | |
| | | | | |SCAN Operator (VA = 1)
| | | | | | FROM TABLE
| | | | | | titleauthor
| | | | | | Using Clustered Index.
| | | | | | Index : taind
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Keys are:
| | | | | | au_id ASC
| | | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | | With LRU Buffer Replacement Strategy for data pages.
| | | | |
| | | | | |SCAN Operator (VA = 2)
| | | | | | FROM TABLE
| | | | | | titles
| | | | | | Using Clustered Index.
| | | | | | Index : titleidind
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Keys are:
| | | | | | title_id ASC
| | | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | | With LRU Buffer Replacement Strategy for data pages.
| | |
| | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Inner Join)
| | | |
| | | | |SCAN Operator (VA = 5)
| | | | | FROM TABLE
| | | | | publishers
| | | | | Table Scan.
| | | | | Forward Scan.
| | | | | Positioning at start of table.
| | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | With LRU Buffer Replacement Strategy for data pages.
| | | | |
| | | | |SCAN Operator (VA = 6)
| | | | | FROM TABLE
| | | | | titles
| | | | | Index : titleind
| | | | | Forward Scan.
| | | | | Positioning by key.
| | | | | Keys are:
| | | | | title ASC
| | | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | | Using I/O Size 2 Kbytes for data pages.
| | | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | |SCAN Operator (VA = 9)
| | | FROM TABLE
| | | titles
| | | Index : titleind
| | | Forward Scan.
| | | Positioning by key.
| | | Keys are:
| | | title ASC
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | Using I/O Size 2 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.