-
CPU bound and distinct select tuning (10g)
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|