on 05-03-2007 11:31 PM
We have been running queries and loading PLM material for aircraft parts and the system is really slow. I captured the sql statement via ST05 and found the highest time periods were not inserts but rather the highest was the FETCH operation.
The explain statement shows execution plan shows 1 and the proper index. The optimizer is working properly.
What is the FETCH and how can we tune the fetch process to reduce the time to respond.
Thanks
Mikie
Here is the info (hopefully this is what you wanted correct)
Fields of MAKT
Fld name Position Data Type Length Decimals Not null Default
MANDT 1 VARCHAR2 9 X '000'
MATNR 2 VARCHAR2 54 X ' '
SPRAS 3 VARCHAR2 3 X ' '
MAKTX 4 VARCHAR2 120 X ' '
MAKTG 5 VARCHAR2 120 X ' '
Indexes for MAKT
Unique index MAKT~0
MANDT
MATNR
SPRAS
Index MAKT~M
MANDT
SPRAS
MAKTG
Plus checking DB20 shows for MAKT:
Statistics are current (|Changes| < 50 %)
Thanks
Mikie
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is the view which is a join of mara and makt:
MANDT CLNT 3 Client
MTART CHAR 4 Material Type
YMAT_LIN CHAR 6 Line Item Number
MATNR CHAR 18 Material Number
SPRAS LANG 1 Language Key
MAKTX CHAR 40 Material Description (Short Text)
Thank
Mikie
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is my select.....so why doesn't the select use the table access by Index Rowid MAKT?
SELECT
/*+
first_rows
*/
*
FROM
"YMAT_VEN"
WHERE
"MANDT" = :A0 AND "SPRAS" = :A1 AND "YMAT_LINE" LIKE :A2
And then the execution plan is:
SELECT STATEMENT ( Estimated Costs = 1,367,619 , Estimated #Rows = 368,588 )
6 FILTER
Filter Predicates
5 NESTED LOOPS
( Estim. Costs = 1,967,619 , Estim. #Rows = 368,588 )
Estim. CPU-Costs = 48,732,198,765 Estim. IO-Costs = 1,363,641
2 TABLE ACCESS BY INDEX ROWID MARA
( Estim. Costs = 560,888 , Estim. #Rows = 368,588 )
Estim. CPU-Costs = 30,830,111,252 Estim. IO-Costs = 557,877
Filter Predicates
1 INDEX RANGE SCAN MARA~T
( Estim. Costs = 18,126 , Estim. #Rows = 5,408,050 )
Search Columns: 2
Estim. CPU-Costs = 1,187,191,534 Estim. IO-Costs = 18,010
Access Predicates
4 TABLE ACCESS BY INDEX ROWID MAKT
TABLE ACCESS BY INDEX ROWID MAKT
( Estim. Costs = 3 , Estim. #Rows = 1 )
Estim. CPU-Costs = 36,867 Estim. IO-Costs = 3
3 INDEX UNIQUE SCAN MAKT~0
Thanks
Mikie
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
YMAT_VEN seems to be a view created by you.
You should provide also the view definition.
It looks like the CBO is going for index MARA~T.
But the conditions is on SPRAS ( belongs to MAKT ) and YMAT_LINE. This one seems also a non standard SAP field, I cannot say to wich table it belongs.
a "quick and dirty" look make me think that index MAKT~M could be interesting. But it would be necessary to know why is not taken by the CBO. For that it is necessary to look at the statistics of the tables/indexes involved to figure out the alternative cost.
Another option could be ( if YMAT_LINE is a field from MAKT ) to create an index on this fields.
More ideas is to tune the query.
1) you are usin first_rows hint, but you are not indicating the rows you want to be returned. This is done automatically by the DBI interface, so I assume that this is not an ABAP query.
2) you are using oracle 10g ( I assume all paramerters are properly set ), perhaps it is better to use the hint first_rows(n) instead.
Hi,
The explain plan shows you the cost that is calculated by the oracle CBO.
a value of 1 does not mean it is the best. it is a number that is compared with the cost of other options that oracle also calculated.
A bug, wrong statistics, parameters ... could cause the CBO to choose the "wrong" access path.
For that reason it is necessary to analyze the query that is running slow.
Usually I check not only the SQL but also ALL the indexes that the table(s) involved have.
If the query only has one table and no "customer" indexes are involved you can put here the query and the table/indexes statistics ( you can see them if you click on the table on the explain plan. )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
would you share the SQL command it is trying to run?
as for issues with listener, forget about this, it will not explain your problem.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hello
please check the envirnment variables
like $ORACLE_HOME , $ORACLE_SID
And are able to login to oracle
sqlplus " / as sysdba"
Check the Oracle listener
lsnrctl status >> this should display you the status of listener
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Fetch is dataset which created based on the query passed.
If the fetch is taking longer time. check the select statement
do note use select * from xyz.
in select statement give the required column name , and
in filter condition see that is done on the key columns.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.