0

i've got a huge Oracle Trace file. The application, wich produced this file, runned 1 hour and 15 minutes. In this Tracefile i found 4 Selects with together a little bit over a hour runtime. The problem is these selects are sampled by the Optimizer.

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE 
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') 
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), 
  NVL(SUM(C2),:"SYS_B_01") 
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("LST_G") FULL("LST_G") 
  NO_PARALLEL_INDEX("LST_G") */ :"SYS_B_02" AS C1, CASE WHEN 
  "LST_G"."SENDUNG_TIX"=:"SYS_B_03" AND "LST_G"."LST_K"=:"SYS_B_04" AND 
  "LST_G"."LST_ART"=:"SYS_B_05" AND "LST_G"."FAK_TIX"=(-:"SYS_B_06") THEN 
  :"SYS_B_07" ELSE :"SYS_B_08" END AS C2 FROM "TMS1033"."LST_G" SAMPLE BLOCK 
  (:"SYS_B_09" , :"SYS_B_10") SEED (:"SYS_B_11") "LST_G") SAMPLESUB


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    56076      3.93       4.21          0          0          0           0
Execute  56076      1.98       1.80          0          0          0           0
Fetch    56076   1127.54    1122.77        222   46487004          0       56076
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   168228   1133.45    1128.79        222   46487004          0       56076

This is one of the four, they look nearly the same. I think i found the original Statements and these are executed from a Uniface-Service. I have no idea how Uniface works, i am only the db guy. The problem is that i have no Idea why the Optimizer rebuild this statement. The original one don't use the dynamic_sample hint. Also i found these, i think so, original Statments additionally in the trace file .

select count(*) 
from
 lst_g where sendung_tix = 10330805990396 and lst_k = 'E' and lst_art = 'G' 
  and fak_tix = -4

Thats why i am not sure what these sampled Statements are. Any idea?

Thanks a lot.

hat
  • 25
  • 1
  • 10

2 Answers2

1

Dynamic sampling is turned on for that query. Either

  1. The query uses the /*+ DYNAMIC_SAMPLING */ hint
  2. The code issues an alter session set optimizer_dynamic_sampling= command
  3. optimizer_dynamic_sampling is set in the database spfile.

For example

alter session set OPTIMIZER_DYNAMIC_SAMPLING = 2;

Then issue a query against a big table with a very selective (but not exact) condition that can use an index.

select * from mtl_system_items /* biiig table */ 
where organization_id = 92 
and segment1 LIKE 'DY_'  /* very selective condition with index */

Run it and you get data back quickly. But then,

alter session set OPTIMIZER_DYNAMIC_SAMPLING = 10;

and re-run the same SELECT and it's out to lunch, sampling every block in the table.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
-1

I believe these are queries used by Statistics Gathering jobs for updating statistics on database objects. Please check if any stats gathering / update jobs were running at the time when the trace file got generated.

In your case, from the SQL posted it seems statistics was being gathered on "TMS1033"."LST_G". The same will be the case with the other 3 SQLs you found out.

Hope this helps.

ArtBajji
  • 949
  • 6
  • 14
  • Sorry, I hate to down vote, but this isn't right. These queries are not from DBMS_STATS. They're from the optimizer performing dynamic sampling. You can tell by (A) the OPT_DYN_SAMP comment in the query and (B) by the fact that the query has a WHERE clause on specific columns in the table. DBMS_STATS would sample from all the rows (no where clause, just a sample size). – Matthew McPeak Jun 03 '16 at 17:01