Home » RDBMS Server » Performance Tuning » SQL with high consistent gets
SQL with high consistent gets [message #218109] |
Tue, 06 February 2007 19:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vipin
Messages: 40 Registered: May 2002
|
Member |
|
|
Hi,
Below are some resource intensive sqls from statspack. Can you please validate my understanding?
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,431,909 8 178,988.6 12.8 13.16 33.05 2225735611
Module: engine.exe
SELECT /*+ INDEX_ASC(cash cash_3ind) */ Ref,Zero,DbCr,Ban
k,Acct,BankRef,BankCashDate,OrigBal,RemainBal,Curr,FxOrigBal,FxR
emainBal,UOrigBal,URemainBal,SecIDType,Cusip,FxRate,ValueDate,Ca
shDesc,LastMatch,Ledger,BankSecIDType FROM pamcash WHERE Bank>=:
Bank AND(Bank>:Bank OR(Acct>=:Acct AND(Acct>:Acct OR(Ledger>=:Le
429,636 20 21,481.8 5.5 10.69 60.36 4173079977
Module: RG.exe
Select Distinct ASSETID, SECURITYIDTYPE,FROMDATE,TODATE,DATEBA
SIS,COSTBASIS,PORTFOLIONUMBER,USERID From RPT_HOLDINGS_Temp W
here PORTFOLIONUMBER= :"SYS_B_00" And trunc(FROMDATE)= TO_DATE(:
"SYS_B_01",:"SYS_B_02") And trunc(TODATE)= TO_DATE(:"SYS_B_03",:
"SYS_B_04") And DATEBASIS= :"SYS_B_05" And COSTBASIS= :"SYS_B_06
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
423,731 20 21,186.6 61.8 10.69 60.36 4173079977
Module: RG.exe
Select Distinct ASSETID, SECURITYIDTYPE,FROMDATE,TODATE,DATEBA
SIS,COSTBASIS,PORTFOLIONUMBER,USERID From RPT_HOLDINGS_Temp W
here PORTFOLIONUMBER= :"SYS_B_00" And trunc(FROMDATE)= TO_DATE(:
"SYS_B_01",:"SYS_B_02") And trunc(TODATE)= TO_DATE(:"SYS_B_03",:
"SYS_B_04") And DATEBASIS= :"SYS_B_05" And COSTBASIS= :"SYS_B_06
- High buffer gets in the first sql is due to high "get per exec". Consistent gets could not be the reason as it is not having high physical reads.
- Second sql is due to consistent reads as it is having Physical reads, despite a relatively high gets per exec.
Now,to tuning. The first sql definitely has to be tuned to the app. requirement. Can we expect the performance to improve for the second sql by switching to CBO? Or Can you please advice as to what would be appropriate?
Thank you,
[Updated on: Wed, 07 February 2007 01:08] by Moderator Report message to a moderator
|
|
|
Re: SQL with high consistent gets [message #218165 is a reply to message #218109] |
Wed, 07 February 2007 01:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Post the explain plans for each SQL, and the **FORMATTED** SQLs in full. Enclose them in [code] / [/code] tags so that we can read them.
Include the sizes of each table, the selectivity of WHERE clauses, the number of rows you expect to be returned, the current runtime, and your performance expectations.
What would be even better is the TK*Prof output of a trial run.
Ross Leishman
[Updated on: Wed, 07 February 2007 01:13] Report message to a moderator
|
|
|
Re: SQL with high consistent gets [message #218361 is a reply to message #218165] |
Wed, 07 February 2007 19:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vipin
Messages: 40 Registered: May 2002
|
Member |
|
|
Hi Ross,
Thank you for the reply. Please find the details below. I assume the record count in the explain plan is the expected resultset. I see the first sql as poorly written, and I expect the second one to improve by switching to CBO. Rpt_holdings_temp table has high dml activity.
select count(*) from pamcash;
COUNT(*)
--------------------
410774
Elapsed: 00:00:00.25
select count(*) from rpt_holdings_temp;
COUNT(*)
--------------------
316919
SELECT /*+ INDEX_ASC(pamcash pamcash_3ind) */ Ref,Zero,DbCr,Bank,Acct
,BankRef,BankCashDate,OrigBal,RemainBal,Curr
,FxOrigBal,FxRemainBal,UOrigBal,URemainBal,SecIDType
,Cusip,FxRate,ValueDate,CashDesc,LastMatch
,Ledger,BankSecIDType
FROM pamcash
WHERE Bank>='xxx'
AND(Bank>'xxx' OR ( Acct>=18 AND(Acct>18 OR(Ledger>=to_date('28-feb-03', 'dd-mon-yy')))));
345685 rows selected.
Elapsed: 00:00:49.30
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=335 Card=155459 Byte
s=21919719)
1 0 TABLE ACCESS (FULL) OF 'PAMCASH' (Cost=335 Card=155459 Byt
es=21919719)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
31512 consistent gets
3529 physical reads
0 redo size
52622339 bytes sent via SQL*Net to client
2558425 bytes received via SQL*Net from client
23047 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
345685 rows processed
Select Distinct ASSETID, SECURITYIDTYPE,FROMDATE,TODATE,DATEBASIS,COSTBASIS,PORTFOLIONUMBER,USERID
From RPT_HOLDINGS_Temp
Where PORTFOLIONUMBER= '3093'
And trunc(FROMDATE)= TO_DATE('01-12-2006','dd-mm-yyyy')
And trunc(TODATE)= TO_DATE('31-12-2006','dd-mm-yyyy')
And DATEBASIS= 'Ledger'
And COSTBASIS= 'GAAP';
3824 rows selected.
Elapsed: 00:00:06.28
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'RPT_HOLDINGS_TEMP'
3 2 INDEX (RANGE SCAN) OF 'RPT_HOLDINGS_TEMP_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4780 consistent gets
3886 physical reads
60 redo size
451801 bytes sent via SQL*Net to client
28624 bytes received via SQL*Net from client
256 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3824 rows processed
[Edited to format code]
[Updated on: Thu, 08 February 2007 06:26] by Moderator Report message to a moderator
|
|
|
Re: SQL with high consistent gets [message #218370 is a reply to message #218361] |
Wed, 07 February 2007 21:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
The first SQL is selecting 83% of the rows in PAMCASH. It is right to perform a Full Table Scan in this case. An index scan will make it slower. This query is already running as fast as possible, it's just doing a lot of necessary work.
The second SQL is performing a Range Scan. I can't tell whether a Full Scan would be more efficient because of the DISTINCT. Looking at the relatively small Consistent Gets and Physical Reads, I suspect the index use is warranted.
Without knowing what columns are in the RPT_HOLDINGS_TEMP_PK index, I don't know whether this could be made faster or not. If the dates are in the index, you should convert the TRUNC functions to BETWEEN clauses.
Finally, the samples you sent used constant values in the SQL, whereas the Statspack showed bind variables. This may give you a different plan, so my comments above are probably worthless.
You should rerun the plans using bind variables.
Ross Leishman
|
|
|
Re: SQL with high consistent gets [message #218384 is a reply to message #218370] |
Wed, 07 February 2007 23:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vipin
Messages: 40 Registered: May 2002
|
Member |
|
|
Agreed FTS is better for the first sql. There is an index on the selection criteria and it is interesting why 83% of the records is fetched by the App. I verified the plan with bind variables and there is no change.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35614 consistent gets
2 physical reads
0 redo size
61507055 bytes sent via SQL*Net to client
3028511 bytes received via SQL*Net from client
27282 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
409208 rows processed
For the second sql there is index on the selection criteria. Isn't it that RBO use index defined for the leading columns of where clause. I think trunc shouldn't make a difference. I saw high consistent (causing physical reads) and on a dev box the query is pretty fast, which points to the user activity. What would be the options available to us?
Thanks once again for the help.
|
|
|
Re: SQL with high consistent gets [message #218392 is a reply to message #218384] |
Thu, 08 February 2007 00:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
vipin wrote on Thu, 08 February 2007 16:44 | For the second sql there is index on the selection criteria.
|
Exaclty which columns are in the index?
Ross Leishman
|
|
|
|
Re: SQL with high consistent gets [message #218477 is a reply to message #218109] |
Thu, 08 February 2007 06:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think your understanding of buffer gets and physical gets needs clarification:
High buffer gets in the first sql is due to high "get per exec".
Consistent gets could not be the reason as it is not having high physical reads.
The total Buffer Gets figure is simply the No. of Executions multiplies by the Gets Per Execution.
Gets per Execution is simply the average number of blocks of data read from the buffer cache for an execution of this piece of SQL
- Second sql is due to consistent reads as it is having Physical reads, despite a relatively high gets per exec.
Ok, each and every Physical Read will also show up as a Buffer Get. A Physical Read is (put simply) where the database looks for the data in the Buffer Cache, doesn't find it, and has to read the data from disk, into the buffer cache, so that it can then read the data from the buffer cache.
A consistent read describes the processes that have to take place for Oracle to return the block of data that it has got from the Buffer Cache to the state that it would have been in at the start of the current query.
Consistent Reads are not caused by, nor do they cause Physical Reads.
|
|
|
Re: SQL with high consistent gets [message #218594 is a reply to message #218401] |
Thu, 08 February 2007 21:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
That index range scan is scanning ONLY on portfolionumber. It scans ALL of the rows with matching portfolionumber, and filters out the non-matching fromdate, todate, datebasis, costbasis values.
For minimal IO, you could rearrange the columns in the index to put the dates last:
ie. PORTFOLIONUMBER, datebasis, costbasis, fromdate, todate.
Then you could improve it a little more by altering your query:And FROMDATE >= TO_DATE('01-12-2006','dd-mm-yyyy')
AND FROMDATE < 1 + TO_DATE('01-12-2006','dd-mm-yyyy') this will allow it scan on the fromdate as well as the other three columns.
Alternatively, you could make the index function-based by changing the indexed columns fromdate, todate to trunc(fromdate), trunc(todate)
Note that all of these suggestions may affect the perfromance of other queries - either adversely or beneficially.
Ross Leishman
|
|
|
Re: SQL with high consistent gets [message #218668 is a reply to message #218594] |
Fri, 09 February 2007 04:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vipin
Messages: 40 Registered: May 2002
|
Member |
|
|
Hi JRRowbottom, Ross,
Thanks for replying.
I said the first sql is resource intensive as the number of executions is low. In my understanding consistent gets does trigger physical reads, when it has to construct data from UNDO TS, though it is possible that the data can be constructed from buffer. Isn't it that we try to reduce consistent gets in our sql? Doesn't reducing the LIO reduce the physical reads?
Ross, I will rearrange the columns and try it. Besides, sql tuning is there anything that we should keep in mind to reduce the consistent gets?
Thanks again for your help.
|
|
|
Re: SQL with high consistent gets [message #218743 is a reply to message #218109] |
Fri, 09 February 2007 14:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
If I understood your query correctly, then try following one;
SELECT Ref,Zero,DbCr,Bank,Acct
,BankRef,BankCashDate,OrigBal,RemainBal,Curr
,FxOrigBal,FxRemainBal,UOrigBal,URemainBal,SecIDType
,Cusip,FxRate,ValueDate,CashDesc,LastMatch
,Ledger,BankSecIDType
FROM pamcash
WHERE Bank = 'xxx' AND Acct = 18 AND Ledger >= to_date('28-feb-03', 'dd-mon-yy')
UNION ALL
SELECT Ref,Zero,DbCr,Bank,Acct
,BankRef,BankCashDate,OrigBal,RemainBal,Curr
,FxOrigBal,FxRemainBal,UOrigBal,URemainBal,SecIDType
,Cusip,FxRate,ValueDate,CashDesc,LastMatch
,Ledger,BankSecIDType
FROM pamcash
WHERE Bank = 'xxx' AND Acct > 18
UNION ALL
SELECT Ref,Zero,DbCr,Bank,Acct
,BankRef,BankCashDate,OrigBal,RemainBal,Curr
,FxOrigBal,FxRemainBal,UOrigBal,URemainBal,SecIDType
,Cusip,FxRate,ValueDate,CashDesc,LastMatch
,Ledger,BankSecIDType
FROM pamcash
WHERE Bank > 'xxx' ;
HTH.
Michael
|
|
|
Goto Forum:
Current Time: Thu Sep 12 07:48:57 CDT 2024
|