Performance Tuning :: Low Cost And High Cpu?
Jul 23, 2010
I am facing one performance issue, in which the query cost is very low compare to cpu cost and as a result the cpu always show the high graph.I am also attaching the gv$sql and gv$sql_plan data of this query.
This is the query:
SELECT PTLS.ITEMTYPE , PTLS.ITEMID , PTLS.STAGEID, TS.USERID, SUM(PREVIOUSHOURS) AS PREVIOUSHOURS, MIN(STARTDATE) AS STARTDATE, MAX(STARTDATE) AS ENDDATE FROM PROJECTTIMELOGSSTAGE PTLS, PROJECTTIMESHEETITEM PTSI, TIMESHEET TS WHERE PTLS.PROJECTID = :B2 AND TS.TIMESHEETID = PTSI.TIMESHEETID AND TS.USERID = :B1 AND PTSI.TIMESHEETID = PTLS.TIMESHEETID AND PTSI.ITEMTYPE = PTLS.ITEMTYPE AND PTSI.ITEMID = PTLS.ITEMID AND (PTSI.ISPWFITEM = 'N' OR PTSI.ISPWFITEM IS NULL) AND PTLS.ITEMTYPE NOT IN ('OtherTsk','NewTsk','Loc','Glb') AND (PTLS.ITEMTYPE, PTLS.ITEMID ) IN (SELECT ITEMTYPE, ITEMID FROM PROJECTTIMELOGSSTAGE PTLS1 WHERE PTLS1.PROJECTID = :B2 AND PTLS1.TIMESHEETID = :B3 ) GROUP BY PTLS.ITEMTYPE, PTLS.ITEMID, PTLS.STAGEID, TS.USERID
View 17 Replies
ADVERTISEMENT
Jan 22, 2009
how to reduce the cpu cost for a query at query level.
View 10 Replies
View Related
Aug 4, 2010
I am working on Tuning a Materialized view. I was getting an error saying ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P002, instance hrms-stg-db01:HRSTG1 (1)
ORA-01652: unable to extend temp segment by 16 in tablespace TEMP1.
so i increased the temp size. I am trying out various hints to get it rectified. But i am not sure if i should go for less cost or less bytes.
View 1 Replies
View Related
Mar 21, 2013
I have Following wjich takes some minutes to executes i want to be tune so this query Executes fast.
Query :
SELECT a.CHDR_EXCH_CD ,TMHR_EXCH_TM_CD,'S' Sec_type,
round(SUM (Decode(csdt_Depo_Typ,'I',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-
[Code]...
Explain Plan Result :
Plan
SELECT STATEMENT ALL_ROWS
Cost: 1,669 Bytes: 67 Cardinality: 1
15 HASH GROUP BY
Bytes: 67 Cardinality: 1
14 CONCATENATION
[Code].....
After i see result , no 4 in explain plan result gives TABLE ACCESS FULL . i want to be indexing on that how to do this..
This table MG_COLL_SCP_DTL have index like this
CREATE UNIQUE INDEX CSDT_PK ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_TRAN_SR_NO, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD,
CSDT_STSC_CD, CSDT_CHDR_CLNT_CD, CSDT_CHDR_CLNT_TM_CD)
[Code]....
How to Reduce cost ???
View 4 Replies
View Related
Sep 1, 2010
For an query, cost was 16Lakhs and was taking 30min, I brought down the cost to 1.5lakhs, but still it is taking 30min.
There were many outer joins and same table has been Used(FROM clause) 5 times in the query. I have introduced WITH clause, and brought down the cost.
View 7 Replies
View Related
Mar 27, 2012
Following is the query on TPC-H schema.
explain plan for select
count(*)
from
orders,
lineitem
where
o_orderkey= l_orderkey.
The trace 10053 (as shown below) for this query shows nested loop join with Lineitem as outer table and Orders as inner table. It is effectively join on composite index (pk_lineitem) of Lineitem and unique index(Pk_orderkey) of Orders table. The cost calculation formula as given in the book as "outer table cost + cardinality of outer table * inner table cost " fails here. I am not able to understand this.
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: LINEITEM Alias: LINEITEM
#Rows: 6001215 #Blks: 109048 AvgRowLen: 124.00
Column (#1): L_ORDERKEY(NUMBER)
AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 1 Max: 6000000
[code]....
how the cost has been calculated. This does not follow the traditional nested loop cost formula as mentioned in the book.
View 7 Replies
View Related
Jan 13, 2009
Is there any way to tune the following query using lot of CPU:-select description,time_stamp,user_id from bhi_tracking where description like 'Multilateral:%'The explain plan for this is query is:-
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 178K| 6609K| 129K|
| 1 | TABLE ACCESS FULL| BHI_TRACKING | 178K| 6609K| 129K|
----------------------------------------------------------------
Bhi_tracking is used for reporting purpose and contain millions of records.Generally we keep one year data in this table and delete the remaining.Can I drop the table after taking export and then import it back or can i truncatethe table and then insert the rows into it to enhancethe performance.
View 14 Replies
View Related
Feb 13, 2013
I have two tables with same columns(15 of them), I am trying to find difference between two tables using minus operator and then insert in stage table using below code
Issue is table1 has 50 million records
table2 is empty
so when first time when we execute this v_collection1,v_collection2 collection will have 50 million records in it which will go in memory, I think this is not good, because going in memory will eat memory and resources while sorting and other activities ?
After fetching records in collection we are inserting that in stage table and then COMMIT so i think that wont be good because committing 50 million will generate large amount of redo?
below is snippet of my code
DECLARE
type lst_collection1
IS
TABLE OF table1.col1%type INDEX BY binary_integer;
type lst_collection2
IS
[code].......
View 4 Replies
View Related
Jun 4, 2010
The prod stats has been implemented in development. The stats has been gathered 2 months back on dev while in production the stats has been gathered 2 weeks back.
My question shouldn't the high volume of data causes changes in plan in both the environment? My thinking is that plan can be different as the high volume of data are changing in prod it may lead to a different plan.
View 6 Replies
View Related
Nov 27, 2012
The below query is utilizing more than 17 Gb temp space. But still it is getting failed out due to insufficient temp space. is there any way to rewrite this query to reduce the temp utilization?
SELECT T12.FRGHT_AMT_CURCY_CD,T23.LAST_UPD,T11.PAR_OU_ID,T9.MAIN_PH_NUM,T23.DISCNT_PERCENT,T23.X_ERROR_NUM,T18.ADDR,T14.X_ECO_B_END_1141,
T14.X_ECO_A_END_1141,T9.X_ECO_VALIDATION_FLG,T23.X_ECO_ERR_DESCR,T14.ASSET_NUM,T20.NAME,T23.X_ECO_REASON2,T14.X_ECO_B_END_ID,
T14.ASSET_NUM,T14.X_ECO_B_END_IWPC,T23.X_AE_CON_PH_NUM,T23.SHIP_ADDR_ID,T19.NAME,T23.X_BE_CON_LST_NAME,T23.CREATED_BY,T23.X_ECO_LOCATION,T8.LOC,
T3.MODIFICATION_NUM,T10.INTEGRATION_ID,T23.INTEGRATION_ID,T23.X_MESSAGE,T9.PR_ADDR_ID,T12.ACCNT_ID,T23.X_BEARERNO,T23.X_SUB_STATUS_CD,
[code]....
View 3 Replies
View Related
Apr 13, 2011
In a 3-node RAC setup; one node is showing high CPU utilization around 40~50%. The CPU utilization was less than 20% 10 days back but from 9th oldest day it jumped and consistently shows the double figure. I ran AWR reports on all three nodes and found one node with high CPU utilization and shows below tops events-
EVENT WAITS TIME(S) AVG WAIT(MS) %TOTAL CALL TIME WAIT CLASS
CPU
time 5,802 34.9
RFS
ping 15 5,118 33,671 30.8 Other
Log file sequential
read 234,831 5,036 21 30.3 System I/O
Sql*Net
more data from
client 24,1711,08745 6.5 Network
Db file sequential
read130,939 4533 2.7 User I/O
Findings:-
On AWR report(file attached) for node= sipd207; we can see that "RFS PING" wait event takes 30% of the waits and "log file sequential read" wait event takes 30% of the waits that occurs in database.
1)Are these symptoms of undersized log buffer?
2)I feel Network wait can be reduced by tweaking SDU & TDU values based on MDU.
View 2 Replies
View Related
Jul 31, 2012
We are using the 11g AMM feature and Memory_Target set to 96GB and total RAM on the Server is 128GB Now the top and free shows up only 200MB memory free on the system.
There are 2 process dbw0 and dbw1 which consumes the top memory and this is 30GB per dbw.
Why is the dbw process taking up so much memory when there is not much load on the database.
View 4 Replies
View Related
Feb 4, 2011
How to find the tables in the database on which high DMLs are firing.
View 5 Replies
View Related
Mar 28, 2013
How can I find out the particular oracle session which was consuming high memory in the past?
I can't get the data in v$sessstat
Unable to get the information in AWR
dba_hist_active_session_history do not have field which indicate memory related information
Shall I concetrate on EVENT in dba_hist_active_session_history which continuosly had sort, direct path read
Or
Locate sql_id from dba_hist_sqlstat with high SORTS_DELTA for snapshots belonging to problematic time period and then using the sql_id query dba_hist_active_session_history
which approach I shall take to find out the session which consumed most memory in the past?
View 8 Replies
View Related
Dec 24, 2012
The below query is taking high CPU almost 98% and longer time to execute.
SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
[code]....
View 15 Replies
View Related
Jul 12, 2010
Looking to understand the difference between instance tuning and database tuning.
What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.
However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?
View 1 Replies
View Related
Oct 31, 2011
I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
[code].....
View 39 Replies
View Related
Sep 30, 2010
How the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
View 24 Replies
View Related
Jun 16, 2010
I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..
I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.
Any useful script or query to do this, any interesting oracle system view?
View 2 Replies
View Related
Oct 20, 2010
There is a simple way to increase the performance of a query by reducing the row-size of the table it hits. I used it in the past by dividing the table into smaller parts and querying respective smaller table in each query.
what is this method called ? just forgot the method and can't recall it. what this type of row-reduction optimization is called ?
View 6 Replies
View Related
Jun 16, 2011
How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?
300 Millions in only one table with 500K transactions / day is too much?
Simple database with simple schema.
How many records begin to be too many?
View 2 Replies
View Related
Nov 15, 2010
Testing our 9i to 11g upgrade, we've imported the entire DB into the new machine.We've found that certain procedures are really suffering performance problems. BUT, we've also found, that if we check out a production copy of the procedure from our source code control, and reinstall it, the performance issue goes away. Just alter the procedure and recompiling does NOT work.
The new machine where the 11g database exists is slightly different than the source, but it's not like we have this problem with every procedure. It's only a couple.
any possible reason that we'd have to re-install a procedure to correct a performance problem?
View 13 Replies
View Related
Apr 12, 2013
I need to check the package performance and need to improve the package performance.
1. how to check the package performance(each and every statement in the package)?
2. In the package using the delete statement to delete all records and observed that delete is taking long time to delete all the records in the table(Table records 7000000). This table is like staging table.Daily need to clean the data before inserting the data into it. what can I use instead of Delete.
View 13 Replies
View Related
Aug 9, 2010
Somewhere I read that we should not use hints in Oracle production environments, but we can use hints in the development environment and on achieving the desired execution plan we can adjust the 'statistics' to follow that plan without hints.
Q1. If it is true what statistics do we adjust for influencing the execution plan and how?
For example, I have the following simple query:
select e.empid, e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
emp.empid, emp.deptno and dep.deptno columns have indexes and the tables have the standard structure as found in the basic oracle examples.
If I look at the execution plan of the above query then I see that the driving table is empand the driven table is dept.Also the type of join that is taking place is 'Nested Loop'.
Questions: With respect to the above query,
Q 2. If I want to make dept the driving table and emp the driven table then how can I adjust the statistics to achieve that?
Q 3. If I want to use hash join instead of a nested loop join then then how can I adjust the statistics to achieve that?
I can put the ordered and the use_hash hint to effect this but again I have heard that altering statistics is a more robust way to control an execution plan as compared to hints.
View 6 Replies
View Related
Dec 6, 2011
I have an issue with export(expdp).
When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.
expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk
Do i need any look into any memory parameters for this?
View 1 Replies
View Related
Oct 17, 2011
The following query gets input parameter from the Front End application, which User queries to get Reports.There are many drop down boxes like LOB, FAMILY, BRAND etc., The user may or may not select values from drop down boxes.
If the user select any one or more values ( against each drop down box) it has to fetch all matching values from DB. If the user does'nt select any values it has to fetch all the records, in this case application will send a value 'DEFAULT' (which is not a value in DB ) so that the DB will fetch all the records.
For getting this I wrote a query like below using DECODE, which colleague suggested that will hamper performance.From the below query all the variables V_ are defined in procedure which gets the values selected by user as a comma separated string here V_SELLOB and LOB_DESC is column in DB.
DECODE (V_SELLOB, 'DEFAULT', V_SELLOB, LOB_DESC) IN
OPEN v_refcursor FOR
SELECT /*+ FULL(a) PARALLEL(a, 5) */
*
FROM items a
WHERE a.sku_status = 'A'
[code]...
View 9 Replies
View Related
Sep 3, 2010
what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.
View 10 Replies
View Related
Mar 17, 2012
are the most important performance keys we have to calculate or take in account to preserve or to increase the DB performance in terms of response times, and whatsoever according to performance ?
View 8 Replies
View Related
Mar 27, 2013
I am working on an assignement where client is using Oracle 10g but stuck to using RBO Now the application team, from the GUI available to them build dynamic queries and some of them run very slow.
Neither the code can not be changed to tune the queries nor do we get the exact step in the plan which is an issue (being RBO).For some long running queries the Tuning advisor is not producing any recommendations.
Another hurdle is that all the application users are using same application user id so we can not write a logon trigger to use CBO for some particular queries to see what is happening in the background!
View 11 Replies
View Related
Jul 4, 2012
I want to tuning the next sql sentence. In this sql I want to get the hash_value and sql_text of the sentences that it's causing TX blocks. Is it possible?. This sentence works fine but sometimes It's slow.
SELECT DISTINCT hash_value,
sql_text
FROM gv$sql sq
WHERE hash_value IN (SELECT DISTINCT prev_hash_value
FROM gv$session se
WHERE sid IN (SELECT sid
FROM gv$lock l
WHERE type = 'TX'
AND ctime >= 2000
AND l.inst_id = se.inst_id
AND l.sid = se.sid)
AND sq.inst_id = se.inst_id);
[code]....
View 7 Replies
View Related
