| SQL Tunning - Please Help Me [message #662067] |
Mon, 17 April 2017 05:56  |
 |
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 #662103 is a reply to message #662082] |
Tue, 18 April 2017 02:05  |
|
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 #662107 is a reply to message #662106] |
Tue, 18 April 2017 02:41 |
Roachcoach
Messages: 1576
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 #662237 is a reply to message #662235] |
Fri, 21 April 2017 03:16 |
cookiemonster
Messages: 13920
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 |
John Watson
Messages: 8931
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 ) |
|
|
|
| Re: SQL Tunning - Please Help Me [message #662241 is a reply to message #662240] |
Fri, 21 April 2017 03:56 |
cookiemonster
Messages: 13920
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 #662244 is a reply to message #662242] |
Fri, 21 April 2017 06:15 |
cookiemonster
Messages: 13920
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 |
 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
