Re: What's the deal with PAGEIOLATCH_SH?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: James Bradley (jbradley_at_isa-og.com)
Date: 04/26/04 

Date: Mon, 26 Apr 2004 11:37:06 -0600

On Mon, 26 Apr 2004 11:07:06 +1000, "Greg Linwood" <g_linwoodQhotmail.com> wrote:

I tried posting an attachment, but it didn't go through - I'm pasting the text into the message :-(

Hi Greg

It took me a while, but I put together the create table/index calls for these tables. I had to
rename the fields because of confidentiality issues.

I've also attached the output from
dbcc show_statistics for the relevant indices.

I ran the full update statement, it took 2 hours, 24 minutes. I've attached the text output from
the statistics for that run.

I tried all 3 queries (the current one and the two you suggested below), and the query execution
plans are identical!

I don't need to do this atomically - I have exclusive use of the database when I need to run this.
I could easily break it into several batches.

One thing that is still bothering me -- let's say SQL is choosing a bad execution plan (hash join,
etc.). Wouldn't I see the server busy doing this, even if it is inefficient? Right now, the
server is not limited by either CPU or Disk I/O, and I would assume one of those must be limiting,
unless locks/latches are blocking it.

Thanks for all the help!

Stats for hasChildren execution:
--------------------------------------------------
Total Time: 2:24:34

(1583816 row(s) affected)

Application Profile Statistics
  Timer resolution (milliseconds) 0 0
  Number of INSERT, UPDATE, DELETE statements 1 1.35294
  Rows effected by INSERT, UPDATE, DELETE statements 1.58382e+006 369121
  Number of SELECT statements 1 3.23529
  Rows effected by SELECT statements 7 19.7647
  Number of user transactions 5 11.8235
  Average fetch time 0 0
  Cumulative fetch time 0 0
  Number of fetches 0 0
  Number of open statement handles 0 0
  Max number of opened statement handles 0 0
  Cumulative number of statement handles 0 0
                 
Network Statistics
  Number of server roundtrips 3 3
  Number of TDS packets sent 3 3
  Number of TDS packets received 4 22644.9
  Number of bytes sent 656 938.353
  Number of bytes received 4498 1.34218e+007
                 
Time Statistics
  Cumulative client processing time 388 55.3529
  Cumulative wait time on server replies 0 3.17271e+007
--------------------------------------------------

Table1 SQL Create with indices:
--------------------------------------------------
CREATE TABLE [Table1] (
   [Idx2] [numeric](18, 0) NOT NULL ,
   [Idx4] [numeric](18, 0) NULL,
   [Idx3] [int] NULL ,
   [Idx5] [int] NULL ,
   [Field5] [smallint] NOT NULL ,
   [Field6] [nvarchar] (250) NULL ,
   [Field7] [nvarchar] (250) NULL ,
   [Field8] [nvarchar] (50) NULL ,
   [Field9] [nvarchar] (50) NULL ,
   [Field10] [numeric](18, 1) NOT NULL ,
   [Field11] [numeric](18, 0) NOT NULL ,
   [Field12] [datetime] NULL ,
   [Field13] [numeric](18, 1) NULL ,
   [Field14] [numeric](18, 0) NULL ,
   [Field15] [datetime] NULL ,
   [Field16] [numeric](18, 1) NOT NULL ,
   [Field17] [numeric](18, 0) NOT NULL ,
   [Field18] [numeric](18, 0) NOT NULL ,
   [Field19] [numeric](18, 2) NULL ,
   [Field20] [numeric](18, 2) NULL ,
   [Field21] [datetime] NULL ,
   [Field22] [numeric](18, 2) NOT NULL ,
   [Field23] [numeric](18, 2) NOT NULL ,
   [Field24] [numeric](18, 2) NOT NULL ,
   [Field25] [numeric](18, 2) NOT NULL ,
   [Field26] [numeric](18, 2) NOT NULL ,
   [Field27] [numeric](18, 2) NOT NULL ,
   [Field28] [nvarchar] (50) NULL ,
   [Field29] [nvarchar] (25) NULL ,
   [Field30] [nvarchar] (25) NULL ,
   [Field31] [nvarchar] (25) NULL ,
   [Field32] [bit] NOT NULL ,
   [Field33] [bit] NOT NULL ,
   [Field34] [nvarchar] (80) NULL ,
   [Field35] [nvarchar] (20) NULL ,
   [Field36] [nvarchar] (20) NULL ,
   [Field37] [datetime] NULL ,
   [Field38] [nvarchar] (20) NULL ,
   [Field39] [nvarchar] (250) NULL ,
   [Field40] [decimal](18, 2) NULL CONSTRAINT [DF_Table1_Field40] DEFAULT (0),
   [Field41] [decimal](18, 0) NULL CONSTRAINT [DF_Table1_Field41] DEFAULT (0),
   [Field42] [decimal](18, 2) NULL CONSTRAINT [DF_Table1_Field42] DEFAULT (0),
   [Field43] [decimal](18, 0) NULL CONSTRAINT [DF_Table1_Field43] DEFAULT (0),
   [Field44] [datetime] NULL ,
   [Field45] [bit] NULL CONSTRAINT [DF_Table1_Field45] DEFAULT (0),
   [Field46] [decimal](18, 2) NULL CONSTRAINT [DF_Table1_Field46] DEFAULT (0),
   [Field47] [decimal](18, 0) NULL CONSTRAINT [DF_Table1_Field47] DEFAULT (0),
   [Field48] [datetime] NULL ,
   [Field49] [bit] NULL CONSTRAINT [DF_Table1_Field49] DEFAULT (0),
   [Field50] [decimal](18, 2) NULL CONSTRAINT [DF_Table1_Field50] DEFAULT (0),
   [Field51] [decimal](18, 0) NULL CONSTRAINT [DF_Table1_Field51] DEFAULT (0),
   [Field52] [datetime] NULL ,
   [Field53] [bit] NULL CONSTRAINT [DF_Table1_Field53] DEFAULT (0),
   [Field54] [datetime] NULL ,
   [Field55] [nvarchar] (20) NULL ,
   [Field56] [nvarchar] (250) NULL ,
   [Field57] [decimal](18, 2) NULL ,
   [Field58] [decimal](18, 0) NULL ,
   [Field59] [nvarchar] (25) NULL ,
   [Field60] [nvarchar] (25) NULL ,
   [Field61] [decimal](18, 2) NULL ,
   [Field62] [decimal](18, 0) NULL ,
   [Field63] [decimal](18, 0) NULL ,
   [Field64] [nvarchar] (5) NULL ,
   [Field65] [nvarchar] (5) NULL ,
   [Field66] [nvarchar] (10) NULL ,
   [Field67] [nvarchar] (10) NULL ,
   [Field68] [bit] NULL ,
   [Field69] [bit] NULL ,
   [Field70] [nvarchar] (3) NULL ,
   [Field71] [nvarchar] (50) NULL ,
   [Idx1A] [nvarchar] (25) NULL ,
   [Field73] [int] NULL ,
   [Field74] [nvarchar] (10) NULL ,
   [Field75] [nvarchar] (12) NULL ,
   [Field76] [nvarchar] (25) NULL ,
   [Field77] [nvarchar] (1) NULL ,
   [Field78] [nvarchar] (60) NULL ,
   [Field79] [nvarchar] (50) NULL ,
   [Field80] [nvarchar] (50) NULL ,
   [Field81] [nvarchar] (15) NULL ,
   [Field82] [nvarchar] (8) NULL ,
   [Field83] [nvarchar] (50) NULL ,
   [Field84] [nvarchar] (50) NULL ,
   [Field85] [nvarchar] (20) NULL ,
   [Field86] [nvarchar] (30) NULL ,
   [Field87] [money] NULL ,
   [Field88] [money] NULL ,
   [Field89] [money] NULL ,
   [Field90] [decimal](18, 2) NULL ,
   [Field91] [nvarchar] (2) NULL ,
   [Field92] [nvarchar] (3) NULL ,
   [Field93] [datetime] NULL ,
   [Field94] [int] NOT NULL ,
   [Field95] [int] NOT NULL ,
   [Field96] [int] NOT NULL ,
   [Field97] [nvarchar] (4) NULL ,
   [Field98] [int] NULL ,
   [Field99] [int] NULL ,
   [Field100] [int] NULL ,
   [Field101] [nvarchar] (4) NULL ,
   [Field102] [int] NULL ,
   [Field103] [int] NULL ,
   [Field104] [int] NULL ,
   [Field105] [nvarchar] (4) NULL ,
   [Field106] [datetime] NULL ,
   [Field107] [int] NOT NULL ,
   [Field108] [int] NOT NULL ,
   [Field109] [int] NOT NULL ,
   [Field110] [nvarchar] (4) NULL ,
   [Field111] [bit] NULL ,
   [Field112] [nvarchar] (250) NULL ,
   [Field113] [bit] NULL ,
   [Field114] [bit] NULL ,
   [Field115] [numeric](18, 1) NULL ,
   [Field116] [numeric](18, 0) NULL ,
   [Field117] [numeric](18, 2) NULL ,
   [Field118] [numeric](18, 2) NULL ,
   [Field119] [numeric](18, 2) NULL ,
   [Field120] [numeric](18, 0) NULL ,
   [Field121] [numeric](18, 1) NULL ,
   [Field122] [numeric](18, 0) NULL ,
   [Field123] [datetime] NULL ,
   [Field124] [numeric](18, 1) NULL ,
   [Field125] [numeric](18, 0) NULL ,
   [Field126] [nvarchar] (50) NULL ,
   [Field127] [numeric](18, 1) NULL ,
   [Field128] [numeric](18, 0) NULL ,
   [Field129] [nvarchar] (50) NULL ,
   [Field130] [datetime] NULL ,
   [Idx1B] [int] NULL ,
   [Field132] [numeric](20, 1) NULL ,
   [Field133] [numeric](20, 0) NULL ,
   [Field134] [numeric](18, 1) NULL ,
   [Field135] [datetime] NULL ,
   [Field136] [datetime] NULL ,
   [Field137] [bit] NOT NULL ,
   [Field138] [numeric](18, 1) NOT NULL ,
   [Field139] [numeric](18, 0) NOT NULL ,
   [Field140] [datetime] NULL ,
   [Field141] [numeric](20, 1) NULL ,
   [Field142] [numeric](20, 0) NULL ,
   [Field143] [datetime] NULL ,
   [Field144] [numeric](20, 1) NULL ,
   [Field145] [numeric](20, 0) NULL ,
   [Field146] [int] NULL ,
   [Field147] [numeric](38, 14) NULL ,
   [Field148] [numeric](38, 14) NULL ,
   [Field149] [int] NULL ,
   [Field150] [numeric](21, 1) NULL ,
   [Field151] [numeric](21, 0) NULL ,
   [Field152] [datetime] NULL ,
   [Field153] [datetime] NULL ,
   [Field154] [bit] NULL CONSTRAINT [DF_Table1_Field154] DEFAULT (0),
   [Field155] [bit] NULL CONSTRAINT [DF_Table1_Field155] DEFAULT (0)
) ON [PRIMARY]
GO

CREATE INDEX [Table1Idx1] ON [dbo].[Table1]([Idx1A], [Idx1B]) ON [PRIMARY]
CREATE INDEX [Table1Idx2] ON [dbo].[Table1]([Idx2]) ON [PRIMARY]
CREATE INDEX [Table1Idx3] ON [dbo].[Table1]([Idx3]) ON [PRIMARY]
CREATE INDEX [Table1Idx4] ON [dbo].[Table1]([Idx4]) ON [PRIMARY]
CREATE INDEX [Table1Idx5] ON [dbo].[Table1]([Idx5]) ON [PRIMARY]
--------------------------------------------------

Table 2 Create with Indices:
--------------------------------------------------
CREATE TABLE [dbo].[Table2] (
   [parentChildID] [int] IDENTITY (1, 1) NOT NULL ,
   [parentID] [int] NOT NULL ,
   [parentType] [char] (1) NOT NULL ,
   [childID] [int] NOT NULL ,
   [childType] [char] (1) NOT NULL ,
   [codeRelationship] [char] (1) NOT NULL ,
   [customRelationship] [char] (1) NOT NULL ,
   [sequence] [int] NOT NULL
) ON [PRIMARY]
GO

 CREATE INDEX [Table2Idx1] ON [dbo].[Table2]([parentID], [childID], [codeRelationship]) ON
[PRIMARY]
 CREATE INDEX [Table2Idx2] ON [dbo].[Table2]([childID], [parentID], [codeRelationship]) ON
[PRIMARY]
--------------------------------------------------

Table 1 Index stats:
--------------------------------------------------
"Updated","Rows","Rows Sampled","Steps","Density","Average Key Length"
Apr 25 2004 2:22PM ,4640566,4640566,11,2.2519677E-7,9.0

"All Density","Average Length","Columns"
2.2519703E-7,9.0,Idx2

"RANGE_HI_KEY","RANGE_ROWS","EQ_ROWS","DISTINCT_RANGE_ROWS","AVG_RANGE_ROWS"
39558,0.0,2.0,0,0.0
72301,43686.0,2.0,30189,1.4470834
121189,42555.0,2.0,36719,1.1589053
177216,55829.0,2.0,43438,1.2852571
186552,8528.0,2.0,6831,1.2482436
324134,145967.0,2.0,117944,1.2375958
386384,56849.0,2.0,46394,1.2253524
426171,46005.0,2.0,38061,1.2086859
591173,174099.0,2.0,155217,1.121649
35345622,48327.0,2.0,45990,1.0507926
39279394,4018700.0,1.0,3919758,1.0252419

--------------------------------------------------

Table 2 Index stats:
--------------------------------------------------
"Updated","Rows","Rows Sampled","Steps","Density","Average Key Length"
Apr 22 2004 11:14PM ,53082,53082,197,4.445008E-5,13.0

"All Density","Average Length","Columns"
5.227665E-5,4.0,parentID
1.8838778E-5,8.0,parentID, childID
1.8838778E-5,9.0,parentID, childID, codeRelationship
1.8838778E-5,13.0,parentID, childID, codeRelationship, parentChildID

"RANGE_HI_KEY","RANGE_ROWS","EQ_ROWS","DISTINCT_RANGE_ROWS","AVG_RANGE_ROWS"
40391,0.0,5.0,0,0.0
83053,197.0,134.0,96,2.0520833
83635,344.0,14.0,203,1.6945813
83672,91.0,9.0,20,4.3333335
83873,192.0,8.0,110,1.7297298
84153,282.0,4.0,154,1.8193549
84479,229.0,4.0,156,1.4679487
84878,195.0,7.0,153,1.2745098
85174,121.0,18.0,60,2.0166667
85662,193.0,73.0,98,1.949495
5326405,237.0,6.0,156,1.5095541
5327870,384.0,4.0,282,1.3617021
5329031,412.0,5.0,196,2.1020408
5331331,235.0,5.0,156,1.5064102
5336716,328.0,5.0,232,1.4077253
5337963,230.0,5.0,179,1.2777778
5338297,176.0,5.0,109,1.6146789
5339365,340.0,5.0,220,1.5454545
5339765,167.0,4.0,131,1.2748091
5342837,380.0,30.0,260,1.4615384
5345526,233.0,30.0,171,1.3546512
5346637,153.0,4.0,91,1.6813186
5347108,253.0,4.0,180,1.3977901
5347449,205.0,7.0,101,2.0297029
5347819,255.0,5.0,163,1.5644171
5348515,208.0,5.0,167,1.2380953
5348764,189.0,4.0,144,1.3125
5349126,219.0,5.0,164,1.3272728
5349684,184.0,7.0,96,1.9166666
5350204,274.0,3.0,165,1.6506025
5350787,216.0,4.0,115,1.8782609
5351324,325.0,5.0,167,1.9345238
5351856,366.0,4.0,224,1.6339285
5352194,276.0,5.0,152,1.8157895
5352414,206.0,5.0,102,2.0196078
5353083,383.0,2.0,182,2.0928962
5353802,483.0,1.0,235,2.0553191
5353974,197.0,5.0,86,2.2643678
5354371,252.0,18.0,137,1.839416
5355287,305.0,4.0,225,1.3555555
5356025,223.0,5.0,142,1.5704225
5356900,350.0,4.0,140,2.4822695
5357108,255.0,2.0,103,2.4519231
5357593,300.0,5.0,147,2.0408163
5357858,254.0,3.0,100,2.54
5358171,383.0,3.0,172,2.2267442
5358691,367.0,1.0,172,2.1337209
5359222,233.0,5.0,106,2.1981132
5359410,151.0,6.0,50,3.02
5359670,257.0,7.0,120,2.1416667
5359980,237.0,7.0,100,2.3465347
5360412,255.0,2.0,116,2.1982758
5360632,200.0,8.0,69,2.8571429
5360833,153.0,7.0,66,2.3181818
5361060,259.0,7.0,122,2.1229508
5362076,324.0,8.0,177,1.8305085
5362390,127.0,3.0,91,1.3956044
5363240,255.0,1.0,209,1.2142857
5364151,255.0,1.0,198,1.2878788
5366678,357.0,12.0,265,1.3421053
5367000,195.0,12.0,93,2.0744681
5367237,220.0,7.0,117,1.8644068
5367373,115.0,7.0,55,2.0535715
5367679,248.0,11.0,102,2.4077671
5368084,252.0,13.0,118,2.1355932
5368405,195.0,14.0,76,2.5657895
5368485,56.0,8.0,28,2.0
5368776,229.0,11.0,94,2.4361701
5368892,88.0,12.0,36,2.4444444
5369146,254.0,8.0,98,2.5918367
5369394,254.0,11.0,112,2.2477877
5369828,232.0,11.0,97,2.367347
5370293,303.0,12.0,112,2.681416
5370484,205.0,11.0,62,3.3064516
5370555,101.0,16.0,37,2.6578948
5370697,193.0,4.0,75,2.5394738
5370983,252.0,10.0,132,1.9090909
5372149,367.0,20.0,160,2.2795031
5372319,110.0,20.0,42,2.5581396
5372763,368.0,3.0,113,3.2566371
5373009,255.0,2.0,113,2.2566371
5373470,254.0,5.0,100,2.5148516
5378768,255.0,2.0,199,1.281407
5383389,383.0,1.0,258,1.4787645
5385419,255.0,1.0,180,1.4166666
5387255,255.0,2.0,169,1.5088757
5389641,381.0,5.0,276,1.3804348
5391634,379.0,5.0,217,1.7465438
5392525,255.0,1.0,172,1.4825581
5393256,146.0,3.0,119,1.2166667
5395768,255.0,4.0,94,2.7127659
5396071,264.0,76.0,87,3.0344827
5396472,255.0,1.0,107,2.3611112
5396692,205.0,55.0,73,2.7702703
5397042,230.0,76.0,57,3.9655173
5397473,111.0,36.0,71,1.5633802
5398108,257.0,3.0,105,2.447619
5400000,376.0,20.0,220,1.7013575
5401311,143.0,6.0,100,1.4299999
5401866,127.0,1.0,64,1.984375
5403149,258.0,9.0,121,2.1147542
5403823,261.0,3.0,168,1.5443788
5404657,273.0,4.0,152,1.7960526
5406124,266.0,6.0,127,2.0944881
5407661,211.0,90.0,87,2.4252872
5408056,163.0,104.0,40,3.9756098
5408174,121.0,159.0,12,10.083333
5408178,0.0,160.0,0,0.0
5408539,267.0,1.0,75,3.5599999
5408745,124.0,98.0,75,1.6533333
5408820,163.0,98.0,20,8.1499996
5409182,184.0,139.0,30,5.9354839
5409298,161.0,139.0,14,10.733334
5409394,169.0,104.0,18,8.8947372
5409706,250.0,139.0,45,5.5555553
5409711,0.0,139.0,0,0.0
5410252,256.0,98.0,103,2.4615386
5410256,89.0,99.0,1,89.0
5410341,124.0,20.0,20,6.1999998
5410484,147.0,1.0,37,3.8684211
5411064,254.0,3.0,113,2.2280703
5411468,368.0,27.0,139,2.6285715
5411815,255.0,4.0,108,2.3394496
5412533,253.0,4.0,72,3.5138888
5413007,383.0,2.0,152,2.5197368
5413342,378.0,49.0,102,3.7058823
5413816,131.0,9.0,76,1.7012987
5415018,251.0,11.0,141,1.7676057
5415688,305.0,5.0,153,1.993464
5415927,154.0,7.0,84,1.8117647
5416424,153.0,9.0,53,2.8867924
5416582,121.0,9.0,37,3.1842105
5417999,352.0,14.0,263,1.338403
5418723,237.0,8.0,142,1.6573427
5419035,214.0,6.0,121,1.7540984
5419706,255.0,1.0,155,1.6451613
5420550,252.0,5.0,110,2.2909091
5420760,255.0,1.0,82,3.109756
5428164,164.0,11.0,93,1.7634408
5431319,132.0,14.0,32,4.125
5436195,125.0,9.0,45,2.7777777
5439160,207.0,27.0,103,2.0097086
5439234,242.0,84.0,24,10.083333
5439316,156.0,131.0,19,7.8000002
5439352,252.0,50.0,16,15.75
5439455,247.0,160.0,17,14.529411
5439512,117.0,75.0,23,5.0869565
5439569,152.0,73.0,25,6.0799999
5439583,103.0,99.0,8,11.444445
5439596,150.0,588.0,6,21.428572
5439605,89.0,117.0,4,22.25
5439640,145.0,125.0,12,12.083333
5439665,252.0,14.0,13,19.384615
5439738,277.0,218.0,35,7.9142857
5439771,204.0,92.0,23,8.869565
5439781,187.0,314.0,7,26.714285
5439784,6.0,409.0,2,3.0
5439815,238.0,43.0,17,13.222222
5439831,242.0,74.0,15,16.133333
5439834,195.0,120.0,2,97.5
5439843,254.0,24.0,7,36.285713
5439861,207.0,58.0,10,18.818182
5439874,250.0,17.0,8,27.777779
5439888,228.0,89.0,10,22.799999
5439925,320.0,233.0,20,15.238095
5439935,229.0,96.0,5,45.799999
5439944,208.0,342.0,5,34.666668
5439958,237.0,77.0,6,33.857143
5439982,166.0,101.0,11,15.090909
5440002,167.0,333.0,5,27.833334
5440028,240.0,17.0,10,21.818182
5440043,186.0,127.0,8,23.25
5440075,254.0,88.0,8,31.75
5440105,35.0,308.0,5,5.8333335
5440123,130.0,132.0,4,32.5
5440181,229.0,46.0,17,12.722222
5440193,80.0,184.0,5,16.0
5440236,188.0,87.0,10,17.09091
5440305,255.0,6.0,25,10.2
5440330,255.0,1.0,12,21.25
5440411,240.0,22.0,38,6.1538463
5440449,201.0,83.0,17,11.823529
5440471,181.0,81.0,8,22.625
5440500,227.0,49.0,14,16.214285
5440537,131.0,170.0,9,14.555555
5440793,256.0,1.0,68,3.710145
5443666,246.0,11.0,134,1.8222222
5444676,257.0,13.0,117,2.1965811
5445757,366.0,6.0,185,1.967742
5446870,340.0,8.0,162,2.0987654
5449438,270.0,7.0,152,1.7647059
5451534,382.0,2.0,223,1.7053572
5452423,251.0,6.0,125,1.9920635
5453142,259.0,30.0,145,1.7739726
5454730,351.0,8.0,177,1.9830508
5455678,82.0,1.0,57,1.4385965
5455720,0.0,1.0,0,0.0
--------------------------------------------------

>Hi James
>
>Can you please send the full DDL for these two tables, including create
>table, indexes, keys, relations & DBCC SHOW_STATISTICS for these tables /
>indexes? I'm sceptible about it's choice of a hash join to perform this
>work.
>
>Another question for you - does this work need to be performed atomically as
>a single unit of work or is it possible to break it into two or three
>batches? This may increase performance tremendously.
>
>I'd suggest that the query written differently might yield a better plan,
>eg:
>
>UPDATE Table1
>SET hasChildren=1
>WHERE ID in (SELECT parentID FROM Table2)
>
>or
>
>UPDATE Table1
>SET hasChildren=1
>WHERE exists (SELECT * FROM Table2 where parentid = Table1.id)
>
>I'd suggest that batching updates might help even further if either of those
>re-written queries did yield better plans.
>
>Regards,
>Greg Linwood
>SQL Server MVP

Relevant Pages

  • Re: PCA C code contradictions
    ... I thought that was the end of it, and didn't realize Greg came ... written comprehension in Chinese to be admitted to that grade. ... the formula for the REAL correlation matrix! ... You are just a chronic excuse-making, know-nothing in statistics, ...
    (sci.stat.math)
  • Re: Built-in for large dining room
    ... rudimentary shelving for the pantry. ... I would love to know if there are plans out ... The responses to this post are the reason I regularly check the wreck. ... Greg, good luck on your project, however you end up doing it. ...
    (rec.woodworking)
  • Freezing database statistics
    ... Freezing database statistics ... statistics will also freeze the plans, i.e., same plan will be used ...
    (comp.databases.oracle.server)
  • Re: How is possible that avg_data_blocks_per_key = 0 even with fresh statistics ?
    ... Again you assume the table was empty at some point when statistics ... and at times unreasonable execution plans. ... But the optimizer creates these bad plans based ...
    (comp.databases.oracle.server)
  • Re: How is possible that avg_data_blocks_per_key = 0 even with fresh statistics ?
    ... optimizer may or may not come up with bad plans if the statistics say ... that a table is empty when it is no longer in that condition. ... and at times unreasonable execution plans. ... But the optimizer creates these bad plans based ...
    (comp.databases.oracle.server)