Jedno zapytanie - dwa różne plany wykonań? Jak to możliwe?
Jedno zapytanie uruchomione w bloku anonimowym, drugie bezpośrednio z SQL Developera.
Testy przeprowadziłam na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.
Tabela EMP_PUZZLE - kopia tabeli EMPLOYEES ze schematu HR.
Test 1:
EXPLAIN PLAN FOR SELECT * FROM emp_puzzle WHERE employee_id = :EMP_ID; SELECT * FROM table(DBMS_XPLAN.DISPLAY);
EXPLAIN PLAN:
Plan hash value: 2996013062 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 72 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP_PUZZLE | 1 | 72 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | EMPLOYEE_ID_EMP_PUZZLE_PK | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=:EMP_ID)
Test 2:
DECLARE nEmp_id number; recEmp_puzzle emp_puzzle%rowtype ; begin nEmp_id := 107; SELECT * into recEmp_puzzle FROM emp_puzzle where employee_id = nEmp_id; end; /
Trace:
SQL ID: cpzb79fuy3a0s Plan Hash: 1175338861 SELECT * FROM EMP_PUZZLE WHERE EMPLOYEE_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 6 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 6 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 102 (HR) (recursive depth: 1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 TABLE ACCESS FULL EMP_PUZZLE (cr=6 pr=0 pw=0 time=78 us starts=1 cost=3 size=72 card=1)
Jakieś pomysły?* * * Rozwiązanie * * *Na trop rozwiązania zagadki naprowadziło mnie uruchomienie i przeanalizowanie jeszcze jednej wersji zapytania.
Test 3:EXPLAIN PLAN FOR SELECT * FROM emp_puzzle WHERE employee_id = '107'; SELECT * FROM table(DBMS_XPLAN.DISPLAY);Plan hash value: 2996013062 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 72 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP_PUZZLE | 1 | 72 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | EMPLOYEE_ID_EMP_PUZZLE_PK | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"='107')
Ten wynik już mnie kompletnie zaskoczył. Dlaczego, przy wyszukiwaniu po wartości typu VARCHAR2 zapytanie korzysta z indexu, a przy zmiennej typu NUMBER nie? Przecież kolumna EMPLOYEE_ID, na którym założony został klucz główny (PRIMARY KEY EMPLOYEE_ID_EMP_PUZZLE_PK) jest typu NUMBER? Tak jak we wszystkich innych tabelach w systemie? Prawda?Prawda???PRAWDA??????
CREATE TABLE HR.EMP_PUZZLE (EMPLOYEE_ID VARCHAR2(10 BYTE), FIRST_NAME VARCHAR2(20 BYTE), LAST_NAME VARCHAR2(25 BYTE) NOT NULL ENABLE, EMAIL VARCHAR2(25 BYTE) NOT NULL ENABLE, PHONE_NUMBER VARCHAR2(20 BYTE), HIRE_DATE DATE NOT NULL ENABLE, JOB_ID VARCHAR2(10 BYTE) NOT NULL ENABLE, SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6,0), DEPARTMENT_ID NUMBER(4,0), CONSTRAINT EMPLOYEE_ID_EMP_PUZZLE_PK PRIMARY KEY (EMPLOYEE_ID) ) ;
Mimo, że w całym schemacie kolumna EMPLOYEE_ID była typu NUMBER, w tej jednej tabeli ktoś, zapewne nieumyślnie, ustawił typ kolumny na VARCHAR2. Procedury PL/SQL korzystały z kolumny tak, jakby w kolumnie były wartości typu NUMBER. Spowodowało to niejawną konwersję i ogromne kłopoty wydajnościowe! Dodatkowo, bardzo trudno było znaleźć ten błąd. Wszyscy byli przekonani, że kolumna jest typu NUMBER (gdyż w całych schemacie tak właśnie było), że nawet tego nie weryfikowano. EXPLAIN PLAN pokazywał zaś, że zapytanie korzysta z indexu (TEST 1). Doszukiwano się więc czegoś bardziej skomplikowanego....
TEST 3 jednak naprowadził mnie na trop. Okazało się, że EXPLAIN PLAN, jeśli zamiast literału ma podaną zmienną, tworzy plan zapytania zakładając, że zmienna jest "prawidłowego" typu czyli w tym przypadku VARCHAR2 (TEST 1). Dopiero po podstawieniu prawdziwych wartości do zapytania (107, '107') TEST 2 i TEST 3 - udało się odkryć prawdziwą przyczynę błędu.
Problem został zdiagnozowany (nie chwaląc się....) i naprawiony. Czas przetwarzania, szacowany na ponad 50 godzin - został drastycznie zmniejszony i przetworzenie zostało zakończone w przeciągu około 3 godzin od naprawy błędu.
Czasem duże problemy wydajnościowe nie są powodowane skomplikowanymi problemami z bazą danych. Czasem duże problemy wydajnościowe powodowane są przez proste, wręcz banalne błędy: zły typ kolumny, niejawna konwersja i wiele wiele innych.....
Skrypty:EMP_PUZZLE_DDL.SQL
Komentarze
Prześlij komentarz