Browser Compare Reports in Peopleosoft

Browser Compare Reports in Peoplesoft

Accessing Browser Reports

To access a browser report double-click on the line in the output window containing "Browser Compare Report Created.”

PeopleSoft Compare Report Viewer

The Viewer menu contains menu options related to navigating within the report. The Definition Types menu enables you to dill down into the compare data for each definition type compared. The Definition Types menu only contains the definitions compared.

Sharing Browser Reports

PeopleSoft Application Designer writes the browser reports to a subdirectory named after the project name within the Report Output Directory specified on the General tab on the Options dialog. For example, if the Report Output Directory is c:\psreports and the project name is “PROJECT,” then the browser reports exist in c:\psreports\PROJECT. To view the compare results, double-click CompareViewer.html and then select the desired project name.

For each type of definition you compare, there is a subdirectory containing XML files storing the compare data for that definition type. For example, if you compared pages, the XML file containing compare data for the page compare exists in c:\psreports\PROJECT\Pages. These files can be used as source data for custom displays of the compare data. The PeopleSoft Compare Report Viewer uses these XML files along with various HTML and XSL files to present your compare data.

To share the browser reports, PeopleSoft recommends:

· Copying the entire high-level report output directory (containing all subdirectories, HTML, and XSL files) and copying it to a shared network drive.

· Compressing the entire directory and sending or posting individual copies as needed.

Article posted by

- Ganesh A.M

0 comments  

Understanding Compare reports status in peoplesoft

Understanding Compare reports status in peoplesoft

Determining Definition Status

PeopleSoft Application Designer defines the status of a definition in the source and the target database.

Unknown

Definition has not been compared. This is the default status for all definitions inserted manually into a project and the permanent status of all non-comparison definitions.

Absent

The definition was found in the other database, but not in this one. When upgrading to a new PeopleSoft release, all new PeopleSoft definitions should have Absent status in the target database and all of your custom definitions should have Absent in the source database.

Changed

The definition has been compared, the LASTUPDOPRID value is PPLSOFT, and the LASTUPDDTM value is greater than the date/time stamp of the comparison release database. This indicates that PeopleSoft modified the definition since the comparison release.

Unchanged

The definition has been compared, the LASTUPDOPRID value is PPLSOFT, and the LASTUPDDTM value is less than or equal to the date/time stamp of the comparison release database. This indicates that PeopleSoft modified the definition prior to the comparison release.

*Changed

The definition has been compared, the LASTUPDOPRID value is not PPLSOFT, and the LASTUPDDTM value is greater than the date/time stamp of the comparison release database. This indicates that a customer modified the definition since the comparison release.

*Unchanged

The definition has been compared, the LASTUPDOPRID value is not PPLSOFT, and the LASTUPDDTM value is less than or equal to the date/time stamp of the comparison release database. This indicates that a customer modified the definition prior to the comparison release.

Same

The definition has been compared and is defined the same in both databases. When a definition in one database has this status its counterpart in the other database will have the equivalent status. This status can be seen when performing a project comparison because with a project comparison the definitions are static; the project is not repopulated based on the comparison results. This status is not seen in a database comparison, because when doing so the project is populated only with definitions defined differently.

Note. Compare by release date is determined by finding the highest release label in common between the source and target databases. PSRELASE.RELEASELABEL column is searched on both source and target to find the latest common value between the two. Then PSRELEASE.PSRELEASEDTTM for that label is used for compare by release date for the source and target, respectively. You can override this value to set a different release label, or specific compare by date, on the Upgrade Options interface. Only common release labels (found on both source and target PSRELEASE table) can be used.

- Ganesh A.M

0 comments  

Visual Compare reports in Peoplesoft

Visual Compare reports in Peoplesoft

The PeopleSoft Application Designer displays a Property Value panel to show details about every difference or each changed page control. To the right, each page is displayed side-by-side enabling the visual comparison.

Note. The pages appear in a read-only state. You cannot make any changes to either page definition from the Visual Compare interface.

Determining Differences between Page Definitions

When there are differences between source and target page definitions, the system illustrates this in the Layout tab by surrounding the page field with a red box with a red 'X' at the top, left-hand corner. The field in which you have placed your cursor has a dotted line surrounding it.

On the Order tab, page controls that have differences are highlighted in red. Also, if you select a page control, you can see easily if its order is different as the highlighted areas do not match.

To launch a visual compare for pages:

1. Open the upgrade project, and make sure that you are in the Upgrade view.

2. Open the Pages window within the upgrade project.

3. Select the page(s) that you want to compare.

4. Right-click on the Pages window to display the context menu.

5. Select Visual Compare, and then select one of these options:

o From Database: select if you are comparing the current definitions with definitions in another database. After doing so, sign on to the appropriate database using the PeopleSoft Signon window.

o From File: select if you are comparing the current definitions with definitions in a file. After doing so, navigate to the appropriate file using the Visual Compare from File: Select Project dialog box.

-Ganesh A.M

0 comments  

Printing the online PeopleSoft Page

Printing the online PeopleSoft Page

To print a particular, we can make use of html area and JavaScript command (Window.print ()).

Steps to be followed:

1. Place an html area on the page.

2. Double click over the html area, property window opens. Choose the value as “constant” and enter the following code: <form><input type = "button" value="print" onclick="window.print () ;">< /input></form>

3. In the page, you will be able to see the print button. By clicking the button, Print dialog box will be opened.

- Ganesh A.M

0 comments  

Peoplecode automatic code formatting - Peoplecode, Behind the Scenes

Why Peoplecode automatically formats itself? - Peoplecode, Behind the Scenes

Just as with any other PeopleSoft object, a Peoplecode program is stored in the database. Table PSPCMPROG contains the actual code. Do a SELECT on this table and you will see that each program has a key that designates its event (Field Change, etc.) and the object (record or component, for example) to which it belongs.

The code itself is stored in a long character field called PROGTXT. Look at that and you will see "garbage". Is this compiled code? Not really--it's an intermediate form of the source code. Peoplecode is an interpreted language and is never actually compiled. When you change and save a PeopleCode program, it is immediately scanned and condensed. Variables are identified and flagged with a special character; extra whitespace is removed; numeric constants are found and translated to a consistent internal format; keywords are replaced with special one-byte codes; and so on. The result is no longer readable to us, but it is more efficient. Some of this code can be run dozens of times during one transaction. We don't want to have to wait as the program is interpreted each time.

We can now answer the question about automatic code formatting. Enter a statement and then either save the program or click on the syntax check (checkmark) button. At that time, the program is reformatted with appropriate indenting, the extra whitespace is removed, and standard capitalization of keywords is applied. This is not just a handy service provided by App Designer. Since the code has been compressed into a more efficient form, it's about the only way the code could be displayed. For example, if a keyword is replaced with a single one-byte code, then the way we entered it initially (all caps, all lower case, or mixed) is lost and it must be displayed using the standard capitalization. Occasionally we may want to format the code in a certain way, but in general, this automatic reformatting is a good thing.

You may occasionally have Peoplecode programs "go bad". An error message may report that the program is invalid. Even before clearing your cache, you might want to try this. Open the program in App Designer, make a small change (such as adding a space at the end of a line), and save. The program is then re-scanned. That often fixes the problem.

One last tip... You might see some custom code and wonder who wrote it and when. The record or component to which the code belongs has its own user ID and date, and that is what is displayed on the properties panel. I don't know of a way to find the user ID and date for a particular PeopleCode program through App Designer, but you can see it by doing a SELECT on PSPCMPROG. The ID and datestamp fields are stored right there with the program.

-Ganesh A.M

0 comments  

Manipulating Date on a Grid: Scroll Peoplecode

Manipulating Data on a Grid: Scroll Peoplecode

When you have a grid that is filled with data; data that you don’t want. How do you clear it out and then fill it with the data set that you do want? Well, here are the simple steps to do just that.

Lets say we have a Grid on a page and the main record on the grid is called COMPETENCIES. Well we would first want to define a Rowset object to manipulate. The code for this would look as follows:

Local Rowset &COMPETENCIES;

Next we will want to instantiate the object. Instantiate just means to represent by an instance. If you notice, I am not creating a new Rowset. I am getting the Rowset from the current grid called competencies, using the GetRowSet function. And, I am defining the Grid object as a Rowset.

&COMPETENCIES = &REVIEW(CurrentRowNumber(1)).GetRowset(Scroll.COMPETENCIES);

Once that is complete, I want to remove all rows of data from the Grid. To do that I simply use the Flush method.

&COMPETENCIES.Flush();

Now lets fill the grid with new data that we really want.

&COMPETENCIES.Select(Record.COMPETENCIES,”WHERE EMPLID = :1 AND EVALUATION_ID = :2 AND COMPETENCY IN ” &IN ” AND EFFDT = %DATEIN(:3)”, &EMPLID, &EVALUATION_ID, &REVIEW_DT);

What I am doing in the above line of code is selecting the data that I want to use to fill the grid. By selecting the record I am in essence asking to select all the fields that are on the grid from the COMPETENCIES table with the where clause.

Thats it! It’s that simple.

Article posted by

- Ganesh A.M

0 comments  

Inserting rows into record using Peoplecode

Inserting rows into record using Peoplecode with better performance

When inserting rows using Peoplecode, you can either use the Insert method with a record object, or create a SQL Insert statement using the SQL object.

• If you're doing a single insert, use the Record Insert method

• If you're in a loop, and therefore calling the insert more than once, use the SQL object.

Why? Because the SQL object uses dedicated cursors and if the database you're working with supports it, bulk insert.

A dedicated cursor means that the SQL only gets compiled once on the database, so People Tools only looks for the meta-SQL once. This can mean better performance.

For bulk insert, inserted rows are buffered and only sent to the database server when the buffer is full or a COMMIT occurs. This cuts down on the number of roundtrips to the database. Again, this can mean better performance.

The following is an example of using the record insert method:

&REC = CreateRecord (Record.GREG);

&REC.DESCR.Value = "Y" &I;

&REC.EMPLID.Value = &I;

&REC.Insert();

The following is an example using a SQL object for doing an insert:

&SQL = CreateSQL("%INSERT(:1)");

&REC = CreateRecord(Record.GREG);

&SQL.BulkMode = True;

For &I = 1 to 10

&REC.DESCR.Value = "Y" &I;

&REC.EMPLID.Value = &I;

&SQL.Execute(&REC);

-Ganesh A.M

1 comments  

Oracle 9i Architecture



Click on the image to view a larger picture...

0 comments  

ORACLE SYNTAX

Useful Site for Oracle Syntax:

http://www.iselfschooling.com/syntax/OracleSyntax.htm

Regards
Sasi Kumar. D

0 comments  

PeopleSoft - Request Flow

PeopleSoft Architecture - Internals Snapshot...

Request flow from WSH to DB & Vice Versa...


Request flow from JSH to DB & Vice Versa...

Click on the image to view a larger picture...

-Sandeep G

0 comments  

File layouts for inserting data in to multiple records

File layouts for inserting data into parent-child records. (File layouts for inserting data in to multiple records).

Define the file layout with the child record as sub-segment. In File layout segment properties define the file record id. A number that should appear as a first column in the input file to identify that it contains the data for its corresponding record.

Since we have more than one record, we need to set this file record id for the both records / segments.

888 – File record id - This record contains only one field and its value is A.

000 – File record id – Contains the data for the other record.

Sample file layout structure.

888 A

000 8001 VAC 1981-09-12 1981-09-26 14 .0 P Y

888 A

000 8001 VAC 1983-03-02 1983-03-07 5 .0 P Y

888 A

000 8001 VAC 1983-08-26 1983-09-10 13 .0 P Y

888 A

000 8516 MAT 1986-06-06 1986-08-01 56 .0 P Y

- Ganesh A.M

0 comments  

Syntax to find Total,Used and Free Space in Oracle Database

Syntax to Find Total Size of Database

Size in Megabytes:

Select sum(bytes)/1024/1024 from dba_data_files;

Syntax to Find Used Size of Database

Select sum(bytes)/1024/1024 from dba_segments;

Syntax to Find Free Size of Database

Select sum(bytes)/1024/1024 from dba_free_space;

Thanks

D.Sasi Kumar

0 comments  

Steps for Oracle Upgradation

10 steps for Upgrading Oracle from 8.1.7 to 10.1.0.2.0

  1. Install 10-G in new ORACLE_HOME path.
  2. Change to 10-G Path as below:

cd $ORACLE_HOME/rdbms/admin

copy utlu101i.sql to new dir

  1. In 8.1.7 database run utlu101i.sql, which gives the output about system requirements, the Parameter should be updated etc.

SQL> spool upgr.lst

SQL>@ utlu101i.sql;

SQL>spool off;

From the upgr.lst, note the changes to be made.

  1. Copy init file from 8.1.7 dbs to <newpath> make changes to init parameters as mentioned in upgr.lst
  2. Startup the database in upgrade mode:

SQL> startup pfile=’<newpath>/inittest.ora’ upgrade;

  1. Create sysaux tablespace

SQL> create tablespace sysaux

Datafile ‘<newpath>/sysaux.dbf’ size 500M

Extent management local

Segment space management auto online;

  1. Run the upgrade script.

SQL> @ ‘$ORACLE_HOME/rdbms/admin/u0801070.sql’;

  1. Shutdown the database.

SQL>shutdown immediate;

  1. Startup the database

SQL> startup;

  1. Recompile the Invalid objects:

SQL> @’$ORACLE_HOME/rdbms/admin/utlrp.sql’

Thanks and Regards

D.Sasi kumar

0 comments  

How to insert data with single qoute

INSERT INTO TABLE - SINGLE QUOTE

If a field has a ' (single quote) in it, it will be considered as an end of string when appended right? How do we prevent this?

INSERT INTO TABLE VALUES ('DE''SE'); -> This will insert values DE’SE into the database…

One More Scenario:

Generally in Oracle you also have issue when you are trying to INSERT –

INSERT INTO TABLE VALUES ('DE&P'); - Coz “&” is used for Substitution…

Solution: INSERT INTO TABLE VALUES ('DE\&P'); - “\” ill act as escape char…

Else we can use SET SCAN OFF and the INSERT INTO TABLE VALUES ('DE&P'); -> In this case no escape sequence is required…


0 comments  

Peoplesoft - Process Status

The below few lines will help us know the Process Status of a process that is currently active...

1. Use the below SQL to correctly identify the Oracle session of a currently active process.

SELECT CLIENT_INFO, PROGRAM, SID, SERIAL#, SQL_ADDRESS FROM V$SESSION WHERE CLIENT_INFO LIKE 'SAN%' AND STATUS = 'ACTIVE';

2. We can use the SID from the above SQL in the below SQL statement to get the SQL being executed currently ...

SELECT SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID= 98) ORDER BY PIECE;

Also, there are scenarios where the session might not be executing any SQL because it is busy executing others stuff in Code, or else it might be in the Queued or Posting State...

0 comments  

PeopleSoft - AE Trace

To determine all the AE programs that have TRACE enabled...

We some time enable the trace and forget about it, if incase you need to verify the TRACE setting for Application Engine, the below SQL will be helpful:

SELECT PRCSNAME, PARMLIST FROM PS_PRCSDEFN WHERE UPPER(PARMLIST) LIKE '%TRACE%' AND PRCSTYPE = 'Application Engine';

0 comments  

PeopleCode debugging - PSDBGSRV

Setting up the Application Server for Debugging:

To set up your application server for PeopleCode debugging the minimum requirements are:

1. The application server domain requires at least two PSAPPSRV server processes.

2. The application server must have Enable Debugging turned on in PSADMIN. - This can done while booting the application server:

Do you want to enable PeopleCode Debugging (PSDBGSRV) (y/n)? [n]:y

For the debugger to work, it has to run in parallel with the application it's debugging. Suppose your domain only has one PSAPPSRV server process running. In this case, the PSAPPSRV can process the requests of only one component at a time, and therefore debugging is not possible.
However, provided that you have two PSAPPSRV server processes running, one PSAPPSRV handles the debugger program while the other handles the application you're stepping through with the debugger. In this case, the two programs run in parallel and provide interactive debugging.

Make sure to start two PSAPPSRV processes prior to debugging. To do this, in PSADMIN set the Min Instances parameter in the PSAPPSRV section to 2. For example,
Min Instances=2

It is also important to set the Service Timeout parameter for PSAPPSRV to zero. For example,
Service Timeout=0
Disabling service timeouts prevents the application server processes from timing out if you stop at a particular point in your program while debugging.

0 comments  

About : Trigger Creation

Try to answer the following question.

Is it possible to create a trigger using “CREATE OR REPLACE TRIGGER TRIGGER_NAME”, where the trigger_name is already being used by another trigger under some other table ?. If your answer is YES, then this article may help you.

In general we used to say that, if we create a trigger using Create or replace statement, then the trigger will be altered with the new trigger code. But if use CREATE OR REPLACE statement to create a trigger and if the trigger name is already being used by a trigger under another table, then ORACLE will raise an ORA-04095 exception.

The following code will give you some more clarity on the above explanation:

Table 1 Creation :

create table trig1(id number(3));

Trigger 1 creation for the above table :

create or replace trigger trg_trig1

before insert on trig1

begin

null;

end;

/

Table 2 Creation :

create table trig1(id number(3));

Trigger 2 creation for the above table :

create or replace trigger trg_trig1

before insert on trig2

begin

null;

end;

/

create or replace trigger trg_trig1

*

ERROR at line 1:

ORA-04095: trigger 'TRG_TRIG1' already exists on another table, cannot replace

It

Explaination :

Thus the above code creates two table called trig1 and trig2. Trig1 has a before insert trigger called trg_trig1.Then we have the code for trg_trig1 trigger on trig2. Oracle doesn’t allow you to create the trigger by raising ORA-04095 exception.

Thanks

Vivek Member – C9

0 comments  

About - Mutating and Constraining table in Oracle

I would like to share, few more details on “Mutating and Constraining table in Oracle” written by Sasi. Mutating error won’t occur when we go for statement level trigger. In general, it is said, that Mutating error will occur, if we refer the mutating table at the trigger body, in a row level trigger. There is an exceptional case, where the mutating error won’t occur, if we refer the mutating table in the trigger body. It is for before insert, at row level. The concept here is, if we perform a single row insert (by using Values clause), and if we refer the same table (the mutating table) at the trigger body, then we won’t get any mutating table error. Also the insert should be a single row one. So if you perform an multiple row insert(say : insert into tableA select * from table B), then Mutating error will occur. Also if multiple row insert statement, inserts only one record, we will get the mutating error. So in general, only for the before insert statement (with single row) at row level, won’t raise the mutating error.

The following table will explain you about the occurrences of mutating error.

-----------------------------------------------------------------

OPERATION TYPE MUTATING?

-----------------------------------------------------------------

insert before/statement-level No

insert after/statement-level No

update before/statement-level No

update after/statement-level No

delete before/statement-level No

delete after/statement-level No

insert before/row-level Single row Multi-row

No Yes

insert after/row-level Yes

update before/row-level Yes

update after/row-level Yes

delete before/row-level Yes

delete after/row-level Yes

-----------------------------------------------------------------

Example :

Table Creation :

create table tab_trig(id number(3));

Trigger Creation:

create or replace trigger trig1

before insert on tab_trig

for each row

declare

v_mx tab_trig.id%type;

begin

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value : 'v_mx);

update tab_trig set id=:new.id+7;

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value After Incrementing Id by 7 : 'v_mx);

update tab_trig set id=:new.id-7;

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value After Decrementing Id by 7: 'v_mx);

end;

/

Sample Execution:

SQL> insert into tab_trig values(&id);

Enter value for id: 1

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(1)

Max Value : 0

Max Value After Incrementing Id by 7 : 0

Max Value After Decrementing Id by 7: 0

1 row created.

SQL> /

Enter value for id: 2

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(2)

Max Value : 1

Max Value After Incrementing Id by 7 : 9

Max Value After Decrementing Id by 7: -5

1 row created.

SQL> /

Enter value for id: 3

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(3)

Max Value : 2

Max Value After Incrementing Id by 7 : 10

Max Value After Decrementing Id by 7: -4

1 row created.

Thanks

Vivek Member - Cloud Nine

0 comments  

PeopleSoft - psprcsrv.env

To run processes using software that is not supplied by PeopleSoft

Directly editing the PSPRCSRV.ENV file to update the PATH variable will not persist if you re-configure PRCS, hence follow the below method to add the path info to PATH variable...

Add To Path

The Add To Path parameter in the Tuxedo Settings section of the PeopleSoft Process Scheduler configuration file is set with all of the libraries that are needed to run the executables that are delivered with PeopleSoft. If you plan to run processes using software that is not supplied by PeopleSoft, include all of the directory paths that are needed to run the process through PeopleSoft Process Scheduler successfully. Re-create the Tuxedo configuration as noted previously.

You can verify the current library path that is included in the appropriate environment variable by browsing the contents of the PSPRCSRV.ENV file.

Note. Be sure to enter a full path in the Add To Path parameter in the Process Scheduler configuration file. For example, you may enter /bin:/usr/bin

0 comments  

New Features of 11g Oracle Database

Oracle Database 11g Top New Features:

Database Replay

Explore Database Replay, the new tool that captures SQL statements and lets you replay them at will.

Transaction Management

Get an introduction to Flashback Data Archive and explore Enterprise Manager's Log Miner interface.

SQL Plan Management

Use bind variables that pick the right plan every time and ensure a new execution plan is perfect before it's used.

SQL Access Advisor

Get advice about optimal table design based on actual use of the table, not just data.

RMAN

Explore Data Recovery Advisor, do parallel backup of the same file, and create and manage virtual catalogs.

Automatic Storage Management

Learn about new SYSASM role, variable extent sizes, and other ASM improvements

Schema Management

Add columns with a default value easily and explore invisible indexes, virtual columns, and read only tables.

SQL Performance Analyzer

Accurately assess the impact of rewriting of SQL statements and get suggested improvements.

Manageability

Explore automatic memory management, multicolumn statistics, online patching, and more features.

PL/SQL: Efficient Coding

Triggers that fire several times at different events and ability to force triggers of the same type to follow a sequence are some new gems.

Thanks

D.Sasi Kumar

0 comments  

Oracle - Query Parameter in EXP

How to use Query Parameter in exp command line:

When your column in the where clause is of TYPE VARCHAR

exp file=partial1.dmp log=partial2.log tables=PS_JOB,PS_JOB_JR,PS_COMPENSATION,PS_AS_AUDIT_JOB query=\"where emplid=\'220627489\'\" userid=scott/tiger

When your column in the where clause is of TYPE NUMBER

exp file=partial1.dmp log=partial2.log tables=PS_JOB,PS_JOB_JR,PS_COMPENSATION,PS_AS_AUDIT_JOB query=\"where empno=10\" userid=scott/tiger

Thanks

D.Sasi Kumar

0 comments  

Oracle Hints

Some of Frequently Used Oracle Hints:

Hints

Description

ALL_ROWS

The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption)

FIRST_ROWS

The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

This hint causes the optimizer to make these choices:

  • If an index scan is available, the optimizer may choose it over a full table scan.
  • If an index scan is available, the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.

If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation.

FULL

The FULL hint explicitly chooses a full table scan for the specified table.

INDEX

The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B*-tree, and bitmap indexes.

USE_NL

The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table

USE_MERGE

The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join

USE_HASH

The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join

PARALLEL

The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion. If any parallel restrictions are violated, the hint is ignored.

NOPARALLEL

You can use the NOPARALLEL hint to override a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.

Thanks

D.Sasi Kumar

0 comments  

PeopleSoft - Query PSQRYDEFN

How to check if a Query is Public or Specific to user?
Select * from psqrydefn where qryname = 'your query'
From the above select, is OPRID column blanks? If it is not, then it is not a public query and only the operator listed for the row can see the query. Make it a public query and everyone can see it.
To find out all of the records being used in the Query use the below query...
SELECT * FROM PSQRYRECORD WHERE QRYNAME = 'name of query trying to find' ;

0 comments  

MY DATABASE IS SLOW, WHAT SHOULD I DO

PURPOSE

This Article is designed to allow someone with little or no experience in database tuning to quickly tune an Oracle Database.

SCOPE & APPLICATION

The intended audience of this Article is anyone responsible for the tuning of an Oracle database. The intent of this article is to provide a step by step process for checking the most common performance problem areas and a way of correcting the problem

MY DATABASE IS SLOW, WHAT SHOULD I DO

The following steps will allow you to quickly analyze and/or fix most tuning problems associated with an Oracle Database.

The steps are ordered in such a way to find problems quickly.

Log Switches are costly

When Oracle performs a log switch a number actions are performed that are costly from a system resource standpoint. If Oracle is forced to switch between log files too frequently then the Oracle database is slowed down unnecessarily. The allocation of larger log files solves this problem.

SQL Statements must be tuned

Any SQL statement that is executed against the database has a cost in terms of resources used to complete the query. The most costly resource from a performance standpoint is disk i/o. Therefore any SQL statements that consume a large amount of disk i/o should be reviewed to insure that the i/o is necessary and that there is not another way to accomplish the same task with less i/o. For instance, an index may need to be defined on the table.

Sorts to disk are slow

All database systems seek to reduce disk i/o as much as possible by doing as much in memory as possible. Disk i/o's are significantly slower than memory access. Therefore if sorts are being done to disk they are not only much slower but are also competing with other oracle i/o operations.

The Cost Based Optimizer cannot optimize queries against tables that have not been analyzed.

If the blocks column of the dba_tables query is null or 0 then the table in question has not been analyzed. When this is the case the Cost Based Optimizer does not have any data in order to determine the best type of optimization for queries against the table. This can result in queries that consume much more system resource than is necessary, usually in the form of disk i/o.

Oracle can suffer from self inflicted memory starvation

When the Oracle software is executing and providing access to the data in the database it requires system resources. There are four types of system resources provided to Oracle by the hardware/operating system. Memory, CPU cycles, Disk Bandwidth and Network Bandwidth. DB_BLOCK_BUFFERS and SHARED_POOL_SIZE are examples of Oracle configuration parameters that may artificially starve Oracle for access to memory.

Oracle requires resources to perform a given workload

When the Oracle software is executing and providing access to the data in the database it requires system resources. There are four types of system resources provided to Oracle by the hardware/operating system. Memory, CPU cycles, Disk Bandwidth and Network Bandwidth. Because Oracle may be running on a system that has other software running on it, it is possible that the Oracle software is being starved for some type of system resource. Even if the Oracle software and database are tuned perfectly the database may still be slow. This can happen when the system is overloaded and one of the system resources is in short supply. Typically the system administrator finds these problems and deals with them but it is essential that the DBA is aware of the possibilities in the event that a performance problem is associated with system configuration.

Thanks

D.Sasi Kumar

0 comments  

Oracle - About Inserting Date Values

We all know, that the default format support by Oracle (from 9i) is DD-Mon-RR.

Does this mean, we need to give the same format, while inserting records to the table?.

If your answer is Yes, then continue reading the article.

In general, oracle assumes that the date needs to be given in dd, followed by Mon followed by RR/YYYY.

So in between the data components, we are allowed to use any character. So the following insert statements

will insert records in to the table dat1(with 1 column, called col1 of type date), without any error.

Note: FYI, In all the cases, to_date( ) function is not used.

insert into dat1 values('12?Jan?2007')

insert into dat1 values('12/Jan?2007')

insert into dat1 values('12!Jan#2007')

insert into dat1 values('12$jan%2007')

insert into dat1 values('12^Jan&2007')

insert into dat1 values('12/Jan?2007')

SQL> select * from dat1;

COL1

---------

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

6 rows selected.

Thanks & Regards

Vivek Member - Cloud Nine

0 comments