Home » RDBMS Server » Performance Tuning » How to reduce query Cost (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
How to reduce query Cost [message #687995] Tue, 08 August 2023 00:03 Go to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Is the cost of the below query acceptable for the cardinality shown here
SELECT * FROM MH_OT_INVOICE_HEAD;
What can be done to reduce this cost drastically? Already the primary key constraint is there and another 11 more column are indexed. I also did gathering table statistics but the cost remains the same.

Plan
SELECT STATEMENT  ALL_ROWS Cost: 29,104  Bytes: 568,919,720  Cardinality: 871,240      
    1 TABLE ACCESS FULL TABLE MH_OT_INVOICE_HEAD Cost: 29,104  Bytes: 568,919,720  Cardinality: 871,240
EXEC DBMS_STATS.GATHER_TABLE_STATS('ORION11JNEW', 'MH_OT_INVOICE_HEAD', estimate_percent => 25, cascade => TRUE); 
Re: How to reduce query Cost [message #687996 is a reply to message #687995] Tue, 08 August 2023 00:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
When you are selecting all columns and all rows from a table, the most efficient plan is a full table scan, which is what it is doing.  There really isn't anything that you can do to improve that.  If you were only selecting certain columns or certain rows, based on some criteria, then it might make use of indexes or bind variables or various other things.

In general, when tuning, you should be more concerned about how long it runs using set timing on than guessing how long that might be based on an execution plan.  According to experts like Tom Kyte, the cost is something used internally by Oracle and should be ignored.
Re: How to reduce query Cost [message #687997 is a reply to message #687995] Tue, 08 August 2023 01:12 Go to previous messageGo to next message
John Watson
Messages: 8917
Registered: January 2010
Location: Global Village
Senior Member
"cost" is not something you need to "reduce". It is only a figure used to compare different execution plans and operations within a plan. It has no units, being a combination of both IO and CPU.
However, there are two obvious errors in your code.
First, you are projecting all the columns. Why? If you project only the columns you need, then the optimizer may have other options. For example, you have numerous indexed columns: an index join access path might be possible, instead of the full table scan.
SEcond, you are gathering statistics with estimate_percent=>25. Wrong! You should never use estimate_percent as it disables the 12c histogram types, reverting to the old height balance histograms.

Finally, if an FTS really is the best way to run the statement, you could investigate using direct or indirect read, and whether it is running parallel or serial
Re: How to reduce query Cost [message #687999 is a reply to message #687997] Tue, 08 August 2023 07:28 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Thank you for the above 2 clarifications.
I have 2 tables joined together and on selecting Col-A, Col-B the query is fast as index is used but when i also select Col-C in the same query the index is not used. I can see all these 3 columns are indexed in that tables. If cost is not to be considered, how to exactly know which condition is causing the problem and how to rectify it.
Re: How to reduce query Cost [message #688001 is a reply to message #687999] Tue, 08 August 2023 07:44 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Those 2 tables together have 1 GB of data and am trying to breakdown to see which condition is making the full scan of these tables likewise.
Re: How to reduce query Cost [message #688002 is a reply to message #688001] Tue, 08 August 2023 07:45 Go to previous message
John Watson
Messages: 8917
Registered: January 2010
Location: Global Village
Senior Member
You appear to be describing a completely different problem, for which you have shown neither the query, nor the exec plan, nor the table and index definitions.
Previous Topic: Bind by position
Next Topic: Regarding dba_scheduler_windows
Goto Forum:
  


Current Time: Mon Mar 04 22:13:39 CST 2024