The contents are of my own testing and deployments. Not guaranteed that these might work in your environment. Please test before usage.
Summary: in this tutorial, you will learn How to run the sqltrpt report in oracle database.
- What is sqltrpt scripts?
- What is the use of that scripts?
- How to run that scripts?
- What is sqltrpt scripts?
Actually, In general, Database slowness is very hard to handle the dba side so oracle provide some recommendation or profiles to speed up the SQL statements
- What is the use of that scripts?
It might give various recommendations like gathering stats/Index Rebuild and accepting a SQL profile,
- How to run that scripts?
Login the server
Goto the cd $ORACLE_HOME/rdbms/admin/sqltrpt/sql
Then provide the active or needed sqlid after that oracle provide recommendations like gathering stats/Index Rebuild all the information
[oracle@ltitest admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@ltitest admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin
[oracle@ltitest admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 13 21:44:00 2023
Copyright (c) 1982, 2014, Oracle. 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
SQL> @sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
616m6uhpa2usu 2.29 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,
cvn54b7yz0s8u 1.97 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length
7u49y06aqxg1s 1.75 select /*+ rule */ bucket, endpoint, col#, epvalue, epv
1p5grz1gs7fjq 1.45 select obj#,type#,ctime,mtime,stime, status, dataobj#,
3un99a0zwp4vd 1.37 select owner#,name,namespace,remoteowner,linkname,p_tim
39m4sx9k63ba2 1.19 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length
7fwum1yuknrsh 1.09 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#
3pvgdy7v961q3 1.04 select /*+ index(d) */ dir_id, f_id, type, state, flag
96g93hntrzjtr 1.01 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null
14d7z6mh1sxuz 0.95 select name,intcol#,segcol#,type#,length,nvl(precision#
ga9j9xk5cy9s0 0.80 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
6ajkhukk78nsr 0.63 begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_sn
6qz82dptj0qr7 0.62 select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.fil
8swypbbr0m372 0.62 select order#,columns,types from access$ where d_obj#=:
c6awqs517jpj0 0.61 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,leng
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
b6usrg82hwsa3 44.90 call dbms_stats.gather_database_stats_job_proc ( )
616m6uhpa2usu 17.51 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,
cvn54b7yz0s8u 14.79 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length
7u49y06aqxg1s 13.63 select /*+ rule */ bucket, endpoint, col#, epvalue, epv
4phvdvx32a3mf 11.84 begin prvt_ilm.stopjobs(-1,true,true,:1); end;
3un99a0zwp4vd 9.57 select owner#,name,namespace,remoteowner,linkname,p_tim
96g93hntrzjtr 8.93 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null
fhf8upax5cxsz 8.13 BEGIN sys.dbms_auto_report_internal.i_save_report (:rep
4bymnttwnjmw7 7.93 DECLARE job BINARY_INTEGER := :job; next_date TIMESTAM
dhpn35zupm8ck 7.92 select o.name, o.owner# from obj$ o, type$ t where o.o
572fbaj0fdw2b 7.63 select output from table(dbms_workload_repository.awr_r
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
adzjh275fvvx4 7.25 call WWV_FLOW_WORKSHEET_API.DO_NOTIFY ( )
0w26sk6t6gq98 7.21 SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1
dfffkcnqfystw 6.99 WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSI
1p5grz1gs7fjq 6.97 select obj#,type#,ctime,mtime,stime, status, dataobj#,
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 616m6uhpa2usu
Sql Id specified: 616m6uhpa2usu
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_247
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/13/2023 21:44:22
Completed at : 03/13/2023 21:44:25
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 616m6uhpa2usu
SQL Text : select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.
property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.di
stkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samples
ize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.
pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.def
errable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.
spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),25
6)),nvl(i.evaledition#,1),nvl(i.unusablebefore#,0),nvl(i.unusable
beginning#,0), ist.cachedblk,ist.cachehit,ist.logicalread from
ind$ i, ind_stats$ ist, (select enabled, min(cols)
unicols,min(to_number(bitand(defer,1)))
deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$
where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order
by i.obj#
Bind Variables :
1 - (NUMBER):360
2 - (NUMBER):360
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- --------------
--
1 2556297882 2023-03-13/21:16:33 0.001 Cursor Cache
Information
-----------
- Because no execution history for the Original Plan was found, the SQL
Tuning Advisor could not determine if any of these execution plans are
superior to it. However, if you know that one alternative plan is better
than the Original Plan, you can create a SQL plan baseline for it. This
will instruct the Oracle optimizer to pick it over any other choices in
the future.
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_247',
owner_name => 'SYS', plan_hash_value => xxxxxxxx);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2606284882
--------------------------------------------------------------------------------
Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 342 |
5 (20)| 00:00:01 |
| 1 | SORT GROUP BY | | 2 | 342 |
5 (20)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 2 | 342 |
4 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 2 | 312 |
2 (0)| 00:00:01 |
| 4 | TABLE ACCESS CLUSTER | IND$ | 2 | 184 |
2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | I_OBJ# | 1 | |
1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| IND_STATS$ | 1 | 64 |
0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I_IND_STATS$_OBJ# | 1 | |
0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 15 |
1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_COBJ# | 1 | |
0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("I"."BO#"=:1)
7 - access("I"."OBJ#"="IST"."OBJ#"(+))
8 - filter("ENABLED"(+)>1 AND "I"."OBJ#"="ENABLED"(+))
9 - access("OBJ#"(+)=:1)
------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 1
------
Plan Origin :Cursor Cache
Plan Hash Value :2556297882
Executions :2417
Elapsed Time :0.001 sec
CPU Time :0.000 sec
Buffer Gets :9
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | By
tes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 2 |
374 | 6 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 |
374 | 6 (34)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 2 |
374 | 5 (20)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 2 |
288 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS CLUSTER | IND$ | 2 |
184 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | I_OBJ# | 1 |
| 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | IND_STATS$ | 1 |
52 | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I_IND_STATS$_OBJ# | 1 |
| 0 (0)| 00:00:01 |
| 8 | VIEW | | 1 |
43 | 3 (34)| 00:00:01 |
| 9 | SORT GROUP BY | | 1 |
15 | 3 (34)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| CDEF$ | 1 |
15 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | I_CDEF2 | 4 |
| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("I"."OBJ#"="C"."ENABLED"(+))
5 - access("I"."BO#"=:1)
7 - access("I"."OBJ#"="IST"."OBJ#"(+))
10 - filter("ENABLED">1)
11 - access("OBJ#"=:1)
-------------------------------------------------------------------------------
Thanks for your time!!!
0 Comments