Tune SQL statements

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Administer and tune schema object to support various access methods
[2] SQL Tuning Advisor
[3] Use SQL ACCESS ADVISOR.
[4] The SQL Performance Analyzer.

[1] Administer and tune schema object to support various access methods

-> Access Methods (or Paths) are something totally related to the optimizer.
-> So, what you should study here are the access methods that are possible inside
Oracle for either Tables, B-Tree Indexes, Bitmap Indexes or Clusters:

Full Table Scans
Table Access by Rowid
Sample Table Scans
Index Unique Scans
Index Range Scans
Index Full Scans
Index Fast Full Scans
Index Skip Scans
Index Join Scans
Bitmap Index Single Value
Bitmap Index Range Scans
Bitmap Merge
Bitmap Index Range Scans
Cluster Scans
Hash Scans

You will only be good in this topic with practice. In my opinion, 
studying explain plans and access paths will only give you 
theory concepts (that serves for almost nothing in the OCM exam). 
Practice with daily work is the best approach for this topic.
[2] SQL Tuning Advisor

SQL> alter session set container=cdb$root;

Session altered.

SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO

SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED


SQL> select client_name,count(*) from dba_autotask_job_history
2 group by client_name;

CLIENT_NAME COUNT(*)
---------------------------------------------------------------- ----------
auto optimizer stats collection 17
auto space advisor 17
sql tuning advisor 16

SQL> alter session set container=cdb$root;

Session altered.

SQL> select client_name,count(*) from dba_autotask_job_history
2 group by client_name;

CLIENT_NAME COUNT(*)
---------------------------------------------------------------- ----
auto optimizer stats collection 45
auto space advisor 45
sql tuning advisor 24


SQL> alter session set container=pdb2;

Session altered.

SQL>
SQL> grant connect,resource to teststs identified by teststs;

Grant succeeded.

SQL> alter user teststs default tablespace users
2 ;

User altered.

SQL> alter user teststs default tablespace users
quota unlimited on users; 2

User altered.

SQL> create table teststs.emp as select * from dba_objects;

Table created.

SQL> connect teststs/teststs@pdb2
Connected.
SQL>
SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------
EMP USERS


SQL> select count(distinct object_type) from emp where object_id in (select object_id from emp where object_type='TABLE')
2 ;

COUNT(DISTINCTOBJECT_TYPE)
--------------------------
1

SQL> set autotrace traceonly exp
SQL> r
1 select count(distinct object_type) from emp where object_id in (select object_id from emp where object_type='TABLE')
2*

Execution Plan
----------------------------------------------------------
Plan hash value: 3253359190

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 867 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | VM_NWVW_1 | 47 | 611 | 867 (1)| 00:00:01 |
| 3 | HASH GROUP BY | | 47 | 1316 | 867 (1)| 00:00:01 |
|* 4 | HASH JOIN RIGHT SEMI| | 1970 | 55160 | 866 (1)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 1970 | 27580 | 433 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 92579 | 1265K| 433 (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("OBJECT_ID"="OBJECT_ID")
5 - filter("OBJECT_TYPE"='TABLE')

SQL>

From OEM

PDB2 -> Performance -> SQL Tunig Set
-> Create STS
-> SELECT SQL and Schedule SQL Tuning Advisor.


declare
cmd varchar2(400);
sname varchar2(400);
begin
cmd := 'create index TESTSTS.IDX$_00160001 on TESTSTS.EMP("OBJECT_TYPE","OBJECT_ID")';
EXECUTE IMMEDIATE cmd;
END;
/


SQL> select count(distinct object_type) from emp where object_id in (select object_id from emp where object_type='TABLE');

Execution Plan
----------------------------------------------------------
Plan hash value: 2073332129

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 98 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | VM_NWVW_1 | 47 | 611 | 98 (3)| 00:00:01 |
| 3 | HASH GROUP BY | | 47 | 1316 | 98 (3)| 00:00:01 |
|* 4 | HASH JOIN RIGHT SEMI | | 1970 | 55160 | 97 (2)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX$_00160001 | 1970 | 27580 | 8 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| IDX$_00160001 | 92579 | 1265K| 88 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("OBJECT_ID"="OBJECT_ID")
5 - access("OBJECT_TYPE"='TABLE')


-bash-4.1$ cat EXPDAT_STS_TESTSTS2.LOG
Starting "SYS"."DP_EXPDAT_STS_TESTSTS2":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "SYSTEM"."STS_TESTSTS2_STGTAB" 1.062 MB
Total estimation using BLOCKS method: 1.062 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SYSTEM"."STS_TESTSTS2_STGTAB" 55.56 KB 7 rows
Master table "SYS"."DP_EXPDAT_STS_TESTSTS2" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.DP_EXPDAT_STS_TESTSTS2 is:
/u01/app/oracle/et/bad/EXPDAT_STS_TESTSTS2.DMP


-bash-4.1$ impdp system/oracle_4U@pdb1 DIRECTORY=PDB1_DIR DUMPFILE=EXPDAT_STS_TESTSTS2.DMP TABLES=STS_TESTSTS2_STGTAB

Import: Release 12.1.0.2.0 - Production on Sun Sep 29 14:07:10 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@pdb1 DIRECTORY=PDB1_DIR DUMPFILE=EXPDAT_STS_TESTSTS2.DMP TABLES=STS_TESTSTS2_STGTAB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."STS_TESTSTS2_STGTAB" 55.56 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sun Sep 29 14:07:55 2019 elapsed 0 00:00:42

-bash-4.1$ sqlplus system/oracle_4U@pdb1
[3] Use SQL ACCESS ADVISOR.

Via SQL Access Advisor you got recommendations to tune the specific sql statements by creating materialized views,partitions or indexes
(b-tree,bitmap and function-based).

SQL> grant dba to usr_sqlaccess_test identified by test;
sql> alter user usr_sqlaccess_test default tablespace users quota unlimited on users;
SQL> connect usr_sqlaccess_test/test
SQL> show user
USER is "USR_SQLACCESS_TEST"
SQL>
SQL> create table tbl_mv_objects
2 as
3 select * from dba_objects
4 where object_id is not null;

Table created.

SQL> insert into tbl_mv_objects
2 select * from tbl_mv_objects;

77899 rows created.

SQL> /

155798 rows created.

SQL> /

311596 rows created.

SQL> /

623192 rows created.

SQL> create table tbl_mv_extents
2 as
3 select * from dba_extents;

Table created.

SQL> insert into tbl_mv_extents
2 select * from tbl_mv_extents;

12146 rows created.

SQL> /

24292 rows created.

SQL> /

48584 rows created.

SQL> /

97168 rows created.

SQL> /

194336 rows created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TBL_MV_OBJECTS');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TBL_MV_EXTENTS');

PL/SQL procedure successfully completed.

SQL> run
1 SELECT /* SQL_ACCESS_ADVISOR */ A.OBJECT_NAME,COUNT(EXTENT_ID)
2 FROM TBL_MV_OBJECTS a,TBL_MV_EXTENTS b
3 WHERE a.object_name = b.segment_name
4* GROUP BY a.object_name

SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL
2 WHERE SQL_TEXT LIKE '%SQL_ACCESS_ADVISOR%';

SQL_ID SQL_TEXT
--------------------------------------------------------------------------------
g7w9869vxs126 SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SQL_ACCESS_ADVISOR%'

60j52tdcbhcdy SELECT /* SQL_ACCESS_ADVISOR */ A.OBJECT_NAME,COUNT(EXTENT_ID) FROM TBL_MV_OBJEC
TS a,TBL_MV_EXTENTS b WHERE a.object_name = b.segment_name GROUP BY a.object_name

SQL> set linesize 150
col owner format a10
col description format a40
select * from dba_sqlset;

ID CON_DBID NAME OWNER DESCRIPTION CREATED LAST_MODIFIED STATEMENT_COUNT
---------- ---------------------------------------- -----------------
4 2980127119 STS_FOR_SQLACCESS_ADV SYS Sep 30 2019 11:15:45 Sep 30 2019 11:16:37 1
1 2980127119 SQL_DETAIL_1569742940382 SYS Automatically generated by SQL Details Sep 29 2019 13:13:07 Sep 29 2019 13:13:07 0
2 2980127119 STS_TESTSTS TESTSTS Sep 29 2019 13:18:38 Sep 29 2019 13:20:38 0
3 2980127119 STS_TESTSTS2 TESTSTS Sep 29 2019 13:24:41 Sep 29 2019 13:26:44 1

SQL> select sqlset_name,sql_id,plan_hash_value from dba_sqlset_statements where sqlset_owner = 'SYS';

SQLSET_NAME SQL_ID PLAN_HASH_VALUE
-------------------------------------------------------------------------
STS_FOR_SQLACCESS_ADV 60j52tdcbhcdy 2651194781


SET LINESIZE 150
COL STATUS_MESSAGE FORMAT A40
SELECT TO_CHAR(EXECUTION_START,'hh24:mi:ss'),TO_CHAR(execution_end,'hh24:mi:ss'),status,status_message,
pct_completion_time
from dba_advisor_tasks
where task_name='SQLACCESS8932231';

TO_CHAR( TO_CHAR( STATUS STATUS_MESSAGE PCT_COMPLETION_TIME
-------- -------- ----------- ---------------------------------------
11:18:01 11:18:04 COMPLETED Access advisor execution completed 100


SET LINESIZE 150
COL USERNAME FORMAT A20
SELECT USERNAME,SQL_ID,BUFFER_GETS,DISK_READS,CPU_TIME,ROWS_PROCESSED,PRECOST,POSTCOST,ELAPSED_TIME
FROM DBA_ADVISOR_SQLA_WK_STMTS
WHERE TASK_NAME='SQLACCESS8932231';

USERNAME SQL_ID BUFFER_GETS DISK_READS CPU_TIME ROWS_PROCESSED PRECOST POSTCOST ELAPSED_TIME
-------------------- ------------- ----------- ---------- ----------
USR_SQLACCESS_TEST 60j52tdcbhcdy 17640 16016 191000 4001 4689 15 199863


Before Implementation of SAA Recommendations
Execution Plan
----------------------------------------------------------
Plan hash value: 2651194781

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6767 | 383K| 3939 (1)| 00:02:18 |
| 1 | HASH GROUP BY | | 6767 | 383K| 3939 (1)| 00:02:18 |
|* 2 | HASH JOIN | | 177K| 9M| 3934 (1)| 00:02:18 |
| 3 | VIEW | VW_GBC_5 | 6767 | 218K| 721 (2)| 00:00:26 |
| 4 | HASH GROUP BY | | 6767 | 151K| 721 (2)| 00:00:26 |
| 5 | TABLE ACCESS FULL| TBL_MV_EXTENTS | 388K| 8729K| 711 (1)| 00:00:25 |
| 6 | TABLE ACCESS FULL | TBL_MV_OBJECTS | 1246K| 29M| 3210 (1)| 00:01:53 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="ITEM_1")

PostPlan after Implementation of SAA Recommendations

Execution Plan
----------------------------------------------------------
Plan hash value: 2356672226
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4001 | 101K| 7 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV$_00240000 | 4001 | 101K| 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

#From CLI

- We connect the SH schema
CONN SH/SH@pdb2

- Disabling an output of the consultations that we will run
SET AUTOTRACE TRACEONLY

- We launched several consultations with an identifier that allows us to find them easily in the
Shared Pool

SELECT /* TESTADVISOR01 */
CH.CHANNEL_CLASS,
C.CUST_CITY,
T.CALENDAR_QUARTER_DESC,
SUM (S.AMOUNT_SOLD) SALES_AMOUNT
FROM
SH.SALES S,SH.TIMES T, SH.CUSTOMERS C, SH.CHANNELS CH
WHERE S.TIME_ID = T.TIME_ID
AND S.CUST_ID = C.CUST_ID
AND S.CHANNEL_ID = CH.CHANNEL_ID
AND C.CUST_STATE_PROVINCE = 'CA'
AND CH.CHANNEL_DESC IN ('Internet', 'Catalog')
AND T.CALENDAR_QUARTER_DESC IN ('1999 to 1901', '1999 to 1902')
GROUP BY CH.CHANNEL_CLASS, C.CUST_CITY, T.CALENDAR_QUARTER_DESC;

SELECT /* TESTADVISOR02 */
CH.CHANNEL_CLASS,
C.CUST_CITY,
T.CALENDAR_QUARTER_DESC,
SUM (S.AMOUNT_SOLD) SALES_AMOUNT
FROM SH.SALES S,SH.TIMES T, SH.CUSTOMERS C, SH.CHANNELS CH
WHERE S.TIME_ID = T.TIME_ID
AND S.CUST_ID = C.CUST_ID
AND S.CHANNEL_ID = CH.CHANNEL_ID
AND C.CUST_STATE_PROVINCE = 'CA'
AND CH.CHANNEL_DESC IN ('Internet', 'Catalog')
AND T.CALENDAR_QUARTER_DESC IN ('1999 to 1903', '1999 to 1904')
GROUP BY CH.CHANNEL_CLASS, C.CUST_CITY, T.CALENDAR_QUARTER_DESC;

SELECT /* TESTADVISOR03 */
C.country_id, C.CUST_CITY, C.CUST_LAST_NAME
FROM SH.CUSTOMERS C
WHERE C.country_id IN (52790, 52798)
ORDER BY c.country_id, C.CUST_CITY, C.CUST_LAST_NAME;

- Deactivate the way AUTOTRACE
SET AUTOTRACE OFF

- We locate the consultations that we have implemented in the Shared Pool
SELECT SQL_ID, SQL_TEXT
FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('SQL_TEXT LIKE ''SELECT /* TESTADVISOR%'''))
ORDER BY SQL_ID;

- We connect as SYSDBA to generate the STS

connect system/oracle_4U@pdb2
EXEC DBMS_SQLTUNE.CREATE_SQLSET('STS_TEST');

- Create the STS with previous consultations
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('SQL_TEXT LIKE ''SELECT /* TESTADVISOR%''')) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS_TEST', populate_cursor => cur);
CLOSE cur;
END;
/


--Consider the implementation of the previous year with DBMS_ADVISOR package.

- We have already generated the STS, because then we have to recreate it (STS_TEST)
- We check that we have still
SELECT OWNER,NAME,STATEMENT_COUNT FROM DBA_SQLSET;

- Another thing we've done during the wizard is to use a template for Data Warehouse
- We can see all the templates have
SELECT * FROM DBA_ADVISOR_TEMPLATES;

- Create a task for the ADVISOR
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXEC :task_name := 'SQLACCESS_TEST';
EXEC DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name, template => 'SQLACCESS_WAREHOUSE');

- We get the ID of the task for possible searches
PRINT :TASK_ID;

- Add the STS reference
EXEC DBMS_ADVISOR.ADD_STS_REF ('SQLACCESS_TEST', 'SYS', 'STS_TEST');

- Run the Advisor
EXEC DBMS_ADVISOR.EXECUTE_TASK('SQLACCESS_TEST');

- We see the recommendations
SELECT REC_ID, RANK, BENEFIT FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = :task_name;

- We see the actions of each recommendation
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions WHERE task_name = :task_name
ORDER BY rec_id, action_id;

SQL> EXEC DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('SQLACCESS_TEST'), 'PDB2_DIR','sqladvisor_deploy5.sql');

PL/SQL procedure successfully completed.

SQL> QUIT

-bash-4.1$ cat /tmp/sqladvisor_deploy5.sql
Rem SQL Access Advisor: Version 12.1.0.2.0 - Production
Rem
Rem Username: SH
Rem Task: SQLACCESS_TEST
Rem Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
"SH"."CUSTOMERS"
WITH ROWID, SEQUENCE("CUST_ID","CUST_CITY","CUST_STATE_PROVINCE")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."CHANNELS"
WITH ROWID, SEQUENCE("CHANNEL_ID","CHANNEL_DESC","CHANNEL_CLASS")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."TIMES"
WITH ROWID, SEQUENCE("TIME_ID","CALENDAR_QUARTER_DESC")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."SALES"
WITH ROWID, SEQUENCE("CUST_ID","TIME_ID","CHANNEL_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SH"."MV$_00320000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, SH.CUSTOMERS.CUST_CITY C2, SH.CHANNELS.CHANNEL_CLASS
C3, SH.CHANNELS.CHANNEL_DESC C4, SH.TIMES.CALENDAR_QUARTER_DESC C5, SUM("SH"."SALES"."AMOUNT_SOLD")
M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.CHANNELS, SH.TIMES, SH.SALES WHERE SH.SALES.CHANNEL_ID = SH.CHANNELS.CHANNEL_ID
AND SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID
AND (SH.TIMES.CALENDAR_QUARTER_DESC IN ('1999 to 1904', '1999 to 1903'
, '1999 to 1902', '1999 to 1901')) AND (SH.CHANNELS.CHANNEL_DESC IN (
'Internet', 'Catalog')) AND (SH.CUSTOMERS.CUST_STATE_PROVINCE = 'CA')
GROUP BY SH.CUSTOMERS.CUST_STATE_PROVINCE, SH.CUSTOMERS.CUST_CITY, SH.CHANNELS.CHANNEL_CLASS,
SH.CHANNELS.CHANNEL_DESC, SH.TIMES.CALENDAR_QUARTER_DESC;

begin
dbms_stats.gather_table_stats('"SH"','"MV$_00320000"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH"."MV$_00320001"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT "SH"."CUSTOMERS"."COUNTRY_ID" M1, "SH"."CUSTOMERS"."CUST_CITY" M2, "SH"."CUSTOMERS"."CUST_LAST_NAME"
M3 FROM SH.CUSTOMERS WHERE (SH.CUSTOMERS.COUNTRY_ID IN (52798, 52790));

begin
dbms_stats.gather_table_stats('"SH"','"MV$_00320001"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE BITMAP INDEX "SH"."MV$_00320002_IDX$_00320000"
ON "SH"."MV$_00320000"
("C5")
COMPUTE STATISTICS;
[4] The SQL Performance Analyzer.


SQL> connect usr_sqlaccess_test/test@pdb2
Connected.
SQL>
SQL>
SQL> SELECT /* sql_perf_analyzer */ * from tbl_mv_objects where object_name = 'MY_OBJECT';

no rows selected

SQL> SELECT /* sql_perf_analyzer */ * from tbl_mv_objects where upper(owner) = 'MY_USR01';

no rows selected

SQL> COL sql_text format a50
SQL> select sql_id,sql_text from v$sql
2 where sql_text like '%sql_perf_analyzer%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------
3d8y8tvkz8bdu SELECT /* sql_perf_analyzer */ * from tbl_mv_objec
ts where object_name = 'MY_OBJECT'

6rjy0zgwwnsp4 SELECT /* sql_perf_analyzer */ * from tbl_mv_objec
ts where upper(owner) = 'MY_USR01'

142zzgq897f1k select sql_id,sql_text from v$sql where sql_text l
ike '%sql_perf_analyzer%'

SQL> PROMPT STS and capture these two sql statement base of hint
STS and capture these two sql statement base of hint
SQL>


Use OEM for further steps.


SQL> show user
USER is "USR_SQLACCESS_TEST"
SQL>
SQL> CREATE INDEX IDX_MV_OBJECTS_OBJNAME ON TBL_MV_OBJECTS(OBJECT_NAME);

Index created.

SQL> CREATE INDEX IDX_MV_OBJECTS_OWNER ON TBL_MV_OBJECTS(UPPER(OWNER));

Index created.
=====================
Path to Documentation:
=====================
Performance Tuning Guide -> 11 The Query Optimizer

The new Adaptive Statistics and
Adaptive Plans features in 12c should also be focused.
So take also a look at DBMS_SPD package and SQL Plan Directives.

Database PL/SQL Packages and Types Reference -> DBMS_SPD
Oracle Database SQL Tuning Guide-> Query Optimizer Concepts
Oracle Database SQL Tuning Guide-> Optimizer Statistics Concepts

Database SQL Tuning Guide
19 Managing SQL Tuning Sets
20 Analyzing SQL with SQL Tuning Advisor
21 Optimizing Access Paths with SQL Access Advisor

Database 2 Day + Performance Tuning Guide SQL Tuning
11 Identifying High-Load SQL Statements
12 Tuning SQL Statements
13 Optimizing Data Access Paths

Thank you for visiting this blog 🙂