This forum is currently locked. You can't register or post questions at this time. (read more)

Performance Issue on Production

All posts relating to Oracle database administration.

Moderator: Tim...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Mon Mar 18, 2013 5:50 pm

Hi.

I didn't say this *is* the cause. I said it is *one of the causes*. More important is that you understand why you are using dynamic sampling all the time. If you are doing dynamic sampling it means either is it explicitly requested or Oracle thinks your stats are rubbish. Identify this issue and your stats will be fine. At that point I would not expect the dynamic sampling/hard parse issue to happen anymore.

Remember though, the elapsed time for all of this is very quick, so even if you eliminate the issue, it is unlikely to see notice the difference in performance. It is doing 55 hard parses, including dynamic samples in 0.1 seconds. What you do think you will achieve by improving this? 0.05 seconds? Are you going to notice that difference?

Is your statement running in parallel? If so, then Oracle ignores the dynamic sampling setting, choosing instead to make the decision itself. If your statement runs in parallel Oracle can elect to do dynamic sampling on each run, which would cause the hard parse. See this:

http://coskan.wordpress.com/2012/05/31/ ... -sampling/

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Mon Mar 18, 2013 7:56 pm

Hi,
just i saw that TKPROF Report Again.. Dynamic Sampling had executed lot of times. for every query.. GT_TRANSACTION_TAX this the table which it shows dynamic sampling , here i had posted that statement.. this table is Global Temporary table.. in TKPROF Report DY SAMP Query shows for GTT Tables... there is no statistics on this table where i verified at last_analyzed on user_tables .. cann't We avoid DYN SAMP.. on GTT... i am not using Parallel Query or Hints anywhere...


How can i Trace based on schema.. i will more number of connection for that schema.. so can't use sid and serial# for all session.. for client_id need set mannually using trigger.. or other method can't use service name and module name..
i used sql_trace=true; with entire db and verified every trace file.. it takes more time.. please tell me to trace based on schema... with multiple session...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Mon Mar 18, 2013 9:25 pm

Hi.

You can turn off dynamic sampling for a specific query ysing the dynamic sampling hint.

Code: Select all

/*+ DYNAMIC_SAMPLING(0) */


You could use a logon trigger to set the client_id = username. That way you can trace on the client ID, which will actually give you the username. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Tue Mar 19, 2013 12:50 pm

Hi,
I will analyze that query soon and come back to u after verfied using hints to disable Dynamic Sampling. i am looing some more Trace files.. most of the Queries looks Parse and Execute equally.... even bind variable used... how can i avoid more parse here.. any idea.. Please check below Query..


Code: Select all

SELECT TBLOPTIONTIMEZONE.STIMEDIFFERENCE
FROM
 TBLOPTIONTIMEZONE,TBLUSER WHERE TBLUSER.ITIMEZONEID=TBLOPTIONTIMEZONE.ID AND
  TBLUSER.ID=:"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       70      0.00       0.00          0          0          0           0
Execute     70      0.00       0.00          0          0          0           0
Fetch       70      0.00       0.00          0        350          0          70
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      210      0.00       0.01          0        350          0          70

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS  (cr=5 pr=0 pw=0 time=53 us)
      1   TABLE ACCESS BY INDEX ROWID TBLUSER (cr=3 pr=0 pw=0 time=25 us)
      1    INDEX UNIQUE SCAN TBLUSER_PK (cr=2 pr=0 pw=0 time=14 us)(object id 54400)
      1   TABLE ACCESS BY INDEX ROWID TBLOPTIONTIMEZONE (cr=2 pr=0 pw=0 time=16 us)
      1    INDEX UNIQUE SCAN TBLOPTIONTIMEZONE_PK (cr=1 pr=0 pw=0 time=7 us)(object id 54097)


Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Wed Mar 20, 2013 8:39 am

Hi.

But if the dynamic sampling is causing the hard parse, removing the dynamic sampling will fix this. Try that before launching down another path.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Wed Mar 20, 2013 11:44 am

Hi,
i have implemented.. to disable dynamic sampling .. in trace file also got removed.. Query looks..good in TKPROF.. But in Statspack Report.. it looks more problematic Query... parse also more ... i think parse can reduce only by developers..am i rite..?

Code: Select all

SQL ID: 0ugsd1km7dgyc Plan Hash: 2528763625

SELECT /*+ DYNAMIC_SAMPLING(0) */ INVOICEID, INVOICENUMBER, INVOICEDATE,
  INVOICEBRANCH, INVOICEDEPT, INVOICEPARTY, INVOICEPARTYADDRESS,
  INVOICECURRENCY, LOCALCURRENCY, INVOICEEXCHANGERATE, INVOICETYPE,
  INVOICECLASS, INVOICE_ROUND_OFF_AMOUNT, INVOICE_GROSS_AMOUNT,
  INVOICEREMARKS, INVOICE_REFERENCE_TYPE, INVOICE_REFERENCE_NO,
  GSIP.EXPORT_IMPORT_INDICATOR, COMPANY_PANNO, COMPANY_TANNO, TERMINAL_CHANO,
  TERMINAL_SERVICE_TAXNO, GSIP.CUSTOMERID, GSIP.SHIPPERID, SHIPPERADDRESS,
  CONSIGNEE, CONSIGNEEADDRESS, HAWB_HBL_NO, HAWB_HBL_JOBNO, HAWB_HBL_DATE,
  MAWB_CONSOLE_NO, MAWB_CONSOLE_JOBNO, MAWB_CONSOLE_DATE, CONSOLETYPE, OBLID,
  OBLDATE, IS_DIRECT, GSIP.GROSSWEIGHT, DECODE(GSIP.SHIPMENTMODE, 1,
  CHARGEABLEWEIGHT, GSIP.GROSSWEIGHT) CHARGEABLEWEIGHT, NOOFPACKAGES,
  GSIP.VOLUME, GSIP.ORIGINTERMINAL, DESTINATIONTERMINAL, POL, POD, FLIGHTDATE,
   FLIGHT_VOYAGE_NO, CARRIER_VESSEL_NAME, ETD, GSIP.ETA, INCOTERMS,
  USER_EMAIL_ID, GSIP.SHIPMENTMODE, GSIP.INVOICEBRANCHADDRESS, CUSTOMERNAME,
  GSIP.GOODSDESCRIPTION, PURCHASEORDERNO, COMMERCIALINVNO,
  INVOICEAMOUNTINWORDS, INVOICENAME, GSIP.UOW, GSIP.UOV, GSIP.IGMNO,
  GSIP.IGMDATE, GSIP.NATUREOFGOODS, GSIP.PACKAGETYPE, GSIP.DESCRIP,
  GSIP.CHARGEREF, GSIP.PRICECURR, GSIP.PRICE_EXCG_RATE, GSIP.PRICEAMT,
  GSIP.TRANSAMT, GSIP.INV_ADV_AMOUNT ADVANCEAMT, GSIP.INV_BALANCE_AMOUNT,
  GSIP.CHARGE_SERIAL_NO, GSIP.JOB_ID, (SELECT COUNT(1)
FROM
 GT_SALES_INVOICE_PRINT GSIP1 WHERE GSIP1.INVOICEID = GSIP.INVOICEID GROUP BY
  GSIP1.INVOICEID) CHGCOUNT, GSIP.SHIPPER_INVOICE_NO, GSIP.CONTAINER_TYPE,
  GSIP.CONTAINER_NO, GSIP.SHIPPERNAME, GSIP.CONSIGNEENAME, (SELECT
  SUM(TA1.TAX_AMOUNT_TRANSACTION_CURR) FROM GT_TRANSACTION_TAX TA1 WHERE
  TA1.TRANSACTION_ID = GSIP.INVOICEID GROUP BY TA1.TRANSACTION_ID) TAXAMOUNT,
  GSIP.TAX_PERCENT, GSIP.NOTIFYNAME, HDR.TOORDER TO_ORDER FROM
  GT_SALES_INVOICE_PRINT GSIP LEFT JOIN FS_FR_HOUSEDOCHDR HDR ON
  HDR.JOBFILENO = GSIP.HAWB_HBL_JOBNO AND HDR.HOUSEDOCNO = GSIP.HAWB_HBL_NO
  ORDER BY GSIP.CHARGE_SERIAL_NO


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       22      0.00       0.00          0          0          0           0
Execute     22      0.00       0.00          0          0          0           0
Fetch       22      0.01       0.02          4        423          0         115
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       66      0.01       0.02          4        423          0         115

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 88
Number of plan statistics captured: 22

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          2  SORT GROUP BY NOSORT (cr=4 pr=0 pw=0 time=56 us cost=2 size=14 card=1)
         5          5         10   TABLE ACCESS FULL GT_SALES_INVOICE_PRINT (cr=4 pr=0 pw=0 time=34 us cost=2 size=14 card=1)
         1          1          1  SORT GROUP BY NOSORT (cr=4 pr=0 pw=0 time=38 us cost=2 size=27 card=1)
         6         10         27   TABLE ACCESS FULL GT_TRANSACTION_TAX (cr=4 pr=0 pw=0 time=28 us cost=2 size=27 card=1)
         5          5         10  SORT ORDER BY (cr=19 pr=0 pw=0 time=701 us cost=48 size=169434 card=9)
         5          5         10   NESTED LOOPS OUTER (cr=12 pr=0 pw=0 time=520 us cost=47 size=169434 card=9)
         5          5         10    TABLE ACCESS FULL GT_SALES_INVOICE_PRINT (cr=3 pr=0 pw=0 time=90 us cost=29 size=169182 card=9)
         0          2         10    TABLE ACCESS BY INDEX ROWID FS_FR_HOUSEDOCHDR (cr=8 pr=0 pw=0 time=390 us cost=2 size=28 card=1)
         0          3         10     INDEX RANGE SCAN FS_FR_HOUSEDOCHDR_HDOCNO (cr=5 pr=0 pw=0 time=358 us cost=1 size=0 card=1)(object id 90215)



Statspack Report

Code: Select all

  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
   6716.34           69      97.34   55.0    6120.91              44  931283142
Module: i-net Crystal-Clear
SELECT /*+ DYNAMIC_SAMPLING(0) */ INVOICEID, INVOICENUMBER, INVO
ICEDATE, INVOICEBRANCH, INVOICEDEPT, INVOICEPARTY, INVOICEPARTYA
DDRESS, INVOICECURRENCY, LOCALCURRENCY, INVOICEEXCHANGERATE, INV
OICETYPE, INVOICECLASS, INVOICE_ROUND_OFF_AMOUNT, INVOICE_GROSS_


mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Fri Mar 22, 2013 9:32 am

Hi,
Any Update on this..

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Fri Mar 22, 2013 9:49 am

Hi.

OK. So now you've got rid of the sampling, but you are still getting the parses. All of them are soft parses though. You can tell this because of, "Misses in library cache during parse: 0", so you are performing a soft parse for each of them, which means it is not getting aged out of the shared pool.

So now I would question what your session_cached_cursors is set to.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Performance Issue on Production

Postby mail.madhankumar » Fri Mar 22, 2013 2:10 pm

Hi,
In that server parameter value of session_cached_cursors is 50. i verified now by Statspack and Trace file.. Dynamic sampling shows again for 1st time in that session.. cursor_sharing is EXACT

Statspack Report

Code: Select all


    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
  68457.17          125     547.66 ######   70918.45   8,428,664,618  931283142
Module: i-net Crystal-Clear
SELECT /*+ DYNAMIC_SAMPLING(0) */ INVOICEID, INVOICENUMBER, INVO
ICEDATE, INVOICEBRANCH, INVOICEDEPT, INVOICEPARTY, INVOICEPARTYA
DDRESS, INVOICECURRENCY, LOCALCURRENCY, INVOICEEXCHANGERATE, INV
OICETYPE, INVOICECLASS, INVOICE_ROUND_OFF_AMOUNT, INVOICE_GROSS_


TKPROF Report: 1st execution ..

Code: Select all

SQL ID: 0ugsd1km7dgyc Plan Hash: 1233723548

SELECT /*+ DYNAMIC_SAMPLING(0) */ INVOICEID, INVOICENUMBER, INVOICEDATE,
  INVOICEBRANCH, INVOICEDEPT, INVOICEPARTY, INVOICEPARTYADDRESS,
  INVOICECURRENCY, LOCALCURRENCY, INVOICEEXCHANGERATE, INVOICETYPE,
  INVOICECLASS, INVOICE_ROUND_OFF_AMOUNT, INVOICE_GROSS_AMOUNT,
  INVOICEREMARKS, INVOICE_REFERENCE_TYPE, INVOICE_REFERENCE_NO,
  GSIP.EXPORT_IMPORT_INDICATOR, COMPANY_PANNO, COMPANY_TANNO, TERMINAL_CHANO,
  TERMINAL_SERVICE_TAXNO, GSIP.CUSTOMERID, GSIP.SHIPPERID, SHIPPERADDRESS,
  CONSIGNEE, CONSIGNEEADDRESS, HAWB_HBL_NO, HAWB_HBL_JOBNO, HAWB_HBL_DATE,
  MAWB_CONSOLE_NO, MAWB_CONSOLE_JOBNO, MAWB_CONSOLE_DATE, CONSOLETYPE, OBLID,
  OBLDATE, IS_DIRECT, GSIP.GROSSWEIGHT, DECODE(GSIP.SHIPMENTMODE, 1,
  CHARGEABLEWEIGHT, GSIP.GROSSWEIGHT) CHARGEABLEWEIGHT, NOOFPACKAGES,
  GSIP.VOLUME, GSIP.ORIGINTERMINAL, DESTINATIONTERMINAL, POL, POD, FLIGHTDATE,
   FLIGHT_VOYAGE_NO, CARRIER_VESSEL_NAME, ETD, GSIP.ETA, INCOTERMS,
  USER_EMAIL_ID, GSIP.SHIPMENTMODE, GSIP.INVOICEBRANCHADDRESS, CUSTOMERNAME,
  GSIP.GOODSDESCRIPTION, PURCHASEORDERNO, COMMERCIALINVNO,
  INVOICEAMOUNTINWORDS, INVOICENAME, GSIP.UOW, GSIP.UOV, GSIP.IGMNO,
  GSIP.IGMDATE, GSIP.NATUREOFGOODS, GSIP.PACKAGETYPE, GSIP.DESCRIP,
  GSIP.CHARGEREF, GSIP.PRICECURR, GSIP.PRICE_EXCG_RATE, GSIP.PRICEAMT,
  GSIP.TRANSAMT, GSIP.INV_ADV_AMOUNT ADVANCEAMT, GSIP.INV_BALANCE_AMOUNT,
  GSIP.CHARGE_SERIAL_NO, GSIP.JOB_ID, (SELECT COUNT(1)
FROM
 GT_SALES_INVOICE_PRINT GSIP1 WHERE GSIP1.INVOICEID = GSIP.INVOICEID GROUP BY
  GSIP1.INVOICEID) CHGCOUNT, GSIP.SHIPPER_INVOICE_NO, GSIP.CONTAINER_TYPE,
  GSIP.CONTAINER_NO, GSIP.SHIPPERNAME, GSIP.CONSIGNEENAME, (SELECT
  SUM(TA1.TAX_AMOUNT_TRANSACTION_CURR) FROM GT_TRANSACTION_TAX TA1 WHERE
  TA1.TRANSACTION_ID = GSIP.INVOICEID GROUP BY TA1.TRANSACTION_ID) TAXAMOUNT,
  GSIP.TAX_PERCENT, GSIP.NOTIFYNAME, HDR.TOORDER TO_ORDER FROM
  GT_SALES_INVOICE_PRINT GSIP LEFT JOIN FS_FR_HOUSEDOCHDR HDR ON
  HDR.JOBFILENO = GSIP.HAWB_HBL_JOBNO AND HDR.HOUSEDOCNO = GSIP.HAWB_HBL_NO
  ORDER BY GSIP.CHARGE_SERIAL_NO


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.10       0.11          0      44155          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.12       0.12          0      44155          0           6

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT GROUP BY NOSORT (cr=3 pr=0 pw=0 time=55 us cost=2 size=14 card=1)
         6          6          6   TABLE ACCESS FULL GT_SALES_INVOICE_PRINT (cr=3 pr=0 pw=0 time=38 us cost=2 size=14 card=1)
         1          1          1  SORT GROUP BY NOSORT (cr=3 pr=0 pw=0 time=34 us cost=2 size=27 card=1)
        15         15         15   TABLE ACCESS FULL GT_TRANSACTION_TAX (cr=3 pr=0 pw=0 time=16 us cost=2 size=27 card=1)
         6          6          6  SORT ORDER BY (cr=44155 pr=0 pw=0 time=111015 us cost=33100 size=153770768 card=8168)
         6          6          6   HASH JOIN RIGHT OUTER (cr=44149 pr=0 pw=0 time=110722 us cost=1132 size=153770768 card=8168)
     40100      40100      40100    TABLE ACCESS FULL FS_FR_HOUSEDOCHDR (cr=44146 pr=0 pw=0 time=130952 us cost=1101 size=1119832 card=39994)
         6          6          6    TABLE ACCESS FULL GT_SALES_INVOICE_PRINT (cr=3 pr=0 pw=0 time=71 us cost=30 size=153542064 card=8168)

********************************************************************************

SQL ID: gxt8gy80v4w03 Plan Hash: 1395081586

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
  NVL(SUM(C2),:"SYS_B_1")
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("SYS_ALIAS_1")
  FULL("SYS_ALIAS_1") NO_PARALLEL_INDEX("SYS_ALIAS_1") */ :"SYS_B_2" AS C1,
  CASE WHEN "SYS_ALIAS_1"."TRANSACTION_ID"=:"SYS_B_3" THEN :"SYS_B_4" ELSE
  :"SYS_B_5" END AS C2 FROM "FLYJACETRANS26PROD210312"."GT_TRANSACTION_TAX"
  "SYS_ALIAS_1") SAMPLESUB


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       10      0.00       0.00          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         35          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       30      0.00       0.00          0         35          0          10

Misses in library cache during parse: 0
Misses in library cache during execute: 5
Optimizer mode: ALL_ROWS
Parsing user id: 88     (recursive depth: 1)
Number of plan statistics captured: 10

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=4 pr=0 pw=0 time=58 us)
        15         10         21   TABLE ACCESS FULL GT_TRANSACTION_TAX (cr=4 pr=0 pw=0 time=49 us cost=29 size=114352 card=8168)

********************************************************************************





TKPROF 2nd time Execution in Same Tracefile with same session..
Why it shows different execution..

Code: Select all

SQL ID: 0ugsd1km7dgyc Plan Hash: 2528763625
SELECT /*+ DYNAMIC_SAMPLING(0) */ INVOICEID, INVOICENUMBER, INVOICEDATE,
  INVOICEBRANCH, INVOICEDEPT, INVOICEPARTY, INVOICEPARTYADDRESS,
  INVOICECURRENCY, LOCALCURRENCY, INVOICEEXCHANGERATE, INVOICETYPE,
  INVOICECLASS, INVOICE_ROUND_OFF_AMOUNT, INVOICE_GROSS_AMOUNT,
  INVOICEREMARKS, INVOICE_REFERENCE_TYPE, INVOICE_REFERENCE_NO,
  GSIP.EXPORT_IMPORT_INDICATOR, COMPANY_PANNO, COMPANY_TANNO, TERMINAL_CHANO,
  TERMINAL_SERVICE_TAXNO, GSIP.CUSTOMERID, GSIP.SHIPPERID, SHIPPERADDRESS,
  CONSIGNEE, CONSIGNEEADDRESS, HAWB_HBL_NO, HAWB_HBL_JOBNO, HAWB_HBL_DATE,
  MAWB_CONSOLE_NO, MAWB_CONSOLE_JOBNO, MAWB_CONSOLE_DATE, CONSOLETYPE, OBLID,
  OBLDATE, IS_DIRECT, GSIP.GROSSWEIGHT, DECODE(GSIP.SHIPMENTMODE, 1,
  CHARGEABLEWEIGHT, GSIP.GROSSWEIGHT) CHARGEABLEWEIGHT, NOOFPACKAGES,
  GSIP.VOLUME, GSIP.ORIGINTERMINAL, DESTINATIONTERMINAL, POL, POD, FLIGHTDATE,
   FLIGHT_VOYAGE_NO, CARRIER_VESSEL_NAME, ETD, GSIP.ETA, INCOTERMS,
  USER_EMAIL_ID, GSIP.SHIPMENTMODE, GSIP.INVOICEBRANCHADDRESS, CUSTOMERNAME,
  GSIP.GOODSDESCRIPTION, PURCHASEORDERNO, COMMERCIALINVNO,
  INVOICEAMOUNTINWORDS, INVOICENAME, GSIP.UOW, GSIP.UOV, GSIP.IGMNO,
  GSIP.IGMDATE, GSIP.NATUREOFGOODS, GSIP.PACKAGETYPE, GSIP.DESCRIP,
  GSIP.CHARGEREF, GSIP.PRICECURR, GSIP.PRICE_EXCG_RATE, GSIP.PRICEAMT,
  GSIP.TRANSAMT, GSIP.INV_ADV_AMOUNT ADVANCEAMT, GSIP.INV_BALANCE_AMOUNT,
  GSIP.CHARGE_SERIAL_NO, GSIP.JOB_ID, (SELECT COUNT(1)
FROM
 GT_SALES_INVOICE_PRINT GSIP1 WHERE GSIP1.INVOICEID = GSIP.INVOICEID GROUP BY
  GSIP1.INVOICEID) CHGCOUNT, GSIP.SHIPPER_INVOICE_NO, GSIP.CONTAINER_TYPE,
  GSIP.CONTAINER_NO, GSIP.SHIPPERNAME, GSIP.CONSIGNEENAME, (SELECT
  SUM(TA1.TAX_AMOUNT_TRANSACTION_CURR) FROM GT_TRANSACTION_TAX TA1 WHERE
  TA1.TRANSACTION_ID = GSIP.INVOICEID GROUP BY TA1.TRANSACTION_ID) TAXAMOUNT,
  GSIP.TAX_PERCENT, GSIP.NOTIFYNAME, HDR.TOORDER TO_ORDER FROM
  GT_SALES_INVOICE_PRINT GSIP LEFT JOIN FS_FR_HOUSEDOCHDR HDR ON
  HDR.JOBFILENO = GSIP.HAWB_HBL_JOBNO AND HDR.HOUSEDOCNO = GSIP.HAWB_HBL_NO
  ORDER BY GSIP.CHARGE_SERIAL_NO


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        6      0.01       0.01          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          1         91          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       18      0.01       0.01          1         91          0          35

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Number of plan statistics captured: 6

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT GROUP BY NOSORT (cr=4 pr=0 pw=0 time=44 us cost=2 size=14 card=1)
        10          6         10   TABLE ACCESS FULL GT_SALES_INVOICE_PRINT (cr=4 pr=0 pw=0 time=24 us cost=2 size=14 card=1)
         1          1          1  SORT GROUP BY NOSORT (cr=3 pr=0 pw=0 time=28 us cost=2 size=27 card=1)
         3          5         21   TABLE ACCESS FULL GT_TRANSACTION_TAX (cr=3 pr=0 pw=0 time=21 us cost=2 size=27 card=1)
        10          6         10  SORT ORDER BY (cr=15 pr=0 pw=0 time=343 us cost=42 size=112956 card=6)
        10          6         10   NESTED LOOPS OUTER (cr=9 pr=0 pw=0 time=188 us cost=41 size=112956 card=6)
        10          6         10    TABLE ACCESS FULL GT_SALES_INVOICE_PRINT (cr=4 pr=0 pw=0 time=84 us cost=29 size=112788 card=6)
         0          2          8    TABLE ACCESS BY INDEX ROWID FS_FR_HOUSEDOCHDR (cr=5 pr=0 pw=0 time=68 us cost=2 size=28 card=1)
         0          2          8     INDEX RANGE SCAN FS_FR_HOUSEDOCHDR_HDOCNO (cr=3 pr=0 pw=0 time=37 us cost=1 size=0 card=1)(object id 90215)


Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Performance Issue on Production

Postby Tim... » Fri Mar 22, 2013 3:16 pm

Hi.

There can be a couple of reasons for this:

1) Cardinality feedback: Oracle estimates the cardinality of operations and uses that to determine the execution plan. When it executes the statement, it checks to see if its cardinality estimate was correct. If not, it will record the real cardinality and flag the statement as needing to be reparsed, using the new cardinality estimate next time. This can result in a new execution plan.

2) Adaptive Cursor Sharing: Oracle notices that SQl statements are bind aware and produces a new plan for the same statement, based on the values in the bind variables.

http://www.oracle-base.com/articles/11g ... -11gr1.php

Note. In all the cases you've posted, you have either "Misses in library cache during parse: 1" (a single hard parse, followed by multiple soft parses) or "Misses in library cache during parse: 0", not hard parses.

Having multiple soft parses is down to the way your application works with the database, unless your session_cached_cursors is set too low. In your case this doesn't seem to be the case.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests

cron