Oninit® Archive
Results for “EXPLAIN_STAT”
Re: sysptprof questoin
isreads is ISAM () reads i.e. read calls to functions in the lower (storage) layer of the database engine. This counts internal function calls, not i/o's. The associated i/o's can be from buffers or storage. As per Madison Pruet - "also a single isam read/write can hit multipl…
Not enough room in ROOT DBspace
Reduce the size of your PHYSFILE. You probably moved it to another dbspace after setting up the first server. Start it smaller, then create the plog dbspace and move it there and resize. j. On 1/4/17 4:09 PM, STEVEN AB wrote: > Hi, > > I'm trying to setup a new server with the …
Re: problems with set explain on
Hi Lin-Chuan, My guess is sqexplain.out file is already existing in your home dir. with informix as owner and group and 660 permissions. As I said it is just a guess. Cheers, Ravi..... ------------------------------------------------------------------------ On 6 Aug 96 , Lin…
Sessions and transactions
Thanks, Dave. The sqexplain output was bizarre and I will spare the family the details. As to your explanation of duplicates: I was beginning to suspect that my issue might have something to do with the fact that one session is often associated with multiple threads. (Try combi…
SQL query cost is very high
Indika: Ignore the "cost" of the query. That is just an internal number used to evaluate different query plans for the current version of the query. You cannot compare the cost between different versions of the query and a better query plan does not necessarily result in a lower …
Index in Temp table
There are no issues using indexes on temp tables in particular - they can improve performance just like any index on any table if they are used correctly. In this case, I would suggest testing the performance of the query below: DELETE FROM optim_cs_csdi cb WHERE cust_numb IN (s…
Informix 11.70: Stored Procedure SQL exceptions
Alexandre, I should also explain that I set up a WHILE loop to read from a select cursor. Thanks, Bob…
ONMODE -Y on the client side
"onmode -Y" turns on SET EXPLAIN for given session. "onmode" isn't distributed with the client software and connects to an instance's shared memory so is strictly server only. Presumably you don't control what the client is doing otherwise the obvious thing to do would be to run…
sqexplain.out
Possibly...could be a number of things... - You ran 3 selects after setting explain on - The sqexplain file will be appended to each time, so may have explain plans from previous executions - Could be subqueries in the SQL - these are broken out from the main query - Could be th…
Issue with statistics
The set explain remain active, and will continue writing to the file until you end the session or run: set explain off; -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI LONG Sent: Thursday, March 15, 2018 8:00 AM To: ids@iiug…
SET EXPLAIN PLAN
Hello, I am using SET EXPLAIN ON before executing any query to see the cost of the query, but it is not generating any output file. Im using data studio editor. I tried SET EXPLAIN TO <'path/filename'>. how to generate set explain plan can you provide the steps please Thank …
Re: sysptprof questoin
No..... ISAMreas are read function calls. Don't assume nothing beyond that.... Even things like BATCHEDREAD_* can change it.... for the same workload On Feb 17, 2017 19:59, "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net> wrote: IDS 1150.FC8 I am debugging a "hot table contenti…
Long lasting dostats
Oops that got away from me. Need to add: This is why dostats prints out those warnings at the beginning if you don't enable parallelism and enough memory. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myvie…
IDS 12.1- Query cost High
Mike, I would love to understand what caused a same query plan to be significant slower in an N+1 version compared to an N version. That's not neither normal or usual. I've seen a corrupted index cause the query to use a different query plan (if run on a secondary server) or ca…
new features about dynamic parameter added in 12.1
Attention, this is not documented. For Informix 12.10, another way you can get the list is to use the cf_flags column on syscfgtab to determine which onconfig parameters are dynamic: SELECT cf_name FROM sysmaster:syscfgtab WHERE bitand(cf_flags,'0x1') = 0 --removes Unsupported p…
Performance issue after migrate to IDS from Online
Questions: 1. After the database was recreated and loaded did you run the recommended suite of Update Statistics commands as described in the Performance Guide manual (or as implemented by my dostats utility) or are you depending on Auto Update Statistics (AUS)? 2. So, you m…
Explain Plan through JDBC
Set explain on Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 > On Sep 19, 2014, at 16:19, "JACOB SHADIX" <jacobshadix@gmail.com> wrote: > > I need a means to collect explain plans using JDBC on client. Is EXPLAIN_SQL > an option here? Are there any better solut…
BTS & Outer Joins
Thanks Art. However, I should have gone with the full query, as it presents a tougher situation: a double Outer Join, with the second join related to the first. When the second outer is included, the query fails with the same indexing error. What is strange is that the explain …
Poorly Optimized SQL
So here's an interesting little nugget that I just realized: The Explain Plan doesn't always print out the "Query Statistics" portion. The plan of tables and joins is printed, but not the stats breakdown. This section prints a long time after the query is started, so if I kill th…
IDS 7.x SET EXPLAIN ON for all query
Hi, how can I set the configuration/directive on the fly to explain all the query that hit the database? I want to trace certain query from a program which we don't have the sql documentation for it. Thanks.…
IDS 12.10 - dbimport very slow
Hi Markus, Is your import logged ? Regards, Henri MOUKOURI Le 25 juin 2013 à 10:45, "MARKUS BRINKBäUMER" <markus.brinkbaeumer@gmail.com> a écrit : > Hello, > > we have a new server (Windows 2012, 8 GB RAM, 4 x 600 GB SAS 15k HDD). On the > server we have installed an IBM Inf…
Execution Plan
As to getting the execution plan, in your Java app, or a small test app, change the query as follows: SELECT {+ EXPLAIN AVOID_EXECUTE} ... -- Produces a query plan and estimated costs and estimated number of rows returned but no statistics about an actual run. SELECT {+ EXP…
Update Stats Issue
11.50.FC8 AIX We have two identical servers (ER pairs), the database/tables are exact mirror images of each other with respect to data, table schema etc. The layout of the instances are the same etc. as are the $ONCONFIGS. But, for one query I get two different optimization pla…
Performance Tuning Discussion
Ok it seems you have a big hardware, what´s your Informix version? What´s your box architecture? For performance tips, there is a good article in Andrew Ford´s blog, that should clarify your thoughts.... http://www.informix-dba.com/p/informix-innovator-c-tuning-basics.html This…
Config Help
Your biggest performance problem is that you are running your server on a VM. IO performance under VMWare is VERY VERY POOR and, as I've said several times already this month, databases live and die on IO performance. Art Art S. Kagel Advanced DataTools (www.advancedatatools.co…
What are best onconfig parameters for a DSS app
Frank.... first question: Is your database installation being made for a production system???? Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional …
Fw: Optimizer
One thing Peter, have you tried running this query under SET OPTIMIZATION LOW? You have 10 tables in there, I'm willing to bet that a significant portion of the runtime is optimizing the query since there are 3,628,800 query plans that have to be examined and have cost calculated…
Fw: Why dbexport places procs before index builds?
This is IDS 11.70.TC4IE on Windows 2008 Enterprise, SP2 . My question has to do with dbexport/dbimport. We know that dbexport is writing out the âbuild indexâ statements *after* the stored procedures and functions. The âupdate statisticsâ lines do not have one with âfor…
Monitoring Queryplan onmode -Y
Hi all I am trying to monitor the optimizer query plan of a session with onmode -Y sid 1. With onstat -g sql I can see that the session changes the queries frequently. So I assume that each new query of the session will generate a query plan. This is what I want to see. Manual…
Physical Log Size
How did you "increase" the size? You can only do that song onparams. You cannot do that just by changing the Onconfig file Art On Jan 25, 2011 12:10 PM, "KENT GRUNDEN" <k.grunden@hoogwegtus.com> wrote: > IDS 11.50.FC4WE Windows Server 2008 64bit > > I keep getting the message wh…
Merge Statement not utilising index
This sounds like you have OPTCOMPIND set to 2. You can try setting it to 0 and bouncing the instance, but you can also use an optimizer directive to force the use of the index. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org)…
PERFOMANCE IN AN INSERT
Ignore the costs in the sqexplain file, especially for inserts except for comparisons between the cost of inserting with and without an additional index. Otherwise these cost values are useless. The REAL numbers you want to know are: - How many inserts per second/minute/hour ca…
Query works fine in IDS 10 but crashes 11.5 engine
Jonathon wrote: I'd post what the optimizer has to say, but sadly it never gets that far. It prints out the last query right before this one (which completes successfully) and then the oninit process hangs and the optimizer information for the query in question never gets printed…
SQL Tunning
Leonardo, What version of IDS are you running? If you are running 11.10 or newer use onmode -ym EXPLAIN_STAT=1 then rerun the query with explain on to see what part of the query is taking the most time. It will give you a better idea on where in the query you need to concentrat…
Random Server Slowdown "Redone"
There are some usual things you could do for now: 1) calc your database ratios, I think there are several NETTYPE, BUFFERS and even BTSCANNER options that could be optimized, you could download Mr. Art script to automatically know these important numbers!!! It´s on the download …
Re: Found a possible bug in IDS 11.x... and a nasty one at that... has anyone suffered this?
> Sorry to top post. > Not very good at posting @ 3:00 in the morning. :-) No worries Ian... We all have been there... ^_^ > > Just out of curiousity, why are you using floats and not the DECIMAL > > data types? Actually the DDLs comes from another team, who defines the tables…
Simple question about log
Hi! I have this record in log file. 15:29:32 Maximum server connections 132 15:29:32 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 1, Plog used 50241, Llog used 32703 15:29:39 Explain file for session 15085 : /root/sqexplain.out 15:30:17 Explain file fo…
Seeing one oninit working hard while others are no
No the parameter is SQLTRACE which appears to be off, at least the config line you posted is commented out: #SQLTRACE level=low,ntraces=1000,size=2,mode=global Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that…
await_MC3 ??? How release a thread?
I think seeing a query plan would help maybe... (set explain). MM…
Insert into an indexed table is really SLOW!
MIKE DUNHAM-WILKIE wrote: > I am running into performance problems inserting into an indexed table, using > Informix 11.50. The following timings for loading into > compressed/uncompressed, indexed/non-indexed tables using dbload/HPL-Deluxe > more illustrate the problem. The numb…
IDS 7.31 Solaris8 - Long Checkpoints
Comments below: Art On Mon, Mar 16, 2009 at 12:20 PM, kate <kate@iiug.org> wrote: > To shorten checkpoints in version 7.31 you should > > 1) increase the checpoint interval to 500sec (you don't want the interval > to > ever force the checkpoint) I STRONGLY disagree. You ONLY …
AIO VP has more load then KAIO
Hmm, it could be you have set explain turned on, or tracing enabled in a SPL? You could try looking at onstat -g ioq aio in conjunction with onstat -g iof to see what files the aio vps are writing too. Jacques…
Question re. sysptprof
my 2 0.01 on this: if you do an insert/delete and may be updates, the indexes will be hit. the btcleaner, rebalancing the tree etc. so using sysptprof is not really bullitproof to kick out indexes i guess. i suggest to grab also the qrys hitting the table (if possible..) and us…
SET EXPLAIN FILE TO
Hello, IDS 11.10 FC2W1 on solaris 10 Could any one pls help me with the exact syntax for SET EXPLAIN FILE TO option. I want the explain plan file to be created at /usr/informix/ with file name as explain.out. Can i create this file on another server? ie create the explain pla…
explain output for update statistics ?
Joerg Rueschenschmidt schrieb: > Hi, > > following document shows an example of an explain output for an update > statistics statement. > > http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html > > > I used following statement in dbaccess…
Informix external directives ?
Dirk Gunsthï¿œvel schrieb: > Hi, > > first you should be able to set the optimizer goal per session using > SET OPTIMIZATION FIRST_ROWS > > secondly how do you see that the optimizer hints (that is what you > mean, right?) are not used? Do you have any helpfull output? > > (I h…
Caching Data to Buffers
Regarding Update Stats - short answer It synchs meta data with your real data, providing the SQL optimizer with the best possible query plan(s). MM…
Identify Optimizer issues
On Jan 2, 10:07 am, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> wrote: > mohitanch...@gmail.com wrote: > > On Jan 2, 9:16 am, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> > > wrote: > > >> mohitanch...@gmail.com wrote: > > >>> Version: IDS 10 > > >> Platform and exact version/…
generic question: update statistics low drop distr
Andreas.KUTSCHE@spar.at wrote: > Hi, > > why should anyone run > update statistics low drop distributions ? > (maybe except after database upgrades - and even there it is not > possible if the database has serveral terabytes) > Even on a large database you could run this table b…
Activate dynamic explain
Hi, The difference to set explain in an application and dynamically is that the output file is created at a different location. If executed by statement, the output is created in the home dir of the connected user. If you set explain with onmode, it is created in the home of the…
4gl/esql syntax
On Wed, 2007-08-08 at 13:35 -0400, sergio kapul wrote: > looking for correct syntax to create directives inside > queries to overwrite optimizer query execution plan, > to be used in 4gl/esql, I got something that works in straight sql, but it's > ignored when I put it inside of …
Informix engin
SET EXPLAIN ON; SELECT .... ; SET EXPLAIN OFF; The query plan will be written to the file sqexplain.out in your home directory on the server host. Art S. Kagel ----- Original Message ----- From: Nandkishor Singare <ids@iiug.org> At: 8/02 7:19:26 Hi, How to get the details of…
Re: IDS Optimizer Query Plan, declaring and naming indexes
Yea, because depending on the data distribution, the optimizer may decide it is easier to scan the table than to use the index. You have update stats high, so you'd think the optimizer would make the correct decision, but if the query is slower, I guess it didn't. You can force t…
Re: IDS Optimizer Query Plan, declaring and naming indexes
Do you have the same data in both tables ? Do both tables have good statistics in sysdistrib ? -----Original Message----- From: NCC1701BOB@gmail.com [mailto:NCC1701BOB@gmail.com] Sent: Friday, June 15, 2007 09:44 AM To: informix-list@iiug.org Subject: IDS Optimizer Query Plan, d…
IDS Optimizer Query Plan, declaring and naming indexes
I'm seeing differences in performance for the same SQL being executed on two slightly different databases. Using Set Explain, the difference is when database1 exectues part of the WHERE, it goes down the Index Path for that particular table and it's fast. On database2, the query …
query execution plan
As Jack Parker mentioned earlier, there may not be any problem, optimizer just choosing query path based on number of rows. If you are seeing performance problem with one plan versus other, may consider using optimizer directive to enforce the correct path. -Sanjit Chakraborty…
Re: onmode -Y
Hi, a) the stack traces not showing more info may be a platform dependent problem. I'm not sure on which platform you are with this system ... ? b) could it be that your system is running out of (the system wide maximum of) concurrent open file descriptors? Or the oni…
update statistics performance issue
If the column b3recapiid is the lead key of an index there is a good chance it will use and index scan and avoid the sequential scan. This can be very slow do to the random I/O generated. You can try setting the environment variable DBUPSPACE which has the following format to th…
Re: slow query
I was also suspicious of the select statement in the select list. Yes, pull that out of the query run the whole query without that clause and look at the results. Floyd Wellershaus wrote: > Ah, but you are so right. > Here it is: > { TABLE "sentrycf".scprofil row size = 909 numb…
Re: index width
You can also set SQEXPLAIN=1 in your env and run dbschema on the table to see the sql statements. FYI Floyd Wellershaus wrote: > I'm reading that B-tree indexes don't perform well on wide columns, specifically those above 32bytes. > > I'm trying to find out the width of all our…
question on sqexplain output
Aahhh. Duh. I guess the key word there is Lower Index Filter. Thanks for pointing that out Art. ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-…
Re: help with complicated query, and joining on views
I am not convinced that there is a general problem with views. I haven't seen problems with view performance over other the similar queries written using temp tables. In fact I had some queries run faster using views than temp tables but it was more a factor of the optimizer choi…
Informix 9 and correlated subqueries
ian.miell@gmail.com wrote: > We recently upgraded to Informix v 9 from 7.31 with one of our > customers, and we are having trouble with some correlated subqueries. > > In dbaccess they appear to run fine, but under some processes they run > slowly. > > We suspect a poor query pla…
Easy INFORMIX questions
I am very new to informix so be kind. - Do stored procs have a procompiled/cached query plan in informix? If you have any links discussing it would be very helpful. - Does informix have anything similar to DTS packages in MS SQL Server for doing optimized nightly loads of datafi…
Performance issue after 732 to 94fc6 upgrade
Hello, Not easy without knowning the structure of you tables or the number of rows. First thing I would look into is the sequential scans. But as the cost is very low, I assume the tables have only a couple of rows, so this should not make any difference. If you say the snippet …
Re: Optimizing a BaaN installation
Based on that look at every session doing more than 10,000 reads. Do onstat -g ses <session-id> and try to catch what sql they are running and which database they are in when they run it. Then do - dbaccess <database name> - Run query set explain on; Run query SELECT --+A…
Shell Variable to set EXPLAIN on
Good afternoon: We use a product on AIX box, which uses Informix IDS 9.4 as database. We would like to enable EXPLAIN on for certain users for the whole session. Is there an Unix shell variable which when set thru .profile, can capture the information about all SQLs that ran fr…
Re: PREPAREd statement optimization
malc_p@btinternet.com wrote: > A query. (4GL 7.20UD8 (both RDS & I4GL), IDS 9.30HC5, HP-UX 11i) > Does anyone know the process whereby the optimizer decides on the best > access path for a statement PREPAREd in 4GL? We've a query here in a > 4GL which is prepared with placemarker…
Re: IDS Next Version - back on topic
Christopher said: > TBP wrote: >> Maybe the Query plan could be local to the session! > > Then you could get the optimizer taking up a significant portion of > processing time. Well, then -- don't use the feature! PDQPRIORITY itself has a number of places where it's inappropriat…
IDS Next Version - back on topic
TBP wrote: > Maybe the Query plan could be local to the session! Then you could get the optimizer taking up a significant portion of processing time. Our application (for example) uses lots and lots of stored procedures for every session. If each session had to recompile and r…
set explain out
Hi all, I am using IDS 9.3 version. I am getting a strange out put in set explain for the select queries. The set explain out shows scanning for all the fragments even though there is no fragmentation defined on the database. Also some of the conditions are not listed in the ou…
Re: purpose of optcompind and optgoal
calyanram@hotmail.com wrote: > Hi > > what is purpose of these two parameters ? > > regards > s.kalyan I'll answer since I was one of the users pressing for something like OPT_GOAL to be implemented back in the 7.24 days. OPT_GOAL tells the optimizer whether to optimize for the…
purpose of optcompind and optgoal
calyanram@hotmail.com wrote: > Hi > > what is purpose of these two parameters ? > > regards > s.kalyan I'll answer since I was one of the users pressing for something like OPT_GOAL to be implemented back in the 7.24 days. OPT_GOAL tells the optimizer whether to optimize for the…
Re: SPL DATETIME bug ????
This performance fall off is only on datetimes, if the where clause uses integers then it's fine. If I drop the datetime select into the exec blade the performance is back to 0.3 Art S. Kagel wrote: > Paul Watson wrote: > > The difference is that effectively the second query/s…
Re: SPL DATETIME bug ????
Paul Watson wrote: The difference is that effectively the second query/spl has replaceable parameters so it can only be partially optimized at SPL compile time. The final optimization is performed at execution time which is likely the extra run time. Fortuneately you are runni…
RE: SPL DATETIME bug ????
check the explain for both query, I believe they are deferent. In the first query the optimizer know the range of date at optimization time, but in the second it do not. In each query he build deferent query plan. You should force the optimizer to work as the first query using di…
set explain analysis
I've recently learned how to use set explain to analyze sql statements. Now I need to know what to do with the information. I was able to greatly improve one process by adding indexes to the tables that were being read sequentially. I figured out what columns to index by looki…
Re: Re: Query Permormance improvement: URGENT
>Nonsense. The order in which the optimiser decides >to do the processing is critical. I Agree, but as long as the optimizer give a plan wich has some _sense_ , which is certain almost all the the time... But in the cases when the optimizer plan is _nonsense_ (statistics et all…
Re: Whatcha' wanta have?????
> - a way to tell in what file one wants output of > SET EXPLAIN ON I like this one, but would suggest a step further (if it makes any sense): Some kind of API to get the explain from the client side. Currently we have to get file system access to the server where the instance…
Re: Explain Plan for queries
Rajib Sarkar wrote: > Actually in 9.30 there's an optimizer directive AVOID_EXECUTE which will > provide the explain plan without executing the query. You can also set it > using SET EXPLAIN ON AVOID_EXECUTE (pls check the syntax though). Ah, bollocks. The documentation doesn't…
Re: Explain Plan for queries
Actually in 9.30 there's an optimizer directive AVOID_EXECUTE which will provide the explain plan without executing the query. You can also set it using SET EXPLAIN ON AVOID_EXECUTE (pls check the syntax though). In 9.40 in addition to the above, you can dynamically turn on expl…
onstat -g iof
Art S. Kagel wrote: > On Tue, 30 Sep 2003 06:52:18 -0400, Vaclav.Boura wrote: > > It means you have a mess of open sessions running with SET EXPLAIN ON; set or > depending whether the feature is available in your version, for which onmode -Y > <sesid> 1 was run. These are open …
set explain on
I am looking for a way to retrieve the "set explain on" results (usually in sqexplain.out) without writing to the file. My sql statements are executed via a cgi script and therefore, the user does not necessarily have an account to write to. Also, I have not been able to execut…
Re: Tetra and Update Statistics
Hi Paul, I ran into the same problem at two clients using the same combination of IDS 7.31 and Tetra CS/3 3.1. After running update statistics high on columns heading an index etc. Tetra became extremely slow especially posting batches and the like. My conclusion was that the …
massive deadlocks after upgrade from 7.3 to 9.4
"Jason" <jasonperrone@pricechopper.com> wrote in message news:XV9Ua.1390$vX1.9146@news.uswest.net... > My OPTCOMPIND is 0. > All tables are row level locking? Update stats done correctly? SET EXPLAIN ON shows all sql is using the correct index and that index is unique?…
OPT_GOAL setting at 4gl level
Hi, I have checked the manuals about where I can set the OPT_GOAL parameter. Below are the IBM docs, but they do not specify if I can set the OPT_GOAL in the 4gl code. Can anyone confirm if I can set this in the 4gl code? If I must do this at the session/user level, I assume this…
Set Explain On
Hi, I have a sql statment which takes a long time to run, and i tried the explain on and have the following output: 1) informix.cut: INDEX PATH Filters: informix.cut.status != 'X' (1) Index Keys: settled_at Lower Index Filter: informix.cut.settled_at >= datetime(2003-01-01 08:…
581 error on Linux system
When I do a "set explain on" using ISQL . I get this error. I have tried various combinations of DBLANG, so I don't think this is the problem. I have not found any install problems. msg/en_us/0333 exists with message files in it. 581 Error loading message file. This SET EXPLA…
Performance Tuning SE-5.0
heyram@my-deja.com wrote: > I am working on Informix SE 5.0 -AIX Platform. I have two tables one > master and child. Both contains more than 2.5 lakh rows. A lakh being 100,000 rows. > Quering these tables takes hell of a day. What does SET EXPLAIN ON tell you about the querie…
SET EXPLAIN from program without modifying code?
Hi Again, I'm running IDS.2000 9.20.UC1 on RH6.2. I want to gather some information via SET EXPLAIN ON while my Java program is running. What I'd like is a utility that I can attach to a running session and say "start monitoring now", let the program run a bit, then turn off mon…
What are DD_HASHSIZE and DD_HASHMAX? How are they tuned?
I am a informix online dynamic workgroup server user .I wanted to know the formula for cost of query ,when we run a set explain on; on a query Regards, Pankaj K…
IFX_DW_OPT - quick explanantion please
Hi all can someone please explain what IFX_DW_OPT affects when set as I a get problems when setting it with some SQL. Cheers Fudd _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com…
performance with very active threads
IO contention could be a problem, but the checkpoints at 20-30 seconds is a definite impact on the OLTP users. You should attack this first. First, the most likely reason for this is that there are a lot of dirty buffers in the buffer cache so there is more work to be done. Th…
How to interpret sqexplain.out
I've got the basics on how to interpret an sqexplain file from the Informix Performance Guide but does anyone know about a more comprehensive resource or advanced guide to understanding the output from SET EXPLAIN ON? Thanks, Ross…
RE: SET EXPLAIN: Error -534
I refer you to the Clown's execellent diagnosis - it's your binaries [cutting] > -SET EXPLAIN does work when I'm user 'informix', though. > Paul Watson WF Software Ltd Tel: +44 1436 674729 Fax: +44 1436 678693 www.wfsoftware.com/informix…
RE: Query Optimization: Wrong execution plan?
>===== Original Message From Xavi Tarafa Mercader <xavier.tarafa@corp.terra.com> ===== >Hello All, > >I have a problem with a query: > >The query is the join of two tables > >select * from tab1, tab2 >where tab1.pk=tab2.fk > >There're indexes on both fields (unique index on tab1.…
Re: Query Optimization: Wrong execution plan?
From: Leonids.Voroncovs@dati.lv > >Xavi Tarafa Mercader wrote: > > > Any suggestion about how to improve the query performance? > >Set OPTCOMPIND parameter ( in onconfig file ) to 0. I'm not sure that will help. The query is going to probably scan both tables anyway, so a nested…
WG: Query Optimization: Wrong execution plan?
Dynamic hash join is a relatively new idee. You can refuse on it, if you set the variable OPTCOMPIND to 0 in you onconfig - file. If it doesn't work, try to use optimizer direktives (only 7.30 and higher). Best regards - Denis > ---------- > Von: Xavi Tarafa Mercader[SMTP:xav…
Re: Query Optimization: Wrong execution plan?
From: Xavi Tarafa Mercader <xavier.tarafa@corp.terra.com> > >I have a problem with a query: Are you sure it's the wrong execution plan? You're going to probably pull back most of the rows in both tables, aren't you? What happens if you run select * from tab1, tab2 where tab1.pk…
Query Optimization: Wrong execution plan?
Hello All, I have a problem with a query: The query is the join of two tables select * from tab1, tab2 where tab1.pk=tab2.fk There're indexes on both fields (unique index on tab1.pk and indexes with duplicates on tab2.fk). The problem is that the query takes too much time. …
Poor Performance with 7.31.uc6
In article <397DE9E1.E3D8921C@bloomberg.net>, blp@bloomberg.net says... >rewritten code. Run each with SET EXPLAIN to see the resulting join code >and >the query path to determine what indexes are missing. (This can also be >turned off >with an environment variable.) SET EXPLA…
Any way to get 'set explain' explanation?
Douglas Wilson wrote: > The query was something like: > > select stuff > from claims h, claim_detail d > where h.claim_id=d.claim_id > and h.member_id=? > and d.service_date between ? and ? Welcome to the wonderful, complex world of query optimization. What you're looking at…
Informix 4GL error code -564
Webmaster wrote: > Hi, > > I am having this error -564 (cannot sort rows) with the system error -5 (I/O > error) repeatedly while openning a cursor. > > I have no idea on what is the I/O error referring? Basically, I have checked > and ensure that the harddisk space should be en…
slow query with 7.31uc2
This is from a subject on 12/1999. Art Kagel wrote: >> Forum: comp.databases.informix >> Thread: Slow query with 7.31UC2 >> Message 6 of 8 Subject: Re: Slow query with 7.31UC2 Date: 12/21/1999 Author: Art S. Kagel <kagel@bloomberg.net> << previous in thread � next in thread…
Web Datablade on Linux
Hi... Does ANYONE have a good set of docs which explain the proper method for installing the Web Datablade on Linux? Thanks in advance, Joe…
Query in Indexes...
>Subject: Query in Indexes... >From: Jojo Morales jmorales@nettaxi.com >Date: 17.03.00 05:32 W. Europe Standard Time >Message-id: <8asdba$822$1@news.xmission.com> > > >Hi Gurus, > > I have a query, is there a safe and tested approach where can i >disable the indexes and enable…
Re: puzzling problem with query
From: Jonathan Leffler <jleffler@earthlink.net> > >Ty O'Kelly wrote: > > > I am something of an Informix newbie and would appreciate any advice > > offered for this rather perplexing problem. I have two queries against > > the same table. The first query runs in a matter of sec…
puzzling problem with query
Ty O'Kelly wrote: > I am something of an Informix newbie and would appreciate any advice > offered for this rather perplexing problem. I have two queries against > the same table. The first query runs in a matter of seconds. The > second query will run for hours until I final…
Re: Slow query with 7.31UC2
In article <385FC9D2.5968225@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >I had a similar problem just yesterday with our PeopleSoft database which we >were doing only UPDATE STATISICS HIGH per PeopleSoft's recommendation. A >query that was running less than a minu…
Re: Performance problem with big table!
Hi Xavier, we have had also the same problem. It is probably one of the many bugs in IDS 7.30-7.31 regarding the false query plans of the optimizer. In our SAP R/3 system it affected the most frequently used transaction. On Friday it worked correctly, the next Monday it did not …
Issue with query path chosen by Informix 7.20 UC2 optimizer ***Help*** :*(
Tim Schaefer wrote: > > Art, > > I am puzzled why nested-loop joins are a preferred solution here > when everything I ever learned about nested loop joins tells me > this is the worst possible solution. Often they are, sometimes they are not. Seldom granted but true. For examp…
Re: Dbschema outputs -284 error in SE 7.24FC6 on Alpha 4.0d
Thanks for the info ... i'm sure its a typo but its SQEXPLAIN ... for the variable. Thanks a million. Lloyd AJ Wilson ************************************************************ Jean-Guy Charron wrote in message ... >Set the environnement variable SQLEXPLAIN=1 >run dbschema >…
ESQL/C and test on NULL
Can someone explain the difference between: - SELECT ... FROM ... WHERE ... mycolumn IS NULL and - SELECT ... FROM ... WHERE .... mycolumn = $hostvar where hostval has been set to NULL appropriately via rsetnull? Pierre…
Informix
John wrote: > > Hi everyone, > > I am new to programming and Informix. What will be most helpful? Where do > I go to see how each table affect each other? Should I do a sql? Welcome to the Informix community. Start by reading the Guide to SQL Tutorial then the Guide to SQL R…
Stored Procedure: Sequential/Index Search
Hi... >When execute, data is read sequentially instead of using index. I have tried >with SET OPTIMIZATION LOW/HIGH/MEDIUM. But still fetching sequentially. >Is there anyway I can force this select stmt to retrieve rows using Index >and not sequentially? If your table is sm…
set explain alternative
Only command "set explain..." has picture access to table ( in line-mode), but you can try graphic utility "onperf". Lucy Bridget Reitsma wrote in message <37A76150.FEC05F80@auckland.ac.nz>... >Hi > >Just a quick question - I want to find out which indexes are being used >to a…
Optimizer question
Hi all, Informix Dynamic Server Version 7.30.UC6 Can someone please tell me if the optimizer takes into account the number of extents for tables when deciding on a query plan? Thanks Steve _______________________________________________________________ Get Free Email and Do …
Online index usage in one table query
In article <37861A36.3B9B6FAB@bloomberg.net>, kagel@bloomberg.net wrote: ..snipped > To the first > question, if your query is prepared with replaceable parameters the > optimizer does not know at prepare time when it is deciding on a query > plan what the actual values will be…
Need help to get execute plan
> I make deamon program with informx ESQL/C > I'm trying to get execute plan, > I use SET EXPLAIN command, but this command's output file sqexplain.out. > So, in the code you used "EXEC SQL set explain on" ? > I want to set output file name "sqexplain.out" every executing SQL >…
Re: Here is a weird one, Not using fragmentation scheme to do fragmentation
Using MOD() to simulate the hash function is a good idea but when you're using the SELECT statement, if you want fragment elimination you cannot use MOD() or any other functions on the fragmented columns. Informix only supports RANGE or EQUALITY expression to eliminate fragment. …
Here is a weird one, Not using fragmentation scheme to do fragmentation elmination
I have a table that looks like this create table mytesting ( fnmae char(30), lastname char (30), i_guest_id int ) fragment by expression (mod(i_guest_id , 14 ) = 0 ) in dbspace13, (mod(i_guest_id , 14 ) = 1 ) in dbspace15, …
Re: Set Explain On in SP with Cursor
Hi, you have to put 'set explain on' in front of the 'create procedure' statement, as the optimizer-plans are evaluated at "compile"-Time. As the underlying tables may change over time, you should run a 'update statistics for procedure ...' statement to get appropriate query-plan…
Set Explain On in SP with Cursor
Hello all Informix lovers, Can anybody help me with this 'set explain' question? I have a cursor SELECT in an Informix stored procedure, and also an UPDATE inside the foreach loop. I would like to execute 'set explain on' so that it outputs the query plan for both the SELECT an…
Re: Arghh! Please Help.
rico@wsx.wsex.com wrote: First, if you post from a browser please manually wrap your lines for those who do not read the newsgroup from a browser that auto-wraps. > Hello All, > > I have upgraded Online 5.0 to IDS 7.3 while also upgrading from a ALR 6xPentiumPro 200 to a Sun E3…
Set Explain in ACE reports
Is it possible to SET EXPLAIN ON in ACE reports? Regards, Jon…
Re: explain plan without actually executing the SQL
I believe the explain plan will be generated upon preparing the statement (e.g.in ESLQ). Regards, bc Jay Walters wrote in message <36D99F9D.2516C1F1@computer.org>... >Is there a way to get the query plan for a query without executing the >query? I can only find set explain on…
set explain on question
How can I get set explain on results for a stored procedure? If I include it at the time the procedure is created, will the results be accurate: set explain on; create procedure dgp_chklock_lookup( (procedure code here) HP-UX 10.20 OnLine 7.23.UC1 Thanks, Dianne…
Performance tuning
Does anyone know of a simple way of getting specific information about jobs that are currently running against Informix? I'm looking for something besides onstat (i.e. querying sysmaster,) that will show me the specific sql statements being executed, and something like an sqexpla…
SET EXPLAIN question
M Ramasubramanian wrote: > > I have an application that links several .ec files, and I need to get the > explain output in sqexplain.out using SET EXPLAIN ON. How should > I place the SET EXPLAIN ON statements : once per file, once per > function, once for the entire executable, …
Re: Way to determine UPDATE STATISTICS
This is a multi-part message in MIME format. --------------551F14D74C30 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Eric_Melillo@agsea.com wrote: > > Get Art Kagels' utilities (from the iiug web site) -- dostats does a good > job of constructing a…
Re: output of set explain ?
I know with version 8.21 of Dynamic Server XPO (which used to be called XPS) ththis feature was added. Here's some info from $INFORMIXDIR/release/SQLSDOC_8.2: The SET EXPLAIN statement has been extended with a new clause: FILE TO. This clause allows users to have better control…
Re: output of set explain ?
Abraham Perl - Application Developer wrote: > > which variable controls where the output of (set explain on) is > being > saved? > > TIA SET EXPLAIN ON will use the current directory in the HPUX platform. To my knowledge, there is no way to control that. John Carlson Informix …
output of set explain ?
which variable controls where the output of (set explain on) is being saved? TIA…
Re: 7.3 Performance with PeopleSoft application
"Thomas J. Girsch" wrote: > > Actually, there is a subtle, but important difference in the 7.3 > recommendations vs. the 7.2 recommendations. It involves composite indexes > like these: > > CREATE INDEX idx1 ON tab(a, b, c, d); > CREATE INDEX idx2 ON tab(a, b, e, f); > > The 7.2…
Re: 7.3 Performance with PeopleSoft application
Actually, there is a subtle, but important difference in the 7.3 recommendations vs. the 7.2 recommendations. It involves composite indexes like these: CREATE INDEX idx1 ON tab(a, b, c, d); CREATE INDEX idx2 ON tab(a, b, e, f); The 7.2x recommendations I have (from the Perform…
Re: 7.3 Performance with PeopleSoft application
Thomas J. Girsch wrote: > > The recommendations for running update statistics have changed somewhat from > 7.2 to 7.3; If you can use sqexplain to see what the optimizer is doing, Thomas, the instructions you posted from the 7.3 release notes are essentially the same as those i…
Re: SQL-trace
You can try setting the environment variable SQLEXPLAIN to "ON", this (may! devpends on your server, I suppose), create a file called sqexplain,out in the current working directory. Don't forget to unset the variable and restart the program after the program starts though. If y…
Re: onstat -g dic question
>Can't find anything in the manuals for onstat -g dic -- except that the >command exists. Does anyone know what the dirty? column means? > Onstat -g dic is used to dump dictionary information. The dictionary is the "in-memory" version of systables/syscolumns/sysindexes/ etc. T…
Re: Distributions
Stefan Weideneder wrote: > > Jay Aymond wrote: [SNIP other stuff] > Do you think it's a good idea to give the optimizer detailled > informations for columns, if the information will not cause > a better query plan ? Don't you believe that it will slow down > those queries that d…
Re: Performance on 7.30
Hi, the query plan on both machines is identical. In both cases the optimizer starts with a ( builds the hash table on a ) and scans c afterwards. How about the temporary dbspaces ? Do they reside on a different disk on both machines ? Although I think it will not make a differ…
IFMXCPUVPPRIORITY
Hi, What does this parameter 'IFMXCPUVPPRIORITY' do ? Sounds as though it sets a priority for the CPU vp's. Could someone explain in detail please. (ODS7.14 on NCR's MP-RAS) TIA Sathish Sadagopan…
Re: PowerBuilder 6 and Informix SE Query Problem
Art S. Kagel wrote: > Rajendra Singh wrote: > > > > I am running PowerBuilder 6 under Windows NT using the native Informix drivers > > (to connect to my database (SE) running under HP-UX 9.07). > > > > I have a simple SELECT statement as follows: > > SELECT a,b,c,d FROM table1 …
Re: Infromix & Data Warehousing Solutions
Dough Metacube Query Optimizer is a tool that as you may Know, is targeted to analyze a specific Multidimensional Model, it actually creates a Matrix for every combination from the Model and based on some methodology will determine what will be the TOP N (You tell it how many) A…
Re: Infromix & Data Warehousing Solutions
Doug Johnson wrote: > > Tim, > > Thanks for the info but we're not on the same page. I'm not talking about > the Informix query optimizer (access plan shown by 'explain plan'). There's > tons of info on www.informix.com on that optimizer. I'm talking about the > new MetaCube Ware…
Re: Infromix & Data Warehousing Solutions
Tim, Thanks for the info but we're not on the same page. I'm not talking about the Informix query optimizer (access plan shown by 'explain plan'). There's tons of info on www.informix.com on that optimizer. I'm talking about the new MetaCube Warehouse Optimizer. My understanding…
Stored Procedure and SET EXPLAIN
I hav a stored procedure and I think that it's slow... I'd like to improve it's performance. But how can I use SET EXPLAIN with stored procedure to see the costs and all that stuff that I can see when using a normal query ? Thanks in advance, Denis Heliszkowski.…
Re: I can't think of the subject name (have not words).
David Kosenko wrote: > "Art S. Kagel" <kagel@bloomberg.net> offerred: > +> > An annoyed Informix user wrote: > +> Do You see index3 on table1(field1, field2)? There isn't sorting > needed. > +WRONG WRONG WRONG! > +> There isn't table reading needed too. index3 contains all > …
Re: I can't think of the subject name (have not words).
"Art S. Kagel" <kagel@bloomberg.net> offerred: +> > An annoyed Informix user wrote: +> Do You see index3 on table1(field1, field2)? There isn't sorting needed. + +WRONG WRONG WRONG! + +> There isn't table reading needed too. index3 contains all information +> queried and in righ…
SET EXPLAIN output file for OWS 7.22 on NT?
Hi people, I'm trying to use the SET EXPLAIN option to work on some queries on my WinNT OWS 7.22 server. I can't find any reference to where the query plan is place. No evidence of an sqexplain.out file, or anything like that. Anyone have any idea what to look for or where th…
OnBar and IBM's Sysback
Are these two compatible? If so, can someone explain to me how to set this up? I am running OnLine 7.23UC1 on AIX 4.2.1…
Re: Update statistics of the informix.xxx tables.
>Is the update statistics scenario of the informix.systables alike tables >of a database the same as for the users tables. ? For the most part the access to these tables use a "fixed plan". Since this information is mainly needed by the optimizer to generate the query plan, we …
Re: Prepare a Statement that uses a temporary table?
Georg Winkens wrote: > > Is there a way in ESQL/C to prepare a Statement that uses a temporary > table (e.g. "Select * from Temp_Table ...") if this temporary table is > not already created at prepare time? No there is not. The purpose of preparing a statement is to save the co…
Re: Questions conerning the Optimizer
In article <34EA1CEF.86D@harrahs.com>, Carlos Bolden <cbolden@harrahs.com> writes >I have a couple of questions: > >I was looking at the sqexplain table ( in sysmaster ) and was wondering, >how does the cost based optimizer use these values to determine the >optimization method t…
Re: Explain w/o execution
Lauri Pietarinen wrote: > > Is it possible to get the explanation of an access path without > executing the query? > > Now the steps to be taken are... > > 1. execute "set explain on" > 2. execute the query > > When operating against a big database, it would be nice to know in > …
Questions conerning the Optimizer
I have a couple of questions: I was looking at the sqexplain table ( in sysmaster ) and was wondering, how does the cost based optimizer use these values to determine the optimization method to be used? ( I was trying to guess upon what methods were being taken, but my guesses …
Re: Optimizer Info
>Are there any ways to know how the optimizer is evaluating all the >possible query plans and how it decides which one it chooses. > >I know about 'set explain' and data distributions and such, but is there >a way to see the process >the optimizer went through. Basically, no. T…
Optimizer Info
I am running 7.1 on Solaris 2.5 (soon to be upgrading to 7.24). Are there any ways to know how the optimizer is evaluating all the possible query plans and how it decides which one it chooses. I know about 'set explain' and data distributions and such, but is there a way to see…
Year 2000 compliance
Informix web site lists that 4gl v6.1 will be the first version of 4gl year 2000 compliant.. Can someone explain to me what that means. Can you not change the settings of DBDATE to MDY4 and enter year 2000+ from your existing applications? Where does informix products pick the de…
Re: Performance - Informix SE 7.22
I've run into similiar problem and found that the problem was with the way the optimizer was looking at the order of the indexes within the index list (systables). EX: select * from table1 where col1 = 1; I have two indexes on the table ione on table1 (co…
Re: fragmentation and sqexplain
Siebe, Sorry if this has been answered already, but I missed the original posting apparently. The 'fragments: 0' vs. 'fragments: 1' only means that in the first query, the data will be found in the first fragment (no. 0) and in number 1 in the second. A quick scan of the data …
Re: query about sysprocplan
davin_harvey@dsti.co.uk wrote: > > Does anyone know what the following statement means=2E =20 > =20 > SQL-Reference P2-35 > =20 > The sysprocplan system catalog table describes the query-execution=20 > plans and dependency lists for data-manipulation s…
Re: DELETE does not use index
>Sorry for my stupid question, but what does UPDATE STATISTICS do in detail? No such thing as a stupid question. UPDATE STATSTICS updates information in the database catalogue, such as systables, sysindexes, sysdistributions. Information from these catalogue tables is read by …
Re: Stored Procedures and querries.
From "Incorporating Stored Procedures and Triggers into Your Informix Databases Training Manual" Version 01-95 Page 6-128: When a stored procedure is created all optimization will be attempted at that time. If the tables cannot be examined at compile time (they may not exist o…
Re: How to select first N rows?
Jacob Salomon wrote: > > Xavi Cheto wrote: > > > > Cosmo Lee <"* N O - S P A M * cosmo"@echonyc.com> escribi� en art�culo > > <347780AD.D83651DE@echonyc.com>... > > > Is there a way to select the first N rows from a table? > > > > > > i.e.: select the first 20 row from a table >…
Re: Running UPDATE STATISTICS on a table being used
sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote: > Since the update statistics was going on parallelly with the query, will the > query use the new plan once the update statistics is over or does it still go > with the old plan ?? The query is just taking for eve…
Re: SYSPROCPLAN locked using Stored Procedures
I wanted to share the following personal e-mail I received from David Berg (dberg@informix.com) with Informix that has helped us clear up this problem. I tend to think that this is not a PB-related issue; although, I could be wrong. I had a similar experience with Version 5.01 b…
SET EXPLAIN and stored procedures
Trying to use SET EXPLAIN inside a stored procedure and the result is an sqexplain.out file that contains one line feed for each SP execution. That's it. A post from April also mentioned this, but Informix tech support claims ignorance, saying there are no known bugs relating t…
Re: Why this error
> Hi, > When i do a Set explain On; and then write a query, i get the message > errno -534, infxmsg 130. > Despite the fact that i have space and permissions to write. > what could be the problem > bye What Informix products do you have installed? You must install your ONLINE a…
Re: SQL Explain from a client?
Jason Harris wrote: > Look in sysmaster in the syssqexplain table. You can get records from > their using your session id. I am not sure if this helps or if the > data will expire before you can get it. Its worth a try though. > On Thu, 16 Oct 1997 22:13:41 -0400, Dirk Fromhein …
Why this error
Hi, When i do a Set explain On; and then write a query, i get the message errno -534, infxmsg 130. Despite the fact that i have space and permissions to write. what could be the problem bye ****************************************************************************** S.Sathish …
SQL Explain from a client?
Is there any way to access an explain from a client? As far as I know when Explain it turned on all the explains are interleaved for all queries in a file on the server. It seems that there is a way in 8.x to set the directory for where this file should be written on a per-sess…
Re: SQL Explain from a client?
Dear Dirk, Look in sysmaster in the syssqexplain table. You can get records from their using your session id. I am not sure if this helps or if the data will expire before you can get it. Its worth a try though. Regards, Jason On Thu, 16 Oct 1997 22:13:41 -0400, Dirk Fromhein…
Re: SQL order of operations question
>What are the order of operations enforced by the Informix SQL >parser with regards to AND/OR in a WHERE clause It depends on if the order makes any logical difference. For instance, the optimizer treats the "and" clauses as equal and will execute them depending on which will…
optimizing - sqexplain
Hi, We are developing an application with New Era 2.20 using Online 7.10 (about to upgrade to 7.23). My goal is to optimize the response times and I'm new in that function. The first idea is to search through the access plan for high cost queries, the problem is that I don't kn…
Re: Record Users' SQL stmts & Their output
gis81566@cis.nctu.edu.tw (Xiong) wrote: : : I want to record users' SQL statements and their output and :store these data in a database. I want to complete the job with triggers. :However, I don't know: : : 1. Which system catalog is used to store users' current SQL statem…
SET EXPLAIN ON
Hello, in SQL and 4GL the SET EXPLAIN ON statement may be used to obtain a measure of the work involved in performing a query. Unfortunately this statement in our implementation is leading to the error code 581, which says that there in an error in loading a message file, which…
Re: Update Statistics Question
> I understand the need for running UPDATE STATISTICS for the tables >in a database. The recalculated data from this command is going to be >placed in the systables, syscolumns, sysindexes and sysdistrib system >catalog tables. After updating statistics for user created t…
Re: First N records in SELECT
Slavik Anosov wrote: > > Does anybody know something in Informix ODS 7.1 that perform functions of > Oracle's ROWNUM pseudo-column? I need to retrieve only first N records in > select. > No, there is no such feature. Assuming that you are using ESQL-C or 4GL you can simply close…
AN INFORMIX WISH LIST - DRAFT - NOT FINAL TILL JULY 18, 1997
INFORMIX WISH LIST 07/01/97 DRAFT :-) --------------------------------------------------------------------------- The following wishes are a collection and summation of request from Informix Users around the globe. They are edited for format…
Re: Set explain utility
In article <5ofjd7$5fv$3@gulfa.kuwait.net>, rferdy@kuwait.net (Rudy Fernandes) wrote: > >>The sqexplain.out file exists immediately after the prepare. >>Please, try it again. > >How can this be? At the PREPARE stage (for any real-life query) >crucial information - values to us…
Re: Set explain utility
Nigel Gall wrote: > > I do not know of any utility that can do this, but since using SET > EXPLAIN implies optimization of queries in a program, which implies > access to the source code of a program, why not create your programs in > a way that allows you to accomplish SET EXPLA…
Re: Set explain utility
Hi Roger, > DEC's old Rdb/VMS product did this and it was real handy. > I'd also like to know about such a utility. > > By the way, could you get the same SET EXPLAIN results > if you wrote an ESQL/C program that does the following? > > - reads a SELECT statement from stdin > - …
RE: Spooling all SQL Statements
Thanks for your reply. set explain on is done at the application level. I want to accomplish the same thing on the server level, independent of setting "set explain on" before the query is executed. -----Original Message----- From: Marco Greco [SMTP:marcog@ctonline.it] Sent: Tu…
Re: SQL Optimizer
Emil Sarkissian wrote: > > Does anyone know what "Estimated Cost" really > means, when the sqexplain.out file is generated > by OnLine 5.1? > > When I add silly conditions like: > > SELECT ps.ln > WHERE ps.sheet_date BETWEEN BeginDate AND EndDate > AND ps.ln = ps.ln -- ??? …
Re: DSA 7.22.UC1/HPUX 10.20
Ian, First, if you think you saw this before, you did; the original post went out on 2/20. This sort of showed back up today, along with a note from "Administrator_at_LLCS-MAXIM @ lendlease.com.au" stating that the message wasn't deliverable. So instead it appar…
Access to Optimizer results
Hi from Finland > We are using version 7.2 on HP-UX 10.10, and would like to know if there is > any way to access to "cost" (from the optimizer) of a query while the query > is executing without having the explain plan written out to a dataset. > > We are trying to develop a que…
Access to Optimizer results
We are using version 7.2 on HP-UX 10.10, and would like to know if there is any way to access to "cost" (from the optimizer) of a query while the query is executing without having the explain plan written out to a dataset. We are trying to develop a quey govenor that can be used…
Re: Set Explain
A tawdry workaround In each user's home directory, create a symbolic link from "sqexplain.out" to /dev/null. That will do it (although there is still a slight increase in overhead because of the explains). Peter Neilson wrote: > > jrichard@mail.ci.aurora.co.us …
Re[2]: Set Explain
jrichard@mail.ci.aurora.co.us wrote: > > We have programmers who are sending code to production with set > explains still in the code. Since we get binaries, we have trying to > figure out a way to stop the set explains from executing and creating > gigantic files. Any ideas??…
Re: Set Explain
My apologies if this has already come up in your mailbox, but my email system crashed, so I dont know if it was sent OK. :David Williams wrote: : :Well I was going to get an sqexplain.out from work and write some C on :my Linux box at ome but plug away.....my idea was to have a…
Re: Set Explain
:David Williams wrote: : :Well I was going to get an sqexplain.out from work and write some C on :my Linux box at ome but plug away.....my idea was to have a version :that stripped out constants from the sql i.e. any 'word' in single :quotes and any numbers. This means two runs…
Re: Set Explain
In article <5dqe1q$rc7@cssun.mathcs.emory.edu>, CSC CIS <spal@watson.den.csci.csc.com> writes >Doug > >The application I had in mind when I suggested this was a standalone = >menu driver which calls executables using the RUN command. Each = >executable calls a common init functio…
RE: Set Explain
Doug The application I had in mind when I suggested this was a standalone = menu driver which calls executables using the RUN command. Each = executable calls a common init function where the SET EXPLAIN ON is = invoked. You are right when you imply that to do this for a reason…
Re: Set Explain
CSC CIS wrote: > > Try creating a function which is called from all the programs at startup = > and which checks the value of an environment variable and turns SET = > EXPLAIN ON. The programmer should turn on the variable during testing, = > and the production environment should…
RE: Set Explain
Try creating a function which is called from all the programs at startup = and which checks the value of an environment variable and turns SET = EXPLAIN ON. The programmer should turn on the variable during testing, = and the production environment should have this turned off. We…
Re: Set Explain
In <32FD75F1.E50@gte.net> "D. Wilson" <dgwilson@gte.net> writes: >jrichard@mail.ci.aurora.co.us wrote: >> >> We have programmers who are sending code to production with set >> explains still in the code. Since we get binaries, we have trying to >> figure out a way to stop the s…
Re: Set Explain
peter@prospect.anprod.csiro.au (Peter Wiley) wrote: :In article <32FD75F1.E50@gte.net> dgwilson@gte.net writes: :>jrichard@mail.ci.aurora.co.us wrote: :>> :>> We have programmers who are sending code to production with set :>> explains still in the code. Since we get binaries, …
Re: Set Explain
In article <32FD75F1.E50@gte.net> dgwilson@gte.net writes: >jrichard@mail.ci.aurora.co.us wrote: >> >> We have programmers who are sending code to production with set >> explains still in the code. Since we get binaries, we have trying to >> figure out a way to stop the set expl…
Re: Set Explain
jrichard@mail.ci.aurora.co.us wrote: > > We have programmers who are sending code to production with set > explains still in the code. Since we get binaries, we have trying to > figure out a way to stop the set explains from executing and creating > gigantic files. Any ideas??…
Set Explain
We have programmers who are sending code to production with set explains still in the code. Since we get binaries, we have trying to figure out a way to stop the set explains from executing and creating gigantic files. Any ideas??? Please post your answers since US Worst has …
RE: Set explain problem
}>From informix-list-owner@rmy.emory.edu Wed Nov 6 14:04:22 1996 }>Received: from rmy.rmy.emory.edu by hobbes.dbintellect.com via ESMTP (940816.SGI.8.6.9/930416.SGI.AUTO) }> for <settler@dbintellect.com> id OAA28254; Wed, 6 Nov 1996 14:04:20 -0700 }>Received: (from ilist@localh…
Set explain problem
HI ALL, We are running 7.13uc3 on unix 10.10. It seems that the only user that can use set explain without a permission error (534) is informix. I contacted Informix Support and get the answer that I always get: UPGRADE! Anyone running into the same problem? Any work-around…
Set explain problem
HI ALL, We are running 7.13uc3 on unix 10.10. It seems that the only user that can use set explain without a permission error (534) is informix. I contacted Informix Support and get the answer that I always get: UPGRADE! Anyone running into the same problem? Any work-around…
Set explain problem
HI ALL, We are running 7.13uc3 on unix 10.10. It seems that the only user that can use set explain without a permission error (534) is informix. I contacted Informix Support and get the answer that I always get: UPGRADE! Anyone running into the same problem? Any work-around…
Set explain problem
HI ALL, We are running 7.13uc3 on unix 10.10. It seems that the only user that can use set explain without a permission error (534) is informix. I contacted Informix Support and get the answer that I always get: UPGRADE! Anyone running into the same problem? Any work-around…
What causes a temp file to be used?
I have a select with a join to another table on the primary key, and an order by of custnum, callnum in the primary table. The set explain shows that a temporary file is created due to this order by, yet I have an index of custnum, callnum on the table. Why would this use a tempo…
SET EXPLAIN ON = ONINIT Error
Help Appreciated. I am using DBACCESS under Informix 7.1. Executing an 8 table join under DBACCESS is fine. As soon as I add SET EXPLAIN ON, Oninit crashes the engine. Is there some environment variable that should be set, identifying the size of the buffer to hold the query …
How to force use of an index.
Online version 5 I've got a very slow select wihch I've analysed using set explain on. I know it would be quicker if I could force it to use a particular index for it's main index path. Any ideas how to force the optimiser to use an index?…
Re: Problem with SET EXPLAIN ON
> I am working on INFORMIX version 5.01. When I try to do SET EXPLAIN ON, > I get the foll. error message - > 581 : Error loading message file. About the quickest way of fixing this is to re-install using the following sequence: Tools Engine Networking HTH. -- Ciao, Billy +…
Problem with SET EXPLAIN ON
Hi, I am working on INFORMIX version 5.01. When I try to do SET EXPLAIN ON, I get the foll. error message - 581 : Error loading message file. According to Error Message Manual : server may be unble to find the file of message templates it uses while preparing explainatory outpu…
Re: problems with set explain on
: On Aug 6, 10:30pm, Lin-Chuan Lee wrote: : > Subject: problems with set explain on : > Platform: SGI Irix 6.2 : > INFORMIX-OnLine Version 7.12.UC3 : > : > When I tried a set explain on as my user id , : > I get following error .. : > 534: Cannot open EXPLAIN output …
Re: problems with set explain on
SailF3 (sailf3@aol.com) wrote: : If you are accessing a remote server, Informix will place your explain : file in your home directory on that server. If you do not have a home : directory, the file will try to go into the root directory. The read/write : permissions are probably …
Re: problems with set explain on
If you are accessing a remote server, Informix will place your explain file in your home directory on that server. If you do not have a home directory, the file will try to go into the root directory. The read/write permissions are probably not allowing your write. Sean Bradley…
Re: problems with set explain on
Ravi Joseph Pinto (ravi@in.DHL.COM) wrote: : Hi Lin-Chuan, : My guess is sqexplain.out file is already existing in your home dir. with : informix as owner and group and 660 permissions. : As I said it is just a guess. No ... no sqexplain.out in current dir or ENTIRE SYSTEM.. …
Re: problems with set explain on
On Aug 6, 10:30pm, Lin-Chuan Lee wrote: > Subject: problems with set explain on > Platform: SGI Irix 6.2 > INFORMIX-OnLine Version 7.12.UC3 > > When I tried a set explain on as my user id , > I get following error .. > 534: Cannot open EXPLAIN output file. > 130: …
SET EXPLAIN Error
I am getting the following two errors when I issue a SET EXPLAIN command: 534: Cannot open EXPLAIN output file. 134: ISAM error: no more locks I am running the command via DB-Access and ISQL. I have tried both online and command line. I have verified that there are available l…
problems with set explain on
Platform: SGI Irix 6.2 INFORMIX-OnLine Version 7.12.UC3 When I tried a set explain on as my user id , I get following error .. 534: Cannot open EXPLAIN output file. 130: ISAM error: no such DBspace I'm working in my home directory which has proper permissions an…
Re: SET EXPLAIN
At 05:17 AM 7/29/96 GMT, you wrote: >Hi >Is there any way of using set explain but not execute the sql >statement. >ORACLE has the explain plan sql statement which only produces the >execution plan(into a table) of the supplied sql statement. > >Informix 7.1.3 UC1 Several people…
Re: help: set explain on
Malcolm Weallans wrote: > saipoh wrote: > > HI, > > > > I am on informix SQL 4.11 and am trying to set explain on. > > I've been getting error msg 581(error loading message file) > > Does anyone know what message file?? > > > I have seen this problem a few times. It normally …
Re: SET EXPLAIN
Paul van Wegen Esq. wrote: > Hi > Is there any way of using set explain but not execute the sql > statement. > ORACLE has the explain plan sql statement which only produces the > execution plan(into a table) of the supplied sql statement. > Informix 7.1.3 UC1 In dbaccess just pr…
Re: SET EXPLAIN
Paul van Wegen Esq. (vanwegen@firststate.com.au) wrote: : Hi : Is there any way of using set explain but not execute the sql : statement. : ORACLE has the explain plan sql statement which only produces the : execution plan(into a table) of the supplied sql statement. Sybase and …
Re: help: set explain on
> > HI, > > I am on informix SQL 4.11 and am trying to set explain on. > > I've been getting error msg 581(error loading message file) > > Does anyone know what message file?? > > Pls help > > > thanks, > saipoh > > > I have seen this problem a few times. It normally comes…
SET EXPLAIN
Hi Is there any way of using set explain but not execute the sql statement. ORACLE has the explain plan sql statement which only produces the execution plan(into a table) of the supplied sql statement. Informix 7.1.3 UC1 Regards pvw --------------------------------------------…
help: set explain on
HI, I am on informix SQL 4.11 and am trying to set explain on. I've been getting error msg 581(error loading message file) Does anyone know what message file?? Pls help thanks, saipoh saipoh@hpmitd27.mal.hp.com…
Another SET EXPLAIN error
When I use SET EXPLAIN ON in isql i get the following error 581: Error Loading mesage file I have checked on permissions on all files etc in /users/informix and they are all 755 We are running on a HP9000/H70 HPUX 9.04 with SE V4.10 UE Any help / info gladly appreciated …
Re: Set Explain Error Message
> jschumac@uns-dv7 (Joel Schumacher) writes: > Justin Farmiloe (justin.farmiloe@bellsouth.co.nz) wrote: > : When I am logged in as informix, I can run the SQL command SET EXPLAIN > : ON fine. When I am logged in as a standard user, and I try it, I get > : the following: > …
Set Explain Error Message
I am hoping someone can help me with this error: When I am logged in as informix, I can run the SQL command SET EXPLAIN ON fine. When I am logged in as a standard user, and I try it, I get the following: > set explain on; > 534: Cannot open EXPLAIN output file. 130: ISAM er…
Errormessage: 581: Error loading message file
I work with Informix-Sql Version 4.10UE2. I have a specific question on the SET EXPLAIN statment. If i run "set explain on",i get the errorcode 581: Error loading message file. Any hint or idea to solve the problem? Henning -- ****************************************************…
Re: SET EXPLAIN QUESTION
When you use a PREPARE the engine optimises the query at PREPARE time only. Subsequent OPEN's do not re-optimise the query - (which is a good thing for performance). Therefore the answer to your question is no. You will only see the queries which have been optimised in the sqex…
Re: SET EXPLAIN QUESTION
-- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com…
Re: SET EXPLAIN QUESTION
Bill Ennis (ennis@ssax.com) wrote: : I have a rather specific question on the SET EXPLAIN output. : Does the output contain EVERY SQL command send to the engine : from the session whether its been previously PREPARE'd or : not? The output from EXPLAIN will output things which r…
SET EXPLAIN QUESTION
Hi, I have a rather specific question on the SET EXPLAIN output. Does the output contain EVERY SQL command send to the engine from the session whether its been previously PREPARE'd or not? Thanks, Bill -- Bill Ennis Voice: 312-474-7516 SSA …
Re: automating tbtape on Informix V5
There is a book from IBM which explains in detail how to do it with raw devices or cooked files (IBM gg24-4335-00 ) . If you have raw devices you need to set your tbonfig TAPEDEV var to point to a file and then back up this file with ADSM or use dd if=/rawdevice of=backfile . …
Re: -534 Cant Open Explain File
root@132, Workstation Administrator wrote: > > I'm scratching my head on this one. > > In an attempt to set explain on in a query, I am returned a -534: > Cannot open EXPLAIN output file error. I'm running the query in my home > directory and have verified that permissions are o…
Re: SET EXPLAIN: Need Help NOW!
When you run set explain from a client the sqexplain.out is created in the home directory of the user you use on the server. Informix requires that the same user will exist on the server. My experience is when both the client and the server are Unix boxes using informix 7.11…
SET EXPLAIN: Need Help NOW!
Can someone please help me. We have several developers all running code in the same directory with the SET EXPLAIN ON option set. The output is going to "sqexplain.out". Because we are all writing to the file, it is VERY difficult to analyze the "sqexplain.out" file. Does any…
Re: Never ending query
In article <46mbv2$fvg@xmission.xmission.com> dirk@xmission.xmission.com (Derrek Poulson) writes: >From: dirk@xmission.xmission.com (Derrek Poulson) >Subject: Re: Never ending query >Date: 25 Oct 1995 15:58:58 -0600 >jparker@hpbs3645.boi.hp.com (Jack Parker) writes: >>In talkin…
Re: Never ending query
jparker@hpbs3645.boi.hp.com (Jack Parker) writes: >In talking with a cow-orker of mine it came to pass that he is having a >similar problem with 7.1 (I don't know HOW similar yet). Apparently when >doing a query against a view which involves a COUNT(*) and a GROUP BY, the >opti…
Never ending query
In talking with a cow-orker of mine it came to pass that he is having a similar problem with 7.1 (I don't know HOW similar yet). Apparently when doing a query against a view which involves a COUNT(*) and a GROUP BY, the optimizer goes out to lunch for 15 minutes EXACTLY of CPU t…
Re: Set Explain with SP's
Jim Gordon (jgordon@us.DHL.COM) wrote: : On Oct 10, 1:29pm, Jon Vemo wrote: : > Subject: Set Explain with SP's : > In attempting to determine how the optimizer is processing queries in : > stored procedures, we've tried turning 'set explain on' within a procedure. : > However, w…
Re: Set Explain with SP's
On Oct 10, 1:29pm, Jon Vemo wrote: > Subject: Set Explain with SP's > In attempting to determine how the optimizer is processing queries in > stored procedures, we've tried turning 'set explain on' within a procedure. > However, when executing the procedure, I get an empty file.…
Re: Set Explain with SP's
CRAIG@CHEMISTRY.CHEM.UTAH.EDU wrote: > Now, with that said, here's a warning. Don't put all of your faith >in the output. I was burned once when I accidentally used a CHAR >variable in a select against an integer column. The set explain said >that it would use my index. Ho…
RE: Set Explain with SP's
> > Jon, > > Because procedures are optimized when they are stored in the database, > that is when you need to set explain on. For example: > > SET EXPLAIN ON; > CREATE PROCEDURE... > > The "SET EXPLAIN ON" must appear outside of your create procedure. Its my impressio…
RE: Set Explain with SP's
Jon, Because procedures are optimized when they are stored in the database, that is when you need to set explain on. For example: SET EXPLAIN ON; CREATE PROCEDURE... The "SET EXPLAIN ON" must appear outside of your create procedure. Now, with that said, here's a …
Set Explain with SP's
In attempting to determine how the optimizer is processing queries in stored procedures, we've tried turning 'set explain on' within a procedure. However, when executing the procedure, I get an empty file. Is there anything special which needs to be done, to get set explain outp…
Re: Help on SET EXPLAIN
>We are using the SET EXPLAIN ON in Informix 4.10 and it is failing at >run time with a #581 error. It can't find the message template to create >the explain file. Does anyone know how to fix thix? I've seen this before. You have installed your products in the *wrong* order a…
Re: Help on SET EXPLAIN
pvar@abs.net (ProVAR) wrote: >We are using the SET EXPLAIN ON in Informix 4.10 and it is failing at >run time with a #581 error. It can't find the message template to create >the explain file. Does anyone know how to fix thix? >Thanks, Rich >drich@pvar.com I've seen this err…
Re: Help on SET EXPLAIN
pvar@abs.net (ProVAR) wrote: >We are using the SET EXPLAIN ON in Informix 4.10 and it is failing at >run time with a #581 error. It can't find the message template to create >the explain file. Does anyone know how to fix thix? > >Thanks, Rich >drich@pvar.com > Improper Informi…
Help on SET EXPLAIN
We are using the SET EXPLAIN ON in Informix 4.10 and it is failing at run time with a #581 error. It can't find the message template to create the explain file. Does anyone know how to fix thix? Thanks, Rich drich@pvar.com…
Re: EXPLAIN result diff. in TEST and REALITY
: In our TEST DB the result of a joined query and SET EXPLAIN is INDEX PATH : for the two tables. On our REAL enviroment this result is for one table : is SEQUENTIAL SCAN, and for the other one is INDEX PATH. Is this normal ?? : The query is awful slow, maybe thatsway ?? Hi, am…
EXPLAIN result diff. in TEST and REALITY
In our TEST DB the result of a joined query and SET EXPLAIN is INDEX PATH for the two tables. On our REAL enviroment this result is for one table is SEQUENTIAL SCAN, and for the other one is INDEX PATH. Is this normal ?? The query is awful slow, maybe thatsway ?? Thanks Pal …
Re: set explain error
johnl@informix.com (Jonathan Leffler) wrote: >You probably installed the 4GL product after you installed the >Engine, and wiped out a critical portion of a error message file. >Try reinstalling the error message files from the engine, >or the whole engines product -- whichever i…
Re: set explain error
You probably installed the 4GL product after you installed the Engine, and wiped out a critical portion of a error message file. Try reinstalling the error message files from the engine, or the whole engines product -- whichever is easier. Installation order -- TEN (Tools, Engin…
Re: set explain error
Santiago Albanese (75030.2743@CompuServe.COM) wrote: : I'm getting an error 581 everytime I try to run set : explain on under dbaccess or 4gl . Then engine is Online 5.0 : sco -unix 4.2 . Does anyone knows what could be the problem ? : Chago I've seen that error (-581, set…
Re: set explain error
In article <3tl6fp$qqc$1@mhafc.production.compuserve.com> 75030.2743@CompuServe.COM "Santiago Albanese" writes: > I'm getting an error 581 everytime I try to run set > explain on under dbaccess or 4gl . Then engine is Online 5.0 > sco -unix 4.2 . Does anyone k…
Re: set explain error
: : I'm getting an error 581 everytime I try to run set : explain on under dbaccess or 4gl . Then engine is Online 5.0 : sco -unix 4.2 . Does anyone knows what could be the problem ? : : Chago : Check Your Order of Installation. This can happen when installation is not in t…
set explain error
I'm getting an error 581 everytime I try to run set explain on under dbaccess or 4gl . Then engine is Online 5.0 sco -unix 4.2 . Does anyone knows what could be the problem ? Chago…
Re: One more question SPL optmiser path
Tom Hogarty <hogarty.pad@sni.de> wrote: > Can anybody tell me how to get the optimiser plan out of my > OnLine stored procedures ? Is it permanently stored with the > SPL or must one fiddle with set explain on ? > > Thanks In Advance The execution plan is created at the time t…
Re: Set explain Comments
> Hi, > > For a couple of days we had a discussion on "update statistics" before > doing a "set explain on". I followed the instruction and seemed to get > better, more meaningful results. I say *seemed* because of my > experiencetoday. > > I was attempting to speed up a report…
SET EXPLAIN COMMENTS
W> Date: Sat, 3 Jun 1995 18:12:34 +0200 W> I was attempting to speed up a report (Fourgen standard), and used "set > explain on" on our test database, getting very encouraging results (*3 in > speed). Made the changes in the report, copied to the live env and timed > the old…
Error -581 with SET EXPLAIN ON
I am attempting to speed up a number of SELECT statements and ran into a problem when I used the SET EXPLAIN ON statement in isql. I get the error -581 which has to do with not being able to load message templates. The docs say to see the person who installed the sodtware. I inst…
Re: SET EXPLAIN question
At 09:32 PM 12/12/94 GMT, Chris Wegener wrote: >-- >When I was checking through the sqexplain.out for an application I am >converting to use Informix I noticed some unexpectedly high Estimated >Cost values on statements where I use ROWID in the WHERE clause. > >For example: > >QU…
Re: SET EXPLAIN question
Chris Wegener writes: -> ->The manual tells me that using ROWID is 'the simplest form of ->nonsequential access' and since the 'rowid value specifies the ->physical location of the row and its page' I thought this would not ->require anything other than a direct read with an Esti…
Re: Optimize Query for slow responce
In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) says: > > 26-Oct-1994 > >I can not understand the Informix Query Optimizer ! >--------------------------------------------------- > > >I have a …
SLOW Queries !!!
In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) writes: |> Now look at the Optimizer Query Plan: |> |> |> QUERY: (LOW) |> ------ |> select * from ks_gdkonto |> where gd_kontonr = "0003573508" |> and gd_prod_kode < 90000 |> |> |> Estimated C…
SET EXPLAIN
Question: I am trying to figure out the total cost of select below. 1) Is it the estimated cost of 6 2) or is it the estimated cost of 6 + subquery cost of 3 ????? QUERY: ------ SELECT miscdata_1[1] FROM lu_lookd_128 WHERE lu_id = (SELECT lu_id …
Re: Query Fails over ISTAR
Yep, That's a problem. Bug # for your reference : 19779, 31739 They are duplicate bugs. Problem is select (current year to second - cs.starttime) .. "current" is refered to current of pca@hpvcp150:current_shift. If you do select current from pca@hpvcp150:current_shift cs; you w…
Query Fails over ISTAR
Why doesn't this work when issued across an Informix Star connection? (I'm using two OnLine 5.02 servers). When I run this on the server with the actual table it works: select (current year to second - cs.starttime), shift from current_shift cs; (Column starttime is datetime y…
ESQL/C: How to abort a time consuming search?
Fellow Informix'ers, Here is an ESQL/C question. Once I start a search, is there a clean way to abort ther search? Even if I kill the process that started the search, the corresponding sqlturbo process does not quit, and am afraid to kill the sqlturbo for the fear of creating i…
Error -581 from online
Hello, We are installing some software on an RS6000 with online 5.01 and when we try to do a set explain from ESQL/C we get the following error -581. I know what the informix error manual says, but why would I get this error? We have run this same program on SCO using SE and it…
Re: query plan
Diane Lombardi in Beach Lab (lombardi@cshl.org) wrote: : I am trying to improve the performance of a particular : query. I am using Sybase sql server v4.9.2. : I noticed that the first time I execute the query : the performance is a little slow. The second time : I execut…
Re: Optimizer's strategy
In article ehu@maicgn.SoftwareEngineering, ar@mai.de (Achim Reiners) writes: > >Hy everybody, > >I've some questions to the optimizer's strategy and to Informix' >plans regarding new versions. My current version is Online 5.01: > >1.) It is NOT possible to only look at the execut…
Optimizer's strategy
Hy everybody, I've some questions to the optimizer's strategy and to Informix' plans regarding new versions. My current version is Online 5.01: 1.) It is NOT possible to only look at the execution plan of a query without really executing it. This feature (with SYBASE: set n…
Re: query plan
In <2thui0INN84u@phage.cshl.org> lombardi@cshl.org (Diane Lombardi in Beach Lab) writes: | I am trying to improve the performance of a particular | query. I am using Sybase sql server v4.9.2. | I noticed that the first time I execute the query | the performance is a little s…
query plan
I am trying to improve the performance of a particular query. I am using Sybase sql server v4.9.2. I noticed that the first time I execute the query the performance is a little slow. The second time I execute the same query, with different search values, the performance i…
Re: SET EXPLAIN ON ERROR
pkb@uk.ac.umds.pru1 (Peter K. Botcherby) writes: >According to the manual the installation order should be >1 - Application Development Tools ISQL, I4GL etc. >2 - Database Servers Online Engine >3 - Additional Products Informix-Net or…
Re: SET EXPLAIN ON ERROR
}Mike Mstowski writes: }-> }-> I'm getting a error '581: Error loading message file' }-> when I try to run set explain on. }-> Can anyone help me out here as to what is going on? }-> I'm on a HP G50 running Informix Online 5.0. }-> }-> Thanks, }-> msm@seas.gwu.edu }-> } }Mi…
Re: SET EXPLAIN ON ERROR
>From: mstowski@seas.gwu.edu (Mike Mstowski) >Subject: SET EXPLAIN ON ERROR >Date: 17 May 1994 20:49:04 GMT >X-Informix-List-Id: <news.6767> > > I'm getting a error '581: Error loading message file' > when I try to run set explain on. > Can anyone help me out here as to what i…
Re: SET EXPLAIN ON
msm@seas.gwu.edu writes: > I'm getting a error '581: Error loading message file' > when I try to run set explain on. > Can anyone help me out here as to what is going on? > I'm on a HP G50 running Informix Online 5.0. > > Thanks, > msm@seas.gwu.edu The order of Informix s…
Re: SET EXPLAIN ON ERROR
Mike Mstowski writes: -> -> I'm getting a error '581: Error loading message file' -> when I try to run set explain on. -> Can anyone help me out here as to what is going on? -> I'm on a HP G50 running Informix Online 5.0. -> -> Thanks, -> msm@seas.gwu.edu -> Mike, This is…
SET EXPLAIN ON ERROR
I'm getting a error '581: Error loading message file' when I try to run set explain on. Can anyone help me out here as to what is going on? I'm on a HP G50 running Informix Online 5.0. Thanks, msm@seas.gwu.edu…
SET EXPLAIN
Does anyone know whether V6.0 implements the capability of determining a query plan *without* actually executing it? This is part of a long term project that I have to do with globally optimising a database's indexes. Paul Hatcher phatcher@cix.compulink.co.uk…
Re: Query Optimiser
John B writes: |Has anyone noticed the 5.X optimizer making bad choices when |choosing a query plan? choosing a query plan? In my case the |optimizer insists in performing a sequential read of a detail table |ignoring the unique index used to join to the master. Performance, …
Re: Set Explain -- Please explain
You may want to check to ensure the last product installed was release 5.00. There's a chance someone installed a 4.10 product last, which would leave you with 4.10 messages (which will not be current with the 5.00 engine). You may want to try installing a 5.00 product and givin…
Query optimizer in 5.01 Informix
Has anyone noticed the 5.X optimizer making bad choices when choosing a query plan? In my case the optimizer insists in performing a sequential read of a detail table ignoring the unique index used to join to the master. Performance, as you can imagine is very, very poor. Infor…
Re: Set Explain -- Please explain
Thanks to all who responded to this request for help. As several people indicated, the problem seems to have been in loading the 4GL tape _after_ the OnLine tape. We reinstalled OnLine and the missing message template files must be back since SET EXPLAIN now works. Thank again t…
Re: Set Explain -- Please explain
}From: daveb@pmafire.inel.gov (Dave Black) }Subject: Set Explain -- Please explain }Date: Fri, 8 Oct 93 14:26:26 GMT }X-Informix-List-Id: <news.4542> } }We could use some help with the SET EXPLAIN command. Normally we would }just call Informix but our boss let our support expire.…
Set Explain -- Please explain
We could use some help with the SET EXPLAIN command. Normally we would just call Informix but our boss let our support expire. Rather than wait several weeks for the contract to be renewed... When trying to use SET EXPLAIN an error -581 is generated. This error tells us that Inf…
SET EXPLAIN ON
I have experienced difficulties with not being able to create the sqexplain.out file in one directory or another. Usually I have had something more important to do than chase down what is really wrong with file and/or directory permissions, but I am sure that is where the confli…
Re: Stored queries under INFORMIX 4.00 or INFORMIX 4.10
In article <1992Nov4.162232.28445@walter.cray.com> n2976@cray.com writes: >Does anybody know if INFORMIX 4.00 or INFORMIX 4.10 supports stored queries? > >If not what about 5.00? What about any INFORMIX product? > We do not support stored queries (I think what you really want he…
Re: Online V4.0 Optimizer
In article <1428@ixos.de> jochen@ixos.de (Jochen Hager) writes: >Hello Informix Specialists! > >Again and again we encounter optimizing unpredictables with >Informix Online V4.0. > >I know this is a very vague question but: > > Does anybody know which strategies are used by the >…
set explain on
I believe that set explain on is new with release 4.0…