RE: stats collection question
- From: Michael Schmitt <mschmitt@xxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Apr 2010 09:51:39 -0500
Hello, It will consistently run for 2 minutes no matter how many times it executes until I update the stats using SYS, which changes the plan. I have flush the shared_pool and buffer_cache as well to see if it makes a difference. There is practically 0 data entry into this system as well, so the data is not really changing from when userA runs stats and SYS runs stats. I compared the num_rows column in dba_tables to confirm. While I have seen some slight differences in the number of rows, my assumption is that it is due to the estimate percentage being set to 30. On a side note, the poor running query has a cost of 10136 while the good one has a cost of 4047. From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Johnson, William L (TEIS) Sent: Thursday, April 29, 2010 9:44 AM To: Michael Schmitt; oracle-l@xxxxxxxxxxxxx Subject: RE: stats collection question Hopefully this is a silly question - but does the query run for 2 minutes if you run the query a second time before updating the stats using the SYS account? Maybe the improved performance is a result of the data residing in the SGA instead of the file system... ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael Schmitt Sent: Thursday, April 29, 2010 10:22 AM To: oracle-l@xxxxxxxxxxxxx Subject: stats collection question Hi All, I have a quick question related to stats collection. This is for a 10203 database on Linux. We have application (that is not designed very well), that drops all its tables, loads data into the tables, collects stats via (exec dbms_stats.gather_schema_stats('<userA>',30,CASCADE=true)), and then compiles a number of views. This process runs as UserA. After the process completes, I can run a query that joins view+view+view and it takes about 2minutes. However, right after that query takes 2 minutes if I run the same stats collection procedure as SYS ( exec dbms_stats.gather_schema_stats('<userA>',30,CASCADE=true)) the query now returns in under 10 seconds (and selects a different plan) until the nightly processing runs again and it goes back to taking 2 minutes again. Therefore, is there a difference in stats collection based on who executes dbms_stats? What am I missing here? The only other thing I can think of is that it is related to the stats being generated prior to UserA compiling their views. I have verified that UserA's command seems to be working by checking the last_analyzed date on dba_tables and dba_indexes. The explain plans are all pretty ugly and 200 lines long, so they have not been much help to me. Any thoughts?