ORA 4031 error, urgent - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: ORA 4031 error, urgent

  1. #21
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmm first time I heard shmmax could cause ORA-04031

    Any experience with that error related to SHMMAX?

  2. #22
    Join Date
    Apr 2002
    Posts
    291
    itīs complaining about large pool I dont understand why you keep on increasing shared pool
    Pando,
    You are right. When i tried increasing the shared pool size from 200M to 350M it didn't, but when i increased large_pool_size from 6M to 10M, it worked fine. But the only thing i'm worried about is, when my users are continuosly calling the same huge reports for testing purpose, it worked fine for 1-2 times, but later again, it gave the same old error ORA-4031. In the same application, when we are running small reports based on some particular client, its working fine ,but when we are trying to run a huge reports of all Currently these are my statistics:

    Shared_pool_size=480M
    large_pool_size=10M
    Sort_area_size=64M

    I can't figure out where exactly the problem is, can you please help me.

    thanks in advance
    PNRDBA

  3. #23
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    If the database runs in DEDICATED MODE, I would choose to set the LARGE_POOL_SIZE=0 instead of 10M.

    The following use LARGE POOL:

    1, Parallel Query, to cache parallel message buffer.

    2, Recovery Manager, to cache I/O buffer.

    3, Shared Server, session memory for each client, the UGA.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  4. #24
    Join Date
    Apr 2002
    Posts
    291
    If the database runs in DEDICATED MODE, I would choose to set the LARGE_POOL_SIZE=0 instead of 10M.

    If i'm using LARGE_POOL_SIZE=0, then my reports are not at all working . how can i make reports working then?
    PNRDBA

  5. #25
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Really? Sounds strange, Server processes would allocate memory from shared pool if the large_pool_size=0. Have you set dbwr_io_slaves to a non-zero value? Try to show parameter large_pool_size when it go to failure.

    Or you can set Large_pool_size=50M, why not? In Oracle 9i, you can modify Large_pool_size dynamically.
    Last edited by Calvin_Qiu; 11-29-2002 at 04:59 AM.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  6. #26
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    your reports are using parallel query then

  7. #27
    Join Date
    Apr 2002
    Posts
    291
    Have you set dbwr_io_slaves to a non-zero value?
    Nope. its value is 0 only. Yes, when i'm using large_pool_size=0,
    but when i use 10M, now the reports are working fine and my users are wokrking.

    Pando,
    my query is not using parallel option.
    PNRDBA

  8. #28
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    how do you know it is NOT using parallel query?

    have you checked the queries execution plans?

    parallel query works when itīs set at instance level, segment level or via HINTS

  9. #29
    Join Date
    Apr 2002
    Posts
    291
    I checked in the execution plan. Here's the plan O/P.

    SQL> select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '
    2 ||decode(id,0,'Cost = '||position) "Query Plan"
    3 from plan_table
    4 where statement_id='exp4';

    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT Cost = 220
    SORT GROUP BY
    HASH JOIN
    HASH JOIN
    TABLE ACCESS FULL M_PROJECT
    TABLE ACCESS FULL I_IMAGE

    Thanks
    PNRDBA

  10. #30
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    looks a small report to me, plus I dont think you query to get the plan table is correct to see parallel query plans

    you have to run

    ?/rdbms/admin/utlxplp.sql

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width