How to Gather SQL Resource Consump6on Metrics in Oracle - - PowerPoint PPT Presentation

▶

Dec 27, 2022 42 likes •534 views

How to Gather SQL Resource Consump6on Metrics in Oracle Karen Morton Sr. Technical Consultant 1 karen.morton@enkitec.com karenmorton.blogspot.com karen_morton If

SLIDE 1

How ¡to ¡Gather ¡SQL ¡Resource ¡ Consump6on ¡Metrics ¡in ¡Oracle ¡

Karen ¡Morton ¡

Sr. ¡Technical ¡Consultant ¡

1 ¡

SLIDE 2

karenmorton.blogspot.com ¡ karen_morton ¡ karen.morton@enkitec.com ¡

SLIDE 3

If ¡you ¡can’t ¡measure ¡it, ¡you ¡can’t ¡manage ¡it. ¡ ¡

―David ¡Garvin ¡ ¡

3 ¡

SLIDE 4

There ¡are ¡two ¡types ¡of ¡ performance ¡problems ¡in ¡this ¡ world... ¡

SLIDE 5

1 ¡

¡Response ¡6me ¡

problems. ¡

SLIDE 6

2 ¡

¡Inefficiencies ¡that ¡aren’t ¡response ¡

6me ¡problems. ¡

Yet. ¡

SLIDE 7

Therefore... ¡

SLIDE 8

¡ You ¡must ¡be ¡able ¡to ¡aOack ¡ response ¡6me ¡problems ¡for ¡ specific ¡tasks ¡that ¡the ¡business ¡ cares ¡about... ¡

SLIDE 9

…even ¡if ¡they ¡are ¡inefficiencies ¡ that ¡aren’t ¡yet ¡no6ceable ¡as ¡user ¡ response ¡6me ¡problems. ¡

SLIDE 10

Why ¡should ¡you ¡care? ¡

SLIDE 11

SLIDE 12

Because ¡

¡ ¡

costs ¡you ¡money ¡

waste ¡

SLIDE 13

TCO ¡$$ ¡

Waste ¡

Labor ¡$$ ¡

Hardware ¡$$ ¡

SoWware ¡$$ ¡

SLIDE 14

And ¡waste... ¡

SLIDE 15

...makes ¡

ther ¡work ¡

go ¡slower… ¡

SLIDE 16

...even ¡your ¡fast ¡stuff ¡

SLIDE 17

Why ¡guess? ¡ When ¡you ¡can ¡know. ¡

SLIDE 18

Resource ¡Consump6on ¡Metrics ¡

SLIDE 19

¡ ¡Time ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡

Resources ¡

& ¡

SLIDE 20

Start ¡simple ¡ with ¡ AUTOTRACE ¡

SLIDE 21

SQL> ¡SET ¡TIMING ¡ON ¡ ¡ SQL> ¡ALTER ¡SESSION ¡SET ¡ ¡ ¡ ¡STATISTICS_LEVEL ¡= ¡ALL; ¡

Can ¡also ¡use ¡/*+ ¡gather_plan_sta;s;cs ¡*/ ¡hint ¡

SLIDE 22

SQL> ¡SET ¡AUTOTRACE ¡TRACEONLY ¡

SLIDE 23

SLIDE 24 24 ¡

Elapsed ¡6me: ¡34.10 ¡seconds ¡

SLIDE 25 25 ¡

SLIDE 26 26 ¡

SLIDE 27

Want ¡more? ¡

SLIDE 28

Mine ¡Shared ¡Pool ¡ V$ ¡Views ¡

SLIDE 29

Find ¡the ¡sql_id ¡& ¡child_number ¡

Put ¡a ¡recognizable ¡comment ¡in ¡your ¡SQL ¡to ¡make ¡it ¡easy ¡to ¡find. ¡

SLIDE 30

Retrieve ¡plan ¡using ¡ DBMS_XPLAN.display_cursor ¡ ('&sql_id','&child_no', ¡ 'ALLSTATS ¡LAST ¡+COST ¡+BYTES’) ¡

Other ¡op6ons ¡available: ¡+PEEKED_BINDS ¡+OUTLINE ¡+ADVANCED, ¡etc. ¡

SLIDE 31 31 ¡

‑-‑Es6mates-‑-‑ ¡
‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑Actuals-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑-‑ ¡

SLIDE 32

Want ¡more? ¡

SLIDE 33

SQL ¡Monitor ¡Reports ¡

SLIDE 34

Automa6cally ¡generated ¡when ¡ SQL ¡consumes ¡5 ¡seconds ¡or ¡ higher ¡of ¡CPU ¡or ¡IO ¡6me ¡

No ¡need ¡for ¡/*+ ¡gather_plan_sta;s;cs ¡*/ ¡hint ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡or ¡STATISTICS_LEVEL ¡= ¡ALL ¡

SLIDE 35

Can ¡use ¡ /*+ ¡monitor ¡*/ ¡ hint ¡

Can ¡turn ¡off ¡using ¡/*+ ¡no_monitor ¡*/ ¡hint ¡

SLIDE 36 36 ¡

select ¡dbms_sqltune.report_sql_monitor ¡ (report_level=>'ALL', ¡ ¡ type=>'TEXT', ¡ ¡ sql_id=>'6x2sh0kvzac0t') ¡monitor_report ¡ ¡ from ¡dual; ¡ ¡

Types ¡= ¡(11.1) ¡TEXT, ¡HTML, ¡XML, ¡plus ¡(11.2) ¡EM, ¡ACTIVE. ¡

Generate ¡Report ¡for ¡any ¡SQL_ID ¡

SLIDE 37 37 ¡

select ¡dbms_sqltune.report_sql_monitor ¡ (report_level=>'ALL', ¡ ¡ type=>'TEXT', ¡ ¡ session_id=>sys_context('userenv','sid') ¡ ) ¡monitor_report ¡ ¡ from ¡dual; ¡ ¡

Generate ¡Report ¡for ¡current ¡session ¡

SLIDE 38 38 ¡

SLIDE 39 39 ¡

SLIDE 40 40 ¡

SLIDE 41 41 ¡

SLIDE 42

Want ¡more? ¡

SLIDE 43

Ac6ve ¡Session ¡History ¡ (ASH) ¡ & ¡ Automa6c ¡Workload ¡Repository ¡ (AWR) ¡

Must ¡have ¡license ¡for ¡Diagnos;cs/Tuning ¡Pack ¡

SLIDE 44

SQL> ¡@ashrp6 ¡ ¡ SQL> ¡@awrsqrpt ¡

SLIDE 45

Want ¡more? ¡

SLIDE 46

Extended ¡SQL ¡Trace ¡ dbms_monitor ¡ session_trace_enable ¡

SLIDE 47 47 ¡

Remember: ¡ The ¡more ¡data ¡you ¡have, ¡ the ¡easier ¡problem ¡diagnosis ¡ will ¡be. ¡

SLIDE 48 48 ¡

How ¡you ¡get ¡the ¡data ¡doesn’t ¡ really ¡maOer. ¡ Just ¡get ¡it. ¡

SLIDE 49

Ques6ons ¡& ¡Answers ¡