MSSQL网页编程之为何AUTOTRACE不会在实行企图中显现分...
用一个库#bak_database存放这些历史数据。显现|实行TheOracle(tm)UsersCo-OperativeFAQ
WhydoesAUTOTRACEnotshowpartitionpruningintheexplainplan?
为何AUTOTRACE不会在实行企图中显现分区截断（partitionpruning）？
--------------------------------------------------------------------------------
Authorsname:NormanDunbar
NormanDunbar
AuthorsEmail:Oracle(at)BountifulSolutions.co.uk
Datewritten:25March2004
写作日期：2004年3月25日
Oracleversion(s):9.2.0.3.0
Oracle版本：9.2.0.3.0
WhyisitthatwhenIuseAUTOTRACEinSQL*Plus,theexplainplannevershowspartitionpruningtakingplace?
为何当我在SQL*Plus中利用AUTOTRACE时，实行企图从不显现产生了分区截断呢？
--------------------------------------------------------------------------------
Autotracenotshowingpartitionpruning/eliminationisbug1426992,but,afterinvestigationOraclehasdecidedthatthisisnotanoptimiserbug,butabuginSQL*Plus.Youcan,withabitofknowledgeofyourdataandalittleexperimentation,deducethatpartitionpruningistakingplacefromtheoutputofautotrace,buttherearemucheasierways!
AUTOTRACE不显现分区截断/是毛病（BUG）1426992，但查询拜访后Oracle发明这不是优化器的毛病，而是SQL*Plus的成绩。对数占有所懂得并经由一点实验后，你能够依据AUTOTRACE的输入揣度动身生了分区截断，但有更复杂的办法！
Thefollowingdemonstrationshowsthefailingsinautotraceanddemonstratesacoupleofothermethodsofdeterminingwhetherornotyourpartitionsarebeingpruned-ornot.
上面的演示显现了AUTOTRACE不克不及显现时用其他办法判别是不是产生了分区截断。
--------------------------------------------------------------------------------
Autotrace
AUTOTRACE
Firstofall,createasimpletablerangepartitionedover6differentpartitions,andfillitwithsometestdataextractedfromALL_OBJECTS.
起首创立一个复杂的依据区间分为6个分区（rangepartitioned）的表，并从ALL_OBJECTS添补一些测试数据。
SQL>createtabletab_part(part_keynumber(1),some_textvarchar2(500))
2partitionbyrange(part_key)(
3partitionpart_1valueslessthan(2),
4partitionpart_2valueslessthan(3),
5partitionpart_3valueslessthan(4),
6partitionpart_4valueslessthan(5),
7partitionpart_5valueslessthan(6),
8partitionpart_6valueslessthan(MAXVALUE));
Tablecreated.
SQL>insert/*+append*/intotab_part
2selectmod(rownum,10),object_name
3fromall_objects;
24683rowscreated.
SQL>commit;
Commitcomplete.
Oncethetablehasbeenfilled,analyseitandseehowthedatahasbeenspreadoverthevariouspartitions.Thefirstandlastpartitionshavemoredatainthemthattheremainingfour,hencethedifferingtotals.
一旦表中填进数据，剖析并检察数据怎样在分歧的分辨别布。第一和最初的分区比其他四个分区有更多的数据。
SQL>analyzetabletab_partcomputestatistics;
Tableanalyzed.
SQL>selectpartition_name,num_rows
2fromuser_tab_partitions
3wheretable_name=TAB_PART
4orderbypartition_name;
PARTITION_NAMENUM_ROWS
----------------------------------------
PART_14937
PART_22469
PART_32469
PART_42468
PART_52468
PART_69872
6rowsselected.
Nowthatwehaveatabletoworkwith,weshallseewhatautotracehastosayaboutpartitionelimination.First,however,notehowmanylogicalreadsafullscanoftheentiretableneeds:
如今我们有了一个实验表，来看看AUTOTRACE是怎样处置分区截断的。不外起首，注重全表扫描所需的逻辑读：
SQL>setautotraceon
SQL>selectcount(*)fromtab_part;
COUNT(*)
----------
24683
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=42Card=1)
10SORT(AGGREGATE)
21PARTITIONRANGE(ALL)
32TABLEACCESS(FULL)OFTAB_PART(Cost=42Card=24683)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
135consistentgets
0physicalreads
0redosize
381bytessentviaSQL*Nettoclient
499bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
Toread24,683rowsofdataOraclehadtoperform135logicalreads.Keepthisinmindandnotethattheautotraceoutputshowsafulltablescan-aswewouldexpectonanunindexedtable.Thenextcountshouldonlylookinasinglepartition:
为了读取24,683行数据，Oracle举行了135次逻辑读。记着这些，并注重AUTOTRACE输入显现了一个全表扫描——正如关于一个无索引表，我们所预期的。上面的COUNT只应该搜刮一个独自的分区：
SQL>selectcount(*)fromtab_partwherepart_key=7;
COUNT(*)
----------
2468
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=17Card=1Bytes=2)
10SORT(AGGREGATE)
21TABLEACCESS(FULL)OFTAB_PART(Cost=17Card=2468Bytes=4936)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
49consistentgets
0physicalreads
0redosize
380bytessentviaSQL*Nettoclient
499bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
Thisseemstohaveagaincarriedoutafulltablescan,butaswealreadyknowthatarealFTStakes135logicalreads,thefactthatonly49wererequiredhereshouldindicatethatsomethingisdifferent.Autotracesoutputisnotshowingpartitionelimination.Ifyoudidntknowhowmanyreadswererequiredtofullscanthetable,youwouldbehardpressedtodeterminethatpartitioneliminationhadtakenplaceinthisscan.
看起来又一次举行了全表扫描，但正如我们所知，一次真实的FTS必要135次逻辑读，现实上这里必要的49次申明有所改动了。AUTOTRACE的输入没有显现分区截断。假如你不晓得全表扫描必要的读（次数），你很难判别这里的搜刮产生了分区截断。
Event10053
事务10053
Thereareothermethodsbywhichwecanobtainatruepictureoftheplanusedbytheoptimiser-a10053traceforexamplewouldshowthedetails.Iveneverhadtousea10053tracesoImunfortunatelynotinapositiontoexplainitsuse,Ileavethisasanexerciseforthereaderastheysay:o)
我们另有其他办法来取得优化器所用企图的全景——比方，一次10053跟踪将显现出具体信息。我历来都不是必需10053跟踪，以是很不幸我也不合适来注释它的用法，我把它留上去作为“读者的一个实习”:o)
SQL_TRACEandTKPROF
IhaveusedSQL_TRACEandTKPROFthough,sohereswhatshowsupwhenSQL_TRACEissettrue.
SQL_TRACE与TKPROF
我用SQL_TRACE与TKPROF，上面是当SQL_TRACE设为TRUE时的输入。
SQL>setautotraceoff
SQL>altersessionsetsql_trace=true;
Sessionaltered.
SQL>altersessionsettracefile_identifier=PARTITION;
Sessionaltered.
SQL>selectcount(*)fromtab_partwherepart_key=7;
COUNT(*)
----------
2468
SQL>altersessionsetsql_trace=false
Sessionaltered.
Atthispoint,exitfromSQL*PlusandlocatethetracefileinUSER_DUMP_DESTwhichhasPARTITIONinitsname.ThisistheoneyouwanttorunthroughTKPROF.Theoutputfromthisisshownbelow:
此时，加入SQL*Plus并依据USER_DUMP_DEST来定位名字含有“PARTITION”的跟踪文件。运转TKPROF来注释这个文件，输入以下：
selectcount(*)fromtab_partwherepart_key=7
callcountcpuelapseddiskquerycurrentrows
-----------------------------------------------------------------------
Parse10.000.000000
Execute10.010.000000
Fetch20.010.0104901
-----------------------------------------------------------------------
total40.020.0104901
Missesinlibrarycacheduringparse:0
Optimizergoal:CHOOSE
Parsinguserid:62
RowsRowSourceOperation
----------------------------------------------------------
1SORTAGGREGATE(cr=49r=0w=0time=10353us)
2468TABLEACCESSFULLTAB_PARTPARTITION:66(cr=49r=0w=0time=6146us)
Theexplainplanclearlyshowsthatpartition6wasthestartandstoppartitioninthescan.Inaddition,therewere49logicalreadsperformedtogetatthecount.Thisisidenticaltowhatwesawabovewithautotrace,exceptwegettoseethatpartitionpruningdidactuallytakeplace.
实行企图分明的显现了分区6是扫描的肇端和停止分区。而且，举行了49次逻辑读。除实在的看到分区截断切实其实产生了，这与我们在下面用AUTOTRACE的了局分歧。
ExplainPlan
实行企图
BackinSQL*Plus,thereisanothermethodthatcanbeused.TheoldfaithfulEXPLAINPLANwillshowhowpartitionpruningdidtakeplace.
回到SQL*Plus，还能够用另外一种办法。陈旧而忠厚的EXPLAINPLAN将显现分区截断是怎样产生的。
SQL>explainplan
2setstatement_id=Norman
3for
4selectcount(*)fromtab_partwherepart_key=7;
Explained.
SQL>setlines132
SQL>setpages10000
SQL>coloperationformata20
SQL>coloptionsformata15
SQL>colobject_nameformata15
SQL>colp_startformata15
SQL>colp_stopformata15
SQL>collevelnoprint
SQL>selectlevel,lpad(,2*level-1)||operationasoperation,
2options,
3object_name,
4partition_startasp_start,
5partition_stopasp_stop,
6cardinality
7fromplan_table
8wherestatement_id=Norman
9startwithid=0
10connectbypriorid=parent_id
11orderbylevel
OPERATIONOPTIONSOBJECT_NAMEP_STARTP_STOPCARDINALITY
-------------------------------------------------------------------------------------------
SELECTSTATEMENT1
SORTAGGREGATE1
TABLEACCESSFULLTAB_PART662468
Onceagain,theplanclearlyshowsthatpartitionpruningtakesplace.Theproblemisthatautotracedoesntshowitatall.Unlessyoureallyknowhowmanyblocksofdatayouhaveinatableandallofitspartitions,youmayfinditdifficulttodeterminewhetherornotyouareseeingatrueplanwhenusingpartitionedtablesandautotrace.
企图再次分明的显现产生了分区截断。成绩是AUTOTRACE不显现。除非真的晓得表中有几数据块与一切的分区，你会发明利用分区表和AUTOTRACE的确很难断定“真正”的企图。
Note:DoyoueversufferfromthePLAN_TABLEgrowingtoobigasdevelopersfailtodeleteoldrowsfromthetable?Alternatively,doyouforgettodeleterowsfromthetable?
注重：有无为PLAN_TABLE增加太快而开辟职员不从表中删除旧行而疾苦？大概，你是不是健忘从表中删除纪录？
Takeacopyof$ORACLE_HOME/rdbms/admin/utlxplan.sqlandeditit.
复制一份$ORACLE_HOME/rdbms/admin/utlxplan.sql并编纂：
Changethis:
修正：
createtablePLAN_TABLE(
statement_idvarchar2(30),...
filter_predicatesvarchar2(4000));
Tothis:
为：
createglobaltemporarytablePLAN_TABLE(
statement_idvarchar2(30),...
filter_predicatesvarchar2(4000))
oncommitpreserverows;
NowlogintoSQL*PlusasSYSand:
如今以SYS上岸SQL*Plus，并：
sql>@?/rdbms/admin/utlxplan_edited/*Orwhateveryourcopyiscalled*/
sql>grantallonplan_tabletopublic;
sql>createpublicsynonymPLAN_TABLEforSYS.PLAN_TABLE;
NowwhendevelopersorDBAsusePLAN_TABLEandlogouttheirrowswillbedeleted.Aself-tidyingPLAN_TABLE.Ofcourse,thisisnogoodifyouwanttokeeprowsinPLAN_TABLEbetweensessions.
如今当开辟职员或DBA们利用PLAN_TABLE并登出时，他们的纪录将被删除。一个自我干净的PLAN_TABLE。固然，假如你必要在会话间保存PLAN_TABLE中的纪录就不可了。
DBMS_XPLAN
UnderOracle9i(release2Ithink)thereisanewPL/SQLpackagewhichyoucanusetoshowexplainplans.Theabovestatementcouldhaveitsplanshownusingthiscommand:
在Oracle9i（我想是Release2）中，有一个新的PL/SQL包能够用于显现实行企图。下面的语句能够用以下指令显现企图：
SQL>Select*fromtable(dbms_xplan.display(statement_id=>Norman));
or,ifthiswastheonlystatementinmyPLAN_TABLE:
大概，假如这时候我PLAN_TABLE中独一的语句：
SQL>Select*fromtable(dbms_xplan.display);
Thereismuchmoreinformationshownwiththisnewfeaturethanwithanormalexplainplanandyoudonthavetoworryaboutallthatformattingeither.
个中比“正轨”的实行企图显现了更多信息，而且你也不用为格局化费心。
Summary
总结
Insummary,autotracedoesntshowpartitioneliminationinOracleuptoversions9irelease2.YoushouldthereforebeawareofthisfactanduseSQL_TRACEorEXPLAIN_PLANtogetatthetrueplanfortheSQLyouaretryingtotune/debug.
AUTOTRACE到Oracle9iR2为止不显现分区截断。以是你应该注重这个现实并利用SQL_TRACE或EXPLAIN_PLAN来取得你必要调剂的SQL的真实的企图。
--------------------------------------------------------------------------------
Furtherreading:
进一步浏览：
Note:166118.1PartitionPruning/EliminationonMetalink.YouwillneedasupportcontracttoaccessMetalink.
注重：166118.1PartitionPruning/EliminationonMetalink.你必要一个撑持条约来会见Metalink。
Bug:1426992SQLPlusAUTOTRACEdoesnotshowcorrectexplainplanforpartitionelimination.AgainonMetalink.
Bug:1426992SQLPlusAUTOTRACEdoesnotshowcorrectexplainplanforpartitionelimination.仍是在Metalink。
--------------------------------------------------------------------------------
本文翻译自http://www.jlcomp.demon.co.uk/faq/autotrace_pruning.html译者仅保存翻译版权
mysql的prepare其实是本地PHP客户端模拟的，并没有根据你mysql的设置做字符集的调整。应该交与mysqlserver端做prepare，同时得调用mysql_set_character_set去操作，server才会按照字符集去做转义。 where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。 换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的 但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右） 如果你是从“学习某一种数据库应用软件，从而获得应聘的资本和工作机会”的角度来问的话。 只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。 大家注意一点。如下面的例子： 总感觉自己还是不会SQL
页:
[1]