Here are replies to a couple Sam, 1) If optimiser mode is chose and any table in a query has stats then CBO will be used. If stats do not exist on any table Oracle has a hard-coded default value which is 100 if I recall a note on here correctly. If stats do exist but they are very old then they will still be used possibly giving a poor path. The best bet is to analyze the tables.
2) 50 user running explain plan at the same time? Some system However the best bet is to select something which you can identify with the trace. Something like 'select this is sams session from dual;' Then do a grep on the udump directory searching for that phrase. 4) Put a trace on that particular session and get the user to run the application and then you can view the trace files afterwards. (see my notes on www.hcresources.co.uk re tracing sessions) Check that performance is bad all the time rather than just at a peak time etc HTH John -----Original Message----- Sent: 11 June 2002 14:24 To: Multiple recipients of list ORACLE-L Hi, I don't have much onhand experience, I thought list is the appropriate place to get information. These are related to Oracle Performance Tuning and Backup & Recovery. I have a system of Oracle 8i under HP-Unix and with ARCHIVELOG on. It has been running and stable since last 2-3 years. This was a brief overview. Que 1: Assuming that I am using Cost based Optimizer (CBO); I have a query which is a join of three tables out of which only one table have been analyzed and in first case rest two were analyzed but long time back and after that I have changed the structure of the table and in second case the other two tables have not been analyzed. Will Oracle use CBO ? I know it will but then what will be the behavior of optimizer ? Will it take the best guess for the other two tables in both the cases and if yes then those best guesses will be depending upon on what ? Que 2: Suppose u have ran EXPLAIN PLAN for a particular query at sqlplus prompt and there are around 50 more users running EXPLAIN PLAN at the same time from the UNIX bos itself. In this case the OSUSER, TERMINAL, PROGRAM will be same (u know it better). Now suppose I want to run TKPROF against the trace file how will I identify which will be my trace file in USER_DUMP_DEST directory ? Que 3: Supposing if I have Oracle Report running on Oracle. I passed 3 parameters first time it took me around 3 seconds to execute and 4 parameters second time its hogging the system taking almost 25 minutes to run. There are no concatenated indexs on the underlying tables. What might be the cause and how would u go about identifying it ? Que 4: A user starts complaining about a particular part of an appln. What will be my first steps to optimize it ? Que 5: Let's say we have a situation where at point "A" I took a complete db backup. Somewhere after point "B" I lost an online redo log file. I recovered the db using the bck taken at point "A" and archived redo log files and started the new incarnation at point "B". I took a complete logical backup at point "B" and taking incremental backup after that. Now again at point "C" I took the complete bck. If I have dropped a tablespace somewhere in between point B and C which are the possible ways to recover it and which one will be the fastest ? (Of course if possible). A---------------B---------------C---------------D If I am not clear on any of the part please let me know and I'll do the needful. Thanks __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
