| SQL too slow [message #398305] |
Thu, 16 April 2009 00:04  |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Hi
I have installed 10g on core2duo machine with 2gb ram. When I connect and run any query through SQL, it slowly displays the data. I wonder why it is so, while on other such machines in my office its(SQL) performance is toooo much fast. Please give me any solution before I reinstall Oracle.
Note: Other forms and reports run quickly and I feel no performance issue.
Thanks
Rz
|
|
|
|
| Re: SQL too slow [message #398418 is a reply to message #398305] |
Thu, 16 April 2009 03:49  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
| rzkhan wrote on Thu, 16 April 2009 12:04 | Hi
I have installed 10g on core2duo machine with 2gb ram. When I connect and run any query through SQL, it slowly displays the data. I wonder why it is so, while on other such machines in my office its(SQL) performance is toooo much fast. Please give me any solution before I reinstall Oracle.
Note: Other forms and reports run quickly and I feel no performance issue.
Thanks
Rz
|
You did not post the original statement (at your office's machine) & the statement in your machine.
You did not post the output of explain plan/tkprof.
You did not describe the relational objects which invoked/retrieved/called data in your statement.
...
|
|
|
|
| Re: SQL too slow [message #398508 is a reply to message #398305] |
Thu, 16 April 2009 08:46 |
ahudspith
Messages: 26
Registered: January 2009
Location: Avoiding the tax man.
|
Junior Member |
|
|
Based on the ZERO information you have provided it is difficult to give an answer.
However I suspect that it is server parameter related.
Your PGA may be too small.
The optimizer could be in an inappropiate mode.
Parallel_max_servers could be wrong.
sort_area_size may be inappropiate...
Plus any other one from a million things.
Nobody can answer this question.
I suggest that you check for differences in the two databases SPFILE and copy the settings from the quick one to the slow one.
If that doesnt help then look at the query being executed - explain it.
In fact - you may also consider reanalyzing all the objects in the database whilst your about it (inclusing SYSTEM_STATS).
Cheers.
|
|
|
|
| Re: SQL too slow [message #398605 is a reply to message #398508] |
Thu, 16 April 2009 21:43 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Sorry for replying late. Here is some sample output with explain plan.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12
18)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
=1218)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
please suggest any solution in light of above ...
|
|
|
|
| Re: SQL too slow [message #398606 is a reply to message #398605] |
Thu, 16 April 2009 21:52 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
If you are looking for correct answer, you should post the correct information.
P/S: Post the first execution of statement, do not post the second result if the statement is a single query.
The one is nothing
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
Prove it:
scott@META> set autotrace traceonly
scott@META> select * from emp;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
447 recursive calls
0 db block gets
82 consistent gets
13 physical reads
0 redo size
1363 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
14 rows processed
scott@META> /
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1363 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
scott@META> alter system flush shared_pool;
System altered.
scott@META> select * from emp;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
447 recursive calls
0 db block gets
83 consistent gets
0 physical reads
0 redo size
1363 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
14 rows processed
scott@META>
14 rows did not cause performance running slowly!
|
|
|
|
| Re: SQL too slow [message #398609 is a reply to message #398305] |
Thu, 16 April 2009 22:30 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Here is at my machine
SQL> set autotrace traceonly
SQL> select * from emp;
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12
18)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
=1218)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> /
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12
18)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
=1218)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> alter system flush shared_pool;
System altered.
SQL> select * from emp;
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12
18)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
=1218)
Statistics
----------------------------------------------------------
464 recursive calls
0 db block gets
83 consistent gets
2 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
|
|
|
|
|
|
|
|
| Re: SQL too slow [message #398769 is a reply to message #398305] |
Fri, 17 April 2009 08:47 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>every row scroll down very slowly
Scroll? Scroll for 14 lines?
Do not (ab)use GUI & no problem will exist/occur.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
