Transcription
Informix SQLPerformance TuningTipsBy Jeff FilippiNew Informix Tech Talks by the IIUGWe are launching a new channel on YouTube for Informix Users! This will bea place for Informix how-to videos. More information will be coming soon.International Informix User GroupWe speak Informix
Jeff FilippiIntegrated Data Consulting, ulting.comInternational Informix User GroupWe speak Informix
OVERVIEW Identify Problem SQL Statements Tracing SQL in Informix Options Available with Set Explain & Reading sqexplain output withtuning examples Methods to use for Improving SQL PerformanceInternational Informix User GroupWe speak Informix
Identify Problem SQL Statements First you have to identify what SQL statements are the culpritsin causing performance issues Use “onstat –g ntt” to identify the last time read/writes occurred Gather slow SQL statements from onstats, Informix HQ and 3rdparty tools like Server Studio. Look at how many times SQL statements have been executedusing SQL Statement Cache (onstat –g ssc) Informix tracing feature (SQLTRACE) Review with developers known problem areas in the application Verify update statistics are current Review what tables/indexes have the most readsInternational Informix User GroupWe speak Informix
Use SQL Statement Cache onstat –g ssc Look at SQL's with a large number of executions. Saving even a second on a SQL statement that is executed 1 milliontimes can make a difference in performance. New features with 14.10 for Statement Cache.International Informix User GroupWe speak Informix
Use SQL Statement CacheIBM Informix Dynamic Server Version 11.70.FC7 -- On-Line -- Up 23 days 23:46:38 -- 2530056 KbytesStatement Cache Summary:#lrus currsize maxsize Poolsize #hits nolimit822491472 40960000 11710464 100Statement Cache Entries:lru hash ref cnt hits flag heap --------------------------------------------0 1400 15 -F bb164020ntlcominformixselect descr, rowid, seq nbr from fl cntrl where uid in ( 'all','cschabel' ) and program name in ( 'all', 'cr inv dl' ) and exc type0 1160 1011 -F aa23bc20ntlcomis not null order by seq nbrinformixupdate state tax set row status "V", updt user id ? where seq nbr ? And0 2070 6003 -F a004f820ntlcom( rec type 6 or rec type 7)informixselect count ( *) from invoice state where cde "CORR" and seq nbr ?2 1380 6244 -F afa9ec20ntlcominformixselect int comm, int comm2, updt user id from invoice cmnts where seq nbr ? and extend ( updt dte, year to second) ( select max ( extend ( updt dte, year to second)) from invoice cmnts where seq nbr ?)International Informix User GroupWe speak Informix
Use SQL Statement CacheStatement cache enhancements New sysmaster:syssscelem pseudo table to coincidewith onstat -g ssc. For more information, see The sysmaster database. Invalidate specific statement(s) in the StatementCache. For more information, see Monitoring usage of theSQL statement cache. Lock query plan(s) in the Statement Cache. For more information, see Monitoring usage of theSQL statement cache.International Informix User GroupWe speak Informix
Use SQL Statement CacheDump query plans from Statement Cache New onconfig parameterSTMT CACHE QUERY PLAN 0 – Disabled 1 – Enabled Disabled by Default WILL consume more of yourSTMT CACHE SIZE Query plan is viewable insysmaster:syssscelem.queryplan columnInternational Informix User GroupWe speak Informix
Review Number of Reads onTable/Index Use the table SYSPTPROF to look at thebuffer reads, page reads, sequential scans.SELECT tabname[1,25], bufreads, pagreads,isreads, seqscansFROM sysmaster:sysptprofORDER BY 2 descInternational Informix User GroupWe speak Informix
Example - Readstabnamebufreadspagreadstrnsit 1-2122091061429trnsit 1-8123143723162-678524115trnsit 1-110705308233-390409810im p route 11806427782247865918944ed rl event1749246222235501709746386loc sup data1479941490ed rl event 739458460 490211251731611003575373042018ed rl event 489352066025725886704767im mv event87010888930477208780365364International Informix User GroupWe speak Informix
New Index Addedtabnamebufreads140 409-845911921cntrct num221 02868878406226191.trnsit172150242212007375trnsit ix11563862910612898627im p route 12304534712904International Informix User GroupWe speak Informix
Tracing SQL in Informix There are more ways to find information to tune inInformix utilizing the tracing of SQL onconfig parameter: SQLTRACE SQL Admin API Informix HQ (NEW)International Informix User GroupWe speak Informix
Tracing SQL in Informix There are a couple ways to turn tracing on in Informix onconfig parameter: SQLTRACE level [off,low,med,high]ntraces [# of traces]size [size of each trace buffer in kb]mode [global,user]Example: SQLTRACE level low,ntraces 1000,size 2,mode global(This allows me to trace the last 1000 sqlstatements of the instance) Informix HQInternational Informix User GroupWe speak Informix
Tracing SQL in Informix Improved SQL tracing with the SQL Admin API inInformix You can use these new commands to manage SQLtracing by databases. SET SQL TRACING DATABASE ADD {Database}CLEARLISTREMOVE {Database} You can also suspend and resume all tracing at theserver without de-allocating any resources. SET SQL TRACING SUSPEND/RESUMEInternational Informix User GroupWe speak Informix
Tracing SQL in Informix Here is how you enable tracing thru the“sysadmin” database by running the followingcommand: EXECUTE FUNCTION task(“set sql tracingon”,1000,2,”low”,”global”) To validate that tracing is turned on by: onstat –g his This option prints information about the SQLTRACEconfiguration parameter.International Informix User GroupWe speak Informix
Tracing SQL in Informixonstat –g hisIBM Informix Dynamic Server Version 14.10.FC5 -- On-Line -- Up 5 days 23:44:15 -- 2530056KbytesStatement history:Trace LevelLowTrace ModeGlobalNumber of traces3000Current Stmt ID939412632Trace Buffer size2024Duration of buffer8241 SecondsTrace Flags0x00001611Control Block9df53018International Informix User GroupWe speak Informix
Tracing SQL in InformixStatement # 94653656:Database:@ 9df68cb00x1700002Statement text:SELECT * FROM invc state WHERE seq nbr ?Iterator/Explain ID Left Right Est Cost Est Rows Num Rows1002646TypeIndex ScanStatement information:Sess id User id Stmt Type76401001SELECTInternational Informix User GroupFinish Time18:44:20Run Time0.0006We speak Informix
Tracing SQL in InformixStatement Read% CacheIDX Read WriteWrite% Cache17100.000LockLK Wait0LockRequests Waits13TotalLogTime (S) Space00.0000TotalAvg0.000 BMax000.00NumDiskMemorySortsSortsSorts00Avg0I/O WaitAvg RowsExecutions Time (S) Time (S) Time (S)IO Wait72940.000000 0.000000 10869.56526.60400.00090.0015Time (S)Per SecEstimated Estimated ActualSQLISAMIsolation nternational Informix User Group0We speak Informix
Tracing SQL in Informix You can also get information on the tracing thru the“syssqltrace” table in the sysmaster database. Ex. {# of queries that ran 2 seconds)SELECT count(*)FROM syssqltraceWHERE sql totaltime 2; Another useful table is the “syssqltrace iter”which gives information in the form of an iterationtree for each SQL. It allows you to identify whichpart of the query plan took the most time to run.International Informix User GroupWe speak Informix
Tracing SQL in Informix You can run the following SQL statement to getthe SQL for the ones that have a higher run timethan ½ a second.select sql runtime, sql statementfrom sysmaster:syssqltracewhere sql runtime .5order by 1 descInternational Informix User GroupWe speak Informix
Tracing SQL in Informix Here is what the “syssqltrace” table looks likesql id9894804sql address13746521704sql sid26646sql uid668sql stmttype2sql stmtnameSELECTsql finishtime 1396011341sql begintxtime 301590260sql runtime4.7sql pgreads0sql bfreads5sql rdcache100.0000000000sql bfidxreads 0International Informix User GroupWe speak Informix
Tracing SQL in Informixsql pgwrites0sql bfwrites0sql wrcache0.00sql lockreq1sql lockwaits 0sql lockwttime 0.00sql logspace0sql sorttotal 0sql sortdisk0sql sortmem0sql executions 1sql totaltime 4.7sql avgtime4.7sql maxtime4.7sql numiowaits 0sql avgiowaits 0.00sql totaliowaits 0.00sql rowspersec 20898.94078138International Informix User GroupWe speak Informix
Tracing SQL in Informixsql estcost23sql estrows46sql actualrows 1sql sqlerror 0sql isamerror 0sql isollevel 1sql sqlmemory 24200sql numiterators 1sql database ir live2sql numtables 4sql tablelist systablessql statement select owner,tabname,tabtype,tabid from informix.systablessql stmtlen117sql stmthash 206750675sql pdq0sql num hvars 0sql dbspartnum 10488544sql aqtNonesql aqtinfo-26500International Informix User GroupWe speak Informix
Tracing SQL in Informix You can also save the history of the SQL tracing information.NOTE: Caution when using this, it can use a lot of space veryquickly. I had a customer fill a 20 gig dbspace in 24 hours. In the Scheduler there is a new task “Save SQL Trace”. Information is saved in the following tables in the sysadmindatabase: mon syssqltrace(SQL Statement text and profile info) mon syssqltrace info (SQL Statement tracing setup info) mon sqltrace iter(SQL Statement iterators)International Informix User GroupWe speak Informix
Tracing SQL in Informix Here is an alternative to saving ALL tracing information. Create a task which saves SQL trace information forSQL’s that have a run time of greater than 10 seconds. This allows you to still trace SQL statements, but onlyshow you the really long running SQL statementsInternational Informix User GroupWe speak Informix
Tracing SQL in Informix Create a Table to save the SQL Trace information Create a new dbspace to put the table in so that if it does fill up a dbspace it does not affect any other processes.create raw table "informix".save sqltrace(date time datetime year to second,sql id int8,sql runtime float,sql sid int8,sql uid int8,sql statement char(11000),sql database char(30)) in sqltrace extent size 100000 next size 50000 lock mode row;create index "informix".idx savesql1 on "informix".save sqltrace (date time) in sqltrace;create index "informix".idx savesql2 on "informix".save sqltrace (sql runtime) in sqltrace;create index "informix".idx savesql3 on "informix".save sqltrace (sql id) in sqltrace;International Informix User GroupWe speak Informix
Tracing SQL in Informix Here is the information that needs to be insertedinto the “ph task” table to activate the task. In my case I was running it every minute. You will want to see what the shortest time thatyour trace buffer is and make the frequency thetask runs smaller than that.0 save trace Saves SQL Trace when run time greater than setvalue. TASK 9251 sysadmin insert into save sqltrace selectcurrent, sql id, sql runtime, sql finishtime, sql sid, sql uid,sql statement, sql database from sysmaster:syssqltracewhere (sql runtime 5 and (sql finishtime (selectmax(sql finishtime) from save sqltrace)) or (sql runtime 5and ((select count(*) from save sqltrace) 0))); 3000:00:00 00:00:00 0 00:01:00 2021-02-2714:54:17 9237 0 t t t t t t t 400 PERFORMANCE t 0 International Informix User GroupWe speak Informix
Use Informix HQ Use Informix HQ to look at sessioninformation to see long running SQL.International Informix User GroupWe speak Informix
Use Informix HQInternational Informix User GroupWe speak Informix
Use Informix HQInternational Informix User GroupWe speak Informix
Options Available with SQEXPLAIN Optimizer Directives – AVOID EXECUTE Generate query plan without executing SQL, useful forgetting query plans for inserts, updates and delete wheredata is manipulated, but you do not want to change data Example: set explain on AVOID EXECUTE; SQL StatementInternational Informix User GroupWe speak Informix
Options Available with Set Explain Set Explain Enhancements You can turn on/off explain statistics thru the onconfig parameter“EXPLAIN STAT”. 0 – Disables the display of query statistics 1 – Enables the display of query statistics You can also set it with the following statement: SET EXPLAIN STATISTICS When this is enabled, the inclusion of the “Query Statistics” section in the explainoutput file. It shows the query plan’s estimated number of rows and the actualnumber of rows returned.International Informix User GroupWe speak Informix
Options Available with Set ExplainQuery StatisticsQUERY:------select * from partsuppwhere ps partkey 1 and ps partkey 100 and ps suppkey 0 and ps suppkey 100000 and ps availqty 1000 and ps availqty 1000000Estimated Cost: 49Estimated # of Rows Returned: 3601) informix.partsupp: INDEX PATH(1) Index Keys: ps partkey ps suppkey ps availqty (Key-First) (Serial, fragments: ALL)Lower Index Filter: informix.partsupp.ps partkey 1 AND (informix.partsupp.ps availqty 1000 ) AND (informix.partsupp.ps suppkey 0 )Upper Index Filter: informix.partsupp.ps partkey 100 AND (informix.partsupp.ps availqty 1000000 ) AND (informix.partsupp.ps suppkey 100000 )Index Key Filters: (informix.partsupp.ps availqty 1000 ) AND (informix.partsupp.ps availqty 1000000 ) AND(informix.partsupp.ps suppkey 100000 ) AND (informix.partsupp.ps suppkey 0 )Query statistics:----------------Table map :---------------------------Internal nameTable ptypetable rows prod est rows rows scantimeest cant1263602600:00:00International Informix User Group49We speak Informix
Dynamic Set Explain Dynamically set explain on for a sessiononmode –Y {session id} {0 1}(0 – Off/1 – On) Output is to a file “sqexplain.out.{session id} With Informix 11 there are a couple changes: An additional value “2” (explain without statistics for session,displays query plan only) Also, you can specify the file name and directory that you want theexplain output to be sent: onmode –Y {session id} {0 1 2} {filename} This is a great feature to allow you to see the SQL statements executed andthe explain plan for each SQL statement. NOTE: make sure that you only have this turned on for a short period oftime, it creates a large file.International Informix User GroupWe speak Informix
Set Explain Output Add “set explain on” before the statement you want toexamine You can specify the directory that you want the file to go: set explain file to “filename” Review the “set explain” output: UNIX: The file “sqexplain.out” will be generated in thedirectory that you run the query from Windows: Look for a file “username.out” in thedirectory on the UNIX server%INFORMIXDIR%\sqexplnInternational Informix User GroupWe speak Informix
Set Explain Output Query – Displays the executed query and indicateswhether “set optimization” was set to high or low Directives Followed – Lists any directives used forthe query Estimated Cost – An estimated of the amount ofwork for the query. The number does not translateinto time. Only compare to same query not others. Estimated number of rows returned – An estimateof the number of rows returned, number based oninformation from system catalog tablesInternational Informix User GroupWe speak Informix
Set Explain Output Numbered List – The order in which tables areaccessed, followed by the access method (indexor sequential scan) Index Keys – The columns used as filters orindexes Query Statistics – Enabled by onconfigparameter “EXPLAIN STAT”International Informix User GroupWe speak Informix
Examples of Explain Plans The following slides will show tuning of SQLbased on the following scenarios: Functions causing index to not be usedCriteria from views causing sequential scansUsing “in” causes sequential scansUse of DirectivesUse of substrings in queriesUse of functions in queriesUsing a better index (Creation of new index)International Informix User GroupWe speak Informix
Functions cause index to not be usedQUERY:-----SELECT DISTINCT BUSINESS UNIT, VOUCHER ID, INVOICE ID, GROSS AMT,INVOICE DT, VENDOR NAME SHORT, VENDOR ID, NAME1, VOUCHER STYLE,ENTRY STATUS SRHFROM PS VOUCHER SRCH VWWHERE BUSINESS UNIT ‘GH'AND UPPER(INVOICE ID) LIKE UPPER('KURT') '%' ESCAPE '\'ORDER BY INVOICE ID, BUSINESS UNIT, VOUCHER ID DESC FOR READ ONLYEstimated Cost: 55943Estimated # of Rows Returned: 1Temporary Files Required For: Order By1) sysadm.ps vendor: SEQUENTIAL SCAN2) sysadm.ps voucher: INDEX PATHFilters: (sysadm.ps voucher.entry status IN ('P' , 'R' , 'T' )AND UPPER(sysadm.ps voucher.invoice id ) LIKE 'KURT%' ESCAPE '\' )(1) Index Keys: vendor id vendor setid business unit (Serial, fragments: ALL)Lower Index Filter: ((sysadm.ps voucher.vendor id sysadm.ps vendor.vendor id AND sysadm.ps voucher.vendor setid sysadm.ps vendor.setid ) AND sysadm.ps voucher.business unit ‘GH' )NESTED LOOP JOINInternational Informix User GroupWe speak Informix
Resolution: Function causes index to not be usedQUERY:-----SELECT DISTINCT BUSINESS UNIT, VOUCHER ID, INVOICE ID, GROSS AMT,INVOICE DT, VENDOR NAME SHORT, VENDOR ID, NAME1, VOUCHER STYLE,ENTRY STATUS SRHFROM PS VOUCHER SRCH VWWHERE BUSINESS UNIT ‘GH'AND INVOICE ID LIKE 'KURT' '%' ESCAPE '\'ORDER BY INVOICE ID, BUSINESS UNIT, VOUCHER ID DESC FOR READ ONLYEstimated Cost: 35009Estimated # of Rows Returned: 1Temporary Files Required For: Order By1) sysadm.ps voucher: INDEX PATHFilters: sysadm.ps voucher.entry status IN ('P' , 'R' , 'T' )(1) Index Keys: business unit invoice id (Serial, fragments: ALL)Lower Index Filter: (sysadm.ps voucher.business unit ‘GH' AND sysadm.ps voucher.invoice id LIKE 'KURT%' ESCAPE '\' )2) sysadm.ps vendor: INDEX PATH(1) Index Keys: vendor id setid (Serial, fragments: ALL)Lower Index Filter: (sysadm.ps voucher.vendor id sysadm.ps vendor.vendor id AND sysadm.ps voucher.vendor setid sysadm.ps vendor.setid )NESTED LOOP JOINInternational Informix User GroupWe speak Informix
Resolution: Function causes indexto not be used Another way is to add a function which converts a character to all upper case andchange the index to include the use of the function.CREATE FUNCTION upper idx(char up char(20))RETURNING char(20) WITH (not variant);DEFINE char out char(20);LET char out upper(char up);RETURN char out;END FUNCTION;CREATE INDEX upper idx on ps vendor(business unit, (upper idx(invoice id))USING btree;International Informix User GroupWe speak Informix
Criteria used to select from views causes Sequential ScansQUERY:-----SELECT BUSINESS UNIT,INV ITEM ID,CM BOOK,DT TIMESTAMP,SEQ NBR,CM DT TIMESTAMP A,CM SEQ NBR A,CM ORIG TRANS DATE,CONSIGNED FLAG,STORAGE AREA,INV LOT ID,SERIAL ID,CM RECEIPT QTY,CM DEPLETE QTY, CM ONHAND QTYFROM PS CM ONHAND VWWHERE BUSINESS UNIT 'RPRO'AND INV ITEM ID '05-04-CVC-6-KINS'AND CM BOOK 'FIN'AND CONSIGNED FLAG 'N'AND CM ONHAND QTY 0ORDER BY CM ORIG TRANS DATE, CM DT TIMESTAMP A, CM SEQ NBR A FOR READ ONLYEstimated Cost: 8425Estimated # of Rows Returned: 1Temporary Files Required For: Order By Group By1) sysadm.ps cm deplete: SEQUENTIAL SCAN2) sysadm.ps cm receipts: INDEX PATH(1) Index Keys: business unit inv item id cm book dt timestamp seq nbr cm dt timestamp a cm seq nbr a (Serial, fragments: ALL)Lower Index Filter: ((((((sysadm.ps cm receipts.dt timestamp sysadm.ps cm deplete.cm dt timestamp ANDsysadm.ps cm receipts.cm dt timestamp a sysadm.ps cm deplete.cm dt timestamp a ) AND sysadm.ps cm receipts.inv item id sysadm.ps cm deplete.inv item id ) AND sysadm.ps cm receipts.seq nbr sysadm.ps cm deplete.cm seq nbr ) ANDsysadm.ps cm receipts.cm seq nbr a sysadm.ps cm deplete.cm seq nbr a ) AND sysadm.ps cm receipts.business unit sysadm.ps cm deplete.business unit ) AND sysadm.ps cm receipts.cm book sysadm.ps cm deplete.cm book )NESTED LOOP JOINInternational Informix User GroupWe speak Informix
Resolution to Criteria used for view causes Sequential ScansQUERY:SELECT BUSINESS UNIT,INV ITEM ID,CM BOOK,DT TIMESTAMP,SEQ NBR,CM DT TIMESTAMP A,CM SEQ NBR A,CM ORIG TRANS DATE,CONSIGNED FLAG,STORAGE AREA,INV LOT ID,SERIAL ID,CM RECEIPT QTY,CM DEPLETE QTY,CM ONHAND QTYFROM PS CM ONHAND VWWHERE BUSINESS UNIT 'RPRO'AND INV ITEM ID '04X35-X-042'AND CM BOOK 'FIN'AND CONSIGNED FLAG 'N'--AND CM ONHAND QTY 0ORDER BY CM ORIG TRANS DATE, CM DT TIMESTAMP A, CM SEQ NBR A FOR READ ONLYEstimated Cost: 10Estimated # of Rows Returned: 1Temporary Files Required For: Order By Group By1) sysadm.ps cm deplete: INDEX PATH(1) Index Keys: business unit inv item id cm book dt timestamp seq nbr cm dt timestamp cm seq nbr cm dt timestamp a cm seq nbr a (Serial,fragments: ALL)Lower Index Filter: ((sysadm.ps cm deplete.inv item id '04X35-X-042' AND sysadm.ps cm deplete.business unit 'RPRO' ) ANDsysadm.ps cm deplete.cm book 'FIN' )2) sysadm.ps cm receipts: INDEX PATHFilters: sysadm.ps cm receipts.consigned flag 'N'(1) Index Keys: business unit inv item id cm book dt timestamp seq nbr cm dt timestamp a cm seq nbr a (Serial, fragments: ALL)Lower Index Filter: ((((((sysadm.ps cm receipts.inv item id sysadm.ps cm deplete.inv item id AND sysadm.ps cm receipts.dt timestamp sysadm.ps cm deplete.cm dt timestamp ) AND sysadm.ps cm receipts.seq nbr sysadm.ps cm deplete.cm seq nbr ) ANDsysadm.ps cm receipts.cm dt timestamp a sysadm.ps cm deplete.cm dt timestamp a ) AND sysadm.ps cm receipts.cm seq nbr a sysadm.ps cm deplete.cm seq nbr a ) AND sysadm.ps cm receipts.business unit sysadm.ps cm deplete.business unit ) ANDsysadm.ps cm receipts.cm book sysadm.ps cm deplete.cm book )NESTED LOOP JOINInternational Informix User GroupWe speak Informix
View used in QueryCREATE VIEW "sysadm".ps cm onhand vw(business unit,inv item id,cm book,dt timestamp,seq nbr,cm dt timestamp a, .cm onhand qty) ASSELECT x1.business unit ,x1.inv item id ,x1.cm book ,x1.cm dt timestamp, .(x0.qty base - sum(x1.qty base) )FROM "sysadm".ps cm receipts x0 ,"sysadm".ps cm deplete x1WHERE (((((((x0.business unit x1.business unit )AND (x0.inv item id x1.inv item id ) )AND (x0.cm book x1.cm book) )AND (x0.dt timestamp x1.cm dt timestamp ) )AND (x0.seq nbr x1.cm seq nbr ) )AND (x0.cm dt timestamp a x1.cm dt timestamp a) )AND (x0.cm seq nbr a x1.cm seq nbr a ) )GROUP BY x1.business unit ,x1.inv item id ,x1.cm book ,x1.cm dt timestamp ,x1.cm seq nbr,x0.cm dt timestamp a ,x0.cm seq nbr a ,x0.cm orig trans date,x0.consigned flag ,x0.storage area ,x0.inv lot id ,x0.serial id,x0.qty base ;International Informix User GroupWe speak Informix
Using “in” causes Sequential ScansQUERY:----------------SELECT inv3 invnoFROM inv3WHERE 448050 IN (inv3 physcode,inv3 altphys1,inv3 altphys2)Estimated Cost: 157852Estimated # of Rows Returned: 5668801) informix.cus3: SEQUENTIAL SCANFilters: 448050 IN (informix.inv3.inv3 physcode , informix.inv3.inv3 altphys1 , informix.inv3.inv3 altphys2 )Query statistics:----------------Table map :---------------------------Internal nameTable name---------------------------t1typeinv3table rows prod est rows rows scan timeest --------------------scant121566880209665200:01.26 157852International Informix User GroupWe speak Informix
Resolution to Criteria used for Using“in” Causes Sequential Scans First, I created two new indexes create index ixinv3 altphys1 on inv3(inv3 altphys1) create index ixinv3 altphys2 on inv3(inv3 altphys2) Then I changed the query to use “or” instead of “in”International Informix User GroupWe speak Informix
Resolution to Criteria used for Using“in” Causes Sequential ScansQUERY:-----SELECT inv3 invnoFROM inv3WHERE (inv3 physcode 448050or inv3 altphys1 448050or inv3 altphys2 448050)Estimated Cost: 13Estimated # of Rows Returned: 91) informix.inv3: INDEX PATH(1) Index Name: informix.ixinv3 physcodeIndex Keys: inv3 physcode (Serial, fragments: ALL)Lower Index Filter: informix.inv3.inv3 physcode 448050(2) Index Name: informix.ixinv3 altphys2Index Keys: inv3 altphys2 (Serial, fragments: ALL)Lower Index Filter: informix.inv3.inv3 altphys2 448050(3) Index Name: informix.ixinv3 altphys1Index Keys: inv3 altphys1 (Serial, fragments: ALL)Lower Index Filter: informix.inv3.inv3 altphys1 448050International Informix User GroupWe speak Informix
Resolution to Criteria used for Using“in” Causes Sequential ScansQuery statistics:----------------Table map :---------------------------Internal name Table name---------------------------t1inv3type table rows prod est rows rows scan time est -----------------------------------scan t12192100:00.24 13International Informix User GroupWe speak Informix
Use of Directives for QueriesQUERY:-----SELECT D.BUSINESS UNIT, D.VENDOR SETID, E.VENDOR ID, E.NAME1, E.NAME2, VNDR LOCFROM PS PAYMENT TBL A, PS PYMNT VCHR XREF B, PS VOUCHER LINE C,PS VOUCHER D, PS VENDOR E, PS VENDOR LOC FWHERE A.BANK SETID B.BANK SETIDAND A.BANK CD B.BANK CDAND A.BANK ACCT KEY B.BANK ACCT KEYAND A.PYMNT ID B.PYMNT IDAND B.BUSINESS UNIT C.BUSINESS UNITAND B.VOUCHER ID C.VOUCHER IDAND C.BUSINESS UNIT D.BUSINESS UNITAND C.VOUCHER ID D.VOUCHER IDAND E.VENDOR ID D.VENDOR IDAND A.PYMNT STATUS 'P'AND A.PYMNT DT BETWEEN '01-01-2020' AND '12-31-2020'AND D.BUSINESS UNIT IN ('CAT',‘SNCPY')AND E.SETID F.SETIDAND E.VENDOR ID F.VENDOR IDAND C.WTHD CD F.WTHD CDEstimated Cost: 57005Estimated # of Rows Returned: 1International Informix User GroupWe speak Informix
Use of Directives in Queries1) informix.f: INDEX PATHFilters: informix.f.effdt subquery (1) Index Keys: setid vendor id vndr loc effdt (desc) eff status (Serial, fragments: ALL)2) informix.e: INDEX PATH(1) Index Keys: vendor id setid (Serial, fragments: ALL)Lower Index Filter: (informix.e.vendor id informix.f.vendor id AND informix.e.setid informix.f.setid ) NESTED LOOP JOIN3) informix.d: INDEX PATHFilters: informix.d.business unit IN ('CAT' , ‘SNCPY' )(1) Index Keys: vendor id vendor setid entry status (Serial, fragments: ALL)Lower Index Filter: informix.d.vendor id informix.f.vendor id NESTED LOOP JOIN4) informix.c: INDEX PATHFilters: informix.c.wthd cd ! informix.f.wthd cd(1) Index Keys: business unit voucher id (desc) voucher line num (Serial, fragments: ALL)Lower Index Filter: (informix.c.voucher id informix.d.voucher id AND informix.c.business unit informix.d.business unit ) NESTED LOOP JOIN5) informix.b: INDEX PATH(1) Index Keys: business unit voucher id (desc) pymnt id bank cd bank acct key (Serial, fragments: ALL)Lower Index Filter: (informix.b.voucher id informix.c.voucher id AND informix.b.business unit informix.d.business unit )NESTED LOOP JOIN6) informix.a: INDEX PATHFilters: ((informix.a.pymnt dt 01/01/2020 AND informix.a.pymnt status 'P' ) AND informix.a.pymnt dt 12/31/2020 )(1) Index Keys: pymnt id (desc) bank acct key bank cd bank setid (Serial, fragments: ALL)Lower Index Filter: (((informix.a.pymnt id informix.b.pymnt id AND informix.a.bank acct key informix.b.bank acct key ) AND informix.a.bank cd informix.b.bank cd ) AND informix.a.bank setid informix.b.bank setid ) NESTED LOOP JOINInternational Informix User GroupWe speak Informix
QUERY:Use of Directives in QueriesSELECT -- ORDEREDD.BUSINESS UNIT, D.VENDOR SETID, E.VENDOR ID, E.NAME1, E.NAME2, B.VNDR LOCFROM PS PAYMENT TBL A, PS PYMNT VCHR XREF B, PS VOUCHER LINE C,PS VOUCHER D, PS VENDOR E, PS VENDOR LOC FWHERE A.BANK SETID B.BANK SETIDAND A.BANK CD B.BANK CDAND A.BANK ACCT KEY B.BANK ACCT KEYAND A.PYMNT ID B.PYMNT IDAND B.BUSINESS UNIT C.BUSINESS UNITAND B.VOUCHER ID C.VOUCHER IDAND C.BUSINESS UNIT D.BUSINESS UNITAND C.VOUCHER ID D.VOUCHER IDAND E.VENDOR ID D.VENDOR IDAND A.PYMNT STATUS 'P'AND A.PYMNT DT BETWEEN '01-01-2020' AND '12-31-2020'AND D.BUSINESS UNIT IN ('CAT',‘SNCPY')AND E.SETID F.SETIDAND E.VENDOR ID F.VENDOR IDAND C.WTHD CD F.WTHD CDDIRECTIVES FOLLOWED:ORDEREDDIRECTIVES NOT FOLLOWED:Estimated Cost: 70888(Cost of Original Query: 57005)Estimated # of Rows Returned: 1International Informix User GroupWe speak Informix
Use of Directives in Queries1) informix.a: INDEX PATHFilters: informix.a.pymnt status 'P'(1) Index Keys: pymnt dt name1 remit setid currency pymnt (Serial, fragments: ALL)Lower Index Filter: informix.a.pymnt dt 01/01/2020Upper Index Filter: informix.a.pymnt dt 12/31/20202) informix.b: INDEX PATHFilters: informix.b.business unit IN ('CAT' , ‘SNCPY' )(1) Index Keys: bank setid bank cd bank acct key pymnt id (Serial, fragments: ALL)Lower Index Filter: (((informix.a.pymnt id informix.b.pymnt id AND informix.a.bank acct key informix.b.bank acct key ) AND informix.a.bank cd informix.b.bank cd ) AND informix.a.bank setid informix.b.bank setid ) NESTED LOOP JOIN3) informix.c: INDEX PATH(1) Index Keys: business unit voucher id (desc) voucher line num (Serial, fragments: ALL)Lower Index Filter: (informix.b.voucher id informix.c.voucher id AND informix.b.business unit informix.c.business unit ) NESTED LOOP JOIN4) informix.d: INDEX PATH(1) Index Keys: voucher id (desc) business unit invoice id (Serial, fragments: ALL)Lower Index Filter: (informix.b.voucher id informix.d.voucher id AND informix.b.business unit informix.d.business unit ) NESTED LOOP JOIN5) informix.e: INDEX PATH(1) Index Keys: vendor id setid (Serial, fragments: ALL)Lower Index Filter: informix.e.vendor id informix.d.vendor id NESTED LOOP JOIN6) informix.f: INDEX PATHFilters: (informix.c.wthd cd ! informix.f.wthd cd AND informix.f.effdt subquery )(1)
tuning examples Methods to use for Improving SQL Performance. . First you have to identify what SQL statements are the culprits in causing performance issues Use "onstat -g ntt" to identify the last time read/writes occurred Gather slow SQL statements from onstats, Informix HQ and 3 rd party tools like Server Studio.
