SEARCH YOUR SOLUTION HERE  

tuning private SQL area?

Why does my PARSE times always equal to EXECUTE times when I query data using the same sql statement with SQLPLUS The initiation parameter OPEN_CURSORS equals to 100, is it too small How can I tune it
Any suggestions will be appreciated.

Posted On: Thursday 15th of November 2012 09:05:08 PM Total Views:  273
View Complete with Replies




Related Messages:

sql query tuning   (102 Views)
Hi I have interesting query SQL> SELECT ttralnktix.CODAPP_OLD, 2 ttralnktix.NUMTRAFRT, 3 TPTFBDR_B.CODPTF codptfold , 4 ttralnktix.NUMTRAFRTLNK, 5 ttralnktix.CODAPP_NEW, 6 TPTFBDR_A.CODPTF codptfnew, 7 ttralnktix.CURDATE 8 FROM ttralnktix, 9 TPTFBDR TPTFBDR_A, 10 TPARCPTTIX TPARCPTTIX_A, 11 TPARGESTIX TPARGESTIX_A, 12 TPTFBDR TPTFBDR_B, 13 TPARCPTTIX TPARCPTTIX_B, 14 TPARGESTIX TPARGESTIX_B 15 WHERE ( ( TPARCPTTIX_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and 16 ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and 17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND 18 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND 19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND 20 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND 21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND 22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT ) 23 AND 24 ( TPTFBDR_A.CODPTF = 'CROSB') ) 25 ; C NUMTRAFRT CODPTFOL NUMTRAFRTLNK C CODPTFNE CURDATE - ---------- -------- ------------ - -------- --------- A 16553901 WARTS 11199599 A CROSB 10-APR-03 A 19059202 WARTS 11199599 A CROSB 11-APR-03 A 19059202 WARTS 11898559 A CROSB 10-APR-03 Elapsed: 00:00:00.13 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 NESTED LOOPS 4 3 NESTED LOOPS 5 4 NESTED LOOPS 6 5 NESTED LOOPS 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR' 8 7 INDEX (UNIQUE SCAN) OF 'IPTFBDR2' (UNIQUE) 9 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX' 10 9 INDEX (RANGE SCAN) OF 'IPARCPTTIX2' (NON-UNI QUE) 11 5 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX' 12 11 INDEX (RANGE SCAN) OF 'IPARGESTIX1' (UNIQUE) 13 4 INDEX (RANGE SCAN) OF 'PK_TTRALNKTIX' (UNIQUE) 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX' 15 14 INDEX (RANGE SCAN) OF 'IPARGESTIX2' (NON-UNIQUE) 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX' 17 16 INDEX (RANGE SCAN) OF 'IPARCPTTIX1' (UNIQUE) 18 1 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR' 19 18 INDEX (UNIQUE SCAN) OF 'IPTFBDR1' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 508 consistent gets 0 physical reads 0 redo size 1070 bytes sent via SQL*Net to client 554 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed when i change the query to SQL> SELECT ttralnktix.CODAPP_OLD, 2 ttralnktix.NUMTRAFRT, 3 TPTFBDR_B.CODPTF codptfold , 4 ttralnktix.NUMTRAFRTLNK, 5 ttralnktix.CODAPP_NEW, 6 TPTFBDR_A.CODPTF codptfnew, 7 ttralnktix.CURDATE 8 FROM ttralnktix, 9 TPTFBDR TPTFBDR_A, 10 TPARCPTTIX TPARCPTTIX_A, 11 TPARGESTIX TPARGESTIX_A, 12 TPTFBDR TPTFBDR_B, 13 TPARCPTTIX TPARCPTTIX_B, 14 TPARGESTIX TPARGESTIX_B 15 WHERE ( ( TPARCPTTIX_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and 16 ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and 17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND 18 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND 19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND 20 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND 21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND 22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT ) 23 AND 24 ( TPTFBDR_B.CODPTF = 'WARTS') ) ; C NUMTRAFRT CODPTFOL NUMTRAFRTLNK C CODPTFNE CURDATE - ---------- -------- ------------ - -------- --------- A 16553901 WARTS 11199599 A CROSB 10-APR-03 A 16868993 WARTS 11199599 A ACTEU 10-APR-03 A 17778227 WARTS 11898558 A CBWAR 10-APR-03 A 19059202 WARTS 11199599 A CROSB 11-APR-03 A 19059202 WARTS 11898559 A CROSB 10-APR-03 Elapsed: 00:01:07.80 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 NESTED LOOPS 4 3 NESTED LOOPS 5 4 NESTED LOOPS 6 5 NESTED LOOPS 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR' 8 7 INDEX (UNIQUE SCAN) OF 'IPTFBDR2' (UNIQUE) 9 6 TABLE ACCESS (FULL) OF 'TPARGESTIX' 10 5 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX' 11 10 INDEX (RANGE SCAN) OF 'IPARCPTTIX2' (NON-UNIQU E) 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX' 13 12 INDEX (RANGE SCAN) OF 'IPARGESTIX1' (UNIQUE) 14 3 INDEX (RANGE SCAN) OF 'PK_TTRALNKTIX' (UNIQUE) 15 2 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX' 16 15 INDEX (RANGE SCAN) OF 'IPARCPTTIX1' (UNIQUE) 17 1 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR' 18 17 INDEX (UNIQUE SCAN) OF 'IPTFBDR1' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 5 db block gets 11400369 consistent gets 0 physical reads 0 redo size 1292 bytes sent via SQL*Net to client 554 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed How can i tune this query why is the plan getting changed regards Hrishy
parameters for allocation tuning   (161 Views)
, does anyone know where i can get parameters to determine which applications access how often any row in a table I need these information for a allocation tuning problem
tuning the SQL..Reduce the response time   (108 Views)
Hi , I am using oracle 8.1.7,solaris 2.8. I have this query which is taking 3 min for execution. SELECT COUNT(*) FROM CART_STATS WHERE COMPANY_ID = 8170926 AND ( ((SHIPMENT_STATUS = 'N' or PAYMENT_CLEARED = 'N') and ARCHIVE != 'D' and STATUS != 'D' and STATUS != 'X' and NUM_TRANSACTIONS > 0) OR ((((SHIPMENT_STATUS = 'Y' and PAYMENT_CLEARED = 'Y' and STATUS != 'D') OR (STATUS = 'X')) and ARCHIVE != 'D') and NUM_TRANSACTIONS > 0 and DATE_MODIFIED >= (sysdate - 60)) ) and the execution plan is Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=283 Card=1 Bytes=25) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CART_STATS' (Cost=283 Card=1 29 Bytes=3225) 3 2 INDEX (RANGE SCAN) OF 'CART_PS_IDX' (NON-UNIQUE) (Cost =5 Card=129) Its using the index which is built on the following columns(composite index) CART_PS_IDX COMPANY_ID CART_PS_IDX PAYMENT_CLEARED CART_PS_IDX SHIPMENT_STATUS CART_PS_IDX STATUS CART_PS_IDX ARCHIVE CART_PS_IDX CART_ID No of distinct values in each of these columns are SQL> select count(distinct company_id) from cart; COUNT(DISTINCTCOMPANY_ID) ------------------------- 88342 SQL> select count(distinct PAYMENT_CLEARED) from cart; COUNT(DISTINCTPAYMENT_CLEARED) ------------------------------ 2 SQL> select count(distinct shipment_status) from cart; COUNT(DISTINCTSHIPMENT_STATUS) ------------------------------ 2 SQL> select count(distinct ARCHIVE) from cart; COUNT(DISTINCTARCHIVE) ---------------------- 3 SQL> select count(distinct status) from cart; COUNT(DISTINCTSTATUS) --------------------- 6 How can i reduce the time take by this query, will generation of histograms help on this. Please advice. regards anandkl
Shared pool and buffer cache tuning   (137 Views)
My Sun server has 4GB of memory with 2 900MHz CPU and running in Solaris 9 and act as a Oracle 9i 9.2.0.1.0 database server. Vendor has set the following Oracle memory usage: shared pool 128MB buffer cache 1008MB large pool 32MB java pool 32MB May I know what's the rationale behind a such a low shared poll and such a high buffer cache The DB needs to run 24X7 with over 100 users connected concurrently. Tks.
Performance tuning in oracle 10g SE1   (93 Views)
i am using Oracle 10g Standard edition One.i have a table where daily 40000 rows are inserted.what can i do to improve performance while fetching from the table.
performance tuning   (120 Views)
oracle guru's, iam a chicken in this database field and i was wondering if anyone of you could help me. I have a front end application for which i dont have the source code and i want to see how a certain form (the query) is getting executed (the explain plan). can anyone tell me how can i see what tables it is accesing and what indexes are being used any help would be gr8 full.
perf tuning   (148 Views)
Hi , I have a PL/SQL application. The code looks like: open a_cur for select a_column from a_table@a_remote_database; loop fetch a_cur into a_variable; exit when a_cur%notfound; -- process other stuff locally including inserts, etc... end loop; My questions are: 1. Does each fetch grab A row from a_remote_database, or Oracle fetch ALL the data into local SGA 2. If each fetch will be through Net, how can I tune this application Thank you!
Parallel automatic tuning   (143 Views)
Hi. I ran an Oracle Expert Tuning session in OEM and the report states that I should set the PARALLEL_AUTOMATIC_TUNING set to TRUE. The report also stated that there would be tremendous benefits in performance. Has anyone tried this Is there actually a benefit
Query tuning - please help!!   (171 Views)
How do I get rid of the sort merge join The explain plan and the query are given below: Code: ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 575 | 19 | | | | 1 | SORT UNIQUE | | 1 | 575 | 19 | | | |* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| ISSUE | 1 | 29 | 2 | ROWID | ROW L | | 3 | NESTED LOOPS | | 1 | 575 | 15 | | | | 4 | NESTED LOOPS | | 4 | 2184 | 13 | | | | 5 | NESTED LOOPS | | 7 | 2457 | 11 | | | | 6 | MERGE JOIN CARTESIAN | | 6406 | 1707K| 11 | | | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- | 7 | PARTITION HASH ALL | | | | | 1 | 8 | |* 8 | INDEX FAST FULL SCAN | SYS_IOT_TOP_30857 | 7 | 1365 | 2 | 1 | 8 | | 9 | BUFFER SORT | | 980 | 76440 | 9 | | | | 10 | PARTITION HASH ALL | | | | | 1 | 12 | |* 11 | INDEX FAST FULL SCAN | SYS_IOT_TOP_30830 | 980 | 76440 | 2 | 1 | 12 | | 12 | PARTITION HASH ITERATOR | | | | | KEY | KEY | |* 13 | INDEX UNIQUE SCAN | SYS_IOT_TOP_30830 | 1 | 78 | | KEY | KEY | | 14 | PARTITION HASH ITERATOR | | | | | KEY | KEY | |* 15 | INDEX RANGE SCAN | SYS_IOT_TOP_30857 | 1 | 195 | 1 | KEY | KEY | |* 16 | INDEX RANGE SCAN | XIF9ISSUE1 | 115 | | 1 | | | ----------------------------------------------------------------------------------------------------
query tuning   (246 Views)
Hi all, below is the query I have, it took for ever to get the data back; however the execution plan look ok please help Code: SELECT P.TRNX_ID, P.MTR_NO, P.PLANT_NO, P.PROD_DT, P.ACCT_DT, P.PROC_GRP_ID, P.RUN_ID, P.USER_ID, P.UPDT_DT, P.UPDT_HV_AS_DEL_IND, P.REC_STATUS_CD, COALESCE(AC.COMP_CD,'0') AS COMP_CD, AC.LIQ_CONVRSN_1, AC.LIQ_CONVRSN_2, AC.MOL_PCT, COALESCE(MPCGL.PRES_BASE_CD, PCGL.PRES_BASE_CD, APCGL.PRES_BASE_CD) AS GL_PRES_BASE, COALESCE(MPCGL.CONV_FCTR, PCGL.CONV_FCTR, APCGL.CONV_FCTR) AS GL_CONV_FCTR, A.HV_CONVRSN_WET, A.HV_CONVRSN_DRY, A.HV_CONVRSN_AS_DEL, '' AS HV_CONVRSN_APPLIED, A.HV_PRES_BASE_CD, A.VOL_CONVRSN_PRES_BASE_CD, A.GPM_26_RVP, A.GPM_12_RVP, A.TEST_CAR_GPM, A.LIQ_CONVRSN_1_TOT, A.LIQ_CONVRSN_2_TOT, A.MOL_PCT_TOTAL, A.ANALYSIS_TYPE_CD, A.PLUS_COMP_CD, A.COMMENTS, A.SAMPLE_DT, A.H2S_PPM, A.SPEC_GRAVITY, A.COMPRESS_FCTR, A.MOD_GAS_FCTR, M.HV_BASIS_CD, M.TEMP, M.PRES_BASE_CD, M.FLOW_PSIG, A.OG_RATIO FROM THS_PAYSTATION P JOIN ACS_ANALYSIS A ON A.PLANT_NO = P.PLANT_NO AND A.MTR_NO = P.ANALYSIS_MTR_NO AND P.PROD_DT BETWEEN A.EFF_DT_FROM AND A.EFF_DT_TO LEFT JOIN ACS_ANALYSIS_COMP AC ON AC.PLANT_NO = A.PLANT_NO AND AC.MTR_NO = A.MTR_NO AND AC.ANALYSIS_TYPE_CD = A.ANALYSIS_TYPE_CD AND AC.EFF_DT_FROM = A.EFF_DT_FROM LEFT JOIN ACS_CNT MPCGL ON MPCGL.PLANT_NO = P.PLANT_NO AND MPCGL.MTR_NO = P.MTR_NO AND MPCGL.COMP_CD = AC.COMP_CD AND MPCGL.ORIG_UOM_CD = 'MCF' AND MPCGL.RESULT_UOM_CD = 'GAL' AND P.PROD_DT BETWEEN MPCGL.EFF_DT_FROM AND MPCGL.EFF_DT_TO LEFT JOIN ACS_PNT PCGL ON PCGL.PLANT_NO = P.PLANT_NO AND PCGL.COMP_CD = AC.COMP_CD AND PCGL.ORIG_UOM_CD = 'MCF' AND PCGL.RESULT_UOM_CD = 'GAL' AND P.PROD_DT BETWEEN PCGL.EFF_DT_FROM AND PCGL.EFF_DT_TO LEFT JOIN ACS_PNT APCGL ON APCGL.PLANT_NO = 'ALL' AND APCGL.COMP_CD = AC.COMP_CD AND APCGL.ORIG_UOM_CD = 'MCF' AND APCGL.RESULT_UOM_CD = 'GAL' AND P.PROD_DT BETWEEN APCGL.EFF_DT_FROM AND APCGL.EFF_DT_TO LEFT JOIN ACS_VAL M ON M.PLANT_NO IN (P.PLANT_NO, 'ALL') AND M.MTR_NO = P.MTR_NO AND M.PROD_DT = P.PROD_DT AND M.UNIT_TM_CD = P.UNIT_TM_CD WHERE P.PHYS_CTR_CD = 'P' AND P.REC_STATUS_CD IN ('OR','CO') AND P.RUN_ID = 1524 AND P.ACCT_MTR_TYPE_CD NOT IN ('US','PT', 'ST') AND P.MTR_NO NOT IN --Don't include DerMtr Derwaga analysis (SELECT DER_MTR_NO FROM ACS_GRP_HDR WHERE PLANT_NO = '001' AND P.PROD_DT BETWEEN EFF_DT_FROM AND EFF_DT_TO AND DER_WAGA_IND = 1) ORDER BY P.TRNX_ID, A.ANALYSIS_TYPE_CD
Query tuning   (114 Views)
, The following Query takes 5 Hrs to complete the the respective tables are analyzed (compute statistics). The Query refers to a view ps_ccc_grad_gpa . Query: select a.emplid STU_ID , a.strm TERM , a.acad_career STU_CAREER , a.crse_career CRSE_CAREER , a.institution INSTITUTION , a.class_nbr CLASS_NBR , a.stdnt_enrl_status STU_ENRL_STATUS , c.cum_gpa , (SELECT cum_gpa FROM ps_ccc_grad_gpa WHERE acad_career = 'SKIL' AND emplid = a.emplid) CUM_GPA_SKIL -- , c.cum_gpa CUM_GPA_SKIL , c.ccc_grad_gpa , c.tot_taken_gpa GRAD_HRS , (SELECT tot_taken_gpa FROM ps_ccc_grad_gpa WHERE acad_career = 'SKIL' AND emplid = a.emplid) GRAD_HRS_SKIL -- , c.tot_taken_gpa GRAD_HRS_SKIL from STG_BIODEMO_DEX1 a , ps_ccc_grad_gpa c where a.emplid= c.emplid and a.institution = c.institution and a.acad_career = c.acad_career ------------------------------------------------ View: CREATE OR REPLACE VIEW PS_CCC_GRAD_GPA ( EMPLID, ACAD_CAREER, INSTITUTION, TOT_TAKEN_GPA, TOT_GRADE_POINTS, CUM_GPA, CCC_TOT_TAKEN_REM, CCC_GRADE_PTS_REM, CCC_GRAD_GPA ) AS SELECT cterm.EMPLID , cterm.ACAD_CAREER , cterm.INSTITUTION , cterm.TOT_TAKEN_GPA , cterm.TOT_GRADE_POINTS , cterm.CUM_GPA , remd.CCC_TOT_TAKEN_REM , remd.CCC_GRADE_PTS_REM , decode((cterm.TOT_TAKEN_GPA - remd.CCC_TOT_TAKEN_REM) ,0 ,0 , '' , cterm.CUM_GPA ,round((cterm.TOT_GRADE_POINTS - remd.CCC_GRADE_PTS_REM)/(cterm.TOT_TAKEN_GPA - remd.CCC_TOT_TAKEN_REM) ,3)) FROM PS_STDNT_CAR_TERM cterm , PS_CCC_TOT_REMEDL remd WHERE cterm.EMPLID = remd.EMPLID AND cterm.ACAD_CAREER = remd.ACAD_CAREER AND cterm.INSTITUTION = remd.INSTITUTION AND cterm.STRM = (SELECT MAX(STRM) FROM PS_STDNT_CAR_TERM cterm2 WHERE cterm2.EMPLID = cterm.EMPLID AND cterm2.ACAD_CAREER = cterm.ACAD_CAREER AND cterm2.INSTITUTION = cterm.INSTITUTION) ----------------------------------------- Based on the explain plan: The tables Ps_STDNT_CAR_TERM and PS_BIODEMO_DEZ1 is going for FULL TABLE SCAN So created two new indexes and analyzed (compute stats) the same. CREATE INDEX STGBIODEX1 ON STG_BIODEMO_DEX1(emplid,institution,acad_career) TABLESPACE SADATA; CREATE INDEX PS_STDNT_CAR_TERM2 ON PS_STDNT_CAR_TERM(ACAD_CAREER, INSTITUTION, STRM) TABLESPACE SADATA ; Still the Query shows a full table scan on same two tables 1. PS_STDNT_CAR_TERM --Rows >2342751 2. STG_BIODEMO_DEX1 -- Rows > 4715005 PS_CCC_GRAD_GPA(view) --Rows >610812 Many
Query tuning   (117 Views)
Hi everybody, What are the steps in SQL query tuning..wrt to explain plan,TKPROF,ANALYZE. say..I have a big query.i will give it to explain plan..some result data is inserted in to plan table. How do u interpret the data in the plan table and improve the query. Suresh.H.T
STATSPACK tuning on a remote database - possible?   (172 Views)
I have created all the necessary statspack objects in a local database but would like to run it against a remote database located on the network. Can this be done I cant immediately see how. Does anyone have experience running statspack
SQL tuning advise   (93 Views)
Code: SELECT ML.PLANT_NO, PH.PLANT_NM, ML.CTR_NO, PH.EFF_DT_TO, PH.EFF_DT_FROM, SUM(NVL(AVJAN.GAS_VOL,0)), SUM(NVL(AVFEB.GAS_VOL,0)), SUM(NVL(AVMAR.GAS_VOL,0)), /*SUM(NVL(AVAPR.GAS_VOL,0)),SUM(NVL(AVMAY.GAS_VOL,0)), SUM(NVL(AVJUN.GAS_VOL,0)), SUM(NVL(AVJUL.GAS_VOL,0)), SUM(NVL(AVAUG.GAS_VOL,0)), SUM(NVL(AVSEP.GAS_VOL,0)), SUM(NVL(AVOCT.GAS_VOL,0)),SUM(NVL(AVNOV.GAS_VOL,0)),SUM(NVL(AVDEC.GAS_VO L,0))*/ 0,0,0,0,0,0,0,0,0 FROM HEP_MTR_LIST ML JOIN HEP_PLANT_HDR PH ON ML.PLANT_NO=PH.PLANT_NO JOIN HEP_CTR_HEADER CH ON ML.CTR_NO=CH.CTR_NO AND CH.MO_13_ADJ_IND='1' LEFT JOIN HAN_ALLOC_VOL AVJAN ON ML.PLANT_NO=AVJAN.PLANT_NO AND ML.MTR_NO=AVJAN.MTR_NO AND AVJAN.PROD_CD='WH' AND AVJAN.DISP_CD='WHDV' AND TO_CHAR(AVJAN.PROD_DT, 'MM')='01' LEFT JOIN HAN_ALLOC_VOL AVFEB ON ML.PLANT_NO=AVFEB.PLANT_NO AND ML.MTR_NO=AVFEB.MTR_NO AND AVFEB.PROD_CD='WH' AND AVFEB.DISP_CD='WHDV' AND TO_CHAR(AVFEB.PROD_DT, 'MM')='02' LEFT JOIN HAN_ALLOC_VOL AVMAR ON ML.PLANT_NO=AVMAR.PLANT_NO AND ML.MTR_NO=AVMAR.MTR_NO AND AVMAR.PROD_CD='WH' AND AVMAR.DISP_CD='WHDV' AND TO_CHAR(AVMAR.PROD_DT, 'MM')='03' LEFT JOIN HAN_ALLOC_VOL AVAPR ON ML.PLANT_NO=AVAPR.PLANT_NO AND ML.MTR_NO=AVAPR.MTR_NO AND AVAPR.PROD_CD='WH' AND AVAPR.DISP_CD='WHDV' AND TO_CHAR(AVAPR.PROD_DT, 'MM')='04' LEFT JOIN HAN_ALLOC_VOL AVMAY ON ML.PLANT_NO=AVMAY.PLANT_NO AND ML.MTR_NO=AVMAY.MTR_NO AND AVMAY.PROD_CD='WH' AND AVMAY.DISP_CD='WHDV' AND TO_CHAR(AVMAY.PROD_DT, 'MM')='05' LEFT JOIN HAN_ALLOC_VOL AVJUN ON ML.PLANT_NO=AVJUN.PLANT_NO AND ML.MTR_NO=AVJUN.MTR_NO AND AVJUN.PROD_CD='WH' AND AVJUN.DISP_CD='WHDV' AND TO_CHAR(AVJUN.PROD_DT, 'MM')='06' LEFT JOIN HAN_ALLOC_VOL AVJUL ON ML.PLANT_NO=AVJUL.PLANT_NO AND ML.MTR_NO=AVJUL.MTR_NO AND AVJUL.PROD_CD='WH' AND AVJUL.DISP_CD='WHDV' AND TO_CHAR(AVJUL.PROD_DT, 'MM')='07' LEFT JOIN HAN_ALLOC_VOL AVAUG ON ML.PLANT_NO=AVAUG.PLANT_NO AND ML.MTR_NO=AVAUG.MTR_NO AND AVAUG.PROD_CD='WH' AND AVAUG.DISP_CD='WHDV' AND TO_CHAR(AVAUG.PROD_DT, 'MM')='08' LEFT JOIN HAN_ALLOC_VOL AVSEP ON ML.PLANT_NO=AVSEP.PLANT_NO AND ML.MTR_NO=AVSEP.MTR_NO AND AVSEP.PROD_CD='WH' AND AVSEP.DISP_CD='WHDV' AND TO_CHAR(AVSEP.PROD_DT, 'MM')='09' LEFT JOIN HAN_ALLOC_VOL AVOCT ON ML.PLANT_NO=AVOCT.PLANT_NO AND ML.MTR_NO=AVOCT.MTR_NO AND AVOCT.PROD_CD='WH' AND AVOCT.DISP_CD='WHDV' AND TO_CHAR(AVOCT.PROD_DT, 'MM')='10' LEFT JOIN HAN_ALLOC_VOL AVNOV ON ML.PLANT_NO=AVNOV.PLANT_NO AND ML.MTR_NO=AVNOV.MTR_NO AND AVNOV.PROD_CD='WH' AND AVNOV.DISP_CD='WHDV' AND TO_CHAR(AVNOV.PROD_DT, 'MM')='11' LEFT JOIN HAN_ALLOC_VOL AVDEC ON ML.PLANT_NO=AVDEC.PLANT_NO AND ML.MTR_NO=AVDEC.MTR_NO AND AVDEC.PROD_CD='WH' AND AVDEC.DISP_CD='WHDV' AND TO_CHAR(AVDEC.PROD_DT, 'MM')='12' GROUP BY ML.PLANT_NO, PH.PLANT_NM, ML.CTR_NO, PH.EFF_DT_TO, PH.EFF_DT_FROM ------------------------------------------------------------ Statement Id=4203132 Type= Cost=2.64039087471493E-308 TimeStamp=02-09-04::08::25:26 (1) SELECT STATEMENT CHOOSE Est. Rows: 328 Cost: 4,937 (31) SORT GROUP BY Est. Rows: 328 Cost: 4,937 (30) HASH JOIN OUTER Est. Rows: 328 Cost: 4,909 (28) HASH JOIN OUTER Est. Rows: 328 Cost: 4,497 (26) HASH JOIN OUTER Est. Rows: 328 Cost: 4,086 (24) HASH JOIN OUTER Est. Rows: 328 Cost: 3,675 (22) HASH JOIN OUTER Est. Rows: 328 Cost: 3,265 (20) HASH JOIN OUTER Est. Rows: 328 Cost: 2,856 (18) HASH JOIN OUTER Est. Rows: 328 Cost: 2,447 (16) HASH JOIN OUTER Est. Rows: 328 Cost: 2,039 (14) HASH JOIN OUTER Est. Rows: 328 Cost: 1,631 (12) HASH JOIN OUTER Est. Rows: 328 Cost: 1,224 (10) HASH JOIN OUTER Est. Rows: 328 Cost: 818 (8) HASH JOIN OUTER Est. Rows: 328 Cost: 413 (6) HASH JOIN Est. Rows: 328 Cost: 8 (2) TABLE ACCESS FULL HEP_PLANT_HDR [Analyzed] (2) Blocks: 2 Est. Rows: 48 of 48 Cost: 2 Tablespace: data_ts (5) HASH JOIN Est. Rows: 328 Cost: 5 (3) TABLE ACCESS FULL HEP_CTR_HEADER [Analyzed] (3) Blocks: 5 Est. Rows: 47 of 94 Cost: 2 Tablespace: CONTRACKER_DATA (4) NON-UNIQUE INDEX FAST FULL SCAN IDX_HEP_MTR_LIST [Analyzed] Est. Rows: 503 Cost: 2 (7) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (7) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (9) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (9) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (11) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (11) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (13) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (13) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (15) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (15) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (17) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (17) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (19) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (19) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (21) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (21) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (23) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (23) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (25) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (25) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (27) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (27) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts (29) TABLE ACCESS FULL HAN_ALLOC_VOL [Analyzed] (29) Blocks: 2,638 Est. Rows: 111 of 133,478 Cost: 402 Tablespace: data_ts oracle do a FTS on HAN_ALLOC_VOL and I have 4 indexes: one on ACCT_DT,PLANT_NO, one on MTR_NO,PROD_DT,PLANT_NO,PROD_CD, one on (RUN_ID,PROCESS_ID, one on TRNX_ID.
Specific sql statement with bind variable tuning   (154 Views)
Hi I am trying to run dbms_sqltune package on a specific sql . DB version is 10.2.4 OS Platform AIX I did the following steps Step1. SQL> DECLARE 2 my_task_name VARCHAR2 (30); 3 my_sqltext CLOB; 4 BEGIN 5 my_sqltext := 'SELECT T_00 . "KNUMA_TEMP" , T_00 . "VBELN" , T_00 . "AEDAT" , T_00 . 6 "ERDAT" , T_00 . "POSNR" , T_00 . "MEINS" , T_00 . "NETPR" , T_00 . 7 "WAERK" , T_00 . "ABGRU" , T_00 . "GEWEI" , T_00 . "LGORT" , T_00 . 8 "VSTEL" , T_00 . "VRKME" , T_00 . "ROUTE" , T_00 . "WERKS" , T_00 . 9 "MATNR" , T_00 . "MATKL" , T_00 . "VGPOS" , T_00 . "VGBEL" , T_00 . 10 "PRODH" , T_00 . "FAKSP" , T_01 . "KUNNR" , T_01 . "VSBED" , T_01 . 11 "VBTYP" , T_01 . "ERNAM" , T_01 . "VKORG" , T_01 . "AUART" , T_01 . "AUGRU" , T_01 . "BNDDT" , T_01 . "ANGDT" , T_01 . "KVGR5" , T_01 . 12 13 "KVGR4" , T_01 . "KVGR3" , T_01 . "KVGR2" , T_01 . "KVGR1" , T_01 . 14 "VKGRP" , T_01 . "VKBUR" , T_01 . "AUDAT" , T_01 . "VGTYP" , T_01 . 15 "SPART" , T_01 . "VTWEG" , T_01 . "VDATU" , T_01 . "AUTLF" , T_01 . 16 "LIFSK" , T_01 . "FAKSK" , T_01 . "VBELN" FROM "SAPR3"."VBAP" T_00 , "SAPR3"."VBAK" T_01 WHERE ( T_01 . "MANDT" = :A0 AND T_01 . "VBELN" = T_00 . "VBELN" 17 18 ) AND T_00 . "MANDT" = :A1 AND T_00 . "ERDAT" BETWEEN :A2 AND :A3'; 19 my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext, 20 bind_list => sql_binds (anydata.convertnumber (9)), 21 user_name => 'SYS', 22 scope => 'COMPREHENSIVE', time_limit => 60, 23 24 task_name => 'm41b_tuning_task', 25 description => 'Tuning Task' 26 ); 27 END; 28 / Step2: BEGIN dbms_sqltune.execute_tuning_task (task_name => 'm41b_tuning_task'); END; / 2 3 4 PL/SQL procedure successfully completed. Step3: SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'm41b_tuning_task'; STATUS ----------- COMPLETED SQL> SET LONG 1000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'm41b_sql_tuning_task') FROM DUAL; SQL> ERROR: ORA-13605: The specified task or object m41b_sql_tuning_task does not exist for the current user. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.PRVT_ADVISOR", line 4744 ORA-06512: at "SYS.DBMS_SQLTUNE", line 1082 ORA-06512: at line 1 But I see the task is owned by SYS user SQL> select task_name,owner from dba_advisor_log where owner in ('SYS','SAPR3') and task_name like'm41%'; TASK_NAME OWNER ------------------------------ ------------------------------ m41b_tuning_task SYS Actually I had gone thru the Create SQL_ADVISOR Job Fails on Error ORA-13605 [ID 549285.1] in metalink article before posting this message . I am not using EM/Grid Control etc for this sql tuning instead I am trying it from sqlplus So where do I set the following then SQL> sho parameter nls_language NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_language string AMERICAN SQL> Please help me resolving this.
Solaris tuning for Oracle Info?   (101 Views)
I have never been able to find a really good doc on tuning solaris for oracle. Does anybody have a favorite link
shared pool tuning   (120 Views)
Assuming that you have a shared pool_size of 9000000. After running this script what percentage shoudl you shoot for Or what percentage should you consider acceptable. select to_number(v$parameter.value) value, v$sgastat.bytes,(v$sgastat.bytes/v$parameter.value)*100 "Percent Free" from v$sgastat, v$parameter where v$sgastat.name ='free memory' and v$parameter.name ='shared_pool_size'; VALUE BYTES Percent Free ---------- ---------- ------------ 9000000 3345984 37.1776
Query tuning   (104 Views)
The following query gives a very high cost. Code: SELECT agr.szagreementno, AGR.SZORGBRANCHCODE, AGR.CPORTFOLIOCODE, AGR.SZPRODUCTOFFEREDCODE FROM AGREEMENTMASTER agr WHERE AGR.CUSERDEFFLAG1 = 'N' AND AGR.CACCOUNTSTATUS = 'L' AND AGR.CPORTFOLIOCODE IN ( SELECT SZVALUE FROM GENERALCONDITIONMASTER WHERE SZSYSTEMNAME='ICAPS' AND SZCONDITION='COMPANY' AND SZFROMVALUE=:p_COMPCODE) ; The explain plan for the query is Code: -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 684 | 14209 | | 1 | HASH JOIN | | 12 | 684 | 14209 | | 2 | SORT UNIQUE | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| GENERALCONDITIONMASTER | 1 | 31 | 3 | | 4 | INDEX RANGE SCAN | PK_GENERALCONDITIONMASTER | 1 | | 2 | | 5 | TABLE ACCESS FULL | AGREEMENTMASTER | 242K| 6154K| 14199 | -------------------------------------------------------------------------------------------- In the table AGREEMENTMASTER,i've created three bitmap indexes on columns(CUSERDEFFLAG1,CACCOUNTSTATUS,CPORTFOLIOCODE) whose cardinality is just 5. After that also,the cost & plan are same. Please give me some tips,so that i can reduce the cost of the query.
Problems in memory tuning   (123 Views)
, I have problems with memory tuning. The reports of Dev2000 in our company are taking a very long time. I checked the indexes on the columns of those tables being accessed and found that they are properly indexed. Then I increased the shared pool size, sort area size and db buffer cache size and now I have problem that the reports have become more slow. What should I do
tuning?   (116 Views)
when tuning on a database, most of task are focus on sql tuning, my question is cost based optimizer is based on data distribution and all that, if data changed, the path optimizer selected may not be best. my question is if we write some code by using jdbc, should we change code all the time and recompile the code all the time to reflect the data distribute change since data change all the time, what's our best choice