compute statistics advice request

Thank you very much for this info and link.  I am printing it out as we speak for reading.

One more question...

You said that my approach is going to make my query run slower, but i did see a performance increase.  Why exactly did this happen?  I want to completely understand.

"rule" hint?

hi

its not needed all the time u analyze the table when u query the table it will be time consuming when there are lots of row and thats not the way to analyze it

u can go for a schedule analyze if u want for the table its better to use dbms_stats package for analyzing

wht u can do if u are feeling the table access is slow just trace that sql and find weather u are getting the proper execution plan

u can trace the process with dbms_system.set_sql_trace_in_session(sid,serial#,true);

u can get the sid ans serial# from v$session and using tkprof utility try to format the trace file;

this is the one way to trace the process..

hints are also welcome in sqls

regards

<< but i did see a performance increase.  Why exactly did this happen?>>
I doubt that you gained performance by just analyzing your table, in your case it would be like placing a comment within your SQL.
Once you have a table analyzed, you can use those statistics stored in the data dictionary to elaborate an execution plan before running your query.
The execution plan will give you enogh details for you to decide what changes need to be made to the SQL, what path to follow, what type of join needs to be forced using hints, what index to be created, etc.
SQL Tuning and the Optimizer is a broad topic that cannot be covered in a forum. That's why a suggested you that link.

Cheers!

One more comment.....

  The hint RULE is kind of a recomendation to the oracle optimizer to always use Indexes....... This may not always help you... If your query is gonna hit less than 5% of the rows in the table then index based retrieval of the rows is good... but when ur query is going to hit more than 5% of the rows in ur table its always better to go for full table scan....

  so if you specify RULE hint in ur query and the rows its going to hit is more than say 5% then its a overhead .Since first it need to read the index after that actual rows in the table....

  so better dont specify hint in ur sql query ... Oracle cost based optimizer will choose the best execution plan for your query...

   but unless you analyze your tables the statistics for the cost based optimizer will not be generated in data dictionary tables.... if you want to knw more abt optimizer modes you can refer to oracle documentation...


thanks & Regards

I'd recommend you pull the query that seems to be benefitting from the analyze from your java.  Execute your analyze statement.  Run the query via sqlplus and check the explain plan.  Run your java as usual to do the inserts and queries.  When it begins to slow down, run the query again from sqlplus and re-check the explain plan.  What is the difference?  Once you understand what is changing, you can postulate a reason and a solution, then test your theory.

Actually, that won't be necessary at this juncture.  I realized that I am missing indexes for key columns that appear in the predicate section of most of my automated SQL statements.  I intend to create and remove some indexes.  i have also been reading up on the CBO.  Very interesting stuff.  I'm glad I was given this link.  

One question (and probably one final question tommorrow)...

Is there a reason why I should not create indexes for all of my columns in a table?  What sort of overhead is involved with maintaining an index?

If you are doing lot of DML operations on table which you have indexes on all the columns ,it will temendously slow down the DML operations......Because every dml operation has to update the index as well as actual rows in the table.....

If your table is read only  i.e you are using the table only for querying you can go for creating indexes on most of the columns.... But you need to understand the fact that indexes will cosume storage space in database........

Thanks for this info.  

One final question...

In the "Introduction to the Optimizer" online document at http://www.cs.umb.edu/cs634/ora9idocs/server.920/a96533/optimops.htm, the following statement appears: "Online transaction processing applications, which consist of short-running SQL statements with high selectivity, are often characterized by the use of index access paths."  However, earlier on in that same paragraph, it says "...index paths should be used for statements that retrieve a small subset of table rows".  The high selectivity mentioned in the first statement (in my question) seems to suggest that a large number of rows will be selected (since high selectivity = selectivty rating close to 1.0 = large # of rows selected).  So, the two statements I have quoted seem to contradict each other.

Can someone shed some light on this for me?

Oh.  Ok.  So, when they speak of high selectivity, they are speaking of a selectivity rating closer to 0, rather than a selectivity rating closer to 1.  Correct?

I see.  I just want to confirm that the selectivty rating of your first example is close to 0, and the selectivity rating of your second example is close to 1...

I don't claim to know what a selectivity rating of 0 or 1 is closer to.  I think of selectivity more in terms of the percentage of rows selected, rather than in the absolute number of rows selected.  For example, in a million-row table, a query that retrieves only 100 rows, I would consider highly selective.  Whether that means it's closer to a selectivity rating of 0 or 1, I don't know.

Thank you all for your help.  I will split the points up as best I can.