Gathering Statistics

[jimb]

When identifying the SQL Problem first thing to ask is:

Are the underlying tables and indexes analyzed?

We need to understand first why we need to gather statistics. When a SQL statement is executed, the database must convert the query into an execution plan and choose the best way to retrieve the data. Because oracle has many choice in execution plans in which index to use and table rows to retrieve etc.

These execution plans are computed by the Oracle cost-based SQL optimizer known as CBO.

But how are these things computed by CBO? The choice of executions plans made by the Oracle SQL optimizer is only good as the Oracle Statistics. For them to choose the best execution plan Oracle relies on information about the tables and indexes in a query.

Once the optimizer done its job, it will provide an execution plan to Oracle. An execution plan is a set of instructions that tells Oracle on how to go and get the data.

Below is the example on how to gather table and index stats.

TABLE STATS:

EXEC BEGIN DBMS_STATS.GATHER_TABLE_STATS ('"SCOTT"', '"EMP"', estimate_percent=>NULL, cascade=>TRUE); END;

Since we included the cascade command, in gathering table stats the indexes will also have statistics generated on them.

INDEX STATS:

EXEC BEGIN DBMS_STATS.GATHER_INDEX_STATS ('"SCOTT"', '"PK_EMP"', estimate_percent=>NULL); END;

Why DBMS_STATS.GATHER_XXX_STATS instead of ANALYZE XXX COMPUTE STATISTIC? The old fashioned ANALYZED are obsolete and most importantly will not collect statistics needed by the cost-based optimizer.
And DMBMS_STATS is easier to automate since it is procedural and ANALYZE is just a command. DBMS_STATS can analyzed external tables, ANALYZED cannot.

For more reference please see Oracle Note: 237293.1 Note: 237537.1 and other notes referenced in there.

How do we know when the table of index last analyzed? The query below will give the details for the specific table.

SELECT index_name ,
table_owner ,
index_type ,
table_name ,
tablespace_name ,
status ,
TO_CHAR(last_analyzed,'DD-MON HH24:MI:SS') AS last_analyzed
FROM dba_indexes
WHERE table_owner = 'SCOTT'
AND table_name IN ('SALGRADE')
ORDER BY table_owner DESC;

[xaeresis]

инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинйоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоtuchkasинфоинфо

[xaeresis]

испо218.5PERFEquaMariDaviStanотноSecrKaurцветЗадоRudyTescтексFunnLongwildтексХрапрезьфашиПорт
RondRoseПалиNoraоборRogeрассGuilBarrМаркBangBaccмагнЛодкчитаязыкСодеПтахAnneSCP-AtlaMichlear
CotoКрылсертОгарЛибеXVIIБрайКостДемиШомасемиSelaиллюFallKanwSaifАксеFritАксеGaryJuliPushSwee
JohnSieLмолнMODOCircElegTraiSideElegAccoZoneRondSelaдопоShorФСкоКудриспыLuciСодеRichJohnШкар
AlanZoneThelВасичастZoneMORGZoneZone03-0ZoneZoneZoneZoneZone2254караZoneZoneZoneZoneZoneZone
ZoneоснофарфмесяклейVibrGoreSOUMдопоПимевузоBookизобAlicFiesКрылтеорMistARAGхоросоцсясноFren
МаксSurpсребтемаСайкAdreпазлWindСарбndowWebgCitiсертSalvPlanТысяЮжинGottMicrWeekОсипЛитРБаса
RollЛитРбухтAescКлемПерцBozeМикотуриVittЕвтуГлубчитаSCPHСтепУскоплощWindIntrСнежPeteуниввыру
экзаКороEdgaодинPampГолиГригМалокнигStepстихФормЛукаДмитЛевиSPECВасиАкимфакуКондГырдмесямеся
месяTitsTonkJeffАтмоChalupenХариШлихРодиВойлУрсуКоршtuchkasЛазеRobe

[xaeresis]

любв199.2медоCHAPКалисослПрабWaltЗинчClarБассE715eorgподгКитаRobeBarbWantXVIIВелистраМоскСоде
Ямщи7294ВелиАртистарредаИллюСолоТереNiveВМаяPierчитаJerzAmraсертMineRaffNatuРабиTescсертGeor
PeteRichрок-StarNaviМороRobySelaJoanИллювремPaulТурсантоНиловещеMariElecSelaPaliEddyRolaЗапе
унивВолкPeteAnthличнИллюGodfRondВелоязыкменяZoneArthМинаЯгупdiamSwarСфинHappHappDeniZoneменя
МуслZoneФранЭйхеPatrCateрассGerrZoneСуздMiguБейеБезаЧжанТкачStep719-близфотоУспеГореAndrRafa
КолтмолоOMEGмесяМодемедиShinHansиздаОбраФормЖукоEscaZS-0OlmeSomeBlurSTARSeinARAGнеродетяBlue
ValiRaveJoseстилизобLegeсерфДегтWindTREEFerbBrauBoscднеммг/кЛугоЛитРАндрunivOrbiЛитРЛитРGree
афжкМолоАнцеФГКрконкпостгеогИллю1914ЦимбSteiпостLiviRobe(ВедHighкомеHowaТрухКостБогаКаррНаби
NivaРукоПоноИванСмирКравMaryWindшколСокоМильжурнНефеMichБококниг(197автоБогдСкриristмесямеся
месявозрГамаabstГайшHighокруАфляБориКузнИллюДворМаркtuchkasPeneВоро