Performance Tuning Level - I
Performance Tuning
Level - I
SQL Tuning – Level I
1. Job
is running slow : Ask for Details – Template for Tickets.
2. SQL
running slow - Find the SQL
3. Find
the Stale Statistics objects, column details and index status.
4. How
long a SQL has been running
5. Explain
plan for SQL Running -- From Cursor Cache
6. Explain
plan for SQL already been completed. – From
AWR
7.
Easy way to find top – running SQL using OEM
8. My
package / procedure running slow – How to find the SQL
9. Enabling
the Trace.
10. Setup
the trace before package starts. – Using Trigger.
11. SQL
Running Slow – I have Explain Plan What is next.
12. SQL
ran fine yesterday but not today – Why.
13.
How to analyze the issue.
Level 2:
1. Understanding
Explain Plan
2. Exporting
an Explain Plan
3. Exporting
a statistics
4. Tracing
on other sessions.
5. SQL
Tuning Advisor – Manual method and through OEM
6. SQL Profile
7. TKPROF
– Report Analysis.
8.
SQLT report.
Level 3:
1. ASH
2.
AWR Report.
3.
AWR comparison Report
4.
Latches, Wait Events
5.
Preparing for reports for Oracle TAR
Contents
1. Job is running slow : Ask for Details
When user complains, ask for more
details. Ask the user to create a ticket from template ITSM ticket # :
1589195.
The questionnaire contains the below:
Gather as much information as possible
:
1. Name
of Auto Sys Job and Database(s) where its being executed:
2.
Is this a production issue: Y/N
3.
Frequency of the job: Daily, Weekly, Monthly,
Quarterly, Ad Hoc or Other
4. Is
this a new job: Y/N
5.
If No, when did it last run successfully:
6.
Date/Time it started:
7.
Who and from where machine/program/session_id/username ?
8.
How long does the query usually run:
9. Is
the issue random or its always a problem :
10.
Explain the issue:
11.
Please attach the query*:
12.
Attach the Explain Plan if Possible:
13. Has
this happened before: If Yes, who fixed it: What was the fix:
14.
Has the code changed since the last successful run: Y/N
If Yes, explain the modification:
15.
Did you have any recent code deployment or Release ?
*Mandatory DBA part ---
1.
Has the an index been added/removed/modified: Y/N If
Yes, specify index details:
2. Are
the statistics on the tables and indexes used by the query STALE: Y/N
2.
Understand
the Problem
With the
information, collected figure out if problem is with a particular sql, 95% of
time we have problems due to plan_change. If the problem is wide spread, get
LEVEL 3 DBAS involved.
If it has a good past history and no
code change, its most likely a plan change(95%)
3.
SQL
running slow - Find the SQL
3.1
Grab the SQL_ID by Username/Module/Program
Recommendation is to go to SQL
Developer and sessions -> Filter by Username,module etc.
3.2
Find SQL using SQL
ID :
SELECT A.SQL_ID sqlid, sql_text
sqltext, username FROM GV$SQL A, GV$SESSION B
WHERE A.SQL_ID = B.SQL_ID
and a.sql_id = ‘&sql_id’;
4.
Get
SQL_TEXT for ACTIVE
SQL_ID from gV$ views
SELECT A.SQL_ID sqlid, sql_text sqltext, username
FROM GV$SQL A, GV$SESSION B
WHERE A.SQL_ID =
B."SQL_ID"
And a.sql_text like
'%select ich.insert_datetime, ich%'
and username != (select sys_context('USERENV',
'CURRENT_USER') from dual);
5.
Get
Complete SQL Text by sql_id from history
6.
Check stats for the objects
involved with that SQL_ID
This query will
help you get the statistics on all the tables involved in the query(SQL_ID).
Your focus should be on the last_analyzed and num_rows.
Taking into consideration the
num_rows can be 0 for temporary/staging tables.
8.
Analyze
Index Availability and Stats :
Clustering Factor: It’s the
relationship between the order of table and order of index. Range Scan
operations rely on the clustering factor.
9.
Find Execution time of SQL for a particular username :
10.
Get
the Execution
PLAN for the Query
10.1
Get SQL _PLAN from CURSOR CACHE
10.2
fetch SQL_PLAN from AWR by sql_id
SQL>
@display_plan_awr.sql
Enter
value for sql_id: ax46x3znstg8x
old
1: SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id',null,null,'allstats
last'))
new
1: SELECT * FROM
TABLE(dbms_xplan.display_awr('ax46x3znstg8x',null,null,'allstats last'))
PLAN_TABLE_OUTPUT
SQL_ID ax46x3znstg8x
UPDATE
E2F.ENROLLMENT_RECON SET SEQ_MEMB_ID = :B13 , SUBSCRIBER_ID =
:B12
, FIRST_NAME = :B11 , LAST_NAME = :B10 , PDG_EFFECTIVE_DATE = :B9
,
PDG_TERM_DATE = :B8 , ELIG_STATUS = :B7 , STATUS = :B6 WHERE MEDICAID_NO = :B5
AND FILE_EFFECTIVE_DATE = :B4 AND FILE_ID = :B3 AND LINE_OF_BUSINESS = :B2 AND
MAINTENANCE_CODE = :B1
|
Plan hash value: 3654680444 |
|
|||||
|
| Id | Operation |
|
Name |
| E-Rows | |
||||
|
|
0 | UPDATE STATEMENT |
| |
| | |
||||
|
| |
1
| |
UPDATE |
|
ENROLLMENT_RECON | |
| |
||
|
| | |
2
| 3 | |
TABLE ACCESS BY INDEX ROWID BATCHED| ENROLLMENT_RECON | INDEX RANGE SCAN | REC_FILE_ID | 1 | |
1 | |
|||
Note
-
Warning: basic plan statistics not available. These are
only collected when:
* hint
'gather_plan_statistics' is used for the statement or
* parameter
'statistics_level' is set to 'ALL', at session or system level
25
rows selected.
11.
Explain plan for SQL
already been completed. – From AWR
Few Queries : Find starting snap id and end snap id that list all
sql_id and sql_text.
select
t.sql_id, t.sql_text, s.executions_total, s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 1000 and
2000;
Or
select
t.sql_id, t.sql_text, s.executions_total, s.elapsed_time_total
from DBA_HIST_SQLSTAT s,
DBA_HIST_SQLTEXT t
where s.snap_id between 1000 and 200
and sql_text like ‘%&1%’;
12.
Sql_id with multiple Execution Plans :
|
5jgbssywbnpg8 |
2323526218 |
4 1,066.752 738.351 20,265,719.3 ############ |
|
5jgbssywbnpg8 |
2226621951 |
1 1,665.421
1,192.946 35,643,012.0 ############------------- - |
13.
Force the Plan
Find the better plan
SQL> @awr_plan_change_sql_id.sql Enter value for
sql_id: 5p0n334phhqp2
old
6: where sql_id = nvl('&sql_id','0cg3n01ptmj3s')
new
6: where sql_id = nvl('5p0n334phhqp2','0cg3n01ptmj3s')
SNAP_ID NODE
BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
|
11669 |
2 16-OCT-19 06.30.10.978 AM |
5p0n334phhqp2 |
3032698797 |
4 |
.898 |
||
|
3,022.3 |
|
|
|
|
|
||
|
11669 |
2 16-OCT-19 06.30.10.978 AM |
5p0n334phhqp2 |
231644794 |
1 |
3.282 |
||
|
272,269.0 |
|
|
|
|
|
||
|
11669 |
2 16-OCT-19 06.30.10.978 AM |
5p0n334phhqp2 |
511337710 |
37 |
|||
|
1.641 |
261,344.9 |
|
|
|
|||
|
11670 |
2 16-OCT-19 07.00.04.363 AM |
5p0n334phhqp2 |
3393651680 |
1 |
|||
|
2.835 |
264,847.0 |
|
|
|
|||
|
11670 2 16-OCT-19 07.00.04.363 AM 264,263.7 |
5p0n334phhqp2 |
511337710 |
3 |
2.807 |
|||
|
11670 |
2 16-OCT-19 07.00.04.363 AM |
5p0n334phhqp2 |
3032698797 |
33 |
|||
|
.075 |
1,746.3 |
|
|
|
|||
|
11670 2 16-OCT-19 07.00.04.363 AM 272,788.0 --purged few lines SNAP_ID NODE BEGIN_INTERVAL_TIME |
5p0n334phhqp2 231644794 SQL_ID PLAN_HASH_VALUE |
2 EXECS |
1.913 |
||||
|
AVG_ETIME AVG_LIO |
|||||||
|
16000 |
1 14-JAN-20 05.00.25.814 PM |
5p0n334phhqp2 |
1497781335 |
105 |
|
||
|
.040 |
1,399.4 |
|
|
|
|
||
|
16000 |
1 14-JAN-20 05.00.25.814 PM |
5p0n334phhqp2 |
2357057496 |
1 |
2.707 |
||
|
274,499.0 |
|
|
|
|
|
||
|
16000 |
2 14-JAN-20 05.00.26.028 PM |
5p0n334phhqp2 |
1770687510 |
2 |
2.337 |
||
|
274,046.5 |
|
|
|
|
|
||
|
16000 |
2 14-JAN-20 05.00.26.028 PM |
5p0n334phhqp2 |
1497781335 |
97 |
.013 |
||
|
1,648.6 |
|
|
|
|
|
||
|
16000 |
2 14-JAN-20 05.00.26.028 PM |
5p0n334phhqp2 |
3979996685 |
1 |
1.609 |
||
|
273,359.0 |
|
|
|
|
|
||
|
16001 |
1
14-JAN-20 05.30.02.877 PM |
5p0n334phhqp2 |
1497781335 |
80 |
.016 |
|
1,197.2 |
|
|
|
|
|
|
16001 |
1 14-JAN-20
05.30.02.877 PM |
5p0n334phhqp2 |
1770687510 |
1 |
1.854 |
|
273,385.0 |
|
|
|
|
|
|
16001 |
1 14-JAN-20
05.30.02.877 PM |
5p0n334phhqp2 |
3979996685 |
1 |
1.236 |
|
273,182.0 |
|
|
|
|
|
|
16001 |
2 14-JAN-20
05.30.02.860 PM |
5p0n334phhqp2 |
1770687510 |
5 |
1.472 |
|
273,393.4 |
|
|
|
|
|
|
16001 |
2 14-JAN-20
05.30.02.860 PM |
5p0n334phhqp2 |
1497781335 |
111 |
|
|
.008 |
893.6 |
|
|
|
|
|
16002 |
1 14-JAN-20
06.00.06.174 PM |
5p0n334phhqp2 |
|
48 |
|
|
.004 |
386.3 |
|
|
|
|
|
16002 |
1 14-JAN-20
06.00.06.174 PM |
5p0n334phhqp2 |
1770687510 |
5 |
1.606 |
|
273,907.4 |
|
|
|
|
|
|
16002 |
2 14-JAN-20 06.00.06.183
PM |
5p0n334phhqp2 |
1497781335 |
63 |
.006 |
|
446.0 |
|
|
|
|
|
|
16002 |
2 14-JAN-20
06.00.06.183 PM |
5p0n334phhqp2 |
1770687510 |
4 |
1.479 |
|
273,445.5 |
|
|
|
|
|
|
16007 |
2 14-JAN-20
08.30.01.221 PM |
5p0n334phhqp2 |
1497781335 |
34 |
.412 |
|
3,578.6 |
|
|
|
|
|
14.
Interesting : A Sql_id with more than 50 plans ..
Not surprising
at all , we have a complicated view with minimum 5 tables and each with
abundance of indexes – to give the optimizer a varied choice.
|
SQL>
@@coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) Enter value
for 1: 5p0n334phhqp2 PLAN_HASH_VALUE AVG_ET_SECS |
||
|
|
3354798767 |
.043 |
|
|
1059365689 |
.054 |
|
|
2182060089 |
.055 |
|
|
1076497745 |
.055 |
|
|
510150725 |
.057 |
|
|
1351212672 |
.058 |
|
|
1497781335 |
.066 |
|
|
3032698797 |
.11 |
|
|
926359509 |
1.926 |
|
|
2489056923 |
1.93 |
|
|
132710990 |
1.978 |
|
659350516 |
2.009 |
|
511337710 |
2.032 |
|
1036540327 |
2.061 |
|
3393651680 |
2.062 |
|
231644794 |
2.083 |
|
3979996685 |
2.152 |
|
2708121874 |
2.164 |
|
1804658475 |
2.205 |
|
2692360083 |
2.233 |
|
682208047 |
2.57 |
|
2336265446 |
2.993 |
|
4253655385 |
22.299 |
|
3935854906 |
24.005 |
|
1321292896 |
33.57 |
|
4153959607 |
54.297 |
|
988831776 |
58.884 |
15.
Check the sql_patch contents
At the end of sql plan, it will
display the name of the SQL_PATCH USed :
To see the hint, first find the
matching_signature of the sql_id.
Use the exact_matching_signature to get the hint used for sql_patch
16.
Dropping SQLPatch
17.
Disabling SQLPatch
18.
Create Script for
creating SQL Profile
19.
Create Profile
Execute the script created in the
above step
20.
Verify Sql Profile
in use- V$SQL
21.
Verify Sql Profile in use : Execution
PLAN
Check if SQL Profile was used add
+NOTE when displaying execution plan
IF Profile is
not being used, display plan and check. At times we have found due to missing
indexes, the profile creation does not complain but the right plan does not get
used.
22.
SQL_Directive
Acting up
To be added…
23.
Check Extended Columns Stats for a Table
SELECT e.extension col_group, t.num_distinct, t.histogram
FROM dba_stat_extensions e
JOIN dba_tab_col_statistics t ON
e.extension_name=t.column_name and t.table_name='&Table'
SQL>
SQL> @check_col_stat_extensions.sql Enter value for table:
MEMBER_ELIG_HISTORY old 6: and t.table_name='&Table'
new
6: and t.table_name='MEMBER_ELIG_HISTORY'
COL_GROUP NUM_DISTINCT
HISTOGRAM
("LINE_OF_BUSINESS","ELIG_STATUS") 617
TOP-FREQUENCY
("LINE_OF_BUSINESS","ELIG_STATUS","CMS_ENROLLMENT_STATUS")
3306 HYBRID
("TERM_DATE","LINE_OF_BUSINESS") 51192
HYBRID ("SEQ_MEMB_ID","SUBSCRIBER_ID","PLAN_CODE","LINE_OF_BUSINESS")
33955840 NONE
("LINE_OF_BUSINESS","SEQ_PROV_ID","IPA_ID") 418848
HYBRID ("EFFECTIVE_DATE","TERM_DATE","LINE_OF_BUSINESS","ELIG_STATUS")
897792
HYBRID ("TERM_REASON","LINE_OF_BUSINESS","CMS_ENROLLMENT_STATUS")
19024 HYBRID
("SEQ_MEMB_ID","TERM_DATE") 119046144 HYBRID
("SEQ_MEMB_ID","ELIG_STATUS") 29003776
HYBRID ("SEQ_MEMB_ID","EFFECTIVE_DATE","TERM_DATE","ELIG_STATUS")
117972992 HYBRID
("SEQ_MEMB_ID","SEQ_SUBS_ID","SEQ_GROUP_ID") 28162048
NONE
("PLAN_CODE","LINE_OF_BUSINESS") 2226
HYBRID
("SEQ_ELIG_HIST","SEQ_MEMB_ID") 121856000 NONE
("SEQ_MEMB_ID","EFFECTIVE_DATE","TERM_DATE") 118472704
HYBRID
("LINE_OF_BUSINESS","IPA_ID") 24006 HYBRID
("LINE_OF_BUSINESS","IPA_ID") 24006 HYBRID
("LINE_OF_BUSINESS","SEQ_PROV_ID") 346432
HYBRID
("SEQ_MEMB_ID","TERM_REASON") 51802112 HYBRID
("EFFECTIVE_DATE","TERM_DATE","ELIG_STATUS") 477184 HYBRID
24.
Check Difference in Statistics collected for a TABLE
SQL> @diff_stats_obj_time.sql SQL> set long 99999
SQL>
select report from table(dbms_stats.diff_table_stats_in_history(ownname =>
'&owner',tabname => upper('&tabname'),time1 => systimestamp,
time2
=> to_timestamp('&time2','yyyy-mm-dd:hh24:mi:ss'), pctthreshold =>
0))
;
Enter
value for owner: DORIS
Enter
value for tabname: ICES_HISTORY
old
1: select report from table(dbms_stats.diff_table_stats_in_history(ownname
=> '&owner',tabname => upper('&tabname'),time1 =>
systimestamp,
new
1: select report from table(dbms_stats.diff_table_stats_in_history(ownname
=> 'DORIS',tabname => upper('ICES_HISTORY'),time1 => systimestamp,
Enter value for time2: 2020-01-01:08:00:00
old
2: time2 => to_timestamp('&time2','yyyy-mm-dd:hh24:mi:ss'),
new
2: time2 => to_timestamp('2020-01-01:08:00:00','yyyy-mm-dd:hh24:mi:ss'),
REPORT
STATISTICS
DIFFERENCE REPORT FOR:
................................. TABLE : ICES_HISTORY
OWNER : DORIS
SOURCE A : Statistics as of 13-JAN-20 09.46.15.027647 PM
-05:00 SOURCE B : Statistics as of 01-JAN-20 08.00.00.000000 AM -05:00
PCTTHRESHOLD : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REPORT
TABLE
/ (SUB)PARTITION STATISTICS DIFFERENCE:
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
|
ICES_HISTORY |
T A 53377 |
32537 |
34 |
53377 |
|
|
B 22060 |
32537 |
34 |
22060 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REPORT
COLUMN
STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
|
CLAIM_TYPE |
A |
2 |
.000009367 YES 0 |
2 |
49 50 53377 |
|
|
|
B |
2 |
.000022665 YES 0 |
2 |
49 50 22060 |
|
|
INSERT_DATETIME A |
15023 .000066564 NO 0 |
|
8 |
78760 78780 53377 |
||
|
|
B 12906 .000077483 NO |
0 |
|
8 78760
78770 22060 |
||
|
SEQ_CLAIM_ID A |
53377
.000018734 NO 0 B 22060
.000045330 NO |
0 |
7 |
C5031 C50B3 53377 7 C5031 C50B2 22060 |
||
UPDATE_DATETIME A
0 0 NO
53377 1 NULL
B 0 0 NO 22060
1 0
UPDATE_PROCESS A 0 0 NO
53377
0 NULL
B 0 0 NO 22060
0 0
*Density=1/NDV
when histogram=NO, helps optimizer for selectivity for eq.joins INDEX /
(SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZ
...............................................................................
INDEX:
PK#SCI
|
PK#SCI |
I A 50508
12539 50508 1 |
1 |
34134 3 12326 |
|
|
B 22304
10084 22304 1 |
1 |
17292 3 5443 |
##############################################################################
#
Note: NDV – Number of distinct values
Density- Denotes the selectivity of the column for
equi join conditions
GATHER_DATABASE_STATS Procedures GATHER_DICTIONARY_STATS Procedure GATHER_FIXED_OBJECTS_STATS Procedure GATHER_INDEX_STATS Procedure GATHER_SCHEMA_STATS Procedures GATHER_SYSTEM_STATS Procedure GATHER_TABLE_STATS Procedure
The important
parameter is method_opt -> ‘FOR ALL INDEXED COLUMNS’ -> Gather
histograms. No_invalidate-> FALSE -> To see immediate impact, it invalids
cursor.
Cascade -> Gather for indexes too
26.
DELETE
STATS/EXPORT-IMPORT STATS/LOCK STATS
27.
Statistics Information: Retention Period/Availabilty/Fetch History
28.
GET_STATS_HISTORY_RETENTION 31 To
find statistics history of a particular table: select
TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history;
Statistics Information : Modify Retention
29.
Restore Stats
of a table :
30.
Tracing & TKPROF Oracle Session
There are few
different ways session can be traced. Mainly for procedure/ packages contains
multiple SQL’s.
Enable
Tracing for SQL ID: Below syntax enable 10043 trace. * add trace identifier
ALTER SYSTEM SET EVENTS 'sql_trace
[sql:&&sql_id] bind=true, wait=true';
Enable
tracing :
EXEC DBMS_SYSTEM.set_ev(si=>15732,
se=>3149, ev=>10046, le=>8, nm=>' ');
Disable Tracing:
EXEC DBMS_SYSTEM.set_ev(si=>15732,
se=>3149, ev=>10046, le=>0, nm=>' ');
Set the level to 0 that disables
tracing.
Si = Sid ; se =
serial# ; le = level ; nm = context default “context forever” Here le is Level
and its description is as below
· 0 - No trace. Like switching
sql_trace off.
· 2 - The equivalent of
regular sql_trace.
·
4 - The same as 2, but with the addition of bind variable values.
·
8 - The same as 2, but with the addition of wait events.
·
12 - The same as 2, but with both bind variable values and wait events
Things to know :
·
Trace before the query execution.
·
Currently running query will not be traced.
·
Naming convention of trace file by oracle Background_dump_dest/InstanceName_ora_unixProcessID.trc
·
Part of Trace file can be named
ALTER SESSION SET
TRACEFILE_IDENTIFIER = "E2F_Qry";
Then the file name will be.
Background_dump_dest/InstanceName_ora_unixProcessID_E2F_Qry.trc
·
To find the trace file location
SELECT p.tracefile FROM v$session s JOIN v$process p
ON s.paddr
= p.addr WHERE s.sid = 635;
Output will be
as below TRACEFILE
/ora/app/oracle/diag/rdbms/careuat/CAREUAT/trace/CAREUAT_ora_61603892.trc
TKPROF:
Tool to convert trace file to more readable report format.
Simple syntax : tkprof
tracefilename.trc outputfile.txt sys=no
Example :
/CAREUAT/trace>tkprof CAREUAT_ora_61603892.trc Care_Report.txt sys=no
waits=yes TKPROF output report with Explain plan :
Syntax : tkprof
CAREUAT_ora_61603892.trc Care_Report.txt sys=no explain=jbritto2@careuat Sys=no
option avoids reporting the sys sqls/ recursive sqls.
Help on tkprof : tkprof then
<Enter>
TKPROF File Content: Please note the
values assigned for bind variables.
TKPROF output Report File : Our
Example file is Care_Report.txt content is as below
Query timing :
1.75 + 2.97 = 4.72 Seconds to execute 22 times. Show Explain used. Parse 0
means that parsing already been completed before trace started.
Tracing through Trigger : Trace
does not capture the SQL already being executed. In case to setup trace before
the session starts, we need to set up log on trigger to enable the trace.
Trigger text is as below
drop trigger
sys.logon_jbritto_start_trace
/
CREATE OR REPLACE TRIGGER
SYS.logon_jbritto_start_trace after logon on sasadmin.schema
begin
execute immediate
'alter session set tracefile_identifier=' || '''E2F_Query''' ; execute
immediate 'alter session set sql_trace = true';
end;
/
Modify the username in the above
code.
after logon on sasadmin.schema ß Username
to be changed.
Which SQL to be analyzed from the trace:
Check the values specified in the
total row of a table for every SQL. Example as below
SELECT ENTITY_ROLE_CHAR_ID,
START_DATE, END_DATE FROM
(SELECT ROW_NUMBER()OVER(PARTITION
BY EERC.ENTITY_ROLE_ID,EERC.CHAR_CODE ORDER BY
NVL(EERC.END_DATE,TO_DATE('12/31/2999','MM/DD/YYYY')) DESC, START_DATE
DESC,EERC.ENTITY_ROLE_CHAR_ID DESC) AS RNK, EERC.* FROM
CARE_ADM.ENTITY_ROLE_CHAR EERC WHERE EERC.ENTITY_ROLE_ID=:B2 AND
EERC.CHAR_CODE=:B1 )WHERE RNK=1
call count cpu elapsed disk query
current rows
|
Parse |
0 |
0.00 |
0.00 |
0 |
|
0 |
|
0 |
0 |
|
|
Execute |
35 |
4.52 |
11.26 |
|
357 |
|
559 |
|
303987 |
9635 |
|
Fetch |
0 |
0.00 |
0.00 |
0 |
|
0 |
|
0 |
0 |
|
total 35 4.52 11.26 357 559 303987 9635
Above tables shows Query took
4.52 + 11.26 = 16.78 to execute 35
times.
Per Execution: 16.78/35 = 0.47
seconds – Not
good
Query explain
has been analyzed, added appropriate index. Tune execution timing is SELECT
ENTITY_ROLE_CHAR_ID, START_DATE, END_DATE
FROM
(SELECT ROW_NUMBER()OVER(PARTITION
BY EERC.ENTITY_ROLE_ID,EERC.CHAR_CODE ORDER BY
NVL(EERC.END_DATE,TO_DATE('12/31/2999','MM/DD/YYYY')) DESC, START_DATE
DESC,EERC.ENTITY_ROLE_CHAR_ID DESC) AS RNK, EERC.* FROM
CARE_ADM.ENTITY_ROLE_CHAR EERC WHERE EERC.ENTITY_ROLE_ID=:B2 AND
EERC.CHAR_CODE=:B1 )WHERE RNK=1
call count cpu elapsed disk query
current rows
Parse 0 0.00 0.00 0 0 0 0
Execute 298274
135.00 265.71 9045 19458 9555494 298274
Fetch 0 0.00 0.00 0 0 0 0
total
298274 135.00 265.71 9045 19458 9555494 298274
Now the timing is
135+265.71 = 400.71 seconds to
execute 198274
Per Execution : 400.71/198274
=/198274 = 0.0020 seconds -- Acceptable
Plan Before :
What is the
Issue : There was an index on char_code that has 36 columns on 390,000 rows
table. Since the less number of distinct value, this index is on range scan,
even though there is no full table scan, consumes more time.
Added index on ENTITY_ROLE_ID and CHAR_CODE , a composite index Plan After :
So, find a sql consumed longer time
from total values in the tkprof.
I
have Explain Plan What is next
Access : Access
is looking for object. Accessing the object to fetch data. Fetch all possible
data. Since access part is looking for object, using index, not filters.
---- Continues on Development
Level– 2 Document.
Comments
Post a Comment