OraFAQ Forum: Performance Tuning » How to reduce query Cost

Home » RDBMS Server » Performance Tuning » How to reduce query Cost (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to reduce query Cost [message #687995]

Tue, 08 August 2023 00:03

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);

Report message to a moderator

Re: How to reduce query Cost [message #687996 is a reply to message #687995]

Tue, 08 August 2023 00:27

Barbara Boehmer
Messages: 9090
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.

Report message to a moderator

Re: How to reduce query Cost [message #687997 is a reply to message #687995]

Tue, 08 August 2023 01:12

John Watson
Messages: 8931
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

Report message to a moderator

Re: How to reduce query Cost [message #687999 is a reply to message #687997]

Tue, 08 August 2023 07:28

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.

Report message to a moderator

Re: How to reduce query Cost [message #688001 is a reply to message #687999]

Tue, 08 August 2023 07:44

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.

Report message to a moderator

Re: How to reduce query Cost [message #688002 is a reply to message #688001]

Tue, 08 August 2023 07:45

John Watson
Messages: 8931
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.

Report message to a moderator

Previous Topic:	Bind by position
Next Topic:	Regarding dba_scheduler_windows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 02:38:07 CDT 2024