Tunning


Hi Guys,

We have performance problem in Database. SQL queries taking too much time. I found that some users are created under SYSTEM tablespace. Table Data and Index are in the same drive. I don't know where I have to tune the database either in DB Buffer or Shared memory etc., Can anyone help me please


Cheers

Ram
Posted On: Thursday 15th of November 2012 09:04:13 PM Total Views:  357
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




tunning sqls

Hi all, a have the next query, the execution plan and the statistics. SELECT t1.empresa, t1.cve_producto, t1.cve_transac, t1.unidad_medida, t1.cve_linea, t1.tipo_embarque, t1.cve_cliente, t1.cve_prov, t1.moneda, t1.almacen, t1.ubicacion, t1.estatus, t1.cve_lp FROM dwh.h_calculos_inventa t1, dwh.d_productos t2, dwh.d_transac t3, dwh.d_clientes t4, dwh.d_proveedores t5, dwh.d_lp t7 WHERE t1.empresa=t2.empresa and t1.cve_producto=t2.cve_producto and t1.cve_linea=t2.cve_linea and t1.empresa=t3.empresa and t1.cve_transac=t3.cve_transac and t1.empresa=t4.empresa and t1.cve_cliente=t4.cve_cliente and t1.empresa=t5.empresa and t1.cve_prov=t5.cve_prov and t1.empresa=t7.empresa and t1.cve_lp=t7.cve_lp / 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS (FULL) OF 'D_LP' 0 TABLE ACCESS (BY INDEX ROWID) OF 'H_CALCULOS_INVENTA' 0 AND-EQUAL 0 INDEX (RANGE SCAN) OF 'CAL_EMP_IDX' (NON-UNIQUE) 0 INDEX (RANGE SCAN) OF 'CAL_LP_IDX' (NON-UNIQUE) 0 INDEX (RANGE SCAN) OF 'PROV_EMP_PROV_IDX' (NON-UNIQUE) 0 INDEX (RANGE SCAN) OF 'PROD_EMP_TRAN_IDX' (NON-UNIQUE) 0 TABLE ACCESS (BY INDEX ROWID) OF 'D_PRODUCTOS' 0 INDEX (RANGE SCAN) OF 'PROD_EMP_PROD_IDX' (NON-UNIQUE) 0 TABLE ACCESS (BY INDEX ROWID) OF 'D_CLIENTES' 0 INDEX (RANGE SCAN) OF 'D_CLIENTES_EMPRESA_IND' (NON-UNIQUE) Statistics ---------------------------------------------------------- 88 recursive calls 4 db block gets 42073358 consistent gets 1 physical reads 0 redo size 480 bytes sent via SQL*Net to client 242 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 12 sorts (memory) 0 sorts (disk) 0 rows processed this query take 11 minuts about to tun, how may i tune it
VIEWS ON THIS POST

169

Posted on:

Thursday 1st November 2012
View Replies!

SQL tunning advisor in 10g

I am testing the automatic SQL tunning STEP1- i had created two tables dept1,emp1 without referential integrity without indexes STEP2- DECLARE tuning_task VARCHAR2(30); sqltext varchar2(1000); BEGIN sqltext := 'select dname,count(ename) from emp1,dept1 where emp1.deptno=dept1.deptno;'; tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => sqltext, user_name => 'SYSTEM', scope => 'COMPREHENSIVE', time_limit => 30, task_name => 'my_tuning_task', description => 'Tuning effort for counting customer orders'); END; STEP3- BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_tuning_task'); END; STEP4- SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_tuning_task') FROM DUAL DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK') ---------------------------------------------------------------------------- GENERAL INFORMATION SECTION ---------------------------------------------------- I didn't find any recommendations which i was expected to have can anybody help me in this regard thanx in advance
VIEWS ON THIS POST

121

Posted on:

Thursday 1st November 2012
View Replies!

Oracle tunning tools for Linux?

Hii, Where can I find tunning and instance monitoring tools that run on Linux Thanx
VIEWS ON THIS POST

108

Posted on:

Friday 2nd November 2012
View Replies!

tunning procedure

I am new to this how would you tune your database without using enterprise manager and what exactly would you be looking for when you tune the database also how would you disk space useage in Oracle on solaris
VIEWS ON THIS POST

308

Posted on:

Friday 2nd November 2012
View Replies!

SQL tunning advise

anyone has any suggestion on the below query: select * from (select distinct ps.ctr_no ,NVL(table_dec,'XXXX') DEPT_ID ,NVL(b.ba_no,'XXXX') ba_no ,NVL(actpay_cd,'') actpay_cd ,NVL(actrec_cd,'') actrec_cd ,NVL(affil_cd,'') affil_cd ,NVL(ap_adr_seq,'') ap_adr_seq ,NVL(ar_adr_seq,'') ar_adr_seq ,ps.net_payable ,ps.net_receivable ,ps.net_adjustment ,ps.ref_invc_no ,c.clsf ,max(decode(naid.ba_use, 'W','W','C')) ba_use from party_settle ps, paystatn p, codetabs ct, ba_hdr b, contract c, naid where ps.ps_no = 'LAM' and ps.busn_dt = TO_DATE('01-JUL-2003','DD-MON-YYYY') and p.ps_no = 'LAM' and p.busn_dt = TO_DATE('01-JUL-2003','DD-MON-YYYY') and ps.ctr_no = p.mstr_ctr_no and c.ps_no = 'LAM' and ps.ctr_no = c.ctr_no and c.edt_to >= TO_DATE('01-JUL-2003','DD-MON-YYYY') and c.edt_from= TO_DATE('01-JUL-2003','DD-MON-YYYY') and c.edt_from
VIEWS ON THIS POST

146

Posted on:

Friday 2nd November 2012
View Replies!

tunning

could someone please give me some input to tune the below query\t SELECT DISTINCT ARRG_KEY, GAS.st_code ST_CODE, GAC.cnty_code CNTY_CODE, LPAD(LOC1, 3, '0') TWP, LPAD(LOC2, 3, '0') RNG, LPAD(LOC3, 2, '0') SEC, LOC4, SUBSTR(LOC4, 1, INSTR(LOC4, '/') - 1) QTR1, SUBSTR(LOC4, INSTR(LOC4, '/') + 2, INSTR(LOC4, '/', 1, 2) - INSTR(LOC4, '/') - 2) QTR2, SUBSTR(LOC4, INSTR(LOC4, '/', 1, 2) + 2, INSTR(LOC4, '/', 1, 3) - INSTR(LOC4, '/', 1, 2) - 2) QTR3, SUBSTR(LOC4, INSTR(LOC4, '/', 1, 3) + 2, INSTR(LOC4, '/', 1, 4) - INSTR(LOC4, '/', 1, 3) - 2) QTR4 FROM I_ALL_AGREEMENTS AA, DOE.TRACT_MASTER_1 TM, DOE.LAND_CONTRACT_MASTER_7 XRF, (SELECT ID, MIN(COUNTY_REF) COUNTY_REF FROM DOE.LAND_CONTRACT_MASTER_9 GROUP BY ID) LCM, AREA_COUNTIES GAC, AREA_STATES GAS, DOE.COUNTYMASTER CM WHERE AA.agmt_num = XRF.id AND AA.subs_num = '000' AND TM.id = XRF.tract_ref AND AA.AGMT_NUM = LCM.id AND SUBSTR(LCM.county_ref, 1, INSTR(LCM.county_ref, '*') - 1) = GAS.st_pstl_abbr --(+) AND GAS.st_code = GAC.st_code AND LCM.county_ref = CM.id AND DESCRIPTION = GAC.cnty_name --(+) AND TM.id IN (SELECT ID FROM DOE.TRACT_MASTER_0 WHERE MAP_TYPE_CODE = 'TR') ORDER BY ARRG_KEY, ST_CODE, CNTY_CODE, TWP, RNG, SEC, QTR1, QTR2, QTR3; ------------------------------------------------------------ Statement Id=4203110 Type= Cost=2.64039087471493E-308 TimeStamp=02-06-04::09::34:49 (1) SELECT STATEMENT CHOOSE Est. Rows: 16,673 Cost: 306,700 (21) SORT UNIQUE Est. Rows: 16,673 Cost: 204,897 (20) FILTER (19) SORT GROUP BY Est. Rows: 16,673 Cost: 204,897 (18) HASH JOIN Est. Rows: 1,283,767 Cost: 1,292 (2) TABLE ACCESS FULL DOE.COUNTYMASTER [Analyzed] (2) Blocks: 5 Est. Rows: 3,187 of 3,187 Cost: 2 Tablespace: USR_DATA_01 (17) HASH JOIN Est. Rows: 867,127 Cost: 687 (3) TABLE ACCESS FULL POPE.AREA_COUNTIES [Analyzed] (3) Blocks: 15 Est. Rows: 3,602 of 3,602 Cost: 4 Tablespace: data (16) MERGE JOIN CARTESIAN Est. Rows: 18,777 Cost: 666 (13) HASH JOIN Est. Rows: 244 Cost: 178 (4) TABLE ACCESS FULL DOE.TRACT_MASTER_0 [Analyzed] (4) Blocks: 15 Est. Rows: 1,967 of 11,803 Cost: 4 Tablespace: USR_DATA_01 (12) HASH JOIN Est. Rows: 1,431 Cost: 173 (10) HASH JOIN Est. Rows: 551 Cost: 156 (8) HASH JOIN Est. Rows: 425 Cost: 153 (5) TABLE ACCESS FULL DOE.LAND_CONTRACT_MASTER_9 [Analyzed] (5) Blocks: 1 Est. Rows: 417 of 417 Cost: 2 Tablespace: USR_DATA_01 (7) TABLE ACCESS BY INDEX ROWID INCOMING.I_ALL_AGREEMENTS [Analyzed] (7) Blocks: 1,048 Est. Rows: 1,001 of 66,060 Cost: 150 Tablespace: mydata (6) NON-UNIQUE INDEX RANGE SCAN INCOMING.AA_SUBS_NUM [Analyzed] Est. Rows: 4 Cost: 2 (9) TABLE ACCESS FULL DOE.LAND_CONTRACT_MASTER_7 [Analyzed] (9) Blocks: 3 Est. Rows: 1,272 of 1,272 Cost: 2 Tablespace: USR_DATA_01 (11) TABLE ACCESS FULL DOE.TRACT_MASTER_1 [Analyzed] (11) Blocks: 73 Est. Rows: 29,986 of 29,986 Cost: 13 Tablespace: USR_DATA_01 (15) BUFFER SORT Est. Rows: 77 Cost: 662 (14) TABLE ACCESS FULL POPE.AREA_STATES [Analyzed] (14) Blocks: 1 Est. Rows: 77 of 77 Cost: 2 Tablespace: data_ts
VIEWS ON THIS POST

183

Posted on:

Friday 2nd November 2012
View Replies!

tunning

Hi , I have view (PS_CATLG_SRCH_RSLT) created based on two tables(ps_CRSE_CATALOG and PS_CCC_CATLG_RSLT) Code: Indexes in PS_CCC_CATLG_RSLT ------------------------------- CREATE UNIQUE INDEX SYSADM.PS_CCC_CATLG_RSLT ON "SYSADM".PS_CCC_CATLG_RSLT(CRSE_ID, EFFDT) TABLESPACE PSINDEX PCTFREE 10 STORAGE(INITIAL 40960 ); CREATE INDEX SYSADM.PSCCCCATLGRSLT_IDX ON "SYSADM".PS_CCC_CATLG_RSLT(EFF_STATUS, INSTITUTION, SUBJECT) TABLESPACE PSDEFAULT PCTFREE 10 STORAGE(INITIAL 204800 NEXT 204800 PCTINCREASE 0 ); Indexes on ps_CRSE_CATALOG ------------------------------ CREATE UNIQUE INDEX SYSADM.PS_CRSE_CATALOG ON "SYSADM".PS_CRSE_CATALOG(CRSE_ID, EFFDT) TABLESPACE PSINDEX PCTFREE 10 STORAGE(INITIAL 1146880 ); CREATE INDEX SYSADM.PS0CRSE_CATALOG ON "SYSADM".PS_CRSE_CATALOG(CRSE_ID, EFFDT) TABLESPACE PSINDEX PCTFREE 10 STORAGE(INITIAL 2129920 ); CREATE INDEX SYSADM.PSACRSE_CATALOG ON "SYSADM".PS_CRSE_CATALOG(RQMNT_DESIGNTN, CRSE_ID, EFFDT) TABLESPACE PSINDEX PCTFREE 10 STORAGE(INITIAL 1392640 ); *********** Executing Query: first time(tables are analyzed) ------------------------ SELECT CRSE_ID, EFFDT, TO_CHAR(EFFDT,'YYYY-MM-DD'), EFF_STATUS, CRSE_OFFER_NBR, INSTITUTION, SUBJECT, CATALOG_NBR, COURSE_APPROVED, CATALOG_PRINT, RQRMNT_GROUP, DESCR, DESCRLONG, UNITS_MINIMUM, UNITS_MAXIMUM, CCC_CAMPUS_CW, CCC_CAMPUS_DA2, CCC_CAMPUS_DO,CCC_CAMPUS_HW, CCC_CAMPUS_KK, CCC_CAMPUS_MX, CCC_CAMPUS_OH, CCC_CAMPUS_TR, CCC_CAMPUS_WR FROM PS_CATLG_SRCH_RSLT WHERE INSTITUTION = 'CCCSA' AND SUBJECT = 'MATH' AND EFF_STATUS = 'A' AND COURSE_APPROVED = 'A' AND CATALOG_PRINT = 'Y' AND EFFDT = (SELECT MAX(EFFDT) FROM PS_CATLG_SRCH_RSLT B WHERE PS_CATLG_SRCH_RSLT.CRSE_ID = B.CRSE_ID AND B.EFFDT
VIEWS ON THIS POST

150

Posted on:

Sunday 4th November 2012
View Replies!

query tunning

any one had suggestion on this query \t [Edited by mike73 on 11-07-2001 at 11:50 AM]
VIEWS ON THIS POST

186

Posted on:

Wednesday 7th November 2012
View Replies!

Sql tunning

Hi , Can someone suggest me the best site to learn sql tunning. I can gather statistics and find what are bad,but i havent got much exp in correcting them.Can someone give me tips to become an expert in sql tunning at any given situation. I want something demo sqls,probs explained and how to correct it as examples.
VIEWS ON THIS POST

163

Posted on:

Wednesday 7th November 2012
View Replies!

tunning

my application running slow tell me step by step with command
VIEWS ON THIS POST

161

Posted on:

Wednesday 7th November 2012
View Replies!

query tunning

UPDATE ACCT A1 SET A1.A_VALUE = (SELECT SUM(A_VALUE) FROM ACCT A2 WHERE A1.MEM = A2.MEM AND A1.MTR_SFX = A2.MTR_SFX AND A1.FIE = A2.FIE AND A2.RUN_ID = 8 AND A2.A_VALUE_TYPE_CD 'FIE') WHERE A1.VALUE = 'FIE' AND A1.RUN = 8 this query run too long on the table only had about 15000 rows, the explain plan is fine and it used its index. any adivses\t\t
VIEWS ON THIS POST

155

Posted on:

Wednesday 7th November 2012
View Replies!

Performance tunning suggestions

Why two laptops How does the DB connect to the App If they were both on the same machine (its for demo purposes after all) you might get much better performance.
VIEWS ON THIS POST

186

Posted on:

Wednesday 7th November 2012
View Replies!

performance tunning

Can someone please explain a little bit about this report on my database: NAME PINHITRATIO sql area .8888104074335375263200974791601673499746 table/proc .969319257036692749082670622412189649142 body .6423135464231354642313546423135464231355 trigger .9300871033344844787229960477341621580139 index .15 cluster .9993451186445870800716922746978354447832 Why index and body and Sqlarea are so poor on the hit ratio \t what do I need to do to improve them
VIEWS ON THIS POST

149

Posted on:

Wednesday 7th November 2012
View Replies!

query tunning

for some reason the PLANT table was doing full table scan, could someone please give me some advises\t\t
VIEWS ON THIS POST

154

Posted on:

Wednesday 7th November 2012
View Replies!