cancel
Showing results for 
Search instead for 
Did you mean: 

Shared cursos cache

Former Member
0 Kudos

Hi Oracle gurus. i'm have question about Shared cursos cache in one of BI server's i'm see very big

buffer gets

the statment are :

SELECT

file#

FROM

file$

WHERE

ts#=:1

Explain shows

SELECT STATEMENT ( Estimated Costs = 2 , Estimated #Rows = 0 )

2 TABLE ACCESS BY INDEX ROWID FILE$

( Estim. Costs = 2 , Estim. #Rows = 16 )

Estim. CPU-Costs = 14.443 Estim. IO-Costs = 2

1 INDEX RANGE SCAN I_FILE2

( Estim. Costs = 1 , Estim. #Rows = 1 )

Search Columns: 1

Estim. CPU-Costs = 7.321 Estim. IO-Costs = 1

Access Predicates

User SYS

What can you advise? Regards.

Accepted Solutions (0)

Answers (1)

Answers (1)

stefan_koehler
Active Contributor
0 Kudos

Hello Sergo,

some important information is missing.

1) Which database version (inclusive patchset) are you using?

2) Did you collect oracle data dictionary statistics (like described in sapnote #838725)?

3) I think you got that SELECT trough ST04 or DBACOCKPIT - how many executions does it have, what is the average execution time and how many buffer gets per execution does it have?

Regards

Stefan

Former Member
0 Kudos

Hello Stefan , thank for answer.

1) The Oracle db are 10.2.0.4

2) Oracle DD are collected last time 2010-03-18

3) I can't check it now, db are restarted .... about 20000 executions and buffer gets about 2.500.000.000

average execution time and buffer gets per execution i will check in a week - two ...

P.S. or can calculate ~ 125.000 buffer gets per execution

Regards.

stefan_koehler
Active Contributor
0 Kudos

Hello Sergo,

i have cross checked your information with our productive BW system.

Here are the information / statistics of the same SQL on our BW system:


Parse Timestamp: 20100328 14:30:44

System: <SID>
SQL_ID  bsa0wjtftg3uw, child number 0
-------------------------------------
select file# from file$ where ts#=:1

Plan hash value: 690176192

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| FILE$   |    32 |   224 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_FILE2 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / FILE$@SEL$1
   2 - SEL$1 / FILE$@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TS#"=:1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "FILE#"[NUMBER,22]
   2 - "FILE$".ROWID[ROWID,10]

Executions: 1.164.157

Buffer Gets: 466.953.357

Buffer Gets per Execution: 401,1

Buffer Gets per row: 2,0

Elapsed Time per Exec: 1.820,6

I have never investigated this issue, but i have compared these values with an OLTP system (like HR). It seems like the respond time of this SQL increases nearly linear with the amount of data files. The execution plan is always the same.

The other part is that this is recursive SQL is the high execution rate, but this can be possible, because of the high amount of performed DDLs in a BW system (which also can destroy the DD cache).

Do you face any performance issues, because of the recursive SQL or do you just notice this one in the shared cursor cache, because of its high amount of buffer gets?

Regards

Stefan

Former Member
0 Kudos

Amount of data files are not so many --112 files (on ERP much more 230).

Do you face any performance issues, because of the recursive SQL or do you just notice this one in the shared cursor cache, because of its high amount of buffer gets?

Yes performance are not so good, and i want understand are this SQL statment degrades performance or not ...

Thanks.

stefan_koehler
Active Contributor
0 Kudos

Hello Sergo,

> Amount of data files are not so many --112 files (on ERP much more 230).

In my BW system we have around 200 data files which also represents the statistics from above.

As i already mentioned, that in a BW system you have many more recursive SQLs on file$, which seems to be dependent on OLAP (CREATE, DROP and REBUILD of database objects).

All this stuff is also just speculation, to proof this we need to trace the SQLs and its recursive ones.

> Yes performance are not so good, and i want understand are this SQL statment degrades performance or not ...

Ok so you have really a performance issue .. so now the questions would be:

1) Do you face the "bad" performance while loading data or processing a query?

2) Is it a general performance issue or just on some particular queries?

3) What are the ratios of executions / parses / recursive SQLs, etc.?

4) What are the other statistics for that general or particular performance problem?

Just some guess again:

1) If i take a look at my statistics from above, i can't imagine that a user will notice an additional recursive time of (1.820) per execution, if the query itself takes a long time.

2) I can imagine that your performance is lost on another way

As you see we really have not information to help you.

Maybe you can run an AWR report (Sapnote #853576 - SQL script awrrpt) for the timeframe, in which you are facing the "bad" performance and provide us this one.

Regards

Stefan