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
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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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 #218401 is a reply to message #218392] Thu, 08 February 2007 01:17 Go to previous messageGo to next message
vipin
Messages: 40
Registered: May 2002
Member
Hi Ross,
For RPT_Holdings_TEMP it is PORTFOLIONUMBER, fromdate, todate, datebasis, costbasis.

and for PAMCASH - BANK, AccT, Ledger.

Thanks.
Re: SQL with high consistent gets [message #218477 is a reply to message #218109] Thu, 08 February 2007 06:24 Go to previous messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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
michael_bialik
Messages: 611
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
Previous Topic: Database Triggers performance issues
Next Topic: how to tune query
Goto Forum:
  


Current Time: Wed Dec 07 22:43:21 CST 2016

Total time taken to generate the page: 0.09808 seconds