Home » SQL & PL/SQL » SQL & PL/SQL » SQL Tunning - Please Help Me (Oracle 10g)
SQL Tunning - Please Help Me [message #662067] Mon, 17 April 2017 05:56 Go to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
I am facing performance issue following sqls, could anyone help me to finding out the root cause of following performance issue?

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

SELECT /*+ index (f, test_ind) */ SUM(NVL(F.pd, 0))

FROM
ft F WHERE F.tnap = :B5 AND F.prod =
:B4 AND NVL(F.rc, '@@@') = NVL(:B3 , '@@@') AND ( :B2 IS NULL AND
tnab IS NULL ) AND ( ( NVL(:B1 , 'Y') = 'N' AND plf
IS NULL ) OR NVL(:B1 , 'Y') = 'Y' )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 52907 89.49 88.00 0 0 0 0
Fetch 52907 25.31 72.78 27775 263046 0 52907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 105815 114.81 160.78 27775 263046 0 52907

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 53 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7 pr=4 pw=0 time=12683 us)
3 FILTER (cr=7 pr=4 pw=0 time=11420 us)
3 TABLE ACCESS BY INDEX ROWID ft (cr=7 pr=4 pw=0 time=11408 us cost=1 size=18 card=1)
7 INDEX RANGE SCAN test_ind (cr=3 pr=2 pw=0 time=11336 us cost=1 size=0 card=2)(object id 118956)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
gc cr grant 2-way 12324 0.00 5.51
db file sequential read 27775 0.05 45.62
gc cr grant congested 50 0.00 0.02
gc remaster 1 0.14 0.14
gcs drm freeze in enter server mode 1 0.14 0.14
********************************************************************************

SELECT DESCRIPTION
FROM
RES_PCT WHERE PDCT = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 52908 23.64 23.89 0 0 0 0
Fetch 52908 5.60 5.18 0 158799 0 52588
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 105817 29.25 29.08 0 158799 0 52588

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 53 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID RES_PCT (cr=3 pr=0 pw=0 time=109 us cost=1 size=117 card=3)
1 INDEX SKIP SCAN RES_PCT_PK (cr=2 pr=0 pw=0 time=87 us cost=1 size=0 card=3)(object id 121870)

********************************************************************************
Re: SQL Tunning - Please Help Me [message #662071 is a reply to message #662067] Mon, 17 April 2017 06:49 Go to previous messageGo to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
Can anyone tell me what the tkprof output says in the above case? How can I do tunning?
Re: SQL Tunning - Please Help Me [message #662072 is a reply to message #662071] Mon, 17 April 2017 06:55 Go to previous messageGo to next message
Roachcoach
Messages: 1478
Registered: May 2010
Location: UK
Senior Member
It's a 0.01 second query. What are your expected times?
Re: SQL Tunning - Please Help Me [message #662073 is a reply to message #662072] Mon, 17 April 2017 07:09 Go to previous messageGo to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
Here CPU time is 114.81 s and elapsed time is 160.78 s, I need to reduce the elapsed time and there is DB file sequential read of 45 sec, that also need to be reduced.
How do you come to know it is 0.01 sec query?
Re: SQL Tunning - Please Help Me [message #662074 is a reply to message #662073] Mon, 17 April 2017 07:10 Go to previous messageGo to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
moreover the fetch and execute count is also a bit high 52K.
Re: SQL Tunning - Please Help Me [message #662075 is a reply to message #662074] Mon, 17 April 2017 07:28 Go to previous messageGo to next message
Roachcoach
Messages: 1478
Registered: May 2010
Location: UK
Senior Member
52907 executions at a total time of 89.49 is a very small time per execution.
Re: SQL Tunning - Please Help Me [message #662082 is a reply to message #662074] Mon, 17 April 2017 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
(recursive depth: 1)

Execute 52908
Fetch 52908
Do you execute this in a loop? if so then execute it only once and return an array of say 100 or 1000 rows at each time, this will be far faster.

Re: SQL Tunning - Please Help Me [message #662103 is a reply to message #662082] Tue, 18 April 2017 02:05 Go to previous messageGo to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
Thanks for reply.

I have further checked the process and run the program two times.
In the 1st run the result was satisfactory but in the 2nd run it took long times.
Given two TKPROF output below.
Could you please figure out the probable reason why i the first run the same query return 33 rows whereas in the 2nd run the same query return 52907 rows?
What may the probable reason the same program behaves differently in two runs while all other parameters are same?
If anybody have any idea, please let me no.


Yes, there is recursive call but why the same sql having same parameters have different recursive call in 2 runs? What may be the probable cause?

1st time run
-------------
********************************************************************************

SELECT /*+ index (f, FTNAP_IND) */ SUM(NVL(F.PD, 0))

FROM
FT F WHERE F.TNAP = :B5 AND F.PDCT =
:B4 AND NVL(F.RC, '@@@') = NVL(:B3 , '@@@') AND ( :B2 IS NULL AND
TNA_BY IS NULL ) AND ( ( NVL(:B1 , 'Y') = 'N' AND PL_FL
IS NULL ) OR NVL(:B1 , 'Y') = 'Y' )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 33 0.09 0.03 0 0 0 0
Fetch 33 0.03 0.03 0 151 0 33
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 67 0.12 0.07 0 151 0 33

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 53 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=2987 us)
2 FILTER (cr=5 pr=0 pw=0 time=2949 us)
2 TABLE ACCESS BY INDEX ROWID FT (cr=5 pr=0 pw=0 time=2942 us cost=1 size=18 card=1)
3 INDEX RANGE SCAN FTNAP_IND (cr=3 pr=0 pw=0 time=2875 us cost=1 size=0 card=2)(object id 118956)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc current block 2-way 34 0.00 0.01
********************************************************************************



2nd time run
-------------
********************************************************************************

SELECT /*+ index (f, FTNAP_IND) */ SUM(NVL(F.PD, 0))

FROM
FT F WHERE F.TNAP = :B5 AND F.PDCT =
:B4 AND NVL(F.RC, '@@@') = NVL(:B3 , '@@@') AND ( :B2 IS NULL AND
TNA_BY IS NULL ) AND ( ( NVL(:B1 , 'Y') = 'N' AND PL_FL
IS NULL ) OR NVL(:B1 , 'Y') = 'Y' )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 52907 89.49 88.00 0 0 0 0
Fetch 52907 25.31 72.78 27775 263046 0 52907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 105815 114.81 160.78 27775 263046 0 52907

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 53 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7 pr=4 pw=0 time=12683 us)
3 FILTER (cr=7 pr=4 pw=0 time=11420 us)
3 TABLE ACCESS BY INDEX ROWID FT (cr=7 pr=4 pw=0 time=11408 us cost=1 size=18 card=1)
7 INDEX RANGE SCAN FTNAP_IND (cr=3 pr=2 pw=0 time=11336 us cost=1 size=0 card=2)(object id 118956)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
gc cr grant 2-way 12324 0.00 5.51
db file sequential read 27775 0.05 45.62
gc cr grant congested 50 0.00 0.02
gc remaster 1 0.14 0.14
gcs drm freeze in enter server mode 1 0.14 0.14
********************************************************************************



Re: SQL Tunning - Please Help Me [message #662104 is a reply to message #662103] Tue, 18 April 2017 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

You did not investigate what I said, didn't you? Or at least you did not answer to my point.
Your problem is in the code NOT in the query.
Just make apply Roachcoach's reply in both cases and you'll see.

Re: SQL Tunning - Please Help Me [message #662105 is a reply to message #662104] Tue, 18 April 2017 02:20 Go to previous messageGo to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
Yes, it is called recursively.
But my point is the same query without changing any parameter, I am running, but how the fetch row is different?
Re: SQL Tunning - Please Help Me [message #662106 is a reply to message #662105] Tue, 18 April 2017 02:21 Go to previous messageGo to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
Is it the program internally modified any parameter?
Re: SQL Tunning - Please Help Me [message #662107 is a reply to message #662106] Tue, 18 April 2017 02:41 Go to previous messageGo to next message
Roachcoach
Messages: 1478
Registered: May 2010
Location: UK
Senior Member
Either your parameters are changing, or your data is changing, or both of the previous or you're hitting a bug (this is unlikely for so simple a query).

Rule out the first three before raising a case with Oracle

Try a level 12 trace to get the waits and the binds out to verify the parameters are unaltered.

Edit: Also the trace doesn't lie, you're EXECUTING the query more in the second case. I feel like there is a lot more to this you are not showing us. If this isn't row by row processing/looping I'll eat my proverbial hat.

[Updated on: Tue, 18 April 2017 02:55]

Report message to a moderator

Re: SQL Tunning - Please Help Me [message #662108 is a reply to message #662106] Tue, 18 April 2017 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
parthaspaul@gmail.com wrote on Tue, 18 April 2017 09:21
Is it the program internally modified any parameter?
Of course, in the first case you executed the query 33 times (in 0.12s) and in the second one 52907 times (in 160.78s).
As I said, stop trying to do anything with this query, review your code this is the culprit.

Re: SQL Tunning - Please Help Me [message #662109 is a reply to message #662108] Tue, 18 April 2017 03:13 Go to previous messageGo to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
Thanks for your reply. I am finding out inside the code.
Re: SQL Tunning - Please Help Me [message #662233 is a reply to message #662067] Fri, 21 April 2017 02:12 Go to previous messageGo to next message
parthaspaul@gmail.com
Messages: 9
Registered: April 2017
Junior Member
No, it is strange , though the parameters are same, sometime it returns 33 row sometimes 52K.
I am really hopeless. How it can be?
Re: SQL Tunning - Please Help Me [message #662235 is a reply to message #662233] Fri, 21 April 2017 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Only YOU know the data and the code.

Re: SQL Tunning - Please Help Me [message #662237 is a reply to message #662235] Fri, 21 April 2017 03:16 Go to previous messageGo to next message
cookiemonster
Messages: 12821
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you are running the same query with the same parameters and getting different numbers of rows back then there's only 2 possible explanations:
1) The amount of data in the tables that matches the query differs between runs.
2) Some client tools (most GUI ones) don't fetch all rows from a query by default, usually the fetch a screens worth and then you have to tell them to get the rest. So you the 33 could be from a GUI and the 52K from sqlplus (which always gets everything).

The other alternative is that despite what you think the parameters do differ.
Re: SQL Tunning - Please Help Me [message #662240 is a reply to message #662067] Fri, 21 April 2017 03:47 Go to previous messageGo to next message
John Watson
Messages: 7030
Registered: January 2010
Location: Global Village
Senior Member
You can simplify the query a bit. I think this is equivalent, getting rid of all the NVLs:
SELECT /*+ index (f, test_ind) */ SUM(f.pd)
FROM   ft F
WHERE  F.tnap = :B5
       AND F.prod = :B4
       AND ( f.rc = :b3
              OR ( f.rc IS NULL
                   AND :b3 IS NULL )
              OR ( :b3 = '@@@'
                   AND f.rc = '@@@' ) )
       AND ( :B2 IS NULL
             AND tnab IS NULL )
       AND ( :B1 = 'N'
             AND plf IS NULL )
Also, are you sure you want to hint that index? How is it defined? For example, if it is a compound index on tnap,prod,rc,tnab,plaf, and pd then Oracle will not have to touch the table at all.
Re: SQL Tunning - Please Help Me [message #662241 is a reply to message #662240] Fri, 21 April 2017 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 12821
Registered: September 2008
Location: Rainy Manchester
Senior Member
I doubt that the '@@@' are necessary if you skip the nvls and you've ignored the final OR. Should be this:
SELECT SUM(F.PD)
FROM FT F 
WHERE F.TNAP = :B5 
AND F.PDCT = :B4 
AND (F.RC = :B3
     OR F.RC IS NULL AND :b3 IS NULL
    )
AND :B2 IS NULL AND TNA_BY IS NULL
AND ((:B1 = 'N' AND PL_FL IS NULL ) 
     OR NVL(:B1 , 'Y') = 'Y' 
    )
Re: SQL Tunning - Please Help Me [message #662242 is a reply to message #662241] Fri, 21 April 2017 04:00 Go to previous messageGo to next message
John Watson
Messages: 7030
Registered: January 2010
Location: Global Village
Senior Member
I've just noticed that there are two different queries in this topic. So I have no idea what OP is really talking about.
Re: SQL Tunning - Please Help Me [message #662244 is a reply to message #662242] Fri, 21 April 2017 06:15 Go to previous messageGo to next message
cookiemonster
Messages: 12821
Registered: September 2008
Location: Rainy Manchester
Senior Member
Took out 1 bracket too many:
SELECT SUM(F.PD)
FROM FT F 
WHERE F.TNAP = :B5 
AND F.PDCT = :B4 
AND (F.RC = :B3
     OR (F.RC IS NULL AND :b3 IS NULL)
    )
AND :B2 IS NULL AND TNA_BY IS NULL
AND ((:B1 = 'N' AND PL_FL IS NULL ) 
     OR NVL(:B1 , 'Y') = 'Y' 
    )
Re: SQL Tunning - Please Help Me [message #662319 is a reply to message #662067] Mon, 24 April 2017 18:21 Go to previous message
mikek
Messages: 29
Registered: January 2017
Junior Member
I have encountered this a few times, is it possible that the Application Database is using
"Fine Grain Access Control"? It would explain the big difference in records return when different
users query the database using the same Query Parameters.
Previous Topic: Partition tablespace name does not change after moving subpartition
Next Topic: NOT LIKE operation on the numbers columns
Goto Forum:
  


Current Time: Tue Aug 22 00:37:21 CDT 2017

Total time taken to generate the page: 0.01862 seconds