SQL Performance Analyzer(SPA) compares the SQL execution result, before and after the change, and generates a report outlining the net benefit on the workload due to the changes. SQL Performance Analyzer is well integrated with existing SQL Tuning Set (STS), SQL Tuning Advisor and SQL Plan Management functionalities.
We will see this feature with practical. We will see how performance will increase after adding index on predicate. SPA will give us a nice html report with differences.
SQL> DECLARE
2 l_cursor DBMS_SQLTUNE.sqlset_cursor;
3 BEGIN
4 OPEN l_cursor FOR
5 SELECT VALUE(a)
6 FROM TABLE(
7 DBMS_SQLTUNE.select_cursor_cache(
8 basic_filter => 'sql_text LIKE ''%TEST%'' and parsing_schema_name = ''SCOTT''', attribute_list => 'ALL')
9 ) a;
10 DBMS_SQLTUNE.load_sqlset(sqlset_name => 'sqlset', populate_cursor => l_cursor);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT sql_text FROM dba_sqlset_statements WHERE sqlset_name = 'sqlset';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM TEST WHERE object_id <= 100
CREATE TABLE TEST AS SELECT * FROM all_objects
/* SQL Analyze(0) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms
SELECT COUNT(*) FROM TEST WHERE object_id <= 1000
SELECT COUNT(*) FROM TEST WHERE object_id BETWEEN 100 AND 1000
SELECT object_name FROM TEST WHERE object_id = 100
BEGIN DBMS_STATS.gather_table_stats(USER, 'TEST', cascade => TRUE); END;
SELECT object_name FROM TEST WHERE object_id = 1000
8 rows selected.
SQL>
3) Create SPA analysis task with Step 2 STS set (sqlset). And execute that analysis task before change as "before_change".
SQL> VARIABLE v_task VARCHAR2(64);
SQL> EXEC :v_task := DBMS_SQLPA.create_analysis_task(sqlset_name => 'sqlset');
PL/SQL procedure successfully completed.
SQL> PRINT :v_task
V_TASK
--------------------------------------------------------------------------------
TASK_2309
SQL> BEGIN
2 DBMS_SQLPA.execute_analysis_task(
3 task_name => :v_task,
4 execution_type => 'test execute',
5 execution_name => 'before_change');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
4) Now create B tree index on object_id(TEST) and gather stats.
SQL> CREATE INDEX TEST_IDX ON TEST(object_id);
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEST', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL>
6) Now run the report and spool it in html format.
SQL> SET LONG 1000000
SQL> SET PAGESIZE 0
SQL> SET LINESIZE 200
SQL> SET LONGCHUNKSIZE 200
SQL> SET TRIMSPOOL ON
SQL>
SQL> SPOOL D:\execute_comparison_report.html
SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task,'HTML','ALL','ALL') FROM dual;
.
.
.
<td class="s27" align="right">97</td>
<td class="s27" align="right"/>
<td class="s27" align="right"/>
</tr>
<tr bgcolor="#F7F777" valign="bottom">
<td class="s27" align="right">2</td>
<td class="s27" ora_space_char=".">
<font color="#F7F777">..</font>
TABLE ACCESS FULL
</td>
<td class="s27">TEST</td>
<td class="s27" align="right">71832</td>
<td class="s27" align="right">6967704</td>
<td class="s27" align="right">289</td>
<td class="s27" align="right">00:00:04 </td>
</tr>
</table>
<hr size="1" width="650" align="left"/>
</body>
</html>
SQL> SPOOL OFF
We will see this feature with practical. We will see how performance will increase after adding index on predicate. SPA will give us a nice html report with differences.
The following steps shows
before change activity...
1) Connect Scott user and perform below steps. We are creating table and gathering stats for the same. Then we are executing some queries on that table.
SQL> CREATE TABLE TEST AS SELECT * FROM all_objects;
Table created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEST', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM TEST WHERE object_id <= 100;
COUNT(*)
----------
99
SQL> SELECT object_name FROM TEST WHERE object_id = 100;
OBJECT_NAME
------------------------------
ORA$BASE
SQL> SELECT COUNT(*) FROM TEST WHERE object_id <= 1000;
COUNT(*)
----------
888
SQL> SELECT object_name FROM TEST WHERE object_id = 1000;
OBJECT_NAME
------------------------------
EXPACT$
SQL> SELECT COUNT(*) FROM TEST WHERE object_id BETWEEN 100 AND 1000;
COUNT(*)
----------
790
SQL>
2) Connect db as sysdba and create SQL Tunning Sets (STS) with the name sqlset and load Step 1 query into it.
SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'sqlset');
PL/SQL procedure successfully completed.
2 l_cursor DBMS_SQLTUNE.sqlset_cursor;
3 BEGIN
4 OPEN l_cursor FOR
5 SELECT VALUE(a)
6 FROM TABLE(
7 DBMS_SQLTUNE.select_cursor_cache(
8 basic_filter => 'sql_text LIKE ''%TEST%'' and parsing_schema_name = ''SCOTT''', attribute_list => 'ALL')
9 ) a;
10 DBMS_SQLTUNE.load_sqlset(sqlset_name => 'sqlset', populate_cursor => l_cursor);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT sql_text FROM dba_sqlset_statements WHERE sqlset_name = 'sqlset';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM TEST WHERE object_id <= 100
CREATE TABLE TEST AS SELECT * FROM all_objects
/* SQL Analyze(0) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms
SELECT COUNT(*) FROM TEST WHERE object_id <= 1000
SELECT COUNT(*) FROM TEST WHERE object_id BETWEEN 100 AND 1000
SELECT object_name FROM TEST WHERE object_id = 100
BEGIN DBMS_STATS.gather_table_stats(USER, 'TEST', cascade => TRUE); END;
SELECT object_name FROM TEST WHERE object_id = 1000
8 rows selected.
SQL>
3) Create SPA analysis task with Step 2 STS set (sqlset). And execute that analysis task before change as "before_change".
SQL> VARIABLE v_task VARCHAR2(64);
SQL> EXEC :v_task := DBMS_SQLPA.create_analysis_task(sqlset_name => 'sqlset');
PL/SQL procedure successfully completed.
SQL> PRINT :v_task
V_TASK
--------------------------------------------------------------------------------
TASK_2309
SQL> BEGIN
2 DBMS_SQLPA.execute_analysis_task(
3 task_name => :v_task,
4 execution_type => 'test execute',
5 execution_name => 'before_change');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
4) Now create B tree index on object_id(TEST) and gather stats.
SQL> CREATE INDEX TEST_IDX ON TEST(object_id);
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEST', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL>
5) After the change, now execute SPA analysis task "after_change".
SQL> BEGIN
2 DBMS_SQLPA.execute_analysis_task(
3 task_name => :v_task,
4 execution_type => 'test execute',
5 execution_name => 'after_change');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SQLPA.execute_analysis_task(
3 task_name => :v_task,
4 execution_type => 'compare performance',
5 execution_params => dbms_advisor.arglist(
6 'execution_name1',
7 'before_change',
8 'execution_name2',
9 'after_change')
10 );
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
6) Now run the report and spool it in html format.
SQL> SET LONG 1000000
SQL> SET PAGESIZE 0
SQL> SET LINESIZE 200
SQL> SET LONGCHUNKSIZE 200
SQL> SET TRIMSPOOL ON
SQL>
SQL> SPOOL D:\execute_comparison_report.html
SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task,'HTML','ALL','ALL') FROM dual;
.
.
.
<td class="s27" align="right">97</td>
<td class="s27" align="right"/>
<td class="s27" align="right"/>
</tr>
<tr bgcolor="#F7F777" valign="bottom">
<td class="s27" align="right">2</td>
<td class="s27" ora_space_char=".">
<font color="#F7F777">..</font>
TABLE ACCESS FULL
</td>
<td class="s27">TEST</td>
<td class="s27" align="right">71832</td>
<td class="s27" align="right">6967704</td>
<td class="s27" align="right">289</td>
<td class="s27" align="right">00:00:04 </td>
</tr>
</table>
<hr size="1" width="650" align="left"/>
</body>
</html>
SQL> SPOOL OFF
7) Open D:\execute_comparison_report.html file in browser.
SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task,'HTML','ALL','ALL') FROM dual; General Information
Execution Information:
Analysis Information:
Report Summary
Projected Workload Change Impact:
SQL Statement Count
Top 6 SQL Sorted by Absolute Value of Change Impact on the Workload
Report Details
SQL Details:
Execution Statistics:
Notes:
Before Change:
After Change:
Findings (2):
Execution Plan Before Change:
Predicate Information (identified by operation id):
Execution Plan After Change:
Predicate Information (identified by operation id):
SQL Details:
Execution Statistics:
Notes:
Before Change:
After Change:
Findings (2):
Execution Plan Before Change:
Predicate Information (identified by operation id):
Execution Plan After Change:
Predicate Information (identified by operation id):
SQL Details:
Execution Statistics:
Notes:
Before Change:
After Change:
Findings (2):
Execution Plan Before Change:
Predicate Information (identified by operation id):
Execution Plan After Change:
Predicate Information (identified by operation id):
SQL Details:
Execution Statistics:
Notes:
Before Change:
After Change:
Findings (2):
Execution Plan Before Change:
Predicate Information (identified by operation id):
Execution Plan After Change:
Predicate Information (identified by operation id):
SQL Details:
Execution Statistics:
Notes:
Before Change:
After Change:
Findings (2):
Execution Plan Before Change:
Predicate Information (identified by operation id):
Execution Plan After Change:
Predicate Information (identified by operation id):
SQL Details:
Execution Statistics:
Notes:
Before Change:
After Change:
Findings (1):
Execution Plan Before Change:
Execution Plan After Change:
SQL> SPOOL OFF
| Task Information: | Workload Information: | ||||||||||||
|
|
Execution Information:
|
|
Analysis Information:
| Before Change Execution: | After Change Execution: | ||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||
| Comparison Metric: ELAPSED_TIME | |||||||||||||||||||||||||||||||||||||
| Workload Impact Threshold: 1% | |||||||||||||||||||||||||||||||||||||
| SQL Impact Threshold: 1% | |||||||||||||||||||||||||||||||||||||
Report Summary
Projected Workload Change Impact:
| Overall Impact | : | 27.76% |
|---|---|---|
| Improvement Impact | : | 27.76% |
| Regression Impact | : | 0% |
SQL Statement Count
| SQL Category | SQL Count | Plan Change Count |
|---|---|---|
| Overall | 8 | 5 |
| Improved | 5 | 5 |
| Unchanged | 1 | 0 |
| Unsupported | 2 | 0 |
Top 6 SQL Sorted by Absolute Value of Change Impact on the Workload
object_id | sql_id | Impact on Workload | Execution Frequency | Metric Before | Metric After | Impact on SQL | Plan Change |
|---|---|---|---|---|---|---|---|
| 20 | 0fu6mv7vxjpyj | 8.81% | 1 | 4845 | 60 | 98.76% | y |
| 23 | cg89jtag4jwgy | 5.13% | 1 | 2824 | 36 | 98.73% | y |
| 26 | fqp20ap9hs2u5 | 5.13% | 1 | 2807 | 20 | 99.29% | y |
| 24 | dpb7b5g4mvqs5 | 4.36% | 1 | 2382 | 15 | 99.37% | y |
| 25 | f64bsx6sp6w9t | 4.34% | 1 | 2370 | 15 | 99.37% | y |
| 21 | 3a4ujc62aphhn | .32% | 1 | 39097 | 38925 | .44% | n |
Report Details
SQL Details:
| Object ID | : 20 |
|---|---|
| Schema Name | : SCOTT |
| SQL ID | : 0fu6mv7vxjpyj |
| Execution Frequency | : 1 |
| SQL Text | : SELECT COUNT(*) FROM TEST WHERE object_id <= 1000 |
Execution Statistics:
Stat Name | Impact on Workload | Value Before | Value After | Impact on SQL |
|---|---|---|---|---|
| elapsed_time | 8.81% | .004845 | .00006 | 98.76% |
| parse_time | -.21% | .000214 | .000216 | -.93% |
| cpu_time | 6.45% | .003466 | 0 | 100% |
| user_io_time | 0% | 0 | 0 | 0% |
| buffer_gets | 16.62% | 1028 | 3 | 99.71% |
| cost | 16.43% | 288 | 4 | 98.61% |
| reads | 0% | 0 | 0 | 0% |
| writes | 0% | 0 | 0 | 0% |
| io_interconnect_bytes | 0% | 0 | 0 | 0% |
| rows | 1 | 1 |
Notes:
Before Change:
|
After Change:
|
Findings (2):
|
Execution Plan Before Change:
| Plan Id | : 8879 |
|---|---|
| Plan Hash Value | : 1950795681 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 5 | 288 | 00:00:04 | |
| 1 | . SORT AGGREGATE | 1 | 5 | |||
| * 2 | .. TABLE ACCESS FULL | TEST | 909 | 4545 | 288 | 00:00:04 |
Predicate Information (identified by operation id):
- 2 - filter("OBJECT_ID"<=1000)
Execution Plan After Change:
| Plan Id | : 8887 |
|---|---|
| Plan Hash Value | : 190015244 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 5 | 4 | 00:00:01 | |
| 1 | . SORT AGGREGATE | 1 | 5 | |||
| * 2 | .. INDEX RANGE SCAN | TEST_IDX | 909 | 4545 | 4 | 00:00:01 |
Predicate Information (identified by operation id):
- 2 - access("OBJECT_ID"<=1000)
SQL Details:
| Object ID | : 23 |
|---|---|
| Schema Name | : SCOTT |
| SQL ID | : cg89jtag4jwgy |
| Execution Frequency | : 1 |
| SQL Text | : SELECT COUNT(*) FROM TEST WHERE object_id BETWEEN 100 AND 1000 |
Execution Statistics:
Stat Name | Impact on Workload | Value Before | Value After | Impact on SQL |
|---|---|---|---|---|
| elapsed_time | 5.13% | .002824 | .000036 | 98.73% |
| parse_time | -.63% | .000155 | .000161 | -3.87% |
| cpu_time | 6.45% | .003466 | 0 | 100% |
| user_io_time | 0% | 0 | 0 | 0% |
| buffer_gets | 16.62% | 1028 | 3 | 99.71% |
| cost | 16.48% | 288 | 3 | 98.96% |
| reads | 0% | 0 | 0 | 0% |
| writes | 0% | 0 | 0 | 0% |
| io_interconnect_bytes | 0% | 0 | 0 | 0% |
| rows | 1 | 1 |
Notes:
Before Change:
|
After Change:
|
Findings (2):
|
Execution Plan Before Change:
| Plan Id | : 8882 |
|---|---|
| Plan Hash Value | : 1950795681 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 5 | 288 | 00:00:04 | |
| 1 | . SORT AGGREGATE | 1 | 5 | |||
| * 2 | .. TABLE ACCESS FULL | TEST | 821 | 4105 | 288 | 00:00:04 |
Predicate Information (identified by operation id):
- 2 - filter("OBJECT_ID"<=1000 AND "OBJECT_ID">=100)
Execution Plan After Change:
| Plan Id | : 8890 |
|---|---|
| Plan Hash Value | : 190015244 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 5 | 3 | 00:00:01 | |
| 1 | . SORT AGGREGATE | 1 | 5 | |||
| * 2 | .. INDEX RANGE SCAN | TEST_IDX | 821 | 4105 | 3 | 00:00:01 |
Predicate Information (identified by operation id):
- 2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=1000)
SQL Details:
| Object ID | : 26 |
|---|---|
| Schema Name | : SCOTT |
| SQL ID | : fqp20ap9hs2u5 |
| Execution Frequency | : 1 |
| SQL Text | : SELECT COUNT(*) FROM TEST WHERE object_id <= 100 |
Execution Statistics:
Stat Name | Impact on Workload | Value Before | Value After | Impact on SQL |
|---|---|---|---|---|
| elapsed_time | 5.13% | .002807 | .00002 | 99.29% |
| parse_time | -4.64% | .000101 | .000145 | -43.56% |
| cpu_time | 6.45% | .003466 | 0 | 100% |
| user_io_time | 0% | 0 | 0 | 0% |
| buffer_gets | 16.63% | 1028 | 2 | 99.81% |
| cost | 16.54% | 288 | 2 | 99.31% |
| reads | 0% | 0 | 0 | 0% |
| writes | 0% | 0 | 0 | 0% |
| io_interconnect_bytes | 0% | 0 | 0 | 0% |
| rows | 1 | 1 |
Notes:
Before Change:
|
After Change:
|
Findings (2):
|
Execution Plan Before Change:
| Plan Id | : 8885 |
|---|---|
| Plan Hash Value | : 1950795681 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 5 | 288 | 00:00:04 | |
| 1 | . SORT AGGREGATE | 1 | 5 | |||
| * 2 | .. TABLE ACCESS FULL | TEST | 90 | 450 | 288 | 00:00:04 |
Predicate Information (identified by operation id):
- 2 - filter("OBJECT_ID"<=100)
Execution Plan After Change:
| Plan Id | : 8893 |
|---|---|
| Plan Hash Value | : 190015244 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 5 | 2 | 00:00:01 | |
| 1 | . SORT AGGREGATE | 1 | 5 | |||
| * 2 | .. INDEX RANGE SCAN | TEST_IDX | 90 | 450 | 2 | 00:00:01 |
Predicate Information (identified by operation id):
- 2 - access("OBJECT_ID"<=100)
SQL Details:
| Object ID | : 24 |
|---|---|
| Schema Name | : SCOTT |
| SQL ID | : dpb7b5g4mvqs5 |
| Execution Frequency | : 1 |
| SQL Text | : SELECT object_name FROM TEST WHERE object_id = 1000 |
Execution Statistics:
Stat Name | Impact on Workload | Value Before | Value After | Impact on SQL |
|---|---|---|---|---|
| elapsed_time | 4.36% | .002382 | .000015 | 99.37% |
| parse_time | -.11% | .000123 | .000124 | -.81% |
| cpu_time | 3.23% | .001733 | 0 | 100% |
| user_io_time | 0% | 0 | 0 | 0% |
| buffer_gets | 16.62% | 1028 | 3 | 99.71% |
| cost | 16.54% | 288 | 2 | 99.31% |
| reads | 0% | 0 | 0 | 0% |
| writes | 0% | 0 | 0 | 0% |
| io_interconnect_bytes | 0% | 0 | 0 | 0% |
| rows | 1 | 1 |
Notes:
Before Change:
|
After Change:
|
Findings (2):
|
Execution Plan Before Change:
| Plan Id | : 8883 |
|---|---|
| Plan Hash Value | : 1357081020 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 30 | 288 | 00:00:04 | |
| * 1 | . TABLE ACCESS FULL | TEST | 1 | 30 | 288 | 00:00:04 |
Predicate Information (identified by operation id):
- 1 - filter("OBJECT_ID"=1000)
Execution Plan After Change:
| Plan Id | : 8891 |
|---|---|
| Plan Hash Value | : 1389866015 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 30 | 2 | 00:00:01 | |
| 1 | . TABLE ACCESS BY INDEX ROWID | TEST | 1 | 30 | 2 | 00:00:01 |
| * 2 | .. INDEX RANGE SCAN | TEST_IDX | 1 | 1 | 00:00:01 |
Predicate Information (identified by operation id):
- 2 - access("OBJECT_ID"=1000)
SQL Details:
| Object ID | : 25 |
|---|---|
| Schema Name | : SCOTT |
| SQL ID | : f64bsx6sp6w9t |
| Execution Frequency | : 1 |
| SQL Text | : SELECT object_name FROM TEST WHERE object_id = 100 |
Execution Statistics:
Stat Name | Impact on Workload | Value Before | Value After | Impact on SQL |
|---|---|---|---|---|
| elapsed_time | 4.34% | .00237 | .000015 | 99.37% |
| parse_time | -2.21% | .000133 | .000154 | -15.79% |
| cpu_time | 3.23% | .001733 | 0 | 100% |
| user_io_time | 0% | 0 | 0 | 0% |
| buffer_gets | 16.62% | 1028 | 3 | 99.71% |
| cost | 16.54% | 288 | 2 | 99.31% |
| reads | 0% | 0 | 0 | 0% |
| writes | 0% | 0 | 0 | 0% |
| io_interconnect_bytes | 0% | 0 | 0 | 0% |
| rows | 1 | 1 |
Notes:
Before Change:
|
After Change:
|
Findings (2):
|
Execution Plan Before Change:
| Plan Id | : 8884 |
|---|---|
| Plan Hash Value | : 1357081020 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 30 | 288 | 00:00:04 | |
| * 1 | . TABLE ACCESS FULL | TEST | 1 | 30 | 288 | 00:00:04 |
Predicate Information (identified by operation id):
- 1 - filter("OBJECT_ID"=100)
Execution Plan After Change:
| Plan Id | : 8892 |
|---|---|
| Plan Hash Value | : 1389866015 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 30 | 2 | 00:00:01 | |
| 1 | . TABLE ACCESS BY INDEX ROWID | TEST | 1 | 30 | 2 | 00:00:01 |
| * 2 | .. INDEX RANGE SCAN | TEST_IDX | 1 | 1 | 00:00:01 |
Predicate Information (identified by operation id):
- 2 - access("OBJECT_ID"=100)
SQL Details:
| Object ID | : 21 |
|---|---|
| Schema Name | : SCOTT |
| SQL ID | : 3a4ujc62aphhn |
| Execution Frequency | : 1 |
| SQL Text | : /* SQL Analyze(0) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */ to_char(count("OWNER")),to_char(substrb(dump(min("OWNER"),16,0,32),1,120)),to_char(substrb(dump(max("OWNER"),16,0,32),1,120)),to_char(count("OBJECT_NAME")),to_char(substrb(dump(min("OBJECT_NAME"),16,0 ,32),1,120)),to_char(substrb(dump(max("OBJECT_NAME"),16,0,32),1,120)),to_char(count("SUBOBJECT_NAME")),to_char(substrb(dump(min("SUBOBJECT_NAME"),16,0,32),1,120)),to_char(substrb(dump(max("SUBOBJECT_N AME"),16,0,32),1,120)),to_char(count("OBJECT_ID")),to_char(substrb(dump(min("OBJECT_ID"),16,0,32),1,120)),to_char(substrb(dump(max("OBJECT_ID"),16,0,32),1,120)),to_char(count("DATA_OBJECT_ID")),to_cha r(substrb(dump(min("DATA_OBJECT_ID"),16,0,32),1,120)),to_char(substrb(dump(max("DATA_OBJECT_ID"),16,0,32),1,120)),to_char(count("OBJECT_TYPE")),to_char(substrb(dump(min("OBJECT_TYPE"),16,0,32),1,120)) ,to_char(substrb(dump(max("OBJECT_TYPE"),16,0,32),1,120)),to_char(count("CREATED")),to_char(substrb(dump(min("CREATED"),16,0,32),1,120)),to_char(substrb(dump(max("CREATED"),16,0,32),1,120)),to_char(co unt("LAST_DDL_TIME")),to_char(substrb(dump(min("LAST_DDL_TIME"),16,0,32),1,120)),to_char(substrb(dump(max("LAST_DDL_TIME"),16,0,32),1,120)),to_char(count("TIMESTAMP")),to_char(substrb(dump(min("TIMEST AMP"),16,0,32),1,120)),to_char(substrb(dump(max("TIMESTAMP"),16,0,32),1,120)),to_char(count("STATUS")),to_char(substrb(dump(min("STATUS"),16,0,32),1,120)),to_char(substrb(dump(max("STATUS"),16,0,32),1 ,120)),to_char(count("TEMPORARY")),to_char(substrb(dump(min("TEMPORARY"),16,0,32),1,120)),to_char(substrb(dump(max("TEMPORARY"),16,0,32),1,120)),to_char(count("GENERATED")),to_char(substrb(dump(min("G ENERATED"),16,0,32),1,120)),to_char(substrb(dump(max("GENERATED"),16,0,32),1,120)),to_char(count("SECONDARY")),to_char(substrb(dump(min("SECONDARY"),16,0,32),1,120)),to_char(substrb(dump(max("SECONDAR Y"),16,0,32),1,120)),to_char(count("NAMESPACE")),to_char(substrb(dump(min("NAMESPACE"),16,0,32),1,120)),to_char(substrb(dump(max("NAMESPACE"),16,0,32),1,120)),to_char(count("EDITION_NAME")),to_char(su bstrb(dump(min("EDITION_NAME"),16,0,32),1,120)),to_char(substrb(dump(max("EDITION_NAME"),16,0,32),1,120)) from "SCOTT"."TEST" t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL, NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/ |
Execution Statistics:
Stat Name | Impact on Workload | Value Before | Value After | Impact on SQL |
|---|---|---|---|---|
| elapsed_time | .32% | .039097 | .038925 | .44% |
| parse_time | 9.06% | .000223 | .000137 | 38.57% |
| cpu_time | 3.23% | .039867 | .038133 | 4.35% |
| user_io_time | 0% | 0 | 0 | 0% |
| buffer_gets | 0% | 1028 | 1028 | 0% |
| cost | 0% | 289 | 289 | 0% |
| reads | 0% | 0 | 0 | 0% |
| writes | 0% | 0 | 0 | 0% |
| io_interconnect_bytes | 0% | 0 | 0 | 0% |
| rows | 1 | 1 |
Notes:
Before Change:
|
After Change:
|
Findings (1):
|
Execution Plan Before Change:
| Plan Id | : 8880 |
|---|---|
| Plan Hash Value | : 1950795681 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 97 | 289 | 00:00:04 | |
| 1 | . SORT AGGREGATE | 1 | 97 | |||
| 2 | .. TABLE ACCESS FULL | TEST | 71832 | 6967704 | 289 | 00:00:04 |
Execution Plan After Change:
| Plan Id | : 8888 |
|---|---|
| Plan Hash Value | : 1950795681 |
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 97 | 289 | 00:00:04 | |
| 1 | . SORT AGGREGATE | 1 | 97 | |||
| 2 | .. TABLE ACCESS FULL | TEST | 71832 | 6967704 | 289 | 00:00:04 |
SQL> SPOOL OFF
