Help - Rows accessed in FTS and Explain Plan [message #233126] |
Tue, 24 April 2007 16:50 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orasaket
Messages: 70 Registered: November 2006
|
Member |
|
|
Hi,
I am getting FTS for particular INSERT Statement which is consuming lot of time- 25-30 minutes
However importing old statistics for a table on which FTS is occurring i am able to get a better execution plan and the stament is taking hardly 7-8 minutes
My question is after gathering fresh stats the num_rows for the table FIN_VOU_HDR was 62335626
After Importing stats taken one week back it was 59860353
I have attached both the plan here for reference.
However in both the Execution plans we can see that rows accessed during FTS does not matches above count of num_rows.
Morever in the same Plan we can see that two occurrences of FTS shows different value for rows accessed.
Can anyone make me understand what is making this difference ?
Note : there is no direct path insert on the table FIN_VOU_HDR
Thanks and Regards,
OraSaket
-
Attachment: index.html
(Size: 133.40KB, Downloaded 1750 times)
|
|
|
Re: Help - Rows accessed in FTS and Explain Plan [message #234035 is a reply to message #233126] |
Sun, 29 April 2007 02:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Each of the three parts of the UNION / UNION ALL have joins of 5 or more tables. This makes for a lot of join order and access path possibilities, and the CBO doesn't evaluate all of them under these circumstances.
When you get the sub-optimal plan, it is possible that Oracle is not even considering the optimal plan. Take a look at the link I provided, and add a LEADING or ORDERED hint to your query to cut down the number of possible plans.
Ross Leishman
|
|
|
Re: Help - Rows accessed in FTS and Explain Plan [message #234102 is a reply to message #234035] |
Mon, 30 April 2007 00:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
dbaxchangedba
Messages: 26 Registered: November 2005
|
Junior Member |
|
|
"However in both the Execution plans we can see that rows accessed during FTS does not matches above count of num_rows.
Morever in the same Plan we can see that two occurrences of FTS shows different value for rows accessed.
Can anyone make me understand what is making this difference ?"
Optimizer's information on the number of rows accessed through a Full Table Scan (FTS) need not necessarily match with num_rows from dba_tables or user_tables. A FTS is a FTS where oracle reads all the blocks associated with the table but what the optimizer is showing you is the number of rows that it "probably" would end up using from the table when there is a condition on the table. When there is a condition on a table on which FTS is being done then oracle uses the following calculation to arrive at the num of rows for FTS:
num_of_rows = num_rows from dba_Tables / num_distinct for the column on which there is a WHERE clause.
An Example:
SQL> select num_rows,blocks from user_tables where table_name = 'ABC';
NUM_ROWS BLOCKS
---------- ----------
20000 40
-- There are no indexes on ABC and so all access will be FTS
SQL> select index_name from user_indexes where table_name = 'ABC';
no rows selected
SQL> explain plan set statement_id = 'abc'
2 for
3 select * from abc;
Explained.
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','abc','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 410K| 9 (12)|
| 1 | TABLE ACCESS FULL | ABC | 20000 | 410K| 9 (12)|
-------------------------------------------------------------------------
>> The rows (20000) identified by the optimizer now matches with num_rows <<
SQL> explain plan set statement_id = 'abc'
2 for
3 select * from abc where object_name = 'XXXXX';
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','abc','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 105 | 9 (12)|
|* 1 | TABLE ACCESS FULL | ABC | 5 | 105 | 9 (12)|
-------------------------------------------------------------------------
Now the optimizer thinks it needs only 5 rows even though it is a FTS because it arrives at that values based on this calculation:
1 select round(num_rows/num_distinct) from user_tables x,user_tab_columns y where x.table_name = 'ABC' and x.table_name = y.table_name
2* and column_name = 'OBJECT_NAME'
SQL> /
ROUND(NUM_ROWS/NUM_DISTINCT)
----------------------------
5
In your case, it could be a condition like "fvh.strvoutype = 'PV'" that's showing a different number of rows as being processed by the optimizer.
Good luck......
http://www.dbaxchange.com
|
|
|