Home » RDBMS Server » Performance Tuning » MR (Metadata Repository) SQL caused poor performance (Oracle Portal 10g, OID, IAS, Windows x86_64)
MR (Metadata Repository) SQL caused poor performance [message #655866] Wed, 14 September 2016 23:20 Go to next message
trantuananh24hg
Messages: 724
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
MR (Metadata Repository) SQL caused poor performance

The Problem:
Somedays ago, I had got a problem case on Portal Database which installed on Windows x86_64 platform, along to OAS and OID in a same server.

The performance was really poor, Database Time was 7 times to Real Time. This problem was fix when I defined the db_cache_size in Portal Database was small
, accroding to ID 1299268.1.

Now, the enq-TX lock was gone, but mod_plsql-generated is remain, it's called by APEX - OWA toolkit from Metadata Repository
declare
rc__ number;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
begin
owa.init_cgi_env(:n__,:nm__,:v__);
htp.HTBUF_LEN := 63;
null;
null;
simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%';
simple_list__(3) := 'utl\_%';
simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%';
simple_list__(7) := 'htf.%';
simple_list__(8) := 'wpg_docload.%';
if ((owa_match.match_pattern(p_string =>
'portal.wwsec_app_priv.process_signon'
/* */,p_simple_pattern =>
simple_list__
,p_complex_pattern =>
complex_list__
,p_use_special_chars =>
false)))
then
rc__ := 2;
else
null;
PORTAL.wpg_session.init();
portal.wwsec_app_priv.process_signon(urlc=>:urlc);
if (wpg_docload.is_file_download) then
rc__ := 1;
wpg_docload.get_download_file(:doc_info);
PORTAL.wpg_session.deinit();
null;
null;
commit;
else
rc__ := 0;
PORTAL.wpg_session.deinit();
null;
null;
commit;
owa.get_page(:data__,:ndata__);
end if;
end if;
:rc__ := rc__;
end;

2. What is this, where is this?
What is this?
The SQL is generated
- When a search for a text string is performed specifying a page and checking 'include subpages', a timeout in the search portlet occurs.
- When a search for that same text string is performed not specifying a page (thereby searching the entire page group), no timeout in the search portlet occurs, and the results are displayed correctly.
The problem occurs with both the custom and advanced search portlets.
The problem may also be seen when using a custom search portlet based on the API WWSRC_API.ITEM_SEARCH.

Where is this?
This issue has been seen when a PL/SQL application is accessed from an Oracle HTTP Server using mod_plsql. Processing occurrs in the Database and a page returned to a client browser.

3. Impact to performance Database?
It cause lot of buffer get, cpu_time and execution_time, exactly, this sql is on Top of AWR.
Example from AWR during 3h:
Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
179,539,030	2,392,328	75.05	50.40	18544.93	19925.38	b3z0tgfh645ff	java.exe	declare rc__ number; simple_...

4. Some poor event walked along to it:
One of poor event is enq: TM - contention, log file parallel write, execute to parse % ratio, and CPU time, all of them took the Database in warning. What should I do?
4.1. Solving the arround problems
- Log file parallel write and enq: TM - contention might not be the root cause, but they absolutely drived the performance into dark zone. Log file parallel write event
appeared when many reasons, but our Portal Database was configured by file-system with RAID-5. Yes, I have just moved all of Redo Log File to RAID-1 volume. The wait
event decreased total waits.
- enq: TM - contention and execute to parse % ratio: The enq: TM-contention waits common generally related to the application code being executed and do not indicate a problem with the DB itself.
One of Database relationship situation to this event is locking and Referential Integrity (ID 33453.1). Of course, I had to verify all of constraints to WWW_x_$ visible. It's not problem.
- execute to parse % ratio is the exciting in my case,
sys@ORCL> select count(distinct sql_id), count(*) from v$sql_shared_cursor;

COUNT(DISTINCTSQL_ID)   COUNT(*)
--------------------- ----------
                 2622       3100
And I know, I cannot decrease the number of parses unless and until the Dev fix the application code.
The application calls prepare statement - and Oracle does it. Oracle does it's work, it is not killer, it's victim.

Look at the cursors parameters, I saw those set to (10g): FORCE, 4000 open_cursors, 1500 cached_cursors, and, more important, there are not Dev's code in top AWR
4.2. The main problem?
Instance acitivity is arround
-----------------------------------------------------------------------
Active% | SQL_ID          | EVENT                     | WAIT_CLASS
-----------------------------------------------------------------------
    62% | b3z0tgfh645ff   | ON CPU                    | ON CPU
    14% | 0mshmwd06njdb   | ON CPU                    | ON CPU
     9% |                 | ON CPU                    | ON CPU
     5% | 4dqasqjrvudqn   | ON CPU                    | ON CPU
     4% | 5tcz20jqrqung   | ON CPU                    | ON CPU
     3% | cgnura58b4pqd   | ON CPU                    | ON CPU
     3% | cksxakxbx54kc   | ON CPU                    | ON CPU
     1% | 9zw45vdttwhzv   | ON CPU                    | ON CPU
     1% | fz8qkd001q5kp   | ON CPU                    | ON CPU
     1% | 68sc60g5z8jxa   | ON CPU                    | ON CPU

SQL_TEXT                     SQL_ID        EXECUTIONS PARSE_CALLS ELAP_EXEC
---------------------------- ------------- ---------- ----------- ---------
declare  rc__ number;  simpl b3z0tgfh645ff    2730744     2731575     22858
e_list__ owa_util.vc_arr;  c
omplex_list__ owa_util.vc_ar
r; begin  owa.init_cgi_env(:
n__,:nm__,:v__);  htp.HTBUF_
LEN := 63;  null;  null;  si
mple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%
';  simple_list__(3) := 'utl
\_%';  simple_list__(4) := '
owa\_%';  simple_list__(5) :
= 'owa.%';  simple_list__(6)
 := 'htp.%';  simple_list__(
7) := 'htf.%';  simple_list_
_(8) := 'wpg_docload.%';  if
 ((owa_match.match_pattern(p
_string =>    'portal.wwdoc_
process.process_download'
 /* */,p_simple_pattern =>
  simple_list__    ,p_comple
x_pattern =>    complex_list
__    ,p_use_special_chars =
>    false)))   then   rc__
:= 2;  else   null;   PORTAL
.wpg_session.init();   porta
l.wwdoc_process.process_down
load;   if (wpg_docload.is_f
ile_download) then    rc__ :
= 1;    wpg_docload.get_down
load_file(:doc_info);    POR
TAL.wpg_session.deinit();
 null;    null;    commit;
 else    rc__ := 0;    PORTA
L.wpg_session.deinit();    n
ull;    null;    commit;
owa.get_page(:data__

Continue to next post ...
Please wait

[Updated on: Wed, 14 September 2016 23:20]

Report message to a moderator

Re: MR (Metadata Repository) SQL caused poor performance [message #655867 is a reply to message #655866] Wed, 14 September 2016 23:45 Go to previous messageGo to next message
trantuananh24hg
Messages: 724
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
5. Appoarch to define the problem
1532032.1, Metalink give me a Doc ID, and wrote
Quote:

Oracle Database is crashing periodically with the following errors in the Database alert log:

ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn3
ORA-04030 / ORA-04031 Out of Process Memory
First appoarch, My Portal Database was not down, it just only slow, it had not got those error, however, I continued to see
Quote:

This issue has been seen when a PL/SQL application is accessed from an Oracle HTTP Server using mod_plsql. Processing occurrs in the Database and a page returned to a client browser. Resulting trace files conclude that it is the HTP code blocks from the PL/SQL Web Toolkit are implicated and a common fact is revealing htp.htbuf_len is reaching a value of -1

declare
rc__ number;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
begin
owa.init_cgi_env(:n__,:nm__,:v__);
htp.HTBUF_LEN := -1;
PL/SQL table used for output buffering HTBUF_LEN number :=

Quote:

This issue could not be reproduced by Oracle. It appears to need a specific load condition in order to reproduce. It has reproduced on multiple customer systems where heavier load is applied either from a load balancer or simulated with a load tool such as Load Runner.
Yes, heavily loading, but in my case, it did not cause my server going to down by memory lossing, it cause buffer get - Logical I/O, the I/O is the main problem.
But why was I/O with high CPU time?

Recently, I read the information from Han's blog:
Because only one CPU can access a particular RAM address at a given time, all other CPUs have to wait. This kind of issues can't be resolved by big/better hardware. 
The more powerful of the host, the worse it gets. For 4 CPUs, 3 have to wait. For 8 CPUs, 7 have to wait, For 16 CPU, 15 have to wait. 
While the CPUs are waiting, they are spinning (controlled by a hidden parameter _spin_count in Oracle). 
Spinning means using 100% CPU doing nothing! 

The solution is to move the above images from database tier to middle tier.

When the image is fetched from database, the PC can't cache it because the PC does not know if the file has changed since last fetch. 
When the image is fetched from Apache, it will be cached in the PC's RAM until the file on Apache server is changed.

After we move the above images into Apache, we will never need to run the culprit API to fetch these files, the workload on database tier will be reduced dramatically. 
As the images are cached in the PC's RAM, the workload on middle tier will be reduced largely as well.

It has made me a lightning bolt, but I have not ever worked neither to Portal nor APEX, I need some advice to move the image from Database to Application.

Thank you.
Re: MR (Metadata Repository) SQL caused poor performance [message #655868 is a reply to message #655867] Wed, 14 September 2016 23:50 Go to previous messageGo to next message
trantuananh24hg
Messages: 724
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Adding the information of the constraints in solving the enq: TM - contention 4.1
sys@ORCL> SELECT
  2      A.TABLE_NAME table_name,
  3      A.CONSTRAINT_NAME key_name,
  4      B.TABLE_NAME referencing_table,
  5      B.CONSTRAINT_NAME foreign_key_name,
  6      B.STATUS fk_status
  7    FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B
  8    WHERE
  9      A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and
 10      B.CONSTRAINT_TYPE = 'R'
 11    ORDER BY 1, 2, 3, 4;
TABLE_NAME           KEY_NAME       REFERENCING_TABLE    FOREIGN_KEY_NA FK_STATU
-------------------- -------------- -------------------- -------------- --------
HS$_BASE_CAPS        HS$_BASE_CAPS_ HS$_CLASS_CAPS       HS$_CLASS_CAPS ENABLED
                     PK                                  _FK2

HS$_BASE_CAPS        HS$_BASE_CAPS_ HS$_INST_CAPS        HS$_INST_CAPS_ ENABLED
                     PK                                  FK2

HS$_BASE_DD          HS$_BASE_DD_PK HS$_CLASS_DD         HS$_CLASS_DD_F ENABLED
                                                         K2

HS$_BASE_DD          HS$_BASE_DD_PK HS$_INST_DD          HS$_INST_DD_FK ENABLED
                                                         2


TABLE_NAME           KEY_NAME       REFERENCING_TABLE    FOREIGN_KEY_NA FK_STATU
-------------------- -------------- -------------------- -------------- --------
HS$_FDS_CLASS        HS$_FDS_CLASS_ HS$_CLASS_CAPS       HS$_CLASS_CAPS ENABLED
                     PK                                  _FK1

HS$_FDS_CLASS        HS$_FDS_CLASS_ HS$_CLASS_DD         HS$_CLASS_DD_F ENABLED
                     PK                                  K1

HS$_FDS_CLASS        HS$_FDS_CLASS_ HS$_CLASS_INIT       HS$_CLASS_INIT ENABLED
                     PK                                  _FK1

HS$_FDS_CLASS        HS$_FDS_CLASS_ HS$_FDS_INST         HS$_FDS_INST_F ENABLED
                     PK                                  K1


TABLE_NAME           KEY_NAME       REFERENCING_TABLE    FOREIGN_KEY_NA FK_STATU
-------------------- -------------- -------------------- -------------- --------
HS$_FDS_INST         HS$_FDS_INST_P HS$_INST_CAPS        HS$_INST_CAPS_ ENABLED
                     K                                   FK1

HS$_FDS_INST         HS$_FDS_INST_P HS$_INST_DD          HS$_INST_DD_FK ENABLED
                     K                                   1

HS$_FDS_INST         HS$_FDS_INST_P HS$_INST_INIT        HS$_INST_INIT_ ENABLED
                     K                                   FK1

OLAP_OLEDB_MDPROPVAL MDPROPVAL_PK   OLAP_OLEDB_MDPROPS   MDPROPVAL_FK   ENABLED
S


TABLE_NAME           KEY_NAME       REFERENCING_TABLE    FOREIGN_KEY_NA FK_STATU
-------------------- -------------- -------------------- -------------- --------
REGISTRY$            REGISTRY_PK    REGISTRY$            REGISTRY_PAREN ENABLED
                                                         T_FK

REGISTRY$            REGISTRY_PK    REGISTRY$SCHEMAS     REGISTRY_SCHEM ENABLED
                                                         A_FK

TRANSFORMATIONS$     SYS_C001313    ATTRIBUTE_TRANSFORMA ATTRIBUTE_TRAN ENABLED
                                    TIONS$               SFORMATIONS_FK

WRM$_DATABASE_INSTAN WRM$_DATABASE_ WRM$_SNAPSHOT        WRM$_SNAPSHOT_ ENABLED
CE                   INSTANCE_PK                         FK


16 rows selected.
Re: MR (Metadata Repository) SQL caused poor performance [message #655876 is a reply to message #655868] Thu, 15 September 2016 02:11 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
You have provided such a mass of information that I cannot see what the problem your users are actually facing is. However, I see two issues that I would fix straight away:
First,
Quote:
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn3
ORA-04030 / ORA-04031 Out of Process Memory
It seems that you are hitting some OS limit on memory usage. That message should be precise enough to find something in MOS, or to raise a TAR.

Second,
Quote:
it's called by APEX - OWA toolkit from Metadata Repository
If you are running APEX, you probably have an old version. I always uprade APEX to the latest (which is 5.0.4 at the moment) that may solve many performance problems.

Re: MR (Metadata Repository) SQL caused poor performance [message #655888 is a reply to message #655876] Thu, 15 September 2016 04:48 Go to previous messageGo to next message
trantuananh24hg
Messages: 724
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, John.

In a seeking period time to troubleshoot the problem, I saw a description from Oracle Support, ID 1532032.1. In which - the ID 1532032.1 descriptor, those error
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn3
ORA-04030 / ORA-04031 Out of Process Memory

would be occurred when HTBUF_LEN := -1 and took the server gone to overload memory. Exactly, the HTBUF_LEN pl/sql table invoke DBMS_OUTPUT (BLOB,CLOB) and Oracle Support suggest to rewirte another procedure with other value to -1.

But, my Portal Database Server did not meet those error, the server is Windows x86_64, and I did not limit anything. Of course, the solution did not affect to my Portal Database situation.

However, when reading the symptoms and cause in ID 1532032.1, in which, has got some information
...
It has reproduced on multiple customer systems where heavier load is applied either from a load balancer or simulated with a load tool such as Load Runner.
...

The bold words heavier load took me to another thinking:
- The main problem SQL in top above had got a function: search portlet based on the API WWSRC_API.ITEM_SEARCH
- The SQL had got many buffer gets - Logical I/O, CPU Time and executions
- The SQL calls to invoke image from Application, therefore, client can not cache image into their PC's. So, I think, it will faster when move the image from Database into App.

Thank you
Re: MR (Metadata Repository) SQL caused poor performance [message #655908 is a reply to message #655888] Thu, 15 September 2016 22:54 Go to previous message
trantuananh24hg
Messages: 724
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
John, I think I have got a better solution from my case. It comes from Doc ID 461237.1 - metalink.

Thank to response
Previous Topic: Improve query execution times
Next Topic: What should i do for tuning this query
Goto Forum:
  


Current Time: Thu Oct 18 21:14:10 CDT 2018