CPU bound and distinct select tuning (10g)

[amasny]

Hi,

I think that my database is CPU bound :


Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 662 71.8
db file sequential read 11,082 66 6 7.2
control file sequential read 51,677 41 1 4.4
db file scattered read 8,133 39 5 4.3
SQL*Net more data to client 113,379 32 0 3.5
-------------------------------------------------------------

================
==================

And such stmt's probably cause this issue :

SQL ordered by CPU

CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
101.49 160 0.63 15.9 102.55 252,160 2426247326
Module: dirdld.exe
SELECT DISTINCT(APPNM) FROM APPLICATION ORDER BY APPNM

===============================================

(SQL ordered by Gets)

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
713,149 33,280 21.4 13.3 39.67 42.70 2402103054
Module: ORACLE.EXE
INSERT INTO "PARAMETER" ("FAMNM","APPNM","PARTID","PARNAMELOC",
"DLDTYPE","VALUE","FLAG","SEQINFO") VALUES (:B8,:B7,:B6,TRIM(:B5
),:B4,TRIM(:B3),:B2,:B1)

489,906 114 4,297.4 9.1 20.16 29.46 2145980626
Module: ORACLE.EXE
DELETE FROM "PARAMETER" "A1" WHERE "A1"."PARTID"=:B1

============================
============================

So is there any possiblility to tune this query ? :
(it is already in keep buffer)

SELECT DISTINCT(APPNM) FROM APPLICATION ORDER BY APPNM

---
(
Plan
SELECT STATEMENT ALL_ROWSCost: 1,008 Bytes: 859,080 Cardinality: 85,908
3 SORT ORDER BY Cost: 1,008 Bytes: 859,080 Cardinality: 85,908
2 HASH UNIQUE Cost: 659 Bytes: 859,080 Cardinality: 85,908
1 TABLE ACCESS FULL TABLE VCESERVICE.APPLICATION Cost: 307 Bytes: 869,150 Cardinality: 86,915
)
---

Best Regards Arkadiusz Masny

[PAVB]

I think that my database is CPU bound

I don't think so...

So is there any possiblility to tune this query ?

Your query hits a single table with no predicate, isn't that a book case study for Full Table Scan?

Do you have an index on APPNM column?
If yes... what happens if you take out the ORDER BY clause?

[amasny]

I don't think so...




Your query hits a single table with no predicate, isn't that a book case study for Full Table Scan?

Do you have an index on APPNM column?
If yes... what happens if you take out the ORDER BY clause?

-----------

PAVB, You are mystery man

If order by is thrown away cost is 1/3 less.
I know that it is case study for FTS, so i don't know if it is possible to make that query to run faster.

Best Regards Arek Masny

[hrishy]

hi

Pavb also asked you if you an index on APPNM column?

Can you also post the explain plan for that query

regards
Hrishy

[amasny]

hi

Pavb also asked you if you an index on APPNM column?

Can you also post the explain plan for that query

regards
Hrishy

-----------------

Index is created on column : application.appnm

--

Plan for query :

SELECT DISTINCT(APPNM) FROM APPLICATION ORDER BY APPNM;

is below:
--
Plan
SELECT STATEMENT ALL_ROWSCost: 1,008 Bytes: 859,080 Cardinality: 85,908
3 SORT ORDER BY Cost: 1,008 Bytes: 859,080 Cardinality: 85,908
2 HASH UNIQUE Cost: 659 Bytes: 859,080 Cardinality: 85,908
1 TABLE ACCESS FULL TABLE VCESERVICE.APPLICATION Cost: 307 Bytes: 869,150 Cardinality: 86,915

-------

and for query :

SELECT DISTINCT(APPNM) FROM APPLICATION

is below :

--

Plan
SELECT STATEMENT ALL_ROWSCost: 656 Bytes: 859,080 Cardinality: 85,908
2 HASH UNIQUE Cost: 656 Bytes: 859,080 Cardinality: 85,908
1 TABLE ACCESS FULL TABLE VCESERVICE.APPLICATION Cost: 307 Bytes: 869,150 Cardinality: 86,915

---------

Thank You.
Best Regards Arek Masny

[PAVB]

Okay... so you have about 87K rows on your table and 86K unique keys on your candidate index on APPNM column; is that correct?

If that's correct the cheaper execution plan you can get is the one you have, FTS on your table.

If your row/key counts are substantially different please gather fresh stats and start again.

[amasny]

Okay... so you have about 87K rows on your table and 86K unique keys on your candidate index on APPNM column; is that correct?

If that's correct the cheaper execution plan you can get is the one you have, FTS on your table.

If your row/key counts are substantially different please gather fresh stats and start again.

------
Thank You PAVB.
Sorry for bothering You but only quick question regarding this.
Someone has adviced me that creating materialized view and setting query rewrite may be good solution (this query is hardcoded in application - i can't change it).
What do you think about this ?

Best Regards Arek Masny

[PAVB]

let me see... you are advised to create a 86K rows MV on top of a 87K table... providing you do not have a real huge row lenght in your table which I have reasons to believe is about 100 bytes per row - that would be bad advice; Materialized Views and Query Rewrite are a great solution for a very different problem.

What's your average row lenght on that table?
How often you insert/delete row on that table?

By the way... if you knew the query was hard-coded and you had no chance in hell to modify it... why where you asking for ways of finetuning it? most finetuning involves touching the query.

[hrishy]

Hi

How long does the query take to execute.

I wouldnt go through the materialized query route based on just the information provided.

I really doubt you have a performance problem

regards
Hrishy