Docstoc

Optimizer statistics

Document Sample
Optimizer statistics Powered By Docstoc
					SELECT
SELECT A."IDENT", A."MASTER_TYPE", A."PREF_ADDR_TYPE_CD", A."LAST_NAME",
A."FIRST_NAME", A."MIDDLE_NAME",
B."ACADMIC_ID", B."COLLEGE", B."SPECIAL_DEGREE", B."YEAR", B."CURRENTCLASS",
C."COLLEGE_CD", C."SHORT_DESCRIPTION" as college_short,
D."NAME_HISTORY_ID", D."NAME",
E."NAME_TYPE_CD", E."NAME_TYPE_DESC" FROM DBA.ID_MASTER A with(fastfirstrow)
LEFT OUTER JOIN DBA.ACADEMIC B ON A."IDENT" = B."IDENT" AND
B."SCHOOL_TYPE_CD" = 1070
LEFT OUTER JOIN DBA.COLLEGE_TYPE C ON B."COLLEGE"= C."COLLEGE_CD"
INNER JOIN DBA.NAME D ON A."IDENT"= D."IDENT"
INNER JOIN DBA.NAME_TYPE E ON D."NAME_TYPE_CD"= E."NAME_TYPE_CD"
WHERE A."IDENT"= D."IDENT" AND D."NAME_TYPE_CD"= E."NAME_TYPE_CD"
and a.ident >=202
Node Statistics
                 Estimates Actual      Description
Invocations      -         1           Number of times the result was computed
RowsReturned 1.8188e+005 2.0012e+005 Number of rows returned
PercentTotalCost 0         5.9335      Run time as a percent of total query time
RunTime          0         0.91745     Time to compute the results
CPUTime          0         -           Time required by CPU
DiskReadTime 0             -           Time to perform reads from disk
DiskWriteTime 0            -           Time to perform writes to disk
CacheHits        -         2.7964e+005 Cache Hits
CacheRead        -         2.7964e+005 Cache reads
CacheReadTable -           2.7964e+005 Cache table reads
Subtree Statistics
                 Estimates Actual        Description
Invocations      -           1           Number of times the result was computed
RowsReturned     1.8188e+005 2.0012e+005 Number of rows returned
PercentTotalCost 100         100         Run time as a percent of total query time
RunTime          101.42      15.462      Time to compute the results
CPUTime          55.74       -           Time required by CPU
DiskReadTime     45.676      -           Time to perform reads from disk
DiskWriteTime    0           -           Time to perform writes to disk
CacheHits        -           2.0421e+006 Cache Hits
CacheRead        -           2.0432e+006 Cache reads
CacheReadTable -             7.0799e+005 Cache table reads
CacheReadIndLeaf -           1.3352e+006 Cache index leaf reads
DiskRead         7165        1350        Disk reads
DiskReadTable    -           2806        Disk table reads
DiskReadIndLeaf -            905         Disk index leaf reads
DiskReadIndInt   -           2           Disk index interior reads
DiskWrite        0           0           Disk writes
IndLookup        -           5.3448e+005 Number of index lookups
Optimizer statistics
                       Value         Description
Costed subplans        32            Number of different enumeration strategies considered
                                     by the optimizer
Estimated cache pages   62712        Estimated cache pages available for this statement
CurrentCacheSize        513164       Current cache size in kilobytes
Isolation_level         1            Controls the locking isolation level
Optimization_goal       All-rows     Optimize queries for first row or all rows
Optimization_level      9            Reserved
                                     Controls whether optimizing for OLAP or mixed
Optimization_workload Mixed
                                     queries
ProductVersion          9.0.2.2451   Product version
                        Override-
User_estimates                       Controls whether to respect user estimates
                        magic
Select list
A.IDENT             int
A.MASTER_TYPE       char(1)
A.PREF_ADDR_TYPE_CDsmallint
A.LAST_NAME         char(20)
A.FIRST_NAME        char(20)
A.MIDDLE_NAME       char(15)
B.ACADMIC_ID        int
B.COLLEGE           smallint
B.SPECIAL_DEGREE    char(1)
B.YEAR              decimal(4,0)
B.CURRENTCLASS      char(1)
C.COLLEGE_CD        smallint
C.SHORT_DESCRIPTION char(10)
D.NAME_HISTORY_ID int
D.NAME              char(45)
E.NAME_TYPE_CD      smallint
E.NAME_TYPE_DESC    char(30)
Options
                        Value                                                Description
                                                                             Mounted
Alias                   advance
                                                                             database name
                                                                             Controls
Ansi_blanks             Off                                                  truncation
                                                                             errors
                                                                             Controls
                                                                             whether
Ansi_integer_overflow Off                                                    integer
                                                                             overflow
                                                                             causes an error
                                                                             Controls the
Ansi_update_constraint                                                       range of
                       Cursors
s                                                                            updates that
                                                                             are permitted
                                                                      Controls
                                                                      interpretation
Ansinull           On
                                                                      of NULL
                                                                      values
BlankPadding       ON                                                 Blank padding
                                                                      Blob extension
                                                                      pages are
                                                                      stored
BlobArenas         ON
                                                                      separately
                                                                      from table
                                                                      pages
                                                                      Controls
                                                                      response to
Blocking           On
                                                                      locking
                                                                      conflicts
                                                                      Database
Capabilities       5EDBFFFDD
                                                                      capability bits
                                                                      Case
CaseSensitive      OFF
                                                                      sensitivity
CharSet            cp1252                                             Character Set
                                                                      Page
Checksum           OFF
                                                                      checksum
                                                                      Clustered
ClusteredIndexes   ON
                                                                      indexes
                                                                      Collation
Collation          1252LATIN1
                                                                      name
                                                                      Expanded
                                                                      command line
CommandLine        -ha -ga C:\advance9.db -n advance -hn0,3784:2112
                                                                      used to start
                                                                      the server
                                                                      Compressed
                                                                      B-Tree
CompressedBTrees   ON
                                                                      indexes are
                                                                      supported
                                                                      Compression
Compression        OFF
                                                                      enabled
                                                                      Controls
                                                                      datatype
Conversion_error   On
                                                                      conversion
                                                                      errors
                                                                      Controls
Date_format        YYYY-MM-DD                                         format for
                                                                      DATE values
                                                                      Controls order
Date_order         YMD                                                of date
                                                                      components
                                        Database file
DBFileFragments        3
                                        fragments
                                        Default
DefaultCollation       1252LATIN1
                                        Collation
                                        Controls
Divide_by_zero_error   On               divide-by-zero
                                        errors
                                        Drive type
                                        where the
DriveType              FIXED
                                        database is
                                        located
                                        Encryption
Encryption             None
                                        type
                                        Controls errors
                                        when using
Extended_join_syntax   On               duplicate
                                        correlation
                                        names in joins
File                   C:\advance9.db   Database file
                                        File size in
FileSize               284736
                                        pages
                                        Database file
FileVersion            41               version
                                        number
                                        Free database
                                        pages
FreePageBitMaps        ON
                                        managed via
                                        bitmaps
                                        Number of
FreePages              3010             free pages in
                                        dbspace
                                        Fixed
                                        histogram hash
HistogramHashFix       ON
                                        implementatio
                                        n
                                        Optimizer
                                        statistics
Histograms             ON
                                        maintained as
                                        histograms
                                        Index
IndexStatistics        ON
                                        Statistics
IsJavaAvailable        NO               Java available
                                        Java objects
JavaObjectsEnabled     NO
                                        enabled
                                        Is Network
IsNetworkServer        NO
                                        Server
                                    Is Runtime
IsRuntimeServer      NO
                                    Server
Language             us_english
                                    Number of
LicenseCount         1
                                    licensed seats
                                    Name of the
LicensedCompany                     licensed
                                    company
                                    Name of the
LicensedUser
                                    licensed user
LicenseType          not licensed   License type
                                    Client liveness
LivenessTimeout      120
                                    timeout default
                                    Log file
LogFileFragments     1
                                    fragments
                                    Name of the
MachineName          ITALIA2
                                    machine
                                    Main heap
MainHeapBytes        658192
                                    bytes in cache
                                    Maximum
                                    number of
                                    cached
Max_plans_cached     20
                                    execution
                                    plans for a
                                    connection
                                    Maximum
MaxCacheSize         513164         cache size in
                                    kilobytes
                                    Minimum
MinCacheSize         2048           cache size in
                                    kilobytes
                                    Multi Byte
MultiByteCharSet     OFF            Character Set (
                                    on/off )
Name                 advance
                                    Named
NamedConstraints     ON
                                    Constraints
                                    Number of
NumProcessorsAvail   1              processors on
                                    server
                                    Maximum
                                    number of
NumProcessorsMax     1
                                    processors
                                    used
                                    Database page
PageSize             4096
                                    size
                                                             Peak cache
PeakCacheSize         513164                                 size in
                                                             kilobytes
                                                             Operating
Platform              WindowsXP                              system
                                                             platform
                                                             Operating
                                                             system
PlatformVer           Windows XP Build 2600 Service Pack 3
                                                             platform
                                                             version
                                                             Maximum
                                                             number of
Precision             30                                     digits in
                                                             decimal
                                                             arithmetic
                                                             Controls
Prefetch              On                                     prefetching of
                                                             rows
                                                             Procedure
ProcedureProfiling    OFF
                                                             profiling
                                                             Processor
ProcessorArchitecture X86
                                                             architecture
                                                             Database read-
ReadOnly              OFF
                                                             only mode
                                                             Request
RequestLogging        NONE
                                                             logging
                                                             Controls
                                                             whether row
Row_counts            Off                                    counts are
                                                             estimates or
                                                             exact
                                                             Minimum
                                                             number of
Scale                 6
                                                             digits after
                                                             decimal point
                                                             Checkpoint
                                                             log maintained
SeparateCheckpointLo
                     ON                                      at end of
g
                                                             system
                                                             dbspace
                                                             Primary and
                                                             foreign key
SeparateForeignKeys   ON                                     indexes are
                                                             stored
                                                             separately
StartTime             2008-12-12 08:07:16.16                 Server start
                                                                        time
                                                                        Controls
                                                                        truncation
String_rtruncation           Off                                        errors on
                                                                        INSERT or
                                                                        UPDATE
                                                                        Fixed string
                                                                        histograms
StringHistogramsFix          ON
                                                                        implementatio
                                                                        n
                                                                        Table bit maps
TableBitMaps                 ON
                                                                        supported
                                                                        Temporary
TempDir                      C:\DOCUME~1\helen\LOCALS~1\Temp
                                                                        directory
                                                                        Database
                             C:\DOCUME~1\helen\LOCALS~1\Temp\asat0000.t
TempFileName                                                            temporary file
                             mp
                                                                        name
                                                                        Server thread
Threads                      20
                                                                        count
                                                                        Controls
Time_format                  HH:NN:SS.SS                                format for
                                                                        TIME values
                                                                        Controls
                                                                        format for
Timestamp_format             YYYY-MM-DD HH:NN:SS.SS
                                                                        TIMESTAMP
                                                                        values
                                                                        Controls
                                                                        recording of
Update_statistics            On                                         statistics
                                                                        during query
                                                                        execution
Userid                       DBA                                        User ID
                                                                        Hash length
                                                                        may be
VariableHashSize             ON
                                                                        specified for
                                                                        BTree indexes

I wasn’t able to save the graphical plan on 9. But here is the gist:

    1. Select 5.98%
    2. Lock 5.40%
    3. Nested loops Join 14.56%
    4. Nested Loops Join 11.02                    5. E 9.42%

6 Left Outer Nexted Loops Join 8.9%               7. D 15.64%
8 Left Outer Nexted Loops Joing 8.75%             9. C 5.33%
10. A 4.36% 11. B 10.69%

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:3/26/2013
language:Unknown
pages:7